Topic on Project:Support desk

Tracked in Phabricator
Task T264683

During upgrade MW 1.31 -> 1.35 the following error occured. How do you recommend to fix it pls?

# php maintenance/update.php --quick --skip-external-dependencies

...

Updating length of lc_lang in `l10n_cache` ...done.
Updating length of ll_lang in `langlinks` ...done.
Updating length of site_language in `sites` ...done.
...skipping update to shared table ipblocks.
Removing ipb_anon_only column from ipb_address_unique index ...Wikimedia\Rdbms\DBQueryError from line 1699 of /var/www/html/includes/libs/rdbms/database/Database.php: Error 1091: Can't DROP 'ipb_address_unique'; check that column/key exists (mysql)

Function: Wikimedia\Rdbms\Database::sourceFile( /var/www/html/maintenance/archives/patch-ipblocks-fix-ipb_address_unique.sql )
Query: ALTER TABLE `zeus_de`.`ipblocks` DROP INDEX ipb_address_unique, ADD UNIQUE INDEX ipb_address_unique (ipb_address(255), ipb_user, ipb_auto )
#0 /var/www/html/includes/libs/rdbms/database/Database.php(1683): Wikimedia\Rdbms\Database->getQueryException('Can't DROP 'ipb...', 1091, 'ALTER TABLE `ze...', 'Wikimedia\\Rdbms...')
#1 /var/www/html/includes/libs/rdbms/database/Database.php(1658): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('Can't DROP 'ipb...', 1091, 'ALTER TABLE `ze...', 'Wikimedia\\Rdbms...')
#2 /var/www/html/includes/libs/rdbms/database/Database.php(1227): Wikimedia\Rdbms\Database->reportQueryError('Can't DROP 'ipb...', 1091, 'ALTER TABLE `ze...', 'Wikimedia\\Rdbms...', false)
#3 /var/www/html/includes/libs/rdbms/database/Database.php(5075): Wikimedia\Rdbms\Database->query('ALTER TABLE `ze...', 'Wikimedia\\Rdbms...')
#4 /var/www/html/includes/libs/rdbms/database/Database.php(5010): Wikimedia\Rdbms\Database->sourceStream(Resource id #2004, NULL, NULL, 'Wikimedia\\Rdbms...', NULL)
#5 /var/www/html/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->sourceFile('/var/www/html/m...')
#6 /var/www/html/includes/libs/rdbms/database/MaintainableDBConnRef.php(35): Wikimedia\Rdbms\DBConnRef->__call('sourceFile', Array)
#7 /var/www/html/includes/installer/DatabaseUpdater.php(727): Wikimedia\Rdbms\MaintainableDBConnRef->sourceFile('/var/www/html/m...')
#8 /var/www/html/includes/installer/MysqlUpdater.php(1222): DatabaseUpdater->applyPatch('/var/www/html/m...', false, 'Removing ipb_an...')
#9 /var/www/html/includes/installer/DatabaseUpdater.php(512): MysqlUpdater->doFixIpbAddressUniqueIndex()
#10 /var/www/html/includes/installer/DatabaseUpdater.php(475): DatabaseUpdater->runUpdates(Array, false)
#11 /var/www/html/maintenance/update.php(181): DatabaseUpdater->doUpdates(Array)
#12 /var/www/html/maintenance/doMaintenance.php(107): UpdateMediaWiki->execute()
#13 /var/www/html/maintenance/update.php(253): require_once('/var/www/html/m...')
#14 {main}

No ipb_address_unique column/key exists in table ipblocks !!!

mysql> describe ipblocks;

+----------------------+---------------------+------+-----+----------------+----------------+
| Field                | Type                | Null | Key | Default        | Extra          |
+----------------------+---------------------+------+-----+----------------+----------------+
| ipb_id               | int(11)             | NO   | PRI | NULL           | auto_increment |
| ipb_address          | tinyblob            | NO   | MUL | NULL           |                |
| ipb_user             | int(10) unsigned    | NO   | MUL | 0              |                |
| ipb_by               | int(10) unsigned    | NO   |     | 0              |                |
| ipb_by_text          | varbinary(255)      | NO   |     |                |                |
| ipb_by_actor         | bigint(20) unsigned | NO   |     | 0              |                |
| ipb_reason           | varbinary(767)      | NO   |     |                |                |
| ipb_reason_id        | bigint(20) unsigned | NO   |     | 0              |                |
| ipb_timestamp        | binary(14)          | NO   | MUL |                |                |
| ipb_auto             | tinyint(1)          | NO   |     | 0              |                |
| ipb_anon_only        | tinyint(1)          | NO   |     | 0              |                |
| ipb_create_account   | tinyint(1)          | NO   |     | 1              |                |
| ipb_enable_autoblock | tinyint(1)          | NO   |     | 1              |                |
| ipb_expiry           | varbinary(14)       | NO   | MUL |                |                |
| ipb_range_start      | tinyblob            | NO   | MUL | NULL           |                |
| ipb_range_end        | tinyblob            | NO   |     | NULL           |                |
| ipb_deleted          | tinyint(1)          | NO   |     | 0              |                |
| ipb_block_email      | tinyint(1)          | NO   |     | 0              |                |
| ipb_allow_usertalk   | tinyint(1)          | NO   |     | 0              |                |
| ipb_parent_block_id  | int(11)             | YES  | MUL | NULL           |                |
+----------------------+---------------------+------+-----+----------------+----------------+

20 rows in set (0.00 sec)

This seems to be related to Rename ipb_address index on ipb_address to ipb_address_unique. I think you can use the information on this SO answer to rename the index by hand and then do the upgrade.

Can you post the output of "SHOW INDEXES FROM ipblocks"?

mysql> SHOW INDEXES FROM ipblocks;
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ipblocks |          0 | PRIMARY             |            1 | ipb_id              | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            1 | ipb_address         | A         |           0 |      255 | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            2 | ipb_user            | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            3 | ipb_auto            | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_user            |            1 | ipb_user            | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_range           |            1 | ipb_range_start     | A         |           0 |        8 | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_range           |            2 | ipb_range_end       | A         |           0 |        8 | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_timestamp       |            1 | ipb_timestamp       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_expiry          |            1 | ipb_expiry          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_parent_block_id |            1 | ipb_parent_block_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

Just noticed the sql script exists for sqlite, but has a different content:

# cat /var/www/html/maintenance/archives/patch-ipblocks-fix-ipb_address_unique.sql

ALTER TABLE /*_*/ipblocks DROP INDEX /*i*/ipb_address_unique, ADD UNIQUE INDEX /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto );

# cat /var/www/html/maintenance/sqlite/archives/patch-ipblocks-fix-ipb_address_unique.sql

DROP INDEX /*i*/ipb_address_unique;

CREATE UNIQUE INDEX /*i*/ipb_address_unique ON /*_*/ipblocks (ipb_address(255), ipb_user, ipb_auto);

Anyway, if attempt to start the wiki website in this state, a database error occurs while ipb_sitewide column is missing as well:

Error 1054: Unknown column 'ipb_sitewide' in 'field list' (mysql)

Function: MediaWiki\Block\DatabaseBlock::newLoad

Query: SELECT ipb_id,ipb_address,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reason_cid`,actor_ipb_by.actor_user AS `ipb_by`,actor_ipb_by.actor_name AS `ipb_by_text`,ipb_by_actor FROM `zeus_de`.`ipblocks` JOIN `comment` `comment_ipb_reason` ON ((comment_ipb_reason.comment_id = ipb_reason_id)) JOIN `actor` `actor_ipb_by` ON ((actor_ipb_by.actor_id = ipb_by_actor)) WHERE ipb_address = '134.34.200.62' OR ((ipb_range_start LIKE '8622%' ESCAPE '`' ) AND (ipb_range_start <= '8622C83E') AND (ipb_range_end >= '8622C83E'))

Backtrace:
#0 /var/www/html/includes/libs/rdbms/database/Database.php(1683): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /var/www/html/includes/libs/rdbms/database/Database.php(1658): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /var/www/html/includes/libs/rdbms/database/Database.php(1227): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /var/www/html/includes/libs/rdbms/database/Database.php(1907): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /var/www/html/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->select(array, array, string, string, array, array)
#5 /var/www/html/includes/libs/rdbms/database/DBConnRef.php(313): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /var/www/html/includes/block/DatabaseBlock.php(302): Wikimedia\Rdbms\DBConnRef->select(array, array, string, string, array, array)
#7 /var/www/html/includes/block/DatabaseBlock.php(1187): MediaWiki\Block\DatabaseBlock::newLoad(User, integer, boolean, string)
#8 /var/www/html/includes/block/BlockManager.php(139): MediaWiki\Block\DatabaseBlock::newListFromTarget(User, string, boolean)
#9 /var/www/html/includes/user/User.php(1603): MediaWiki\Block\BlockManager->getUserBlock(User, WebRequest, boolean)
#10 /var/www/html/includes/user/User.php(1986): User->getBlockedStatus(boolean)
#11 /var/www/html/includes/specials/SpecialUndelete.php(155): User->getBlock()
#12 /var/www/html/includes/specials/SpecialUndelete.php(167): SpecialUndelete->isAllowed(string, User)
#13 /var/www/html/includes/specialpage/SpecialPageFactory.php(506): SpecialUndelete->userCanExecute(User)
#14 /var/www/html/includes/specials/SpecialSpecialpages.php(56): MediaWiki\SpecialPage\SpecialPageFactory->getUsablePages(User)
#15 /var/www/html/includes/specials/SpecialSpecialpages.php(44): SpecialSpecialpages->getPageGroups()
#16 /var/www/html/includes/specialpage/SpecialPage.php(600): SpecialSpecialpages->execute(NULL)
#17 /var/www/html/includes/specialpage/SpecialPageFactory.php(635): SpecialPage->run(NULL)
#18 /var/www/html/includes/MediaWiki.php(307): MediaWiki\SpecialPage\SpecialPageFactory->executePath(Title, RequestContext)
#19 /var/www/html/includes/MediaWiki.php(940): MediaWiki->performRequest()
#20 /var/www/html/includes/MediaWiki.php(543): MediaWiki->main()
#21 /var/www/html/index.php(53): MediaWiki->run()
#22 /var/www/html/index.php(46): wfIndexMain()
#23 {main}

ipb_sitewide was added in 1.32, but there is a patch for it, which hasn't been run either seeminly

Also, ipb_address_unique clearly exists on your schema. And it looks up to date

Does the sql user you're running the change as have permission to drop indexes?

The sql user is root while mysql runs on a docker container, thus it should have permission to drop indexes. As well the error doesn't mention any permissions problem.

Actually the issue can be reproduced on the command line:

mysql> ALTER TABLE /*_*/ipblocks DROP INDEX /*i*/ipb_address_unique, ADD UNIQUE INDEX /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto );

ERROR 1091 (42000): Can't DROP 'ipb_address_unique'; check that column/key exists

But it definitely exists...

If you do it as two commands (ala the SQLite patch), does it work? I'm wondering if it's getting confused with the add and remove in the same line

What version of MySQL/MariaDB are you using? Might be related to that... Works on some, doesn't on other versions

You're using the root user in MW config to connect to MySQL?

No, it doesn't work due to a syntax error:

mysql> DROP INDEX /*i*/ipb_address_unique;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

The MySQL version is 5.7.31 and yes, I am using the root user to connect to MySQL, for example:

# mysql -uroot -p

It fails also by using the correct syntax:

mysql> DROP INDEX /*i*/ipb_address_unique ON /*_*/ipblocks;

ERROR 1091 (42000): Can't DROP 'ipb_address_unique'; check that column/key exists

No it wouldn't, because that's not valid syntax.

https://dev.mysql.com/doc/refman/5.7/en/drop-index.html

DROP INDEX /*i*/ipb_address_unique ON /*_*/ipblocks;

or

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

ALTER TABLE /*_*/ipblocks DROP INDEX /*i*/ipb_address_unique;

see the previous reply pls...

Both are failing with the same error:

mysql> DROP INDEX /*i*/ipb_address_unique ON /*_*/ipblocks;

ERROR 1091 (42000): Can't DROP 'ipb_address_unique'; check that column/key exists

mysql> ALTER TABLE /*_*/ipblocks DROP INDEX /*i*/ipb_address_unique;

ERROR 1091 (42000): Can't DROP 'ipb_address_unique'; check that column/key exists

But the index exists. It is there in your original output.

And the queries all work fine for me... "10.4.14-MariaDB-1:10.4.14+maria~focal-log"

MariaDB [wikidb]> ALTER TABLE mw_ipblocks DROP INDEX /*i*/ipb_address_unique;
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wikidb]> ALTER TABLE mw_ipblocks ADD UNIQUE INDEX /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto);
Query OK, 0 rows affected (0.014 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wikidb]> DROP INDEX /*i*/ipb_address_unique ON mw_ipblocks;
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wikidb]> ALTER TABLE mw_ipblocks ADD UNIQUE INDEX /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto);
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wikidb]> ALTER TABLE mw_ipblocks DROP INDEX /*i*/ipb_address_unique, ADD UNIQUE INDEX /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto );
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

What does SHOW GRANTS tell you?

Just now ipb_address_unique is no more in table ipblocks, just ipb_address

mysql> SHOW INDEXES FROM ipblocks;
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---
| Table    | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | In
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---
| ipblocks |          0 | PRIMARY             |            1 | ipb_id              | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| ipblocks |          0 | ipb_address         |            1 | ipb_address         | A         |           0 |      255 | NULL   |      | BTREE      |         |
| ipblocks |          0 | ipb_address         |            2 | ipb_user            | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| ipblocks |          0 | ipb_address         |            3 | ipb_auto            | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| ipblocks |          0 | ipb_address         |            4 | ipb_anon_only       | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| ipblocks |          1 | ipb_user            |            1 | ipb_user            | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| ipblocks |          1 | ipb_range           |            1 | ipb_range_start     | A         |           0 |        8 | NULL   |      | BTREE      |         |
| ipblocks |          1 | ipb_range           |            2 | ipb_range_end       | A         |           0 |        8 | NULL   |      | BTREE      |         |
| ipblocks |          1 | ipb_timestamp       |            1 | ipb_timestamp       | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| ipblocks |          1 | ipb_expiry          |            1 | ipb_expiry          | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| ipblocks |          1 | ipb_parent_block_id |            1 | ipb_parent_block_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---
11 rows in set (0.00 sec)

So what have you changed? It was clearly there in you previous output.

I didn't change anything, I just tried to drop the index as indicated and I got always an error. Very weird.

Indexes don't just change and rename themselves without some input.

What happens if you run update.php now?

update.php still fails:

...

...skipping update to shared table ipblocks.

Removing ipb_anon_only column from ipb_address_unique index ...Wikimedia\Rdbms\DBQueryError from line 1699 of /var/www/html/includes/libs/rdbms/database/Database.php: Error 1091: Can't DROP 'ipb_address_unique'; check that column/key exists (mysql)

Function: Wikimedia\Rdbms\Database::sourceFile( /var/www/html/maintenance/archives/patch-ipblocks-fix-ipb_address_unique.sql )

Query: ALTER TABLE `zeus_de`.`ipblocks` DROP INDEX ipb_address_unique, ADD UNIQUE INDEX ipb_address_unique (ipb_address(255), ipb_user, ipb_auto )

#0 /var/www/html/includes/libs/rdbms/database/Database.php(1683): Wikimedia\Rdbms\Database->getQueryException('Can't DROP 'ipb...', 1091, 'ALTER TABLE `ze...', 'Wikimedia\\Rdbms...')

#1 /var/www/html/includes/libs/rdbms/database/Database.php(1658): Wikimedia\Rdbms\Database->getQueryExceptionAndLog('Can't DROP 'ipb...', 1091, 'ALTER TABLE `ze...', 'Wikimedia\\Rdbms...')

#2 /var/www/html/includes/libs/rdbms/database/Database.php(1227): Wikimedia\Rdbms\Database->reportQueryError('Can't DROP 'ipb...', 1091, 'ALTER TABLE `ze...', 'Wikimedia\\Rdbms...', false)

#3 /var/www/html/includes/libs/rdbms/database/Database.php(5075): Wikimedia\Rdbms\Database->query('ALTER TABLE `ze...', 'Wikimedia\\Rdbms...')

#4 /var/www/html/includes/libs/rdbms/database/Database.php(5010): Wikimedia\Rdbms\Database->sourceStream(Resource id #1226, NULL, NULL, 'Wikimedia\\Rdbms...', NULL)

#5 /var/www/html/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->sourceFile('/var/www/html/m...')

#6 /var/www/html/includes/libs/rdbms/database/MaintainableDBConnRef.php(35): Wikimedia\Rdbms\DBConnRef->__call('sourceFile', Array)

#7 /var/www/html/includes/installer/DatabaseUpdater.php(727): Wikimedia\Rdbms\MaintainableDBConnRef->sourceFile('/var/www/html/m...')

#8 /var/www/html/includes/installer/MysqlUpdater.php(1222): DatabaseUpdater->applyPatch('/var/www/html/m...', false, 'Removing ipb_an...')

#9 /var/www/html/includes/installer/DatabaseUpdater.php(512): MysqlUpdater->doFixIpbAddressUniqueIndex()

#10 /var/www/html/includes/installer/DatabaseUpdater.php(475): DatabaseUpdater->runUpdates(Array, false)

#11 /var/www/html/maintenance/update.php(181): DatabaseUpdater->doUpdates(Array)

#12 /var/www/html/maintenance/doMaintenance.php(107): UpdateMediaWiki->execute()

#13 /var/www/html/maintenance/update.php(253): require_once('/var/www/html/m...')

#14 {main}

Tomorrow I am going to redo the upgrade to 1.35 and check what is happening with the indexes.

On the next attempt, the error Error 1091: Can't DROP 'ipb_address_unique'; check that column/key exists reoccured during update.php.

mysql> SHOW INDEXES FROM ipblocks;

+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table    | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| ipblocks |          0 | PRIMARY             |            1 | ipb_id              | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |

| ipblocks |          0 | ipb_address         |            1 | ipb_address         | A         |           0 |      255 | NULL   |      | BTREE      |         |               |

| ipblocks |          0 | ipb_address         |            2 | ipb_user            | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |

...

No index ipb_address_unique exists in MW 1.31.

Long story short, the following workaround worked(!) by replacing the content of the sql patch with:

# cat /var/www/html/maintenance/archives/patch-ipblocks-fix-ipb_address_unique.sql

DROP INDEX /*i*/ipb_address ON /*_*/ipblocks;

CREATE UNIQUE INDEX /*i*/ipb_address_unique ON /*_*/ipblocks (ipb_address(255), ipb_user, ipb_auto);

The variant with ALTER TABLE /*_*/ipblocks DROP INDEX /*i*/ipb_address is always failing (due to the Mysql version?)

If the split version works fine, we should probably just swap to that. It's easier all around...

Oh. It's not the split that's the problem. It's the fact the patch is trying to drop the wrong index.

Sigh. Should've been more obvious

I'm getting myself confused now.

Your split version isn't the same.

The updater for patch-ipblocks-rename-ipb_address.sql should've renamed the index, and given it the incorrect signature.

Then patch-ipblocks-fix-ipb_address_unique.sql should've been run (as it is) to correct the signature. But at that point, the ipb_address index shouldn't still exist, it should already be ipb_address_unique.

However, I just noticed "...skipping update to shared table ipblocks.". Which will explain why the first patch isn't being applied.

Right. So it's because the updater returns the same result for whether the table is shared, or if the index exists. This is wrong.

Correct fix in https://gerrit.wikimedia.org/r/632749