MySQL Bugs: #99369: Bugs in "LOCK INSTANCE FOR BACKUP"

Bug #99369	Bugs in "LOCK INSTANCE FOR BACKUP"
Submitted:	27 Apr 2020 13:12	Modified:	29 Apr 2020 8:23
Reporter:	Rahul Gupta	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.17, 8.0.19	OS:	Red Hat (7.5)
Assigned to:		CPU Architecture:	Any

Description:
I am invoking mysqldump in single-transaction mode by taking "lock instance for backup" to avoid any DDL operations which can cause failure to mysqldump. Below are the operations being performed from a java client.

LOCK INSTACE FOR BACKUP
/usr/bin/mysqldump --login-path=jboss --delete-master-logs --flush-logs --add-drop-table --routines --triggers --databases --single-transaction -r /data/ocum-backup/database-dumps-repo/ocum_mysql_full_backup_1587987001741.sql by user jboss
UNLOCK INSTANCE FOR BACKUP

Bug 1:
We have noticed that Alter, Create, Drop commands which are supposed to be blocked by "LOCK INSTACE FOR BACKUP" breaks away from this lock after being blocked "Waiting for backup" for sometime. (Time varies from 1 min- 30 mins). Due to this mysqlbackup fails as single transaction mode is now in inconsistent state. It seems that DDL statements can break BACKUP_LOCK. We tried doing the same from mysql command line but unable to reproduce. But we are able to reproduce from Java in like 2 times out of 10 tries.

Bug 2:
It has been noticed that UNLOCK INSTANCE doesn't unlock the BACKUP_LOCK when called from Java client sometimes. This leads to application being in locked states and several DDL statements in blocked state forever. For time being, we are killing the thread which acquired the BACKUP_LOCK.

In summary: "LOCK INSTACE FOR BACKUP" implementation has some issues where DDL statements can break BACKUP_LOCK sometimes and "UNLOCK INSTANCE" doesn't release lock sometimes.

How to repeat:
I am seeing it 2 out of 10 times during testing. I am running an enterprise application on JBoss.

Hi Mr. Gupta,

Thank you for your bug report.

However, this does not seem like our bug at all.

We need you to reproduce the issue with our tools. We can not help you with your Java application.

There are many possibilities. You could have run out of lock_wait_timeout, thread could be terminated or similar.

Try running LOCK INSTANCE ..... in mysql CLI session and do not leave mysql CLI, while you run mysqldump in another session. 

You could also run LOCK in one thread and run the sleeping loop until mysqladmin has finished and then UNLOCK the instance.

If you can not repeat it that way, then it is not our bug.

Thanks for your response. I figured out it was JBoss connection pooling which caused the issue. Below was the root cause. Closing the bug. 

BACKUP LOCK is connection scoped. When we execute query using JDBCTemplate.execute, 
it internally closes the connection. In case connection pooling is enabled, connection pool manages connection 
lifecyle. As soon as this connection is closed, We loose this lock and we see issues with backup. Fix is to manage lifecycle of this connection ourself.

I wrote a spring boot app to try to mimic this behaviour. Since I was not using any connection pooling, BACKUP LOCK was shortlived 
as soon as "jdbcTemplate.execute("LOCK INSTANCE FOR BACKUP");" statement was executed. 
BACKUP LOCK was active when I executed 
"jdbcTemplate.getDataSource().getConnection().createStatement().execute("LOCK INSTANCE FOR BACKUP");" . 
Difference in later is that connection remains active.

While using the same with connection pool active, when a connection is closed,a wrapper(proxy) around the actual connection is closed. 
It wil under the covers release the actual connection back to the pool. It's further up to the pool to decide whether the actual connection will actually be closed or be reused for a new getConnection() call

Hi Mr. Gupta,

Thank you for the feedback.