MySQL Bugs: #101410: mysqlsh dumpInstance backup consistency is broken

Bug #101410	mysqlsh dumpInstance backup consistency is broken
Submitted:	1 Nov 2020 5:20	Modified:	24 Nov 2020 19:09
Reporter:	Mitani Satoshi (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Document Store: MySQL Shell	Severity:	S2 (Serious)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
sometime, MySQL Shell generates inconsistent backup.

How to repeat:
1. insert a record into 2 table in a transaction with MySQL 8.0.22 mysql server.

#/bin/sh
#mysql -vv -uroot -e "CREATE DATABASE db1";
#mysql -vv -uroot -e "CREATE TABLE db1.t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c INT NOT NULL)";
#mysql -vv -uroot -e "CREATE DATABASE db2";
#mysql -vv -uroot -e "CREATE TABLE db2.t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c INT NOT NULL)";

a=1
mysql -vv -uroot -e "truncate db1.t1"
mysql -vv -uroot -e "truncate db2.t1"
sleep 1
while [ 1 ]
do
  a=$(expr $a + 1)
  mysql -vv -uroot -e "BEGIN; INSERT INTO db1.t1 (c) VALUES('$a'); INSERT INTO db2.t1 (c) VALUES('$a'); COMMIT;"
done

2. during above shell script execution,  do util.dumpInstance repeatedly
mysqlsh> \py
mysqlsh>  for i in range(10): util.dump_instance('/tmp/bk%s' % i, compression='none')

3. check generated backup consistency
count of records should be match. but sometime, the count are not match.
$ tail -n3 bk*/db*@t1*tsv 

Suggested fix:
Check mysql.proc table exists or not, before LOCK TABLES mysql.proc READ.
MySQL 8.0 don't have mysql.proc table. I guess 'mysql.proc' doesn't exist error breaks the transaction during dumpInstance.

Thank you for the bug report. We can confirm that the mysql.proc lock is causing the transaction to break during dump.

As a workaround, you can perform the dump by disabling routine dumping by setting the "routines" flag to false:

   util.dumpInstance("mydump", {routines:false});

Posted by developer:
 
Changelog entry added for MySQL Shell 8.0.23:

MySQL Shell's instance dump utility util.dumpInstance(), schema dump utility util.dumpSchemas(), and table dump utility util.dumpTables() falls back to using the LOCK TABLES privilege to lock dumped tables if the consistent option is set to true, which is the default, and the RELOAD privilege is not available. However, the locking operation could cause an implicit commit on active transactions, meaning that the data was not dumped consistently. The locking has now been corrected to ensure consistency in this situation.