ALTER TABLE and OPTIMIZE TABLE reset the AUTO_INCREMENT counter #544

Reproduction

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT);
INSERT INTO t (name) VALUES ('a'), ('b'), ('c');
DELETE FROM t WHERE id = 3;

ALTER TABLE t ADD COLUMN note TEXT;

INSERT INTO t (name) VALUES ('d');
SELECT id FROM t WHERE name = 'd';
-- 3   (MySQL: 4)

Reproduced on trunk (3.1.0) with PHP 8.5 and SQLite 3.54.1. ALTER TABLE ... MODIFY COLUMN and OPTIMIZE TABLE do the same. The AUTO_INCREMENT table option is lost too:

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT) AUTO_INCREMENT = 1000;
ALTER TABLE t ADD COLUMN note TEXT;
INSERT INTO t (name) VALUES ('d');
-- id = 1   (MySQL: 1000)

When an ID of a deleted row is reused, anything that still points to the deleted row, such as plugin data, now points to the new row.

Cause

ALTER TABLE and OPTIMIZE TABLE recreate the table in recreate_table_from_information_schema(): they create a new table, copy the rows, drop the old table, and rename the new one. SQLite stores the AUTOINCREMENT counter in the sqlite_sequence table. Dropping the old table deletes its counter, and the new table's counter only reaches the highest copied ID. So the next ID is the highest existing ID plus one, not the next value of the old counter.

A possible fix is to read the table's sqlite_sequence value before recreating the table, and to restore it after the rename.

Related

#542 adds an upgrade migration that recreates tables with numeric zero date/time defaults, so it would also reset the counters of those tables.