Bug #77058 mysqldiff: Encoding comments always make databases definitions look different
Submitted: 17 May 2015 1:51 Modified: 4 Aug 2016 19:58
Reporter: Guillaume Huysmans Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities Severity:S2 (Serious)
Version:1.0.3rc1, 1.5.4 OS:Linux (debian-linux-gnu (x86_64))
Assigned to: CPU Architecture:Any
Tags: character set, comment

[17 May 2015 1:51] Guillaume Huysmans
Description:
Comparing an entire database to another one seems impossible...

Software versions:
- mysqld Ver 5.5.43-0+deb7u1
- mysql  Ver 14.14 Distrib 5.5.43
- MySQL Utilities mysqldiff version 1.0.3rc1
- MySQL Utilities mysqldbcompare version 1.0.3rc1

How to repeat:
$ mysqldbcompare --server1=user:pass@localhost --server2=user:pass@localhost bad_diff:bad_diff2
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases bad_diff on server1 and bad_diff2 on server2
#
# Object definitions differ. (--changes-for=server1)
#

--- bad_diff
+++ bad_diff2
@@ -1 +1 @@
-CREATE DATABASE `bad_diff` /*!40100 DEFAULT CHARACTER SET utf8 */
+CREATE DATABASE `bad_diff2` /*!40100 DEFAULT CHARACTER SET utf8 */

ERROR: The object definitions do not match.

# Database consistency check failed.
#
# ...done

Suggested fix:
Don't compare these SQL lines directly.
[17 May 2015 1:53] Guillaume Huysmans
mysqldump -d --compact -u root -p bad_diff

Attachment: bad_diff.sql (text/x-sql), 274 bytes.

[17 May 2015 1:54] Guillaume Huysmans
mysqldump -d --compact -u root -p bad_diff2

Attachment: bad_diff2.sql (text/x-sql), 274 bytes.

[17 May 2015 1:55] Guillaume Huysmans
Replaced the server's version by the utilities' version...
[18 May 2015 9:31] MySQL Verification Team
Hello Guillaume Huysmans,

Thank you for the report.
Please try with  --skip-diff option to ignore the object diff. 
Ref - http://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldbcompare.html#option_mysqldbcompare_...

// with 1.5.4

[root@cluster-repo setups]# mysqldbcompare --version
MySQL Utilities mysqldbcompare version 1.5.4
License type: GPLv2
[root@cluster-repo setups]# mysqldbcompare --server1=root@localhost --server2=root@localhost bad_diff:bad_diff2 --skip-diff
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases bad_diff on server1 and bad_diff2 on server2
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     a                                       SKIP    pass    -
#           - Compare table checksum                                FAIL
#           - Find row differences                                  pass

# Databases are consistent given skip options specified.
#
# ...done

Also, version 1.0.3rc1 is very old and many bugs were fixed since. Please upgrade to current version MySQL Utilities 1.5.4.

Thanks,
Umesh
[21 May 2015 23:07] Guillaume Huysmans
Hello,

Thanks for your fast reply; however, I really want this tool to ignore the difference between schema names! By the way, rows are not important to me since I'm comparing a backup of a production database with a development database: I'd like to enhance my structure locally using MySQL Workbench without having to manually synchronize objects afterwards.

I've upgraded (on another machine but this shouldn't make any difference since it's basically the same script which is running on every platform) to the latest version of this tool and I've got the same message. It really looks like a bug: if this tool doesn't support databases with a different name, this shouldn't be something we have to specify in the command line.

Best regards,
[6 Jun 2015 17:11] Guillaume Huysmans
Still not fixed and without any new reaction.
[9 Jun 2015 10:06] MySQL Verification Team
Thank you for the feedback.

[root@cluster-repo ~]# /usr/bin/mysqldbcompare --server1=root@localhost --server2=root@localhost bad_diff:bad_diff2
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases bad_diff on server1 and bad_diff2 on server2
#
# Object definitions differ. (--changes-for=server1)
#

--- `bad_diff`
+++ `bad_diff2`
@@ -1,1 +1,1 @@
-CREATE DATABASE `bad_diff` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `bad_diff2` /*!40100 DEFAULT CHARACTER SET latin1 */

ERROR: The object definitions do not match.
[4 Aug 2016 19:58] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Utilities 1.6.4 release, and here's the changelog entry:

Removed the "CREATE database" statement from the diff output for mysqldiff
and mysqldbcompare for cases where the database name is the only
difference. That is, the different output should not show a difference
when comparing db1 to db2 when the only difference is the name of the
database. However, the difference is shown for differences in quote usage
and if decorators differ, such as the character set.

Thank you for the bug report.