MySQL Bugs: #69669: We need an AUTO_INCREMENT ignoring option

Bug #69669	We need an AUTO_INCREMENT ignoring option
Submitted:	4 Jul 2013 8:52	Modified:	26 Sep 2013 18:19
Reporter:	曾 zeng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.3.2,1.3.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
# Comparing skyg_base.base_user_auto_id to skyg_base.base_user_auto_id [FAIL] 
 # Object definitions differ. (--changes-for=server1) 
 # 

 @@ -1,4 +1,4 @@ 
 CREATE TABLE `base_user_auto_id` ( 
 `user_auto_id` int(11) NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY (`user_auto_id`) 
 -) ENGINE=InnoDB AUTO_INCREMENT=1010 DEFAULT CHARSET=utf8 
 +) ENGINE=InnoDB AUTO_INCREMENT=5362 DEFAULT CHARSET=utf8 

you see,
The mysqldiff checked AUTO_INCREMENT option ,but usually we need ignore it. 

so ,we need a  AUTO_INCREMENT ignoring  option;

if not,this tool can only used in replication servers;

How to repeat:
Campare two tables which have different AUTO_INCREMENT number using mysqldiff

Hello Edward,

Thank you for the bug report. 
Verified as described on recent 1.3.3 version.

Thanks,
Umesh

// How to repeat:

// db1

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> use db1;
Database changed
mysql>
mysql> drop table if exists keyvalue;
ERROR 1051 (42S02): Unknown table 'keyvalue'
mysql> CREATE TABLE `db1`.`keyvalue` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=Innodb;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into `keyvalue` values (@id:=@id+1),(@id:=@id+1),(@id:=@id+1),(@id:=@id+1);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  insert into `keyvalue`(`id`)
    ->    select @id:=@id+1 from
    ->     `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9,
    -> `keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit
    -> 50000000;
Query OK, 50000000 rows affected, 2 warnings (7 min 4.98 sec)
Records: 50000000  Duplicates: 0  Warnings: 0

mysql> show create table keyvalue\G
*************************** 1. row ***************************
       Table: keyvalue
Create Table: CREATE TABLE `keyvalue` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50000005 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

// db2

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use db2;
Database changed
mysql>
mysql> drop table if exists keyvalue;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `db2`.`keyvalue` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=Innodb;
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into `keyvalue` values (@id:=@id+1),(@id:=@id+1),(@id:=@id+1),(@id:=@id+1);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  insert into `keyvalue`(`id`)
    ->    select @id:=@id+1 from
    ->     `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9,
    -> `keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit
    -> 10000000;
Query OK, 10000000 rows affected, 2 warnings (2 min 7.86 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql>  show create table keyvalue\G
*************************** 1. row ***************************
       Table: keyvalue
Create Table: CREATE TABLE `keyvalue` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000005 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

// 1.3.2

[ushastry@cluster-repo mysql-utilities-1.3.2]$ scripts/mysqldiff.py --server1=root@localhost db1.keyvalue:db2.keyvalue
# server1 on localhost: ... connected.
# Comparing db1.keyvalue to db2.keyvalue                           [PASS]
# WARNING: The tables structure is the same, but the order of some definitions is different. Use --change-for to take the order into account.
Success. All objects are the same.

//  When --difftype=context - Compare failed

[ushastry@cluster-repo mysql-utilities-1.3.2]$ scripts/mysqldiff.py --server1=root@localhost db1.keyvalue:db2.keyvalue --changes-for=server2 --difftype=context
# server1 on localhost: ... connected.
# Comparing db1.keyvalue to db2.keyvalue                           [FAIL]
# Object definitions differ. (--changes-for=server2)
#

*** db2.keyvalue
--- db1.keyvalue
***************
*** 1,4 ****
  CREATE TABLE `keyvalue` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
! ) ENGINE=InnoDB AUTO_INCREMENT=10000005 DEFAULT CHARSET=latin1
--- 1,4 ----
  CREATE TABLE `keyvalue` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
! ) ENGINE=InnoDB AUTO_INCREMENT=50000005 DEFAULT CHARSET=latin1
Compare failed. One or more differences found.

//  When --difftype=sql - Compare failed

[ushastry@cluster-repo mysql-utilities-1.3.2]$ scripts/mysqldiff.py --server1=root@localhost db1.keyvalue:db2.keyvalue --changes-for=server2 --difftype=sql
# server1 on localhost: ... connected.
# Comparing db1.keyvalue to db2.keyvalue                           [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `db2`.`keyvalue`
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`id`),
AUTO_INCREMENT=50000005;

Compare failed. One or more differences found.

// Compare failed

[ushastry@cluster-repo mysql-utilities-1.3.2]$ scripts/mysqldiff.py --server1=root@localhost db1.keyvalue:db2.keyvalue --changes-for=server2
# server1 on localhost: ... connected.
# Comparing db1.keyvalue to db2.keyvalue                           [FAIL]
# Object definitions differ. (--changes-for=server2)
#

--- db2.keyvalue
+++ db1.keyvalue
@@ -1,4 +1,4 @@
 CREATE TABLE `keyvalue` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=10000005 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=50000005 DEFAULT CHARSET=latin1
Compare failed. One or more differences found.

// 1.3.3

[ushastry@cluster-repo mysql-utilities-1.3.3]$ scripts/mysqldiff.py --server1=root@localhost db1.keyvalue:db2.keyvalue --changes-for=server2 --difftype=context
# server1 on localhost: ... connected.
# Comparing db1.keyvalue to db2.keyvalue                           [FAIL]
# Object definitions differ. (--changes-for=server2)
#

*** db2.keyvalue
--- db1.keyvalue
***************
*** 1,4 ****
  CREATE TABLE `keyvalue` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
! ) ENGINE=InnoDB AUTO_INCREMENT=10000005 DEFAULT CHARSET=latin1
--- 1,4 ----
  CREATE TABLE `keyvalue` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
! ) ENGINE=InnoDB AUTO_INCREMENT=50000005 DEFAULT CHARSET=latin1
Compare failed. One or more differences found.
[ushastry@cluster-repo mysql-utilities-1.3.3]$ scripts/mysqldiff.py --server1=root@localhost db1.keyvalue:db2.keyvalue --changes-for=server2 --difftype=sql
# server1 on localhost: ... connected.
# Comparing db1.keyvalue to db2.keyvalue                           [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `db2`.`keyvalue`
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`id`),
AUTO_INCREMENT=50000005;

Compare failed. One or more differences found.
[ushastry@cluster-repo mysql-utilities-1.3.3]$ scripts/mysqldiff.py --server1=root@localhost db1.keyvalue:db2.keyvalue --changes-for=server2
# server1 on localhost: ... connected.
# Comparing db1.keyvalue to db2.keyvalue                           [FAIL]
# Object definitions differ. (--changes-for=server2)
#

--- db2.keyvalue
+++ db1.keyvalue
@@ -1,4 +1,4 @@
 CREATE TABLE `keyvalue` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=10000005 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=50000005 DEFAULT CHARSET=latin1
Compare failed. One or more differences found.

Fixed in release-1.3.5

Fixed as of MySQL Utilities 1.3.5, and here's the changelog entry:

The "mysqldiff" utility now has a new "--skip-table-options" option to
ignore the differences between all table options, such as AUTO_INCREMENT,
ENGINE, CHARSET, etc.). A warning is issued if the "--skip-table-options"
option is used and table option differences are found.

Thank you for the bug report.