OPTIMIZE TABLE and REPAIR TABLE commit partial changes when a rebuild fails #549

Problem

When OPTIMIZE TABLE or REPAIR TABLE fails to rebuild a table, the partial rebuild is committed. Each failed run leaves a hidden _wp_sqlite_tmp_<table>_<id> table in the database, which can hold a full copy of the rows.

Reproduction

CREATE TABLE parent (id INT PRIMARY KEY, name TEXT);
CREATE TABLE child (id INT PRIMARY KEY, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent (id));
INSERT INTO parent VALUES (1, 'a'), (2, 'b');
INSERT INTO child VALUES (1, 1);

OPTIMIZE TABLE parent;
-- Error:  SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed
-- status: Operation failed

SHOW TABLES still lists only child and parent, but a native SQLite query shows the table left behind:

SELECT name FROM sqlite_master WHERE type = 'table' AND name LIKE '%tmp%';
-- _wp_sqlite_tmp_parent_6ad8e3d709520

It holds a copy of both parent rows. REPAIR TABLE parent does the same, and every further run adds another such table.

When a later step of the rebuild fails, a half-rebuilt table is committed. In a test with duplicate values under a unique key, written directly with SQLite, OPTIMIZE TABLE reported UNIQUE constraint failed and left the table without its index.

Verified on trunk at e88a817e89a80c4fab2b46f86606367822695b45, with PHP 8.5.11 and SQLite 3.54.1.

Cause

OPTIMIZE TABLE and REPAIR TABLE rebuild the table in recreate_table_from_information_schema(). It creates the temporary table, copies the rows, drops the old table, renames the new one, and creates the indexes.

When a step fails, execute_administration_statement() catches the error and turns it into result rows. query() doesn't see a failure, so it commits its wrapper transaction, including everything the rebuild did before the failing step.

Expected behavior

A failed rebuild should leave the table and the database unchanged. A possible fix is to run the rebuild of each table in a savepoint, roll back to it when the rebuild fails, and then report the error row.

Related

• ALTER TABLE on a parent table deletes child rows through ON DELETE CASCADE #528: The rebuild in the reproduction fails because foreign keys stay on during the rebuild, the same cause as in ALTER TABLE on a parent table deletes child rows through ON DELETE CASCADE #528.
• ALTER TABLE fails when legacy BLOB columns contain TEXT values #546: A legacy table with TEXT values in a BLOB column fails the copy step, and OPTIMIZE TABLE leaves an empty temporary table behind.
• Normalize zero defaults in date and time columns #542: Its upgrade migration rebuilds tables with OPTIMIZE TABLE. It rolls back the whole configuration when a result row reports an error, so it doesn't leave tables behind.