Bug #21342 mysqldump from 4.1 version downgrade to 4.0, lost 'auto_increment' keyword

Description:
the mysql server version on My workstation is 4.1.18. But some of my production is still using 4.0 version. So, when I mysqldump the whole database which downgrade to 4.0 format with following command:

shell> mysqldump --create-options --compatible=mysql40 db_name > dump_file

the key word 'auto_increment' missing in table definition.

table definition with mysqldump (no downgrade to 4.0)
CREATE TABLE `achdetail` (
  `acd_no` mediumint(10) NOT NULL auto_increment,
  `acd_key` varchar(25) NOT NULL default '',
  `acd_icode1` varchar(10) NOT NULL default '',
  `acd_icode2` int(5) NOT NULL default '1',
  `acd_month` int(2) NOT NULL default '0',
  `acd_year` int(4) NOT NULL default '0',
  `acd_dcode1` varchar(10) NOT NULL default '',
  `acd_dcode2` int(5) NOT NULL default '1',
  `acd_rank` int(2) NOT NULL default '1',
  `acd_share` decimal(14,2) NOT NULL default '0.00',
  `acd_sharevalue` decimal(14,2) NOT NULL default '0.00',
  `acd_bonus` decimal(14,2) NOT NULL default '0.00',
  PRIMARY KEY  (`acd_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table definition with mysqldump which downgrade to 4.0
CREATE TABLE `achdetail` (
  `acd_no` mediumint(10) NOT NULL,
  `acd_key` varchar(25) NOT NULL default '',
  `acd_icode1` varchar(10) NOT NULL default '',
  `acd_icode2` int(5) NOT NULL default '1',
  `acd_month` int(2) NOT NULL default '0',
  `acd_year` int(4) NOT NULL default '0',
  `acd_dcode1` varchar(10) NOT NULL default '',
  `acd_dcode2` int(5) NOT NULL default '1',
  `acd_rank` int(2) NOT NULL default '1',
  `acd_share` decimal(14,2) NOT NULL default '0.00',
  `acd_sharevalue` decimal(14,2) NOT NULL default '0.00',
  `acd_bonus` decimal(14,2) NOT NULL default '0.00',
  PRIMARY KEY  (`acd_no`)
) TYPE=InnoDB;

It make me troublesome because i discover it after i imported to production server.

Thanks.

How to repeat:
create a table with auto_increment keyword included,
then mysqldump with compatible to 4.0.

you should see the auto_increment missing.

Version 4.1.20 still not fixed. Will test source server:

c:\mysql\bin>mysqladmin -uroot create dbx

c:\mysql\bin>mysql -uroot dbx < achdetail.sql

c:\mysql\bin>mysqldump --create-options --compatible=mysql40 dbx
-- MySQL dump 10.9
--
-- Host: localhost    Database: dbx
-- ------------------------------------------------------
-- Server version       4.1.20-community-nt
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,MYSQL40' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `achdetail`
--

DROP TABLE IF EXISTS `achdetail`;
CREATE TABLE `achdetail` (
  `acd_no` mediumint(10) NOT NULL,
  `acd_key` varchar(25) NOT NULL default '',
  `acd_icode1` varchar(10) NOT NULL default '',
  `acd_icode2` int(5) NOT NULL default '1',
  `acd_month` int(2) NOT NULL default '0',
  `acd_year` int(4) NOT NULL default '0',
  `acd_dcode1` varchar(10) NOT NULL default '',
  `acd_dcode2` int(5) NOT NULL default '1',
  `acd_rank` int(2) NOT NULL default '1',
  `acd_share` decimal(14,2) NOT NULL default '0.00',
  `acd_sharevalue` decimal(14,2) NOT NULL default '0.00',
  `acd_bonus` decimal(14,2) NOT NULL default '0.00',
  PRIMARY KEY  (`acd_no`)
) TYPE=InnoDB;

Thank you for the bug report. Duplicate of bug:
http://bugs.mysql.com/bug.php?id=14515 and already fixed on release:

c:\mysql\bin>mysqladmin -uroot create dbx

c:\mysql\bin>mysql -uroot dbx < c:\tmp\achdetail.sql

c:\mysql\bin>mysqldump --create-options --compatible=mysql40 dbx
-- MySQL dump 10.9
--
-- Host: localhost    Database: dbx
-- ------------------------------------------------------
-- Server version       4.1.21-community-nt
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,MYSQL40' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `achdetail`
--

DROP TABLE IF EXISTS `achdetail`;
CREATE TABLE `achdetail` (
  `acd_no` mediumint(10) NOT NULL auto_increment,
  `acd_key` varchar(25) NOT NULL default '',
  `acd_icode1` varchar(10) NOT NULL default '',
  `acd_icode2` int(5) NOT NULL default '1',
  `acd_month` int(2) NOT NULL default '0',
  `acd_year` int(4) NOT NULL default '0',
  `acd_dcode1` varchar(10) NOT NULL default '',
  `acd_dcode2` int(5) NOT NULL default '1',
  `acd_rank` int(2) NOT NULL default '1',
  `acd_share` decimal(14,2) NOT NULL default '0.00',
  `acd_sharevalue` decimal(14,2) NOT NULL default '0.00',
  `acd_bonus` decimal(14,2) NOT NULL default '0.00',
  PRIMARY KEY  (`acd_no`)
) TYPE=InnoDB;