MySQL Bugs: #89050: while executing mysqldbcompare utility

Bug #89050	while executing mysqldbcompare utility
Submitted:	25 Dec 2017 18:38	Modified:	1 Jan 2018 12:40
Reporter:	SUYOG KALAMBATE	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Utilities	Severity:	S3 (Non-critical)
Version:	1.6.5	OS:	CentOS (6.5)
Assigned to:		CPU Architecture:	Any
Tags:	while running mysqldbcompare utilities python errors through.

Description:
I have mysql5.7 server . following rpm packages has been installed on Centos6.5 server.
mysql-community-server-5.7.18-1.el6.i686
mysql-community-common-5.7.18-1.el6.i686
mysql-utilities-1.6.5-1.el6.noarch
mysql-community-libs-5.7.18-1.el6.i686
mysql-shell-1.0.11-1.el6.i686
mysql-community-client-5.7.18-1.el6.i686
mysql-connector-python-2.1.7-1.el6.i686
mysql-community-libs-compat-5.7.18-1.el6.i686

How to repeat:
while using mysqldbcompare utility below errors through-:

Error-:

File "/usr/bin/mysqldbcompare", line 299, in <module>
    servers = connect_servers(server1_values, server2_values, conn_opts)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/server.py", line 489, in connect_servers
    source = get_server(src_name, src_dict, quiet, verbose=verbose)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/server.py", line 336, in get_server
    server_conn.connect()
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/server.py", line 1098, in connect
    self.db_conn = self.get_connection()
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/server.py", line 1178, in get_connection
    db_conn = mysql.connector.connect(**parameters)
  File "/usr/lib/python2.6/site-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python2.6/site-packages/mysql/connector/abstracts.py", line 728, in connect
    self._open_connection()
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 228, in _open_connection
    self._ssl)
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 150, in _do_auth
    ssl_options.get('cipher'))
  File "/usr/lib/python2.6/site-packages/mysql/connector/network.py", line 420, in switch_to_ssl
    ssl_version=ssl.PROTOCOL_TLSv1, ciphers=cipher)
TypeError: wrap_socket() got an unexpected keyword argument 'ciphers'

Which Python and OpenSSL versions do you have installed?

mysql connector version -: mysql-connector-python-2.1.7-1.el6.i686
openssl version-: openssl-1.0.1e-57.el6.i686

Let us take a look at the utility in action. Below are two examples of the utility comparing what should be the same database on two servers. I am using a simple detail shop inventory database used to manage supplies. It consists of two tables (supplier, supplies) and three views (cleaning, finishing_up, and tools). 

In the first example, we see an example where the databases are consistent. When you examine the output, you will see each object is inspected in three passes. First, the object definitions are compared. Any discrepancies would be displayed as a difference in their CREATE statements. If the object is a table, a row count test is performed followed by a comparison of the data. Surely, if the row count test fails we know the data check will fail. 

Note: This is the same output (and indeed the same code) that is used for mysqldiff. The mysqldbcompare utility has all of the same features with respect to difference type presented (unified, differ, and context) which you can select with the same option named ‘--difftype’.

mysqldbcompare --server1=root@localhost --server2=root@backup_host:3310 inventory:inventory
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases inventory on server1 and inventory on server2

Defn Row Data 
Type Object Name Diff Count Check 
--------------------------------------------------------------------------- 
TABLE supplier pass pass pass 
TABLE supplies pass pass pass 
VIEW cleaning pass - - 
VIEW finishing_up pass - - 
VIEW tools pass - - 

Databases are consistent.

# ...done

Normally, the mysqldbcompare utility will stop on the first failed test. This default means you can run the utility as a safeguard on data that you expect to be consistent. However, if you suspect or know there will be differences in the database objects or data and want to run all of the checks, you can use the ‘--run-all-tests’ option. This option will run the tests on all objects even if some tests fail. Note that this does not include system or access errors such as a down server or incorrect login - those errors will cause the utility to fail with an appropriate error message.

In the second example, we expect the databases to be different and we want to know which data is different. As you can see, the utility found differences in the object definitions as well as differences in the data. Both are reported. 

mysqldbcompare --server1=root@localhost --server2=root@backup_host:3310 inventory:inventory --run-all-tests
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases inventory on server1 and inventory on server2

WARNING: Objects in server1:inventory but not in server2:inventory:
VIEW: finishing_up
VIEW: cleaning

Defn Row Data 
Type Object Name Diff Count Check 
--------------------------------------------------------------------------- 
TABLE supplier pass FAIL FAIL 

Row counts are not the same among inventory.supplier and inventory.supplier.

Data differences found among rows:
--- inventory.supplier
+++ inventory.supplier
@@ -1,2 +1,2 @@
code,name
-2,Never Enough Inc.
+2,Wesayso Corporation

Rows in inventory.supplier not in inventory.supplier
code,name
3,Never Enough Inc.

TABLE supplies pass FAIL FAIL 

Row counts are not the same among inventory.supplies and inventory.supplies.

Data differences found among rows:
--- inventory.supplies
+++ inventory.supplies
@@ -1,4 +1,4 @@
stock_number,description,qty,cost,type,notes,supplier
-11040,Leather care,1,9.99,other,,1
-11186,Plastic polish,1,9.99,polishing,,1
-11146,Speed shine,1,9.99,repair,,1
+11040,Leather care,1,10.00,other,,1
+11186,Plastic polish,1,10.00,polishing,,1
+11146,Speed shine,1,10.00,repair,,1

Rows in inventory.supplies not in inventory.supplies
stock_number,description,qty,cost,type,notes,supplier
11104,Interior cleaner,1,9.99,cleaning,,1
11056,Microfiber and foam pad cleaner,1,9.99,cleaning,,1
11136,Rubber cleaner,1,9.99,cleaning,,1
11173,Vinyl and rubber dressing,1,9.99,cleaning,,1
11106,Wheel cleaner,1,9.99,cleaning,,1
11270,Carpet cleaner,1,9.99,cleaning,,1

Rows in inventory.supplies not in inventory.supplies
stock_number,description,qty,cost,type,notes,supplier
11269,Microfiber spray on car wash towel,3,16.99,cleaning,,1
11116,Microfiber wax removal towel,3,16.99,waxing,,1
10665,Glass polish pad,3,10.00,polishing,,1

VIEW tools FAIL - - 

--- inventory.tools
+++ inventory.tools
@@ -1,1 +1,1 @@
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `inventory`.`tools` AS select `inventory`.`supplies`.`stock_number` AS `stock_number`,`inventory`.`supplies`.`description` AS `description`,`inventory`.`supplies`.`qty` AS `qty`,`inventory`.`supplies`.`cost` AS `cost`,`inventory`.`supplies`.`type` AS `type`,`inventory`.`supplies`.`notes` AS `notes`,`inventory`.`supplies`.`supplier` AS `supplier` from `inventory`.`supplies` where (`inventory`.`supplies`.`type` = 'tool')
+CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `inventory`.`tools` AS select `inventory`.`supplies`.`stock_number` AS `stock_number`,`inventory`.`supplies`.`description` AS `description`,`inventory`.`supplies`.`qty` AS `qty`,`inventory`.`supplies`.`cost` AS `cost`,`inventory`.`supplies`.`type` AS `type`,`inventory`.`supplies`.`notes` AS `notes`,`inventory`.`supplies`.`supplier` AS `supplier` from `inventory`.`supplies` where (`inventory`.`supplies`.`type` in ('tool','other'))

Database consistency check failed.

Take a moment to read through the report above. At the top of the report (the first object tested), we see a critical error in the suppliers table. Here we can see that there are two different names for the same supplier_id. All relational database theory aside, that could spell trouble when it comes time to reorder supplies.

Notice the report for the supplies table. In this example, the utility identified three rows that were different among the two databases. It also identified rows that were missing from either table. Clearly, that could help you diagnose what went wrong where in your application (or your data entry).

Lastly, we see a possible issue with the tools view. Here the view definition differs slightly. Depending the use of the view this may be acceptable but it is nice to know nonetheless.
for more information check http://www.sumra.in

Hello Suyog,

Thank you for report.

Thanks,
Umesh

We are using AWS RDS MySQL version 5.6.10. I have installed mysql-utilities from https://github.com/mysql/mysql-utilities via the manual install method (python2 setup.py install) and the mysql-connector-python package from pip (pip install --user mysql-connector-python). We are trying to see the diff between the 2 databases

Since I am using Python2 and by extension, pip from Homebrew, I had to modify the script a little bit (using /usr/local/bin/python2 rather than /usr/bin/python).

For mysqldiff I am running the command:

mysqldiff \
  --server1='[username1]:[password1]@[servername1].rds.amazonaws.com:3306' \
  --server2='[username2]:[password2]@[servername2].rds.amazonaws.com:3306' \
  --difftype=differ \
  [databasename1]:[databasename2]

For mysqldbcompare, I am running the command:

mysqldbcompare \
  --server1='[username1]:[password1]@[servername1].rds.amazonaws.com:3306' \
  --server2='[username2]:[password2]@[servername2].rds.amazonaws.com:3306' \
  --difftype=differ \
  [databasename1]:[databasename2]

However, I am getting the following errors in https://fullinformation.net

# WARNING: Using a password on the command line interface can be insecure.
Traceback (most recent call last):
  File "./src/mysqldbcompare", line 299, in <module>
    servers = connect_servers(server1_values, server2_values, conn_opts)
  File "/usr/local/lib/python2.7/site-packages/mysql/utilities/common/server.py", line 489, in connect_servers
    source = get_server(src_name, src_dict, quiet, verbose=verbose)
  File "/usr/local/lib/python2.7/site-packages/mysql/utilities/common/server.py", line 336, in get_server
    server_conn.connect()
  File "/usr/local/lib/python2.7/site-packages/mysql/utilities/common/server.py", line 1104, in connect
    res = self.show_server_variable('character_set_client')
  File "/usr/local/lib/python2.7/site-packages/mysql/utilities/common/server.py", line 1391, in show_server_variable
    return self.exec_query("SHOW VARIABLES LIKE '%s'" % variable)
  File "/usr/local/lib/python2.7/site-packages/mysql/utilities/common/server.py", line 1327, in exec_query
    cur.close()
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/cursor.py", line 395, in close
    self._connection.handle_unread_result()
  File "/usr/local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 614, in handle_unread_result
    raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

I tried manually connecting via mysql --host='[servername].rds.amazonaws.com' --user=[username] --password='[password]' --port=3306 and I was able to connect with no errors.