Bug #14515 mysqldump omits 'auto_increment' in MySQL4.0 compatibility mode
Submitted: 31 Oct 2005 16:38 Modified: 28 Jul 2006 20:42
Reporter: Marco Vervoort Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:4.1.15/BK 5.0 OS:Linux (Linux)
Assigned to: Magnus Blåudd CPU Architecture:Any

[31 Oct 2005 16:38] Marco Vervoort
Description:
When dumping the structure of a table using 'mysqldump --no-data --compatible=mysql40'
auto_incrementing primary keys are not marked as auto_incrementing.

I get

CREATE TABLE `ganky_articles` (
  `articleId` int(10) unsigned NOT NULL,
[other fields omitted]
  PRIMARY KEY  (`articleId`)
) TYPE=MyISAM;

while with 'mysqldump --no-data' I get (correctly)

CREATE TABLE `ganky_articles` (
  `articleId` int(10) unsigned NOT NULL auto_increment,
[other fields omitted]
  PRIMARY KEY  (`articleId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Server version is 4.1.15.

How to repeat:
$ mysql ganky
mysql > CREATE TABLE test ( `testId` int unsigned NOT NULL auto_increment,  PRIMARY KEY (`testId`) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.18 sec)
 mysql>quit
Bye
$ mysqldump --no-data ganky test
[comments omitted]
--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `testId` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`testId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

$ mysqldump --no-data--compatible=mysql40  ganky test
[comments omitted]
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `testId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`testId`)
) TYPE=MyISAM;
[31 Oct 2005 17:29] MySQL Verification Team
miguel@hegel:~/dbs/5.0> bin/mysqldump --no-data db1 -uroot
-- MySQL dump 10.10
--
-- Host: localhost    Database: db1
-- ------------------------------------------------------
-- Server version       5.0.16-debug

/*!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 `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `testId` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`testId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!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 */;

miguel@hegel:~/dbs/5.0> bin/mysqldump --no-data --compatible=mysql40  db1 -uroot
-- MySQL dump 10.10
--
-- Host: localhost    Database: db1
-- ------------------------------------------------------
-- Server version       5.0.16-debug
/*!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,MYSQL40' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `testId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`testId`)
) TYPE=MyISAM;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
[29 Nov 2005 19:18] Matthew Lord
row_format is also retained when using compatible=mysql40 when it should not be:

$mysqldump -u root -p1942c00m --compatible=mysql40 test t1
-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.16-nightly-20051105-standard-log
/*!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,MYSQL40' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL,
  `name` char(10) default NULL,
  PRIMARY KEY  (`id`)
) TYPE=InnoDB ROW_FORMAT=COMPRESSED;
[16 Dec 2005 18:31] Jim Winstead
See also Bug #15208.
[18 Jan 2006 11:49] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=16585 is marked as duplicate
of this bug.
[21 Jan 2006 16:51] Lukas Smith
I am seeing the same issue when I do:

SET SQL_MODE="ANSI";
DROP TABLE IF EXISTS `autoinc`;
CREATE TABLE `autoinc` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) AUTO_INCREMENT=1;
SHOW CREATE TABLE `autoinc`;
SET SQL_MODE="MYSQL40";
SHOW CREATE TABLE `autoinc`;
[21 Jan 2006 16:52] Lukas Smith
Note I am using mysql 5.0.16 on windows.
[27 Jan 2006 11:09] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=16828 was marked as duplicate of this
bug.
[11 Feb 2006 9:19] Magnus Olstad Hansen
I'm using 5.0.18 on windows and just realized I'm lacking auto_increment in my dumps too. So the issue is still here.

In my case this is very unfortunate since I frequently use mysqldump to transfer db's I've worked on which have changed their structure. The workaround of dumping structure and data separate and edit the structure by hand would be abit tedious. 

I read the other bugs referred to in this one, and in one of them it sounded like auto_increment not being included was a choice made by the development team. Does that mean this not considered a bug and it will not be fixed?
[12 Feb 2006 3:16] Nick Gaugler
They problem is due to NO_FIELD_OPTIONS being set for MYSQL323 and MYSQL40 modes.  In my mind, MYSQL323 and MYSQL40 should allow auto_increment to be added to the show create table since it was available in both versions. There's a few ways to tackle this.  I have come up with two and supplied the changes below.

The first option, and the easiest, is to make it so MYSQL323 and MYSQL40 allow auto_increment.  The patch is available here, as it is too large to paste:  http://mybox.ngworld.net/~nickg/mysql/mysql323-4-keepai-patch.txt

The second solution would be to add an option KEEP_AUTO_INCREMENT, then make that show up under MYSQL323 and MySQL40.  I left the Docs/man edits up to you guys on this one.  This doesn't compiled properly for me, even though it should.  The compiler complains that within sql/sql_show.cc the changes are a "comparison is always 1."  Which it is not.  If I use something other than MODE_KEEP_AUTO_INCREMENT it compiles fine (but does not work obviously) and if I do the logic in two separate IFs it works fine as well.  If this is the route that is chosen, perhaps someone internals will have the answer.  The patch is provided here: http://mybox.ngworld.net/~nickg/mysql/keepai-patch.txt
[16 Feb 2006 18:18] Greg Fortune
Still present in 5.0.18
[10 Mar 2006 16:31] [ name withheld ]
Another solution -- use this simple php script:

<?
$hostname = "localhost";
$username = "root";
$password = "";
$dbName = "yourlocalbd";
mysql_connect($hostname,$username,$password) or die("Can't create connection");
$res1 = mysql_query("SHOW TABLES FROM $dbName");
$i=0;
$fp = fopen ("ai_fix.sql", "w");
while($row1 = mysql_fetch_array($res1)){
	$res2 = mysql_db_query($dbName, "SHOW CREATE TABLE $row1[0]");
	while($row2 = mysql_fetch_array($res2)){
		preg_match("/\s*(\W\w+\W)(.*auto_increment)/", $row2[1], $matches);
		if($matches){
			$i++;
			$str = "ALTER TABLE ".$row2[0]." CHANGE ".$matches[1]." ".$matches[1]."".$matches[2].";\n";
			fwrite($fp, $str);
		}
	}
	mysql_free_result($res2);
}
fclose($fp);
mysql_free_result($res1);
mysql_close();
echo "Entries created: ".$i;
?>

It makes a little sql script, which heeds to apply after the main dump.
[10 Mar 2006 16:32] [ name withheld ]
Another solution -- use this simple php script:

<?
$hostname = "localhost";
$username = "root";
$password = "";
$dbName = "yourlocalbd";
mysql_connect($hostname,$username,$password) or die("Can't create connection");
$res1 = mysql_query("SHOW TABLES FROM $dbName");
$i=0;
$fp = fopen ("ai_fix.sql", "w");
while($row1 = mysql_fetch_array($res1)){
	$res2 = mysql_db_query($dbName, "SHOW CREATE TABLE $row1[0]");
	while($row2 = mysql_fetch_array($res2)){
		preg_match("/\s*(\W\w+\W)(.*auto_increment)/", $row2[1], $matches);
		if($matches){
			$i++;
			$str = "ALTER TABLE ".$row2[0]." CHANGE ".$matches[1]." ".$matches[1]."".$matches[2].";\n";
			fwrite($fp, $str);
		}
	}
	mysql_free_result($res2);
}
fclose($fp);
mysql_free_result($res1);
mysql_close();
echo "Entries created: ".$i;
?>

It makes a little sql script, which needs to be applied after the the main dump.
[20 Apr 2006 4:18] Karl Vollmer
I ran into this problem as well when running a dump using mysqldump 5.0.18 with --compatible=mysql40 or --compatible=mysql323 it drops off all of the auto_increment fields from the dump. If I remove the --compatible flag it dumps correctly. 

I have used this feature extensivly in the past and need it. I do development on Mysql 5.x for my application and then rely on the --compatible flag to create dumps so I can test it on my 3.23 and 4.0 versions of mysql as my application is currently forced into supporting mysql 3.23 all the way up to mysql 5.1. 

Thanks, 
-Karl Vollmer
Ampache Lead Developer
[30 Apr 2006 12:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5729
[1 May 2006 18:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5762
[12 May 2006 14:06] Jani Tolonen
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html
[29 May 2006 12:10] Jani Tolonen
Fixed in 5.0.23.
[31 May 2006 0:57] Paul DuBois
This bug report contains no information to indicate
what was done. Please specify how you decided
to handle this issue. There's a lot of back and forth
in the bug report, but nothing to indicate was the
final disposition was.  Thanks.
[1 Jun 2006 22:25] Nick Gaugler
I don't think this patch will work.  I haven't tested it, but from what I read on http://lists.mysql.com/commits/5729 I see you are removing MODE_NO_FIELD_OPTIONS from modes MYSQL323 and MYSQL40.  I think that is a problem seeing as how TIMESTAMPs that contain on update CURRENT_TIMESTAMP will now be doing that for MYSQL323 and MYSQL40 since NO_FIELD_OPTIONS is no longer set.  That will cause errors for those users in MYSQL40 and MYSQL323.
[7 Jun 2006 10:57] Domas Mituzas
This appears to be fixed in 4.1.21 (or earlier) too - as it was broken in 4.1 branch as well.
[26 Jul 2006 13:41] Magnus Blåudd
Patch applied that outputs information about "auto_increment" columns when SQL_MODE is "", MYSQL40 or MYSQL323. Howver when SQL_MODE is set to NO_FIELD_OPTIONS "auto_increment""is not printed.

See the patch that applies the testcase http://lists.mysql.com/commits/5762
[28 Jul 2006 20:42] Paul DuBois
Noted in 4.1.20, 5.0.23 changelogs.

SHOW CREATE TABLE did not display the AUTO_INCREMENT column attribute
if the SQL mode was MYSQL323 or MYSQL40. This also affected mysqldump,
which uses SHOW CREATE TABLE to get table definitions.
[29 Jul 2006 18:09] MySQL Verification Team
Bug http://bugs.mysql.com/bug.php?id=21342 was marked as duplicate of
this one and the fix was introduced in version 4.1.21.
[29 Jul 2006 18:12] Paul DuBois
The 4.1.x fix went into 4.1.21, not 4.1.20.
[17 Aug 2006 13:54] Joff Lee
this doesnt appear to be fixed in 5:

mysqldump  Ver 10.10 Distrib 5.0.24, for pc-linux-gnu (i686)
[21 Aug 2006 12:11] Magnus Blåudd
What is wrong with the output you get?
[23 Aug 2006 11:24] Joff Lee
I dont get the auto_increment when using compatible=mysql40. The exact same as the original bug description.
[23 Aug 2006 15:00] Magnus Blåudd
Check if @@SQL_MODE contains "NO_FIELD_OPTIONS", in that case the Auto_increment infor is not supposed to be printed.

Otherwise please run the ansi.test in mysql-test-run.pl
$> cd mysql-test
$> ./mysql-test-run.pl --do-test=ansi
[30 Aug 2006 17:21] Edwin L. Culp
I'm seeing this on mysql 5.1.11 beta also.  Could this be me or has it not been fixed in 5.1.11?

Thanks,

ed
[30 Aug 2006 17:51] Edwin L. Culp
Please ignore the comment about 5.1.11.  I was looking at the wrong dump file.  Sorry for the noise.
[30 Aug 2006 17:51] Edwin L. Culp
Please ignore the comment about 5.1.11.  I was looking at the wrong dump file.  Sorry for the noise.
[19 Sep 2006 17:22] Timo Maier
I can reproduce this bug with version 5.0.24a.
[21 Sep 2006 8:46] Magnus Blåudd
Sent mail to Timo who answered:
> Shame on me! I have mixed up my servers, to many ssh sessions opened. ): 
>
> This was a 4.0.20 server, after upgrading to 4.0.21 everything is fine. 
> My 5.0.24 server also. Sorry for the confusion. Can you remove 
> my "bugreport"?
[12 Jan 2008 21:09] Dan Horning
Batch repair tool for this bug

Attachment: mysql_autoincr_batch_repair.php (application/octet-stream, text), 1.59 KiB.

[12 Jan 2008 21:11] Dan Horning
I've made a few modifications to the script in http://bugs.mysql.com/bug.php?id=14515#c59034

file is attached above

I added full server walks and a filter on the code. I know this could be done better still - but this is a one time fix for me and i hope this helps others out.