1.39 upgrade: Modifying tl_namespace field of table templatelinks: Error 1091: Can't DROP INDEX `PRIMARY`
Open, Needs TriagePublic
Actions

Assigned To

None

Authored By

	AbdulazizAlwafaee
	Dec 7 2022, 7:54 AM

Description

After upgrading from 1.38 to 1.39

php maintenance/update.php gives error,

Modifying tl_namespace field of table templatelinks...Wikimedia\Rdbms\DBQueryError from line 1618 of /var/www/html/includes/libs/rdbms/database/Database.php: Error 1091: Can't DROP INDEX `PRIMARY`; check that it exists

Function: Wikimedia\Rdbms\Database::sourceFile( /var/www/html/maintenance/archives/patch-templatelinks-tl_title-nullable.sql )

Query: ALTER TABLE  `templatelinks`

DROP  PRIMARY KEY

mysql> describe templatelinks;

+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| tl_from           | int(10) unsigned    | NO   |     | 0       |       |
| tl_namespace      | int(11)             | NO   | MUL | 0       |       |
| tl_title          | varbinary(255)      | NO   |     |         |       |
| tl_from_namespace | int(11)             | NO   | MUL | 0       |       |
| tl_target_id      | bigint(20) unsigned | NO   | MUL | NULL    |       |
+-------------------+---------------------+------+-----+---------+-------+

SHOW INDEXES FROM templatelinks;
+---------------+------------+----------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table         | Non_unique | Key_name                         | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------------+------------+----------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| templatelinks |          1 | tl_namespace                     |            1 | tl_namespace      | A         |        3300 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_namespace                     |            2 | tl_title          | A         |     2479612 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_namespace                     |            3 | tl_from           | A         |    47112645 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_backlinks_namespace           |            1 | tl_from_namespace | A         |        3686 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_backlinks_namespace           |            2 | tl_namespace      | A         |       14745 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_backlinks_namespace           |            3 | tl_title          | A         |     1346075 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_backlinks_namespace           |            4 | tl_from           | A         |    47112645 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_target_id                     |            1 | tl_target_id      | A         |     1624573 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_target_id                     |            2 | tl_from           | A         |    47112645 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_backlinks_namespace_target_id |            1 | tl_from_namespace | A         |        5130 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_backlinks_namespace_target_id |            2 | tl_target_id      | A         |      298181 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| templatelinks |          1 | tl_backlinks_namespace_target_id |            3 | tl_from           | A         |    47112645 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------------+------------+----------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Related Objects

Mentioned In: T413551: Error with categorylinks table on update from 1.44.2 to 1.45.1
T332333: Update from 1.37.2 to 1.39.2 fails on MariaDB with "Error 1062: Duplicate entry for key 'PRIMARY'" on templatelinks
Mentioned Here: T332333: Update from 1.37.2 to 1.39.2 fails on MariaDB with "Error 1062: Duplicate entry for key 'PRIMARY'" on templatelinks
T172514: Add new PKs to tables.sql (mysql and sqlite)

Event Timeline

AbdulazizAlwafaee created this task.Dec 7 2022, 7:54 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 7 2022, 7:54 AM

Aklapper updated the task description. (Show Details)Dec 7 2022, 8:06 AM

Aklapper renamed this task from mediawiki 1.38 upgrade to 1.39 to 1.39 upgrade: Modifying tl_namespace field of table templatelinks: Error 1091: Can't DROP INDEX `PRIMARY`.Dec 7 2022, 8:18 AM

Aklapper added a project: MediaWiki-Installer.

The Complete Error log:

Modifying tl_namespace field of table templatelinks...Wikimedia\Rdbms\DBQueryError from line 1618 of /var/www/html/includes/libs/rdbms/database/Database.php: Error 1091: Can't DROP INDEX PRIMARY; check that it exists
Function: Wikimedia\Rdbms\Database::sourceFile( /var/www/html/maintenance/archives/patch-templatelinks-tl_title-nullable.sql )
Query: ALTER TABLE templatelinks
DROP PRIMARY KEY

#0 /var/www/html/includes/libs/rdbms/database/Database.php(1602): Wikimedia\Rdbms\Database->getQueryException()
#1 /var/www/html/includes/libs/rdbms/database/Database.php(1576): Wikimedia\Rdbms\Database->getQueryExceptionAndLog()
#2 /var/www/html/includes/libs/rdbms/database/Database.php(952): Wikimedia\Rdbms\Database->reportQueryError()
#3 /var/www/html/includes/libs/rdbms/database/Database.php(3299): Wikimedia\Rdbms\Database->query()
#4 /var/www/html/includes/libs/rdbms/database/Database.php(3238): Wikimedia\Rdbms\Database->sourceStream()
#5 /var/www/html/includes/libs/rdbms/database/DBConnRef.php(103): Wikimedia\Rdbms\Database->sourceFile()
#6 /var/www/html/includes/libs/rdbms/database/DBConnRef.php(806): Wikimedia\Rdbms\DBConnRef->__call()
#7 /var/www/html/includes/installer/DatabaseUpdater.php(718): Wikimedia\Rdbms\DBConnRef->sourceFile()
#8 /var/www/html/includes/installer/DatabaseUpdater.php(1000): DatabaseUpdater->applyPatch()
#9 /var/www/html/includes/installer/DatabaseUpdater.php(547): DatabaseUpdater->modifyField()
#10 /var/www/html/includes/installer/DatabaseUpdater.php(511): DatabaseUpdater->runUpdates()
#11 /var/www/html/maintenance/update.php(202): DatabaseUpdater->doUpdates()
#12 /var/www/html/maintenance/includes/MaintenanceRunner.php(309): UpdateMediaWiki->execute()
#13 /var/www/html/maintenance/doMaintenance.php(85): MediaWiki\Maintenance\MaintenanceRunner->run()
#14 /var/www/html/maintenance/update.php(312): require_once('/var/www/html/...')
#15 {main}

Reedy added a project: MediaWiki-Installer.Dec 7 2022, 9:50 AM

Reedy updated the task description. (Show Details)

The show indexes above shows that there is no unique index on that table, which is a bad situation and not wanted.
With T172514 the unique index was changed to primary key, but I have no idea how that end up without any unique key (that was back in 1.30).

I have no good idea how to fix this from the updater side. As this state is unexpected (from my point of view) and possible the cause of some previous upgrade error, not sure if the updater should be smart enough to handle the case.

Be sure to have a valid backup from before the upgrade.

I would try to get the old primary key added before restarting update.php. But it could be possible that there are some duplicate rows and the primary key does not apply, that needs to be fixed by deleting some rows from the table.

ALTER TABLE /*_*/templatelinks ADD PRIMARY KEY (tl_from,tl_namespace,tl_title);

Another try could be to remove the failing ALTER from patch-templatelinks-tl_title-nullable.sql and retry, not sure if the new primary key could be applied or also have duplicates to remove.

The templatelinks table can be rebuild with the refreshLinks.php, but for big wikis that could run very long, so not sure if that is a good idea. So in the worse case it could be truncated before the old primary key is added.

Maybe someone else can give some ideas.

Rasputin1493 subscribed.Dec 8 2022, 11:01 PM

I started that wiki with version 1.38 as a fresh installation, then upgraded to 1.39, Then the update script started some very long time tasks on some tables (i don't remember their names) after the upgrade.
The wiki remains online and everything is working for now, except for the same reported error that appears when running the update.php.

The old backup is gone, no more available with me.

I've tried restoring a small dummy backup on a test environment with a fresh 1.39 installation, and things seem to be okay.

so, I ended up dropping the (templatelinks) table,
creating the table again,

CREATE TABLE /*_*/templatelinks (
  tl_from INT UNSIGNED DEFAULT 0 NOT NULL,
  tl_target_id BIGINT UNSIGNED NOT NULL,
  tl_from_namespace INT DEFAULT 0 NOT NULL,
  INDEX tl_target_id (tl_target_id, tl_from),
  INDEX tl_backlinks_namespace_target_id (
    tl_from_namespace, tl_target_id,
    tl_from
  ),
  PRIMARY KEY(tl_from, tl_target_id)
) /*$wgDBTableOptions*/;

and running the update script. :)

Captain007 subscribed.Jan 16 2023, 8:04 PM

This comment was removed by Captain007.

Umherirrender unsubscribed.Feb 10 2023, 7:22 PM

Prod mentioned this in T332333: Update from 1.37.2 to 1.39.2 fails on MariaDB with "Error 1062: Duplicate entry for key 'PRIMARY'" on templatelinks.Mar 16 2023, 5:10 PM

In T324646#8455341, @Umherirrender wrote:

I have no good idea how to fix this from the updater side. As this state is unexpected (from my point of view) and possible the cause of some previous upgrade error, not sure if the updater should be smart enough to handle the case.

I experienced also this inexistant PK, this is a scenario:

launch update.php from 1.38 to 1.39 (for instance)
it fails with T332333
you re-launches update.php, with the hope it could recover
now you have this very bug T324646 because the SQL patch is not atomic: first the (existing) PK is dropped, then the new PK cannot be created because of T332333

So this task T324646 is a consequence of:

T332333
more generally the fact that the SQL patches generated by generateSchemaChangeSql.php + executed by the updater are not atomic (=they are partly executed)

(about the non-atomicity I have to analyse + report another bug, unrelated to templatelinks but linked to the non-atomicity)

Umherirrender unsubscribed.Oct 3 2023, 1:31 PM

Zabe mentioned this in T413551: Error with categorylinks table on update from 1.44.2 to 1.45.1.Dec 28 2025, 10:42 PM

1.39 upgrade: Modifying tl_namespace field of table templatelinks: Error 1091: Can't DROP INDEX `PRIMARY`Open, Needs TriagePublicActions

Description

Related Objects

Event Timeline

1.39 upgrade: Modifying tl_namespace field of table templatelinks: Error 1091: Can't DROP INDEX `PRIMARY`
Open, Needs TriagePublic
Actions