MySQL Bugs: #75236: mysqldiff fails to generate a correct ALTER to change DEFAULT NULL to DEFAULT ''

Bug #75236	mysqldiff fails to generate a correct ALTER to change DEFAULT NULL to DEFAULT ''
Submitted:	16 Dec 2014 17:03	Modified:	15 Apr 2015 22:26
Reporter:	Jeremy Tinley	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.5.3	OS:	Linux (rhel6)
Assigned to:		CPU Architecture:	Any
Tags:	mysqldiff

Description:
mysqldiff fails to generate a correct ALTER to change DEFAULT NULL to DEFAULT ''

Generating a --difftype=context gives:

***************
*** 8,13 ****
!   `f7` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
!   `f8` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
--- 8,13 ----
!   `f7` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
!   `f8` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Generating a --difftype=sql gives:

ALTER TABLE `db`.`table` 
  CHANGE COLUMN f7 f7 varchar(64) NOT NULL, 
  CHANGE COLUMN f8 f8 varchar(64) NOT NULL;

Reversing these and generating --changes-for=server2 does not change the ALTER statement generated. It simply appears that handling DEFAULT NULL vs DEFAULT '' cannot be resolved by the code.

How to repeat:
Create a table with a similar structure above and run --difftype=sql. Observe that the ALTER TABLE is unable to resolve the difference.

Suggested fix:
Improve diffing code to generate a proper ALTER.

Hello Jeremy Tinley,
 
Thank you for the report.

Thanks,
Umesh

//

[test]> create database db1;
Query OK, 1 row affected (0.00 sec)

[test]> create database db2;
Query OK, 1 row affected (0.00 sec)

[test]> use db1;
Database changed
[db1]> create table t1(id int not null primary key, `f7` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `f8` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 4 warnings (0.04 sec)

[db1]> use db2;
Database changed
[db2]> create table t1(id int not null primary key, `f7` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `f8` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '')ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected, 4 warnings (0.03 sec)

[db2]> \q

//

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

--- db1.t1
+++ db2.t1
@@ -1,6 +1,6 @@
 CREATE TABLE `t1` (
   `id` int(11) NOT NULL,
-  `f7` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
-  `f8` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
+  `f7` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
+  `f8` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
Compare failed. One or more differences found.
[root@cluster-repo mysql-utilities-1.5.3]#
[root@cluster-repo mysql-utilities-1.5.3]# scripts/mysqldiff.py --server1=root@localhost --server2=root@localhost db1.t1:db2.t1 --difftype=sql
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing db1.t1 to db2.t1                                       [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `db1`.`t1`
  CHANGE COLUMN f8 f8 varchar(64) NOT NULL,
  CHANGE COLUMN f7 f7 varchar(64) NOT NULL;

Compare failed. One or more differences found.
[root@cluster-repo mysql-utilities-1.5.3]# scripts/mysqldiff.py --server1=root@localhost --server2=root@localhost db1.t1:db2.t1 --difftype=sql --changes-for=server2
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing db1.t1 to db2.t1                                       [FAIL]
# Transformation for --changes-for=server2:
#

ALTER TABLE `db2`.`t1`
  CHANGE COLUMN f8 f8 varchar(64) NOT NULL,
  CHANGE COLUMN f7 f7 varchar(64) NOT NULL;

Compare failed. One or more differences found.
[root@cluster-repo mysql-utilities-1.5.3]# scripts/mysqldiff.py --server1=root@localhost --server2=root@localhost db1.t1:db2.t1 --difftype=sql --changes-for=server1
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing db1.t1 to db2.t1                                       [FAIL]
# Transformation for --changes-for=server1:
#

ALTER TABLE `db1`.`t1`
  CHANGE COLUMN f8 f8 varchar(64) NOT NULL,
  CHANGE COLUMN f7 f7 varchar(64) NOT NULL;

Compare failed. One or more differences found.

Posted by developer:
 
Fixed as of the upcoming MySQL Utilities 1.6.2 release, and here's the changelog entry:

The "mysqldiff" failed to generate correct "ALTER" statements that
differentiated between DEFAULT NULL and DEFAULT ''.

Thank you for the bug report.