MySQL Bugs: #14515: mysqldump omits 'auto_increment' in MySQL4.0 compatibility mode

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

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;

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 */;

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;

See also Bug #15208.

Bug: http://bugs.mysql.com/bug.php?id=16585 is marked as duplicate
of this bug.

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`;

Note I am using mysql 5.0.16 on windows.

Bug: http://bugs.mysql.com/bug.php?id=16828 was marked as duplicate of this
bug.

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?

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

Still present in 5.0.18

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.

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.

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

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

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

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

Fixed in 5.0.23.

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.

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.

This appears to be fixed in 4.1.21 (or earlier) too - as it was broken in 4.1 branch as well.

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

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.

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.

The 4.1.x fix went into 4.1.21, not 4.1.20.

this doesnt appear to be fixed in 5:

mysqldump  Ver 10.10 Distrib 5.0.24, for pc-linux-gnu (i686)

What is wrong with the output you get?

I dont get the auto_increment when using compatible=mysql40. The exact same as the original bug description.

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

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

Please ignore the comment about 5.1.11.  I was looking at the wrong dump file.  Sorry for the noise.

Please ignore the comment about 5.1.11.  I was looking at the wrong dump file.  Sorry for the noise.

I can reproduce this bug with version 5.0.24a.

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"?

Batch repair tool for this bug

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

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.