Wikimedia\Rdbms\DBQueryError when upgrading from MediaWiki 1.31.2 to 1.31.6
Closed, DeclinedPublicBUG REPORT
Actions

Assigned To

None

Authored By

	GPHemsley
	Jan 1 2020, 7:11 PM

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.

Product	Version
MediaWiki	1.31.6 (c168a3f) 08:28, December 19, 2019
PHP	7.0.33-0+deb9u6 (apache2handler)
MariaDB	10.1.41-MariaDB-0+deb9u1
ICU	57.1

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	Comment out addTable entry for image_comment_temp	mediawiki/core	REL1_31	+8 -2

Customize query in gerrit

Related Objects

Mentioned Here: rMW7a422138a791: Migrate image descriptions from image_comment_temp
T188132: Merge image_comment_temp table into the image table
rMWd099bb6f9578: Drop the image_comment_temp table
rMW8107e4763759: Drop the image_comment_temp table
T227662: Update.php failure when upgrading from 1.32.1 to 1.33
rMWf1f96285b0aa: Split down patch-comment-table.sql
rMWc168a3f637a6: Start 1.31.7

Event Timeline

GPHemsley created this task.Jan 1 2020, 7:11 PM

Restricted Application added a project: Platform Engineering. · View Herald TranscriptJan 1 2020, 7:11 PM

Restricted Application added a subscriber: Aklapper. · View Herald Transcript

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

gerritbot added a project: Patch-For-Review.Jan 1 2020, 7:25 PM

Reedy added a project: MW-1.31-release.Jan 1 2020, 7:31 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?

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.

In T241686#5769455, @Reedy wrote:

@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 edited projects, added Platform Team Workboards (Clinic Duty Team); removed Platform Engineering.Jan 2 2020, 3:26 PM

Anomie moved this task from Inbox to Discussing on the Platform Team Workboards (Clinic Duty Team) board.

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.

In T241686#5771133, @Anomie wrote:

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?

In T241686#5771145, @Anomie wrote:

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).

In T241686#5775543, @GPHemsley wrote:

In T241686#5771133, @Anomie wrote:

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 subscribed.Jan 28 2020, 10:39 AM

Krinkle edited projects, added MediaWiki-Installer, MW-1.35-release; removed MediaWiki-libs-Rdbms.Jan 29 2020, 4:39 AM

Change 561360 abandoned by Reedy:
Comment out addTable entry for image_comment_temp

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

Marking stalled as no response in months

daniel removed a project: Patch-For-Review.Sep 1 2020, 8:54 PM

Closing, since this seems to be a one-off problem, and we are up to 1.31.8 now, and about to release 1.35. @GPHemsley if this is still a problem for you, please reopen.

Aklapper removed a subscriber: Anomie.Oct 16 2020, 5:28 PM

Sorry for failing to follow up. I was able to get around/through this somehow, possibly with advice given in this ticket, but I don't recall how.

I hit this again attempting to upgrade to MediaWiki 1.31.12.

The migration script query is not specifying the row compression format, and the default value is COMPACT. COMPRESSED was not available, but setting it to DYNAMIC worked.

This is what I did:

SET @@GLOBAL.innodb_default_row_format = DYNAMIC;
SET @@GLOBAL.innodb_file_format = Barracuda;
SET @@GLOBAL.innodb_large_prefix = ON;

Wikimedia\Rdbms\DBQueryError when upgrading from MediaWiki 1.31.2 to 1.31.6Closed, DeclinedPublicBUG REPORTActions

Description

Details

Related Objects

Event Timeline

Wikimedia\Rdbms\DBQueryError when upgrading from MediaWiki 1.31.2 to 1.31.6
Closed, DeclinedPublicBUG REPORT
Actions