Skip to:
Content

Opened 18 months ago

Closed 18 months ago

Last modified 18 months ago

#3145 closed defect (fixed)

Database error blocking import

Reported by: jrf Owned by: johnjamesjacoby
Milestone: 2.6 Priority: normal
Severity: normal Version: trunk
Component: API - Importers Keywords:
Cc: johnjamesjacoby

Description

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

Change History (5)

#1 @jrf
18 months ago

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.

Last edited 18 months ago by jrf (previous) (diff)

#2 @johnjamesjacoby
18 months ago

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.

#3 @johnjamesjacoby
18 months ago

  • Owner set to johnjamesjacoby
  • Resolution set to fixed
  • Status changed from new to closed

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.

#4 @netweb
18 months ago

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

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

#5 @johnjamesjacoby
18 months ago

  • Milestone changed from Awaiting Review to 2.6
Note: See TracTickets for help on using tickets.