MySQL Bugs: #80200: msyqldbcopy does not work with MySQL 5.7

Bug #80200	msyqldbcopy does not work with MySQL 5.7
Submitted:	29 Jan 2016 15:33	Modified:	2 Feb 2016 3:46
Reporter:	Nelson Goncalves	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Utilities	Severity:	S2 (Serious)
Version:	1.6.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
BUG#22608528 : mysqlrplsync does not work with mysql5.7

The mysqlrplsync utility is not working with MySQL 5.7 if we use it in
a topology where there is a master. The problem is due to table
locking.

The mysqldbcopy utility locks the tables it needs to copy with a
WRITE lock and afterwards queries the status of the foreign_key_checks system variable
(SHOW VARIABLES LIKE "foreign_key_checks"). That query fails causing the mysqldbcopy to fail.
This happens since SHOW variables LIKE "foreign_key_checks" requires
information from the "session_variables" table. Until MySQL 5.7.6 the
"session_variables" table was on the INFORMATION_SCHEMA database but
has since moved to PERFORMANCE_SCHEMA
(https://dev.mysql.com/doc/refman/5.7/en/variables-table.html) hence
the issue.

According to the MySQL Manual,
http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html :
"A session that requires locks must acquire all the locks that it
needs in a single LOCK TABLES statement. While the locks thus obtained
are held, the session can access only the locked tables...Tables in
the INFORMATION_SCHEMA database are an exception. They can be accessed
without being locked explicitly even while a session holds table locks
obtained with LOCK TABLES. "

This is a server issue:
Bug#21789221 "SHOW STATUS FAILS WITH LOCK TABLES AND SHOW_COMPATIBILITY_56=OFF"

However since there are 5.7 GA servers out with this bug that Utilities must support,
we must work around this issue.

How to repeat:
Run the Mut copy_db tests:
$ python mut.py --server=root@localhost:13010 --do-tests=copy_db -f

Suggested fix:
Since foreign_key_checks is system variable, use select @@GLOBAL.foreign_key_checks instead
to check its status. By using this instead of SHOW VARIABLES we don't have problems with table locking.

Posted by developer:
 
Fixed in version 1.6.3

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

The mysqlrplsync utility did not function with MySQL 5.7 when used in a
topology with a master. The problem was due to table locking, and began
occurring when session_variables was moved to PERFORMANCE_SCHEMA in MySQL
5.7.6. The master UUID is now retrieved before locking the tables.

Thank you for the bug report.

Note: This entry was merged with the entry for Oracle Bug #22608528