Page MenuHomePhabricator

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

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

Event Timeline

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
Zoranzoki21 added a subscriber: Zoranzoki21.

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!

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.

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

Sbbeachvball added a comment.EditedAug 21 2018, 2:06 AM

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.

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}
Reedy added a subscriber: Reedy.Jun 30 2019, 4:33 PM

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}
Reedy added a comment.EditedJun 30 2019, 4:39 PM

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

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

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

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

Reedy added a comment.EditedJun 30 2019, 7:27 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

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

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