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:
Category:MySQL Utilities Severity:S2 (Serious)
Version:1.6.2 OS:Any
Assigned to: CPU Architecture:Any

[29 Jan 2016 15:33] Nelson Goncalves
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

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:

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.
[29 Jan 2016 16:28] Nelson Goncalves
Posted by developer:
Fixed in version 1.6.3
[2 Feb 2016 3:46] Philip Olson
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