Description:
I've run into a change between 5.1.9 and 5.1.11 that's causing me problems. mysqldump now includes AUTO_INCREMENT=xxx in the table definition, even when you specify --no-data. This appears to be the new default behavior of SHOW CREATE TABLE, which I assume mysqldump is using under the covers.

I understand why this is useful for the purposes of backing up data, and why it would even be useful in some --no-data cases, but the bummer for me is that I now don't have a way to dump my schema cleanly for development purposes. Instead, I have to hand edit the resulting dump file.

How to repeat:
(1) create a table with an autoincrement column
(2) insert one or more rows into the table
(3) mysqldump --no-data

Suggested fix:
either add an option to not output auto_increment or make it so that it doesn't output when you use --no-data

Verified using last bk sources both on 5.0.23 and 5.1.12 as described by reporter:

create table foo(id int primary key not null auto_increment);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into foo values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> \q
Bye

ssmirnova@shella ~/mysql-5.1b
$bin/mysqldump --no-data test foo
-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.1.12-beta

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

DROP TABLE IF EXISTS `foo`;
CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 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 */;

This is the new code in sql_show.cc

    if(create_info.auto_increment_value > 1)
    {
      packet->append(" AUTO_INCREMENT=", 16);
      end= longlong10_to_str(create_info.auto_increment_value, buff,10);
      packet->append(buff, (uint) (end - buff));
    }

It will output the "AUTO_INCREMENT=nnn" if current auto inc value is greater than 1.

This bug is still present in the current production version: 5.0.37

I also think that when --no-data is used, the AUTO_INCREMENT=XXX value shouldn't be included in the output. Since it isn't possible to get the original AUTO_INCREMENT value that was used when the table was created, the current value seen when mysqldump is used is dependent on the data in the table, and therefore is more a property of the data than of the table itself.

The problem with this is that mysqldump just issues a "SHOW CREATE TABLE" query to get the CREATE TABLE statement. It can't therefore request that the output shouldn't include AUTO_INCREMENT=XXX.

I suppose that mysqldump could filter out the AUTO_INCREMENT=XXX if --no-data is used. (Perhaps another option could be introduced to control whether or not AUTO_INCREMENT=XXX should be omitted.)

Alternatively the output from mysqldump could be filtered:

    sed -e 's/ AUTO_INCREMENT=[0-9]\+//'

Its little things like this (and no limits in procs) that make me want to move postgres.

Thank for the sed one-liner though, it works. I modify my file in place:

sed -i -e 's/ AUTO_INCREMENT=[0-9]\+//' mydump.sql

Proposed fix:

=== modified file 'client/mysqldump.c'
--- client/mysqldump.c  2009-03-24 13:58:52 +0000
+++ client/mysqldump.c  2009-04-14 17:59:42 +0000
@@ -52,6 +52,8 @@
 #include "mysqld_error.h"
 #include "../sql/ha_ndbcluster_tables.h"
 
+#include "regex.h"
+
 /* Exit codes */
 
 #define EX_USAGE 1
@@ -2422,6 +2424,25 @@
 
       row= mysql_fetch_row(result);
 
+         if (opt_no_data) {
+               regex_t re;
+               regmatch_t rem;
+
+               if(0 != regcomp(&re, " AUTO_INCREMENT=[[:digit:]]+", REG_EXTENDED)) {
+                       DBUG_RETURN(1);
+               }
+
+               if (0 == regexec(&re, row[1], (size_t)1, &rem, 0)) {
+                       char newrow[strlen(row[1])];
+                       strncpy(newrow, row[1], (int)rem.rm_so);
+                       newrow[(int)rem.rm_so] = '\0';
+                       sprintf(newrow + (int)rem.rm_so, "%s%s", " ", row[1] + (int)rem.rm_eo);
+                       strcpy(row[1], newrow);
+               }
+
+               regfree(&re);
+         }
+
       fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" :
               "/*!40101 SET @saved_cs_client     = @@character_set_client */;\n"
               "/*!40101 SET character_set_client = utf8 */;\n"

no, please no sed or regex on the output. What would you do for

  CREATE TABLE t1 (v varchar(255) default ' AUTO_INCREMENT=15');

eh ?

Corrected regex:

--- client/mysqldump.c  2009-03-24 13:58:52 +0000
+++ client/mysqldump.c  2009-04-16 10:57:28 +0000
@@ -52,6 +52,8 @@
 #include "mysqld_error.h"
 #include "../sql/ha_ndbcluster_tables.h"
 
+#include "regex.h"
+
 /* Exit codes */
 
 #define EX_USAGE 1
@@ -2422,6 +2424,27 @@
 
       row= mysql_fetch_row(result);
 
+         if (opt_no_data) {
+               regex_t re;
+               regmatch_t rem[2];
+
+               if(0 != regcomp(&re, "\n) ENGINE=[[:alnum:]]+( AUTO_INCREMENT=[[:digit:]]+)", REG_EXTENDED)) {
+                       DBUG_RETURN(1);
+               }
+
+               if (0 == regexec(&re, row[1], (size_t)10, rem, 0)) {
+                       char newrow[strlen(row[1])];
+                       strncpy(newrow, row[1], (int)rem[1].rm_so);
+                       newrow[(int)rem[1].rm_so] = '\0';
+                       sprintf(newrow + (int)rem[1].rm_so, "%s%s", "", row[1] + (int)rem[1].rm_eo);
+                       strcpy(row[1], newrow);
+               } else {
+                       printf("not matched\n");
+               }
+
+               regfree(&re);
+         }
+
       fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" :
               "/*!40101 SET @saved_cs_client     = @@character_set_client */;\n"
               "/*!40101 SET character_set_client = utf8 */;\n"

I agree this is not the best fix, but at least one can use it for now.

sveta, you won't give up, will you ? :)

  CREATE TABLE t1 (v varchar(255) default "\n) ENGINE=foobar AUTO_INCREMENT=15");

or, even, to prevent a new patch with even larger regex:

  CREATE TABLE t1 (v varchar(255) default "
CREATE TABLE `t1` (
  `t1_name` varchar(255) DEFAULT NULL,
  `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`t1_id`),
  KEY `t1_name` (`t1_name`)
) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1
",
  id int(10) unsigned NOT NULL AUTO_INCREMENT primary key,
) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1;

what about this?
mysqldump --no-data --compatible=no_table_options

Bug #30957 was marked as a duplicate of this one.

The suggested
  mysqldump --no-data --compatible=no_table_options 

is not a workaround - it also removes the ENGINE=... statement from the resulting DDL, too.

As noted in the comments to Bug #30957 (and evident from the back-and-forth on regex-based "fixes" to this problem), this is an issue that will have to get addressed server-side so mysqldump can tell the server it doesn't want the AUTO_INCREMENT as part of the table structure.

I'm planning to throw a 5 year birthday party for this bug at the end of the month.  Who wants to come?  Anyone from MySQL/Sun/Oracle?  There will be cake.

I suppose people just use sed, as someone suggested. It works for practical reasons. But I was thinking - if you can eliminate the table options, why can't you *extract* them as well (the ENGINE=MyISAM AUTO_INCREMENT=1234 DEFAULT CHARSET=latin1 string), and then just use a regex to remove the AUTO_INCREMENT value from that string? Just an undocumented idea.

And now there has been cake:

http://www.youtube.com/watch?v=oAiVsbXVP6k

Happy 7th birthday MySQL bug #20786!

Maybe easier patch which simply resets auto increment. If you need it simply apply to some 5.5 version (5.5.28 and up should work)

$ bzr diff client/mysqldump.c 
=== modified file 'client/mysqldump.c'
--- client/mysqldump.c	2013-05-17 13:24:36 +0000
+++ client/mysqldump.c	2013-06-28 19:24:45 +0000
@@ -2688,6 +2688,12 @@
                 is_log_table ? "CREATE TABLE IF NOT EXISTS " : "",
                 row[1]);
       }
+      if (opt_no_data)
+      {
+        fprintf(sql_file,
+                "/*!40112 ALTER TABLE %s AUTO_INCREMENT = 1 */;\n",
+                result_table);
+      }
 
       check_io(sql_file);
       mysql_free_result(result);

Although now, in year 2013, I don't like that this will break backward compatibility for --no-data option: I assume some of users like current behavior. Probably official patch should have separate option for resetting current AUTO_INCREMENT value.

See also bug #28968 and bug #70673

I have two versions of mysqldump on my PC, the most recent came bundled with the MySQL Workbench:

  mysqldump  Ver 10.13 Distrib 5.7.9, for Win64 (x86_64)

Neither version has an option to mute/skip the AUTO_INCREMENT-options emitted on a dump.  I'm making this comment because this bug is now 10 years old, it is currently 2016!  

The most recent comment I saw was in 2012 about some concern with backward compatibility.  The 'easy' answer for this is to make the (new) option default to the pre-existing behaviour; I can't see how that could hold things up?

Anyway sed work-arounds and the like are not simple on Windows without taking effort to source software and/or avoid installing sensitive other tools on the database server.  

Much better imho to just supply an option.

Is it fixed in MySQL 8.0?

https://www.youtube.com/watch?v=oAiVsbXVP6k

Guys, please fix this problem!

Still present in a new decade!

We have the same problem on some installations. I use the workaround via sed

mysqldump --defaults-file=logindata.rc --no-data --add-drop-table -h 127.0.0.1 my_database | sed --expression='s/ AUTO_INCREMENT=[0-9]\+//' | gzip > my_database_structure.sql.gz

I just signed up in order to be able to press the "Affects me" and "Subscribe" buttons here.

What's the current score?