MySQL Bugs: #15208: mysqldump --compatible=mysql40 doesn't dump auto

Bug #15208	mysqldump --compatible=mysql40 doesn't dump auto_increment
Submitted:	24 Nov 2005 0:08	Modified:	16 Dec 2005 18:31
Reporter:	Markus Popp	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.0.15, 5.0.16	OS:	Windows (Windows, Linux)
Assigned to:		CPU Architecture:	Any

Description:
If you dump a table that includes an auto_increment column with the option --compatible=mysql40, there is no auto_increment in the dump file.

How to repeat:
Create a table like this:

create table tt (id int unsigned not null auto_increment primary key);

Then dump this table from the console:

mysqldump --databases [database] --tables tt --compatible=mysql40 > dump_file.sql

If you look into the dump file, there is no auto_increment in the table definition.

Suggested fix:
The dump file should include auto_increment in the table definition.

Another thing that could be added to the dump output (this time without --compatible) would be to comment the DEFAULT CHARSET clause (and also COLLATION) for versions below 4.1 - for example instead

CREATE TABLE `w4links_adlinks` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `url` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

to write ...

CREATE TABLE `w4links_adlinks` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `url` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB /*!40100 DEFAULT CHARSET=utf8 */;

Looks to me this is a duplicate of:

http://bugs.mysql.com/bug.php?id=14929

OK, the documentation says that --compatible=mysql40 or --compatible=mysql323 uses the sql_mode NO_FIELD_OPTIONS implicitly. Even though this behaviour seems to be documented like this, I don't believe that it's good.

Because this makes it impossible to create a dump file under MySQL 4.1/5.0 that can be used in MySQL 4.0 or below. So it would actually make sense to include field options that are allowed in earlier MySQL versions to make it possible to create consistent dumps.

Is there any other way to create a dump file out of MySQL 5.0 that includes ALL options that are allowed in MySQL 4.0, but none of the option that's not allowed? I need to transfer a database from 5.0 to 4.0, but don't find a way to create an applicable dump file.

I think, if it's not possible, this should still be considered as bug, even if it's documented otherwise.

Sorry for my delay answer but I was sharing opinion with co-workers with
different local timezone.

We agree it is an essential feature to be able to dump from 5.0 to 4.0.
But that decision belong to development team.

The currently workaround is you create 2 dumps: one just with the tables
definition and you edit manually them and other dump only with data to be imported.

Besides possibly modifying the behaviour of the --compatible modifier, I think that this solution:

CREATE TABLE `w4links_adlinks` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `url` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB /*!40100 DEFAULT CHARSET=utf8 */;

... should also be considered. If all critical parts are signed with /*xyyzz */, it could be possible that every dump from any version could be imported into any other version without even the need to specify a --compatible modifier (as long as the specific options aren't needed for any more particular purpose).

Duplicate of Bug #14515.