ALTER TABLE fails when legacy BLOB columns contain TEXT values #546

Problem

Rebuilding a table created by the legacy driver can fail when a BLOB column contains a value stored as SQLite TEXT. This affects ALTER TABLE, OPTIMIZE TABLE, and REPAIR TABLE.

The legacy driver accepts an ordinary string for a BLOB column and stores it with SQLite's TEXT storage class. The AST driver can open and read that table, but rebuilding it creates a STRICT table and copies the values without converting their storage classes.

Reproduction

Using the legacy driver from v2.2.23, run:

CREATE TABLE blob_demo (id INT PRIMARY KEY, payload BLOB);
INSERT INTO blob_demo VALUES (1, 'abc');

Reopen the same database with the AST driver, then run:

ALTER TABLE blob_demo ADD COLUMN note TEXT;

The statement fails:

SQLSTATE[23000]: Integrity constraint violation: 19 cannot store TEXT value in BLOB column _wp_sqlite_tmp_blob_demo_<suffix>.payload

A native SQLite query confirms that the original value is stored as TEXT:

SELECT payload, typeof(payload) FROM blob_demo;
-- abc | text

On separate copies of the original database, OPTIMIZE TABLE blob_demo and REPAIR TABLE blob_demo return the same failure in their result rows.

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

Expected behavior

Rebuilding a legacy table should preserve its valid data. The shared copy operation needs to handle legacy storage classes while preserving the bytes of binary values.

The relevant path is recreate_table_from_information_schema().

This can also affect sites already running the AST driver, because earlier upgrades retained the original SQLite tables.

Related

• SQLite driver fails to insert TEXT into longblob column #268 covered TEXT-to-BLOB conversion for ordinary inserts and is closed. This report concerns copying existing legacy rows during table reconstruction.
• ALTER TABLE and OPTIMIZE TABLE reset the AUTO_INCREMENT counter #544 tracks another problem in the same rebuild path.
• Normalize zero defaults of temporal columns #542 adds an automatic upgrade migration that rebuilds tables with zero temporal defaults. If an affected table also contains these BLOB values, the failure occurs during connection initialization. This was reproduced separately on that PR.