Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Lock Tables for Backup not working as expected (Index is being created after it)

Description

When analyzing the general log it is possible to see an index being created after Lock Tables for Backup.

Requisites:

set global general_log=1;

Test:

One session run:

sysbench --db-driver=mysql --mysql-user=root --mysql-password=msandbox \ --mysql-port=45008 --mysql-host=127.0.0.1 --mysql-db=test --range_size=100 \ --table_size=1000 --tables=200 --threads=1 --events=0 --time=60 \ --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare

On the other:

/opt/percona_server/5.7.18/bin/mysqldump -uroot -pmsandbox -S /tmp/mysql_sandbox45008.sock --databases test --comments --routines --triggers --events --allow-keywords --master-data=2 --single-transaction --lock-for-backup --max_allowed_packet=1024M | gzip > local.dmp.gz

We can see on the General Log:

2019-05-15T14:46:28.684095Z    75 Query SHOW VARIABLES LIKE 'have_backup_locks'2019-05-15T14:46:28.684095Z    75 Query SHOW VARIABLES LIKE 'have_backup_locks'2019-05-15T14:46:28.688150Z    75 Query SHOW STATUS LIKE 'binlog_snapshot_%'2019-05-15T14:46:28.690225Z    75 Query LOCK TABLES FOR BACKUP2019-05-15T14:46:28.690574Z    74 Query CREATE INDEX k_61 ON sbtest61(k)2019-05-15T14:46:28.690613Z    75 Query SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'performance_schema' AND table_name = 'se2019-05-15T14:46:28.690835Z    75 Query SELECT COUNT(*) FROM performance_schema.session_variables WHERE VARIABLE_NAME LIKE 'rocksdb\_skip\_fill\_cach2019-05-15T14:46:28.691890Z    75 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2019-05-15T14:46:28.691939Z    75 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */2019-05-15T14:46:28.692025Z    75 Query SHOW VARIABLES LIKE 'gtid\_mode'2019-05-15T14:46:28.694892Z    75 Query SHOW STATUS LIKE 'binlog_snapshot_%'2019-05-15T14:46:28.696852Z    75 Query UNLOCK TABLES

Environment

None

AFFECTED CS IDs

254762

Smart Checklist

Activity

George Lorch January 20, 2020 at 8:28 PM

It has been determined that the root cause of this is identical to https://perconadev.atlassian.net/browse/PS-5631#icft=PS-5631 and this is just a different visible symptom.  Closing as a duplicate.

Duplicate

Details

Assignee

Reporter

Affects versions

Priority

Smart Checklist

Created May 15, 2019 at 4:29 PM
Updated March 6, 2024 at 12:08 PM
Resolved January 20, 2020 at 8:29 PM

Flag notifications