With InnoDB, restarting MySQL in certain situations (e.g. right after the latest page and/or revision is deleted), can result in the a page_id or rev_id (not sure if this is problematic for additional tables) being handed out by auto_increment, even though it was previously handed out:
See http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization for why this happens:
To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column.
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
In other words, it forgets the highest auto_increment ever handed out, and only picks the highest currently there (then later adds 1).
To reproduce, without any other edits:
- Create page
- Delete page
- Restart MySQL server
- Create page
- Delete page
(I think it could also occur for just rev_id if you deleted the most recently-edited page, but not the most recently-created).
The latest rows in archive will have duplicates for both ar_page_id and ar_rev_id.
This causes serious problems:
Also, RevisionDelete will show two rows with the same rev_id when you use a URL like:
This is the URL form linked from Special:Undelete.
+-------+--------------+----------------+---------------------------------------------------+------------+-----------+--------------+-----------------------+ | ar_id | ar_namespace | ar_timestamp | ar_title | ar_page_id | ar_rev_id | ar_user_text | ar_comment | +-------+--------------+----------------+---------------------------------------------------+------------+-----------+--------------+-----------------------+ | 81 | 0 | 20160520145527 | Testing_duplicate_page_ID_after_restart_unrelated | 1879 | 4767 | Admin | Created page with "b" | | 80 | 0 | 20160520145417 | Testing_duplicate_page_ID_before_restart | 1879 | 4767 | Admin | Created page with "a" | +-------+--------------+----------------+---------------------------------------------------+------------+-----------+--------------+-----------------------+
This is unlikely to occur in production, but could also be prevented by:
- Before restart, query Auto_increment from affected tables (using SHOW TABLE STATUS) and store it.
- After restart, reset it to that value before bringing the database back online.