Exported CREATE TABLE drops prefix lengths from PRIMARY KEYs, breaking the database import on push

[csmcneill]

Quick summary

The push SQL export drops index prefix lengths (SUB_PART) when reconstructing PRIMARY KEY clauses. A table with a prefixed primary key on a TEXT column — e.g. PRIMARY KEY (session_id(100)) on a mediumtext column — exports as PRIMARY KEY (session_id), which MySQL/MariaDB reject with ERROR 1170 (42000): BLOB/TEXT column used in key specification without a key length. The remote import aborts and Studio shows only the generic "The database failed to import on the live site."

The prefix IS recorded correctly on pull: _wp_sqlite_mysql_information_schema_statistics contains SUB_PART = 100 for the column. The bug is emission-only: in the CREATE TABLE reconstruction (sqlite-database-integration 3.0.0-rc.8, class-wp-mysql-on-sqlite.php), the non-PRIMARY key branch appends sprintf('(%d)', $column['SUB_PART']), but the PRIMARY KEY branch maps columns through quote_mysql_identifier() only and never emits SUB_PART.

In my case the table came from the miniOrange 2FA plugin (wp_mo2f_user_login_info), pulled from a production WordPress.com site. Any site pulled from production with a prefixed-TEXT primary key in any table cannot be pushed back.

Steps to reproduce

1. Create a Studio site and add a table with a prefixed TEXT primary key, e.g. via wp eval '$GLOBALS["wpdb"]->query("CREATE TABLE wp_repro (id MEDIUMTEXT NOT NULL, PRIMARY KEY (id(100)))");' (or pull a production site containing such a table).
2. Export the database (or push to a connected WordPress.com site with Database checked).
3. Inspect the exported SQL: the CREATE TABLE contains PRIMARY KEY (id) with no (100) prefix.
4. Importing that SQL into MariaDB fails: ERROR 1170 (42000): BLOB/TEXT column 'id' used in key specification without a key length. Pushes fail with the generic database-import error.

Verified by replaying the exact exported file into MariaDB 10.6 (default sql_mode, utf8mb4 client): the import aborts at the affected statement; after removing the affected table, the same export imports cleanly.

What you expected to happen

PRIMARY KEY reconstruction should emit SUB_PART like the non-primary branch does, producing PRIMARY KEY (id(100)). Studio should also surface the underlying SQL error instead of the generic message.

What actually happened

Every push with Database checked fails with "The database failed to import on the live site. Review your database and try again." The server-side web logs show the Jetpack import helper streaming the import and returning HTTP 500 at the affected statement, with no actionable error surfaced in the app.

App or CLI?

Studio App

Version

1.20.0

Impact

One

Available workarounds?

No and the app is unusable. However, "No" is misleading, but it might as well be "no" as I couldn't find the cause of the issue without a lot of back-and-forth with Claude.

Only workaround is to drop or restructure the affected table locally (mine belonged to an uninstalled plugin), then push. Finding the table requires manually replaying the export against a local MariaDB, since the app does not surface the failing statement.

Platform

Mac

Architecture

ARM64 (Apple Silicon, Windows or Linux on ARM)

Logs or notes

• Studio 1.20.0 (commit e234e94), macOS 26.6.1, sqlite-database-integration 3.0.0-rc.8, local PHP 8.4
• Remote: WordPress.com Atomic production site
• Related reports filed for two other push failure modes encountered on the same site (invalid exported collation; non-atomic file sync fataling the import bootstrap)

[samuelmbabhazi]

Traced this upstream. The push export builds its dump from the SQLite driver's SHOW CREATE TABLE (wp sqlite export relays it as is), and in the driver the PRIMARY KEY branch emitted only the quoted column names, while the branch handling every other index type preserved SUB_PART and descending order. So PRIMARY KEY (session_id(100)) came back as PRIMARY KEY (session_id), and MySQL rejects the dump with the ERROR 1170 you saw behind the generic import failure message.

Fix with regression tests: WordPress/sqlite-database-integration#500. Verified against a real MySQL 8 server: the previous output fails there with exactly that error, the fixed output imports cleanly, and the emitted clause matches MySQL 8's own SHOW CREATE TABLE for the same table. Once it ships in a release, Studio picks it up through its pinned sqlite-database-integration version.

For what it's worth, #4737 could be the same root cause whenever the pushed database contains a prefixed key on a TEXT/BLOB column.