sqlite-database-integration icon indicating copy to clipboard operation
sqlite-database-integration copied to clipboard

`show create` generates invalid default values for columns

Open jeroenpf opened this issue 1 year ago • 0 comments

The default column values in create table statements after running show create table contain invalid default values that do not correspond to the original tables.

A non-exhaustive list of examples:

  • timestamp/datetime NOT NULL without a default value appears with the default value 0 in the resulting create statement.
  • decimal(x,y) NOT NULL without a default value appears with a default value 0 in the resulting create statement.
  • bigint(x) NOT NULL 0 appears without a default value in the resulting create statement

This issue seems to happen when we run create statements and force NOT NULL columns to always have a default value. This is likely necessary for compatibility with WordPress queries that don't expect strict checks. We could consider storing the original default value (if any) in the _mysql_data_types_cache table and use that as the source of truth when we run show create queries.

jeroenpf avatar Jul 17 '24 08:07 jeroenpf