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: | |
Category: | MySQL Utilities | Severity: | S3 (Non-critical) |
Version: | 1.3.2,1.3.3 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[4 Jul 2013 8:52]
曾 zeng
[5 Jul 2013 15:23]
MySQL Verification Team
Hello Edward, Thank you for the bug report. Verified as described on recent 1.3.3 version. Thanks, Umesh
[5 Jul 2013 15:24]
MySQL Verification Team
// 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.
[4 Sep 2013 15:04]
Chuck Bell
Fixed in release-1.3.5
[26 Sep 2013 18:19]
Philip Olson
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.