MySQL Bugs: #80691: mysqldiff cannot set wait

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

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

Hello Jeremy,

Thank you for the report.

Thanks,
Umesh

--

[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.

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?

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?

interactive_timeout and wait_timeout

Umesh got the right reproduce path.