SHOW CREATE TABLE quotes numeric temporal defaults (DEFAULT 0 → '0'), breaking MySQL import #541

Summary

SHOW CREATE TABLE emits numeric temporal defaults as quoted strings. A column created as TIMESTAMP NOT NULL DEFAULT 0 comes back as DEFAULT '0'. MySQL itself normalizes this default to '0000-00-00 00:00:00'.

In strict SQL mode without NO_ZERO_DATE, MySQL accepts DEFAULT 0 and DEFAULT '0000-00-00 00:00:00', but rejects DEFAULT '0'. Dumps of an SQLite site therefore fail to import into such a server with ERROR 1067: Invalid default value.

Real-world impact

The H5P plugin (about 40k installs) creates its tables with this pattern (source):

created_at TIMESTAMP NOT NULL DEFAULT 0,
updated_at TIMESTAMP NOT NULL DEFAULT 0,

WordPress Studio exports sites with wp sqlite export. Pushing a Studio site that has H5P installed to a MySQL host fails on every attempt. The restore rejects the CREATE TABLE for wp_h5p_contents and wp_h5p_contents_user_data with Invalid default value for 'created_at' / 'updated_at'.

Reproduction (v3.1.0 release zip)

require 'plugin-sqlite-database-integration/wp-includes/database/load.php';
$driver = new WP_SQLite_Driver( new WP_SQLite_Connection( array( 'pdo' => new PDO( 'sqlite::memory:' ) ) ), 'wp' );
$driver->query( 'CREATE TABLE t (id INT UNSIGNED NOT NULL, a TIMESTAMP NOT NULL DEFAULT 0, b DATETIME NOT NULL DEFAULT 0, PRIMARY KEY (id))' );
echo $driver->query( 'SHOW CREATE TABLE t' )[0]->{'Create Table'};

Actual:

  `a` timestamp NOT NULL DEFAULT '0',
  `b` datetime NOT NULL DEFAULT '0',

MySQL output for the same CREATE TABLE (sql_mode='STRICT_TRANS_TABLES'):

  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `b` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

MySQL acceptance of each form in sql_mode='STRICT_TRANS_TABLES':

Also reproduced on v3.0.2. The code path is unchanged on trunk (e88a817).

Cause

format_mysql_column_default() quotes every non-BIT default as a string literal:

The information schema stores the default as written (0). Quoting it produces a literal MySQL does not accept for temporal types.

Expected

Temporal column defaults round-trip the way MySQL reports them: normalize numeric zero defaults for DATETIME/TIMESTAMP (and DATE/TIME/YEAR as applicable) to MySQL's canonical form when the column is created, or when it is emitted in SHOW CREATE TABLE. This is the same class of problem as #125 and #165, for an explicit numeric default instead of an implicit one.

Acceptance criteria

• SHOW CREATE TABLE for TIMESTAMP NOT NULL DEFAULT 0 and DATETIME NOT NULL DEFAULT 0 emits DEFAULT '0000-00-00 00:00:00'.
• The emitted CREATE TABLE executes on MySQL with STRICT_TRANS_TABLES.
• H5P's table definitions round-trip through SHOW CREATE TABLE into MySQL.

AI assistance: Claude (Anthropic), running in the Pi coding agent, traced the failure from restore logs, reproduced it against the v3.1.0 release and MySQL, and drafted this issue at Chris Huber's direction.