MySQL Bugs: #20102: mysqldump --compatible=mysql40 drops autoincrement

Bug #20102	mysqldump --compatible=mysql40 drops autoincrement
Submitted:	27 May 2006 9:01	Modified:	29 May 2006 12:07
Reporter:	jonathan linowes	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0	OS:	MacOS (OS X Intel)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
when i mysqldump from 5, the autoincrement is there
when i use the --compatible flag, the autoincrement is omitted

e.g.

  `xar_uid` int(11) NOT NULL auto_increment,
vs
 `xar_uid` int(11) NOT NULL default '0',

The commands i use are like this
mysqldump --opt xyz > xyz.sql
mysqldump --opt --compatible=mysql40 xyz > xyz.sql

This causes an error when i import the db into mySQL 4.0 using
mysql -D xyz < xyz.sql

How to repeat:
see above

Thank you for a problem report. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html) carefully:

"- NO_FIELD_OPTIONS

Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode. "

"- MYSQL40

Equivalent to NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE."

auto_increment is MySQL-specific column option. It is not supported by other RDBMSes. So, it is not a bug, but intended and documented behaviour.

Formally, this is not a bug. But surely, one would expect MYSQL40 SQL mode to produce code that is compatible with MySQL 4.0, not with any ANSI or "generic" database. 

So, auto_increment column option should not be stripped. For this reason, I think, this is a valid feature request. MYSQL40 SQL mode and its description should be changed. But I am not sure that this change will not break some existing scripts...

thanks for pointing out the references in the mysql manual, i missed that.
fyi, after submitting this bug a friend pointed out this other report which seems to be the same thing and seems like its been corrected.
http://bugs.mysql.com/bug.php?id=14515
Are these the same?

This is duplicate of 14515. It is fixed, documenting.

This is duplicate of 14515. It is fixed, documenting.