Page MenuHomePhabricator

Wikimedia\Rdbms\DBQueryError when upgrading from MediaWiki 1.31.2 to 1.31.6
Open, Needs TriagePublicBUG REPORT

Description

While running php maintenance/update.php, I get the following error:

Creating image_comment_temp table ...[4ecb262880f9524e5c0d24a1] [no req]   Wikimedia\Rdbms\DBQueryError from line 1457 of /.../includes/libs/rdbms/database/Database.php: A database query error has occurred. Did you forget to run your application's database schema updater after upgrading?
Query: CREATE TABLE `image_comment_temp` (
 imgcomment_name varchar(255) binary NOT NULL,
 imgcomment_description_id bigint unsigned NOT NULL,
 PRIMARY KEY (imgcomment_name, imgcomment_description_id)
 ) ENGINE=InnoDB

Function: Wikimedia\Rdbms\Database::sourceFile( /.../maintenance/archives/patch-image_comment_temp-table.sql )
Error: 1071 Specified key was too long; max key length is 767 bytes (localhost)

Backtrace:
#0 /.../includes/libs/rdbms/database/Database.php(1427): Wikimedia\Rdbms\Database->makeQueryException(string, integer, string, string)
#1 /.../includes/libs/rdbms/database/Database.php(1200): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#2 /.../includes/libs/rdbms/database/Database.php(4194): Wikimedia\Rdbms\Database->query(string, string)
#3 /.../includes/libs/rdbms/database/Database.php(4129): Wikimedia\Rdbms\Database->sourceStream(unknown type, NULL, NULL, string, NULL)
#4 /.../includes/installer/DatabaseUpdater.php(683): Wikimedia\Rdbms\Database->sourceFile(string)
#5 /.../includes/installer/DatabaseUpdater.php(726): DatabaseUpdater->applyPatch(string, boolean, string)
#6 /.../includes/installer/DatabaseUpdater.php(482): DatabaseUpdater->addTable(string, string)
#7 /.../includes/installer/DatabaseUpdater.php(446): DatabaseUpdater->runUpdates(array, boolean)
#8 /.../maintenance/update.php(203): DatabaseUpdater->doUpdates(array)
#9 /.../maintenance/doMaintenance.php(94): UpdateMediaWiki->execute()
#10 /.../maintenance/update.php(248): require_once(string)
#11 {main}

Other tickets suggest that this is the result of using utf8mb4, which is resulting in, well, primary keys longer than 767 bytes.

The only related code changes that I could find was T227662 (f1f96285b0aa), but I don't know if that's related.

ProductVersion
MediaWiki1.31.6 (c168a3f) 08:28, December 19, 2019
PHP7.0.33-0+deb9u6 (apache2handler)
MariaDB10.1.41-MariaDB-0+deb9u1
ICU57.1

Details

Related Gerrit Patches:

Event Timeline

GPHemsley created this task.Jan 1 2020, 7:11 PM
Restricted Application added a project: Core Platform Team. · View Herald TranscriptJan 1 2020, 7:11 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Reedy added a subscriber: Reedy.Jan 1 2020, 7:20 PM

FWIW, you shouldn't need to run update.php on that point release upgrade

The only related code changes that I could find was T227662 (f1f96285b0aa), but I don't know if that's related.

Probably related, but that was splitting a file down, the schema change should've already been run on your wiki, and the table dropped again

I do note that it isn't commented out for MySQL and SQLite like it was for postgres....

Change 561360 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@REL1_31] Comment out addTable entry for image_comment_temp

https://gerrit.wikimedia.org/r/561360

Reedy added a subscriber: Anomie.Jan 1 2020, 7:40 PM

@Anomie https://github.com/wikimedia/mediawiki/blob/master/includes/installer/MysqlUpdater.php#L329-L332

Is 1.31+ supposed to be > 1.31 or >= 1.31? T188132: Merge image_comment_temp table into the image table didn't land till 1.32. So re-reading it I'm seeing 1.31+ as ambiguous

I don't think there's actually any need to ever add the table during upgrade, as migrateComments (line 337) won't use it either (see lines 334–335). So you might just comment out this line for 1.31+.

Should Ic8efeddc030f48e82ba861926121b64eca37d169 / 8107e4763759a5f344e7d86d000e6fc51fe95db8 / d099bb6f9578625bc79f613f65e78ca12b4bbb96 have been backported to REL1_31 too?

Anomie added a comment.Jan 2 2020, 3:25 PM

Other tickets suggest that this is the result of using utf8mb4, which is resulting in, well, primary keys longer than 767 bytes.

If you're using utf8mb4 you really should adjust your configuration to enable innodb_large_prefix (which may require adjusting some other settings as well). That seems to have become the default in MySQL 5.7 and MariaDB 10.2.2.

@Anomie https://github.com/wikimedia/mediawiki/blob/master/includes/installer/MysqlUpdater.php#L329-L332
Is 1.31+ supposed to be > 1.31 or >= 1.31? T188132: Merge image_comment_temp table into the image table didn't land till 1.32. So re-reading it I'm seeing 1.31+ as ambiguous

I don't think it's ambiguous, just wrong (it should say 1.32, not 1.31). Also I don't see a "1.31+" in there anyway, the linked line says "MW 1.31 or newer".

I don't think there's actually any need to ever add the table during upgrade, as migrateComments (line 337) won't use it either (see lines 334–335). So you might just comment out this line for 1.31+.

Should Ic8efeddc030f48e82ba861926121b64eca37d169 / 8107e4763759a5f344e7d86d000e6fc51fe95db8 / d099bb6f9578625bc79f613f65e78ca12b4bbb96 have been backported to REL1_31 too?

You'd also need to backport 7a422138a, I think.

Anomie added a comment.Jan 2 2020, 3:35 PM

My best guess as to what's going on here is that they ran into the same error when upgrading to 1.30 or 1.31 at some point in the past, but due to the issue behind T227662 it seemed to "work" when they re-ran update.php. Since 1.31 still has $wgCommentTableSchemaMigrationStage set to MIGRATION_OLD, they never noticed the missing table and columns.

Now that 1.31.6 includes the fix for T227662, update.php no longer incorrectly skips the rest of the schema changes.

Other tickets suggest that this is the result of using utf8mb4, which is resulting in, well, primary keys longer than 767 bytes.

If you're using utf8mb4 you really should adjust your configuration to enable innodb_large_prefix (which may require adjusting some other settings as well). That seems to have become the default in MySQL 5.7 and MariaDB 10.2.2.

I don't believe we're doing anything custom here; just using the versions of MediaWiki and MariaDB as specified. Given your later comment, is this still relevant advice?

My best guess as to what's going on here is that they ran into the same error when upgrading to 1.30 or 1.31 at some point in the past, but due to the issue behind T227662 it seemed to "work" when they re-ran update.php. Since 1.31 still has $wgCommentTableSchemaMigrationStage set to MIGRATION_OLD, they never noticed the missing table and columns.

This does sound familiar (though we would have updated straight from 1.27).

Reedy added a comment.Jan 4 2020, 7:23 PM

Other tickets suggest that this is the result of using utf8mb4, which is resulting in, well, primary keys longer than 767 bytes.

If you're using utf8mb4 you really should adjust your configuration to enable innodb_large_prefix (which may require adjusting some other settings as well). That seems to have become the default in MySQL 5.7 and MariaDB 10.2.2.

I don't believe we're doing anything custom here; just using the versions of MediaWiki and MariaDB as specified. Given your later comment, is this still relevant advice?

Could be. What version of MySQL/MariaDB are you using? Can you easily upgrade rather than changing config?

helmo added a subscriber: helmo.Tue, Jan 28, 10:39 AM