Maniphest T202211

pagelinks and imagelinks table schema update scripts for sqlite have columns in wrong order causing update.php to fail
Closed, ResolvedPublic
Actions

Description

In trying to update a 1.28 mediawiki install (SQLite based) to 1.31, found that update.php script was failing with a SQL UNIQUE violation when trying to copy pagelinks to pagelinks_tmp. The copy is done with a simple insert command which retrieves data a simple select * to copy the data, neither specifies columns, so the columns are expected to be in the same order. Looking at the schemas of the two tables after the failed attempt revealed that the columns were in different orders. I changed the order in pagelinks to match and update.php and that patch completed, but failed on the next table getting a similar update. Updated order of columns in the associated patch file and reran update.php which completed successfully.

Files in error:
maintenance/sqlite/archives/patch-pagelinks-fix-pk.sql
maintenance/sqlite/archives/patch-imagelinks-fix-pk.sql

I noticed on other threads that many table's schemas where getting updates. It might be a good idea to check on similar patches to verify that their order is correct. I expect that some table data sets could end up NOT triggering the issue on copy (ie: the data doesn't violate the UNIQUE constraints).

Details

Subject	Repo	Branch	Lines +/-
Fix SQLite patch-(page\|template)links-fix-pk.sql column order	mediawiki/core	REL1_31	+5 -4
Fix SQLite patch-(page\|template)links-fix-pk.sql column order	mediawiki/core	REL1_33	+5 -4
Fix SQLite patch-(page\|template)links-fix-pk.sql column order	mediawiki/core	REL1_32	+5 -4
Fix SQLite patch-(page\|template)links-fix-pk.sql column order	mediawiki/core	master	+4 -4

Customize query in gerrit

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Resolved		Reedy	T227046 1.31.3 and 1.32.3 maintenance releases
		Resolved		Reedy	T202211 pagelinks and imagelinks table schema update scripts for sqlite have columns in wrong order causing update.php to fail

Event Timeline

Sbbeachvball created this task.Aug 19 2018, 4:03 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 19 2018, 4:03 AM

Sbbeachvball updated the task description. (Show Details)Aug 19 2018, 4:04 AM

Sbbeachvball updated the task description. (Show Details)Aug 19 2018, 4:36 AM

Hi @Sbbeachvball, thanks for taking the time to report this and welcome to Phabricator! Unfortunately this report lacks some information. If you have time and can still reproduce the problem: Please add a more complete description to this report by providing a clear list of specific steps to reproduce the situation, as little details sometimes matter, so that nobody needs to guess how you performed each step, and by describing actual results and by describing expected results after performing the steps to reproduce, plus information about your PHP and sqlite version.

You can edit the task description by clicking Edit Task. Thanks!

Peachey88 added a project: SQLite.Aug 19 2018, 7:37 AM

In T202211#4512449, @Zoranzoki21 wrote:

Unfortunately this report lacks some information. If you have time and can still reproduce the problem

What information exactly are you missing that would help with addressing this report? It seems pretty well reported to me.

In T202211#4512559, @Legoktm wrote:

In T202211#4512449, @Zoranzoki21 wrote:

Unfortunately this report lacks some information. If you have time and can still reproduce the problem

What information exactly are you missing that would help with addressing this report? It seems pretty well reported to me.

Which version of sqlite he use on example.

sqlite version installed on system is:
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

I think that the error would be hard to duplicate since it requires actual table data that would trigger a constraint conflict. But I think that just examining the schema structures and the patch code makes the issue pretty obvious.

From the file: maintenance/sqlite/archives/initial-indexes.sql

CREATE TABLE /*_*/pagelinks_tmp (
  pl_from int unsigned NOT NULL default 0,
  pl_namespace int NOT NULL default 0,
  pl_title varchar(255) binary NOT NULL default ''
);

This is the table schema from the production wiki (at mw version 1.28) that encountered the issue when attempting upgrade to 1.31:

 echo ".schema pagelinks" | sqlite3 fec_wikidb.sqlite-1.28-OK
CREATE TABLE pagelinks (
 pl_from INTEGER  NOT NULL default 0,
 pl_namespace INTEGER NOT NULL default 0,
 pl_title TEXT  NOT NULL default ''
 , pl_from_namespace INTEGER NOT NULL default 0);
CREATE UNIQUE INDEX pl_from ON pagelinks (pl_from,pl_namespace,pl_title);
CREATE UNIQUE INDEX pl_namespace ON pagelinks (pl_namespace,pl_title,pl_from);
CREATE INDEX pl_backlinks_namespace ON pagelinks (pl_namespace,pl_title,pl_from_namespace,pl_from);

From the patch file: maintenance/sqlite/archives/patch-pagelinks-fix-pk.sql

CREATE TABLE /*_*/pagelinks_tmp (
  -- Key to the page_id of the page containing the link.
  pl_from int unsigned NOT NULL default 0,
  -- Namespace for this page
  pl_from_namespace int NOT NULL default 0,

  -- Key to page_namespace/page_title of the target page.
  -- The target page may or may not exist, and due to renames
  -- and deletions may refer to different page records as time
  -- goes by.
  pl_namespace int NOT NULL default 0,
  pl_title varchar(255) binary NOT NULL default '',
  PRIMARY KEY (pl_from,pl_namespace,pl_title)
) /*$wgDBTableOptions*/;

INSERT INTO /*_*/pagelinks_tmp
        SELECT * FROM /*_*/pagelinks;

DROP TABLE /*_*/pagelinks;

ALTER TABLE /*_*/pagelinks_tmp RENAME TO /*_*/pagelinks;

-- Reverse index, for Special:Whatlinkshere
CREATE INDEX /*i*/pl_namespace ON /*_*/pagelinks (pl_namespace,pl_title,pl_from);

-- Index for Special:Whatlinkshere with namespace filter
CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks (pl_from_namespace,pl_namespace,pl_title,pl_from);

Note: I am not seeing where the pl_from_namespace column was originally added. But I don't fully understand the mw structure, so may be missing something obvious here.

Note that the initial-indexes version matches the live db schema with the exception of the missing column.

Notice that the order of the columns is not the same between the actual live schema and the pagelinks_tmp schema outlined in the patch. Also note that the insert/select command does NOT itemize the columns, expecting the columns to align. Because they don't align, the insert/select MAY fail depending on the data. Mine failed for only 6 rows out of 873 (ie: manually running a modified insert or replace/select copy, I ended up with 867 rows instead of 873, indicating only 6 rows of data caused constraint conflicts).

The schema change is related to a series of schema updates to replace table constraints with primary key constraints. Because the errors encountered is dependent on table data, I am concerned that other tables could have incorrect schema patches that would go undetected because the production data just happened to NOT create a constraint conflict). I am guessing that would cause some unexpected, inconsistent, hard to track issues.

Krinkle moved this task from Untriaged to Schema changes on the MediaWiki-libs-Rdbms board.Sep 17 2018, 4:01 PM

Krinkle added a project: MediaWiki-Installer.

I have not exhaustively checked all the patch-fix-pk.sql files, but pretty sure that

maintenance/sqlite/archives/patch-templatelinks-fix-pk.sql

has the same issues by performing a simple grep

grep from_namespace maintenance/sqlite/archives/patch-*-fix-pk.sql

So i guess everyone who updated who didnt get the constraint error now have messed up link tables. That's pretty unfortunate, although i guess at least its slowly self-correcting.

Any fix for this one? I too getting same error when upgrade mw from 1.29 to 1.32

[4e58a7bf345c9c1b9036b569] [no req] Wikimedia\Rdbms\DBQueryError from line 1506 of D:\home\site\wwwroot\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 pagelinks_tmp ( pl_from INTEGER NOT NULL default 0, pl_from_namespace INTEGER NOT NULL default 0, pl_namespace INTEGER NOT NULL default 0, pl_title TEXT NOT NULL default '', PRIMARY KEY (pl_from,pl_namespace,pl_title) ) Function: Wikimedia\Rdbms\Database::sourceFile( D:\home\site\wwwroot/maintenance/sqlite/archives/patch-pagelinks-fix-pk.sql ) Error: 1 table pagelinks_tmp already exists Backtrace: #0 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(1476): Wikimedia\Rdbms\Database->makeQueryException(string, integer, string, string) #1 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(1236): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean) #2 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(4363): Wikimedia\Rdbms\Database->query(string, string) #3 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(4298): Wikimedia\Rdbms\Database->sourceStream(unknown type, NULL, NULL, string, NULL) #4 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(686): Wikimedia\Rdbms\Database->sourceFile(string) #5 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(915): DatabaseUpdater->applyPatch(string, boolean, string) #6 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(485): DatabaseUpdater->renameIndex(string, string, string, boolean, string) #7 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(449): DatabaseUpdater->runUpdates(array, boolean) #8 D:\home\site\wwwroot\maintenance\update.php(203): DatabaseUpdater->doUpdates(array) #9 D:\home\site\wwwroot\maintenance\doMaintenance.php(94): UpdateMediaWiki->execute() #10 D:\home\site\wwwroot\maintenance\update.php(248): require_once(string) #11 {main}

In T202211#5294541, @Ravikzm wrote:

Any fix for this one? I too getting same error when upgrade mw from 1.29 to 1.32

[4e58a7bf345c9c1b9036b569] [no req] Wikimedia\Rdbms\DBQueryError from line 1506 of D:\home\site\wwwroot\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 pagelinks_tmp ( pl_from INTEGER NOT NULL default 0, pl_from_namespace INTEGER NOT NULL default 0, pl_namespace INTEGER NOT NULL default 0, pl_title TEXT NOT NULL default '', PRIMARY KEY (pl_from,pl_namespace,pl_title) ) Function: Wikimedia\Rdbms\Database::sourceFile( D:\home\site\wwwroot/maintenance/sqlite/archives/patch-pagelinks-fix-pk.sql ) Error: 1 table pagelinks_tmp already exists Backtrace: #0 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(1476): Wikimedia\Rdbms\Database->makeQueryException(string, integer, string, string) #1 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(1236): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean) #2 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(4363): Wikimedia\Rdbms\Database->query(string, string) #3 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(4298): Wikimedia\Rdbms\Database->sourceStream(unknown type, NULL, NULL, string, NULL) #4 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(686): Wikimedia\Rdbms\Database->sourceFile(string) #5 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(915): DatabaseUpdater->applyPatch(string, boolean, string) #6 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(485): DatabaseUpdater->renameIndex(string, string, string, boolean, string) #7 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(449): DatabaseUpdater->runUpdates(array, boolean) #8 D:\home\site\wwwroot\maintenance\update.php(203): DatabaseUpdater->doUpdates(array) #9 D:\home\site\wwwroot\maintenance\doMaintenance.php(94): UpdateMediaWiki->execute() #10 D:\home\site\wwwroot\maintenance\update.php(248): require_once(string) #11 {main}

That isn't the same error

Error: 1 table pagelinks_tmp already exists

In this case, it sounds like a migration didn't cleanup after itself and/or failed

sorry pasted wrong error. here is the error I got when running DB upgrade script

[f194f459abc775ee4e037f5f] [no req] Wikimedia\Rdbms\DBQueryError from line 1506 of D:\home\site\wwwroot\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: INSERT INTO pagelinks_tmp SELECT * FROM pagelinks Function: Wikimedia\Rdbms\Database::sourceFile( D:\home\site\wwwroot/maintenance/sqlite/archives/patch-pagelinks-fix-pk.sql ) Error: 19 UNIQUE constraint failed: pagelinks_tmp.pl_from, pagelinks_tmp.pl_namespace, pagelinks_tmp.pl_title Backtrace: #0 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(1476): Wikimedia\Rdbms\Database->makeQueryException(string, integer, string, string) #1 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(1236): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean) #2 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(4363): Wikimedia\Rdbms\Database->query(string, string) #3 D:\home\site\wwwroot\includes\libs\rdbms\database\Database.php(4298): Wikimedia\Rdbms\Database->sourceStream(unknown type, NULL, NULL, string, NULL) #4 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(686): Wikimedia\Rdbms\Database->sourceFile(string) #5 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(915): DatabaseUpdater->applyPatch(string, boolean, string) #6 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(485): DatabaseUpdater->renameIndex(string, string, string, boolean, string) #7 D:\home\site\wwwroot\includes\installer\DatabaseUpdater.php(449): DatabaseUpdater->runUpdates(array, boolean) #8 D:\home\site\wwwroot\maintenance\update.php(203): DatabaseUpdater->doUpdates(array) #9 D:\home\site\wwwroot\maintenance\doMaintenance.php(94): UpdateMediaWiki->execute() #10 D:\home\site\wwwroot\maintenance\update.php(248): require_once(string) #11 {main}

In T202211#4517506, @Sbbeachvball wrote:

Note: I am not seeing where the pl_from_namespace column was originally added. But I don't fully understand the mw structure, so may be missing something obvious here.

MW 1.24

			[ 'addField', 'pagelinks', 'pl_from_namespace', 'patch-pl_from_namespace.sql' ],
			[ 'addField', 'templatelinks', 'tl_from_namespace', 'patch-tl_from_namespace.sql' ],
			[ 'addField', 'imagelinks', 'il_from_namespace', 'patch-il_from_namespace.sql' ],

ALTER TABLE /*_*/pagelinks
	ADD COLUMN pl_from_namespace int NOT NULL default 0;

CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks (pl_from_namespace,pl_namespace,pl_title,pl_from);

So, the problem is caused by this patch, and its sister table patches, where the column is just added onto the end. In MySQL we can specify the position, in SQLite we cannot - https://www.sqlite.org/lang_altertable.html

In most sqlite patches where we create a new table and copy data, we indeed make the (incorrect) assumption that things are in the same order...

Reedy added projects: MW-1.31-release, MW-1.32-release, MW-1.33-release.Jun 30 2019, 4:40 PM

Imagelinks was fixed in https://github.com/wikimedia/mediawiki/commit/68c298ed05ef7b5be8099ff272e6dea20d00e42b (but not backported)

Change 519789 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@master] Fix SQLite patch-(page|template)links-fix-pk.sql column order

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

gerritbot added a project: Patch-For-Review.Jun 30 2019, 4:50 PM

Change 519790 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@REL1_33] Fix SQLite patch-(page|template)links-fix-pk.sql column order

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

Change 519791 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@REL1_32] Fix SQLite patch-(page|template)links-fix-pk.sql column order

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

Change 519792 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@REL1_31] Fix SQLite patch-(page|template)links-fix-pk.sql column order

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

Reedy mentioned this in T226766: Avoid explicit commit references in composer.json in code meant for public consumption.Jun 30 2019, 5:37 PM

Imagelinks fix I do not see in the mw v 1.32.2 and manually applied the fix to SQL file for Image, template and page links

In T202211#5294639, @Ravikzm wrote:

Imagelinks fix I do not see in the mw v 1.32.2 and manually applied the fix to SQL file for Image, template and page links

Yes, because the patches have only just been backported to the relevant branches. They haven't been merged (except the original imagelinks one which was committed before REL1_33 was branched) and they certainly haven't been released.

They'll be in the 1.31.3 and 1.32.3 point releases

Change 519789 merged by jenkins-bot:
[mediawiki/core@master] Fix SQLite patch-(page|template)links-fix-pk.sql column order

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

Change 519790 merged by jenkins-bot:
[mediawiki/core@REL1_33] Fix SQLite patch-(page|template)links-fix-pk.sql column order

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

Change 519791 merged by jenkins-bot:
[mediawiki/core@REL1_32] Fix SQLite patch-(page|template)links-fix-pk.sql column order

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

Change 519792 merged by jenkins-bot:
[mediawiki/core@REL1_31] Fix SQLite patch-(page|template)links-fix-pk.sql column order

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

ReleaseTaggerBot added projects: MW-1.31-release-notes, MW-1.34-notes (1.34.0-wmf.13; 2019-07-09), MW-1.32-notes, MW-1.33-notes.Jun 30 2019, 11:00 PM

Reedy removed projects: MW-1.33-notes, MW-1.32-notes, MW-1.31-release-notes, Patch-For-Review.Jul 1 2019, 11:31 PM

Reedy closed this task as Resolved.Jul 1 2019, 11:35 PM

Reedy claimed this task.

Reedy mentioned this in T227046: 1.31.3 and 1.32.3 maintenance releases.

Reedy added a parent task: T227046: 1.31.3 and 1.32.3 maintenance releases.

Reedy mentioned this in T252311: Fixup SQLite INSERT INTO table_tmp SELECT * FROM table.May 10 2020, 1:13 AM

pagelinks and imagelinks table schema update scripts for sqlite have columns in wrong order causing update.php to fail Closed, ResolvedPublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

pagelinks and imagelinks table schema update scripts for sqlite have columns in wrong order causing update.php to fail
Closed, ResolvedPublic
Actions

Related Objects
Search...