Database error blocking import

[jrf]

I'm attempting an import from a phpBB forum using bbPress trunk and the feedback windows stays suspiciously empty.

In my PHP error log, however, I find the following eror:

WordPress database error Specified key was too long; max key length is 1000 bytes for query CREATE TABLE wp_sroay1346_2_bbp_converter_translator (

		meta_id mediumint(8) unsigned not null auto_increment,

		value_type varchar(25) null,

		value_id bigint(20) unsigned not null default '0',

		meta_key varchar(191) null,

		meta_value varchar(191) null,

	PRIMARY KEY (meta_id),

		KEY value_id (value_id),

		KEY meta_join (meta_key(191), meta_value(191))

) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci made by do_action('wp_ajax_bbp_converter_process'), WP_Hook->do_action, WP_Hook->apply_filters, BBP_Converter->process_callback, BBP_Converter->do_steps, BBP_Converter->step_sync_table, BBP_Converter::sync_table, dbDelta

PHP: 7.0.19
MySql: 5.0.88
WP: 4.8.1
bbPress: trunk / 2.6-rc-4 / revision 6649

[jrf]

I've been looking at the query this applies to and can only come to the conclusion that the error is correct.

As the charset is utf8 and text string chars can therefore be between 1 and 4 characters:
meta_id + value_id + meta_join ( meta_key, meta_value)
translates to:
8 (int) + 20 (int) + 3 * 191 (varchar) + 3 * 191 (varchar) = 1174 characters which is more than the max key length of 1000 for MyISAM tables.

[johnjamesjacoby]

It will take some digging to find what the sweet spot is for those key lengths. UTF8MB4, for example, requires that the 191 length match between the field and the index, otherwise the index is unused.

I know for sure we don’t have anything that’s actually 191 characters long. While there’s probably a strategy we can employ to narrow it down, I’d start by changing all of the 191’s to 75, and see what happens.

I bet we could safely go as low as 50, and take the performance hit on anything over that. Varchar columns aren’t known for behaving much better when indexed anyways, so it probably makes sense to try and keep the size of the index somewhat (more) under control.

[johnjamesjacoby]

In 6666:

Converter: reduce meta_key and meta_value length to 75.

This change lowers the maximum allowed string length from 191 (which was used based on what's in WordPress core.) After further review, 75 is a reasonable maximum to try for now, though it could likely be tuned further if needed.

Trunk, for 2.6. Fixes #3145.

[netweb]

Previously: #3026 Converter table does not limit max index lengths of key [6129]

• $max_index_length = 191; was added in [6129]