MySQL Bugs: #16585: mysqldump not setting auto

Bug #16585	mysqldump not setting auto_increment
Submitted:	17 Jan 2006 22:00	Modified:	18 Jan 2006 11:03
Reporter:	Marc Hale	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	MySql 4	OS:	Windows (Win2003)
Assigned to:		CPU Architecture:	Any

Description:
when reverting back to MySql 4 using mysqldump all worked great accept none of the auto_increment fields were set back to auto_increment. How can i fix this. I have to go back to MySql 4 because Coldfusion MX7 is not compatible with MySql 5 and JDBC Per Macromedia.

mhale@mjksw.com

Thanks
Marc Hale

How to repeat:
mysqldump of database then restore it with database that has auto_increment set

Sorry I don't understand well which bug are you reporting:

Do you have a server 5.0.XX and want to create a dump for to be
restored on server 4.??.??.
If yes please read the Manual regarding the mysqldump option:

c:\mysql\bin>mysqldump --help
mysqldump  Ver 10.10 Distrib 5.0.18, for Win32 (ia32)

<cut>

  --compatible=name   Change the dump to be compatible with a given mode. By
                      default tables are dumped in a format optimized for
                      MySQL. Legal modes are: ansi, mysql323, mysql40,
                      postgresql, oracle, mssql, db2, maxdb, no_key_options,
                      no_table_options, no_field_options. One can use several
                      modes separated by commas. Note: Requires MySQL server
                      version 4.1.0 or higher. This option is ignored with
                      earlier server versions.

Thanks in advance.

Yes that is exactly whay i'm doing i have the dump working but the auto_increment is not being added

This is the table on MySql 5

CREATE TABLE `account` (
  `AccountID` int(11) NOT NULL auto_increment,
  `Balance` double(15,2) NOT NULL default '0.00',
  `IsCredit` tinyint(4) NOT NULL default '0',
  `CreditAppID` int(11) default NULL,
  `shopper_id` int(11) default NULL,
  PRIMARY KEY  (`AccountID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

this is what the dump creates (notice no auto_increment)
--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `AccountID` int(11) NOT NULL,
  `Balance` double(15,2) NOT NULL,
  `IsCredit` tinyint(4) NOT NULL,
  `CreditAppID` int(11) default NULL,
  `shopper_id` int(11) default NULL,
  PRIMARY KEY  (`AccountID`)
) TYPE=MyISAM;

Please see the --opt option below:

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

c:\mysql\bin>mysql -uroot db9
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `account` (
    ->   `AccountID` int(11) NOT NULL auto_increment,
    ->   `Balance` double(15,2) NOT NULL default '0.00',
    ->   `IsCredit` tinyint(4) NOT NULL default '0',
    ->   `CreditAppID` int(11) default NULL,
    ->   `shopper_id` int(11) default NULL,
    ->   PRIMARY KEY  (`AccountID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

mysql> exit
Bye

c:\mysql\bin>mysqldump -uroot --opt db9
-- MySQL dump 10.10
--
-- Host: localhost    Database: db9
-- ------------------------------------------------------
-- Server version       5.0.18-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `AccountID` int(11) NOT NULL auto_increment,
  `Balance` double(15,2) NOT NULL default '0.00',
  `IsCredit` tinyint(4) NOT NULL default '0',
  `CreditAppID` int(11) default NULL,
  `shopper_id` int(11) default NULL,
  PRIMARY KEY  (`AccountID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `account`
--

/*!40000 ALTER TABLE `account` DISABLE KEYS */;
LOCK TABLES `account` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `account` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

but if you put in --compatible=mysql40 it does not work

this is what i'm using

mysqldump -uroot --opt --create-options --port=3306 --password=?? --compatible=mysql40 trainingdata > s:/dump_file.sql

if i remove the compatible then the auto_increment is there

-- MySQL dump 10.9
--
-- Host: localhost    Database: trainingdata
-- ------------------------------------------------------
-- Server version	5.0.18-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 `account`
--

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `AccountID` int(11) NOT NULL,
  `Balance` double(15,2) NOT NULL,
  `IsCredit` tinyint(4) NOT NULL,
  `CreditAppID` int(11) default NULL,
  `shopper_id` int(11) default NULL,
  PRIMARY KEY  (`AccountID`)
) TYPE=MyISAM;

Thank you for the feedback. Indeed mysqldump with --compatible option
behaves as reported. This behavior was already reported and verified:

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

Thank you for the bug report.

so its still not fixed is what you are saying!

Yes it still not fixed. When the development team will begin to work on
you will see in progress status, patch pending, documenting and closed
are the steps when the fix will be introduced.