MySQL Bugs: #54178: SHOW CREATE TABLE does not show column format FIXED/DYNAMIC if set implicitly

Bug #54178	SHOW CREATE TABLE does not show column format FIXED/DYNAMIC if set implicitly
Submitted:	2 Jun 2010 10:34	Modified:	2 Jun 2010 10:43
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql-custer-6.3.33

Description:
When adding a column to a cluster table using ALTER ONLINE the row internally gets the DYNAMIC storage attribute even without specifying "STORAGE DYNAMIC".

This is visible in ndb_desc output but SHOW CREATE TABLE and mysqldump only show it if it was specified explicitly.

This leads to restore problems when restoring the schema
data from a "mysqldump --no-data" dump and only using
ndb_restore to restore actual data rows. 

On the old instace the column that was added online has the 
DYNAMIC attribute set on the cluster level, and is stored as 
such in the cluster backup file. The mysqld level does not 
know about the DYNAMIC attribute though and so does not 
include it in mysqldump output.

So on restoring the schema dump the DYNAMIC attribute is lost
and ndb_restore -r then fails as the column definitions in
the backup and on the cluster to be restored to are not 100%
identical

How to repeat:
- create a cluster table
- add a column to it using ALTER ONLINE
- take a schema only mysqldump and a cluster backup
- restore the schema dump on a fresh cluster
- restore the cluster data using "ndb_restore -r ..."

the ndb_restore will fail with

  m_columns msg != msg
  Restore: Failed to restore data, test/def/t1 table structure doesn't match backup ... Exiting 

ndb_desc on the old and new clusters will show that the column that was added online has teh DYNAMIC attribute on the old cluster but not on the new one.
"ndb_restore --print-meta" will show that DYNAMIC is also set within the cluster backup files.

Suggested fix:
- make sure that the mysqld layer is aware of the different storage format for columns that were added online

- make ndb_restore ignore differences in the DYNAMIC attribute if all other column attributes match

See also bug #53810 for the ndb_restore specific aspect of this bug

See also bug #54242

The fix for Bug#54423 relaxes ndb_restores schema checks so that differences in the Dynamic property do not count as schema differences.

This will allow ndb_restore to restore despite the dynamic property varying.

It is still undesirable that the setting of the DYNAMIC property is not included in the table description from SHOW CREATE TABLE.