MySQL Bugs: #31020: ARCHIVE tables with AUTO_INCREMENT ain't dumped in a backward compatible way

Bug #31020	ARCHIVE tables with AUTO_INCREMENT ain't dumped in a backward compatible way
Submitted:	14 Sep 2007 6:22	Modified:	12 Oct 2007 17:36
Reporter:	Asuka Kenji Siu Ching Pong (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.1.21-beta-community	OS:	Any
Assigned to:	Brian Aker	CPU Architecture:	Any
Tags:	archive, auto_increment, mysqldump, primary key

Description:
The output of mysqldump are designed to be executable on different versions of MySQL servers, because the unavailable features are commented with /*! ... */.

However, the AUTO_INCREMENT and PRIMARY KEY specifications of ARCHIVE tables, which are only available on MySQL > 5.1, are not commented correctly.

How to repeat:
1. Create an ARCHIVE table on MySQL 5.1:

   mysql> CREATE TABLE test.temp (colA BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY);

2. Check that the table was created correctly:

   mysql> SHOW CREATE TABLE test.temp;

3. Dump the table:

   $ mysqldump --database test > test.sql

4. Connect to a MySQL 5.0 instance and source the dump:

   mysql> source test.sql

Suggested fix:
Surround the AUTO_INCREMENT and PRIMARY KEY clauses with "/*!50100" and "*/" in the output of mysqldump.

Sorry, the first step should be:

1. Create an ARCHIVE table on MySQL 5.1:

   mysql> CREATE TABLE test.temp (colA BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE = ARCHIVE;

This is not a bug (and I asked in support about it to get a second opinion).

Where we have added index types to different engines we have never put in compatibility modes. A backward load should get a failure because the definition of the table has changed. See additions to Innodb, and MyISAM over the years as examples. 

If the definition changes, a user should not get nailed when trying to downgrade and then discover that his table definition no longer works correctly.