MySQL Bugs: #72178: --skip-table-options does not work when DIFFTYPE=sql

Bug #72178	--skip-table-options does not work when DIFFTYPE=sql
Submitted:	1 Apr 2014 7:30	Modified:	15 Sep 2014 21:13
Reporter:	HBSpy HB	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.3.6, 1.4.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldiff

Description:
--skip-table-options can ignore the table options, but when the DIFFTYPE=sql, it can't WORK.

ALTER TABLE `nms8`.`zzz_role` 
  ADD INDEX somecol (somecol), 
  ADD COLUMN somecol int(10) unsigned NOT NULL COMMENT 'a comment' AFTER name, 
AUTO_INCREMENT=14;

the AUTO_INCREMENT is always here, when I add -d sql

How to repeat:
mysqldiff --server1 *** --server2=*** --skip-table-options

--- nms8.zzz_role
+++ nms8.zzz_role
@@ -1,5 +1,7 @@
 CREATE TABLE `zzz_role` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(64) NOT NULL,
-  PRIMARY KEY (`id`)
+  `somecol` int(10) unsigned NOT NULL COMMENT 'a comment',
+  PRIMARY KEY (`id`),
+  KEY `somecol` (`somecol`)
 )

mysqldiff --server1 *** --server2=*** --skip-table-options -d sql

# server1 on 10.3.19.151: ... connected.
# server2 on 10.3.18.184: ... connected.
# Comparing nms8.zzz_role to nms8.zzz_role                         [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `nms8`.`zzz_role` 
  ADD INDEX somecol (somecol), 
  ADD COLUMN somecol int(10) unsigned NOT NULL COMMENT 'a comment' AFTER name, 
AUTO_INCREMENT=14;

Suggested fix:
a bug ? or a miss

Thank you for the report.

Verified as described. Bug is only repeatable if there are other changes to the table, not only table options.

What version of Utilities are you using? I have tested against 1.4.2 and the code is successfully skipping table options. Here are my results:

mysql> show create table t1.a1 \G
*************************** 1. row ***************************
       Table: a1
Create Table: CREATE TABLE `a1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` char(20) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table t2.a1 \G
*************************** 1. row ***************************
       Table: a1
Create Table: CREATE TABLE `a1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` char(20) DEFAULT NULL,
  `c` int(10) unsigned NOT NULL COMMENT 'hello',
  PRIMARY KEY (`a`),
  KEY `bb` (`b`),
  KEY `cc` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

$ python ./scripts/mysqldiff.py --server1=root@localhost:13001 t1.a1:t2.a1 --skip-table-options 
# server1 on localhost: ... connected.
# Comparing t1.a1 to t2.a1                                         [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- t1.a1
+++ t2.a1
@@ -1,5 +1,8 @@
 CREATE TABLE `a1` (
   `a` int(11) NOT NULL AUTO_INCREMENT,
   `b` char(20) DEFAULT NULL,
-  PRIMARY KEY (`a`)
+  `c` int(10) unsigned NOT NULL COMMENT 'hello',
+  PRIMARY KEY (`a`),
+  KEY `bb` (`b`),
+  KEY `cc` (`c`)
 )
Compare failed. One or more differences found.

^^^ No table options here!

$ python ./scripts/mysqldiff.py --server1=root@localhost:13001 t1.a1:t2.a1 
# server1 on localhost: ... connected.
# Comparing t1.a1 to t2.a1                                         [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- t1.a1
+++ t2.a1
@@ -1,5 +1,8 @@
 CREATE TABLE `a1` (
   `a` int(11) NOT NULL AUTO_INCREMENT,
   `b` char(20) DEFAULT NULL,
-  PRIMARY KEY (`a`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+  `c` int(10) unsigned NOT NULL COMMENT 'hello',
+  PRIMARY KEY (`a`),
+  KEY `bb` (`b`),
+  KEY `cc` (`c`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
Compare failed. One or more differences found.

^^^ Ah, there they are. But I didn't tell it to skip them so they should be there.

the bug will repeat when you add the option  "DIFFTYPE=sql"

This bug also surfaces when the column order is different. Normally the tool will issue a warning in these cases, however when --skip-table opts is used in comparing two tables that have different column ordering, the check fails.

FWIW the bug seems to be limited to code within the private method _check_tables_structure in  .../mysql/utilities/common/dbcompare.py.

Lines 416-418 should be replaced with:

    diff = None
    if not options.get('skip_table_opts', False):
        table1_opts = db_1.get_table_options(db1, name1)
        table2_opts = db_2.get_table_options(db2, name2)
        diff = _get_diff(table1_opts, table2_opts, object1, object2, diff_type)

I've tested every edge case I can think if with this code in place and the utility performs as expected.

I'm unable to contribute this code -- would someone with a contributor agreement be willing to submit this and double-check the fix?

Fixed as of the upcoming MySQL Utilities 1.5.2 release, and here's the changelog entry:

The "mysqldiff" utility's "--skip-table-options" option would fail when
comparing two tables with different column ordering, or when also setting
the "--difftype" option to SQL.

Thank you for the bug report.