Bug #80691 mysqldiff cannot set wait_timeout
Submitted: 10 Mar 2016 15:20 Modified: 15 Aug 2016 15:27
Reporter: Jeremy Tinley Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Utilities Severity:S3 (Non-critical)
Version:1.5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldiff

[10 Mar 2016 15:20] Jeremy Tinley
Description:
On MySQL servers where wait_timeout is set low (6 seconds), mysqldiff will get kicked off before it can complete on large lists of table comparisons.

Example:

# server1 on server1.example.com: ... connected.
# server2 on server2.example.com: ... connected.
Traceback (most recent call last):
  File "/usr/bin/mysqldiff", line 245, in <module>
    db1, db2, options)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/command/diff.py", line 124, in database_diff
    db1, db2, True, options)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/dbcompare.py", line 290, in get_common_objects
    db2_objects = _get_objects(server2, db2, options)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/dbcompare.py", line 131, in _get_objects
    if not db_obj.exists():
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/database.py", line 296, in exists
    res = server.exec_query(_QUERY % db)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/server.py", line 1239, in exec_query
    cursor_class=MySQLUtilsCursorBufferedRaw)
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 807, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

How to repeat:
Create a database and a few thousand tables in server1, repeat for server2.

Set the server's wait_timeout and idle_timeout to 6 seconds.

Run a diff:
mysqldiff --server1=DSN --server2=DSN db1:db2 --changes-for=server2

Observe that the connection fails in mid-comparison.

Suggested fix:
Allow a flag to pass attributes to the server, perhaps --serverN-options. This would let me set

--server1-options=wait_timeout=300
--server1-options=idle_timeout=300
--server2-options=wait_timeout=300
--server2-options=idle_timeout=300
[11 Mar 2016 12:23] Umesh Shastry
Hello Jeremy,

Thank you for the report.

Thanks,
Umesh
[11 Mar 2016 12:24] Umesh Shastry
--

[root@cluster-repo ~]# mysqldiff --version
MySQL Utilities mysqldiff version 1.5.6
License type: GPLv2

-- Create db1 schema+5K tables on server1/server2

 use test;
 DELIMITER //
 DROP PROCEDURE ct//
 CREATE PROCEDURE ct(IN tblCount INT, IN tblSchema char(10))
 BEGIN
 
	DECLARE v1 INT DEFAULT tblCount;
   	SET @s = CONCAT('CREATE DATABASE IF NOT EXISTS ', tblSchema);
	SELECT @s;
	PREPARE stm FROM @s;
	EXECUTE stm;
	
	WHILE v1 > 0 DO
		SET @s = CONCAT('DROP TABLE IF EXISTS ', tblSchema,'.','t',v1);
		SELECT @s;
		PREPARE stm FROM @s;
		EXECUTE stm;
		SET @s = CONCAT('CREATE TABLE ', tblSchema,'.','t',v1,' (id int not null)');
		SELECT @s;
		PREPARE stm FROM @s;
		EXECUTE stm;
		SET v1 = v1 - 1;
   END WHILE;

 END //
 DELIMITER ;

 call ct(5000,'db1');

 -- Make sure Server1/Server2 has
 
 mysql> set global wait_timeout=6;
 mysql> set global interactive_timeout=6;

 
 ##
 
 [root@cluster-repo ~]# mysqldiff --server1=root@localhost  --server2=ushastry@x.x.x.x:15000 --force db1:db1 --changes-for=server2
# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on x.x.x.x: ... connected.
Traceback (most recent call last):
  File "/usr/bin/mysqldiff", line 245, in <module>
    db1, db2, options)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/command/diff.py", line 124, in database_diff
    db1, db2, True, options)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/dbcompare.py", line 290, in get_common_objects
    db2_objects = _get_objects(server2, db2, options)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/dbcompare.py", line 131, in _get_objects
    if not db_obj.exists():
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/database.py", line 296, in exists
    res = server.exec_query(_QUERY % db)
  File "/usr/lib/python2.6/site-packages/mysql/utilities/common/server.py", line 1239, in exec_query
    cursor_class=MySQLUtilsCursorBufferedRaw)
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 807, in cursor
    raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.
[15 Aug 2016 13:58] Chuck Bell
Posted by developer:
 
Please elaborate on the timeout values mentioned. For example, there exists a wait_timeout and a connection_timeout but no idle_timeout. What is idle_timeout?
[15 Aug 2016 13:58] Chuck Bell
Posted by developer:
 
Please elaborate on the timeout values mentioned. For example, there exists a wait_timeout and a connection_timeout but no idle_timeout. What is idle_timeout?
[15 Aug 2016 15:27] Jeremy Tinley
interactive_timeout and wait_timeout

Umesh got the right reproduce path.