1# 2# MDEV-12882 - Assertion `mdl_ticket->m_type == MDL_SHARED_UPGRADABLE || 3# mdl_ticket->m_type == MDL_SHARED_NO_WRITE || 4# mdl_ticket->m_type == MDL_SHARED_NO_READ_WRITE || 5# mdl_ticket->m_type == MDL_SHARED_READ' 6# failed in MDL_context::upgrade_shared_lock 7# 8CREATE TABLE t1(a INT) ENGINE=InnoDB; 9CREATE TABLE t3(a INT) ENGINE=myisam; 10LOCK TABLES t1 WRITE CONCURRENT, t1 AS t2 READ; 11SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 12LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 13MDL_BACKUP_TRANS_DML Backup lock 14MDL_SHARED_NO_READ_WRITE Table metadata lock test t1 15UNLOCK TABLES; 16LOCK TABLES t1 AS t2 READ, t1 WRITE CONCURRENT; 17SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 18LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 19MDL_BACKUP_TRANS_DML Backup lock 20MDL_SHARED_WRITE Table metadata lock test t1 21MDL_SHARED_READ_ONLY Table metadata lock test t1 22UNLOCK TABLES; 23LOCK TABLES t1 WRITE CONCURRENT, t3 WRITE; 24SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 25LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 26MDL_BACKUP_DDL Backup lock 27MDL_BACKUP_DML Backup lock 28MDL_SHARED_WRITE Table metadata lock test t1 29MDL_SHARED_NO_READ_WRITE Table metadata lock test t3 30MDL_INTENTION_EXCLUSIVE Schema metadata lock test 31UNLOCK TABLES; 32LOCK TABLES t3 WRITE, t1 WRITE CONCURRENT; 33SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 34LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 35MDL_BACKUP_DDL Backup lock 36MDL_BACKUP_DML Backup lock 37MDL_SHARED_WRITE Table metadata lock test t1 38MDL_SHARED_NO_READ_WRITE Table metadata lock test t3 39MDL_INTENTION_EXCLUSIVE Schema metadata lock test 40UNLOCK TABLES; 41LOCK TABLES t1 WRITE, mysql.user WRITE; 42SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 43LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 44MDL_BACKUP_DDL Backup lock 45MDL_SHARED_NO_READ_WRITE Table metadata lock mysql user 46MDL_SHARED_NO_READ_WRITE Table metadata lock test t1 47MDL_INTENTION_EXCLUSIVE Schema metadata lock mysql 48MDL_SHARED_NO_READ_WRITE Table metadata lock mysql global_priv 49MDL_INTENTION_EXCLUSIVE Schema metadata lock test 50UNLOCK TABLES; 51LOCK TABLES mysql.general_log WRITE; 52ERROR HY000: You can't use locks with log tables 53LOCK TABLES t1 WRITE,information_schema.tables READ; 54UNLOCK TABLES; 55DROP TABLE t1,t3; 56# 57# Check MDL locks taken for different kind of tables by open 58# 59CREATE TABLE t1(a INT) ENGINE=InnoDB; 60CREATE TABLE t3(a INT) ENGINE=myisam; 61connect purge_control,localhost,root,,; 62START TRANSACTION WITH CONSISTENT SNAPSHOT; 63connect locker,localhost,root,,; 64connection default; 65FLUSH TABLES WITH READ LOCK; 66connection locker; 67insert into t1 values (1); 68connection default; 69connection default; 70SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 71LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 72MDL_BACKUP_FTWRL2 Backup lock 73MDL_SHARED_WRITE Table metadata lock test t1 74unlock tables; 75connection locker; 76unlock tables; 77connection default; 78FLUSH TABLES WITH READ LOCK; 79connection locker; 80insert into t3 values (2); 81connection default; 82connection default; 83SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.metadata_lock_info; 84LOCK_MODE LOCK_TYPE TABLE_SCHEMA TABLE_NAME 85MDL_BACKUP_FTWRL2 Backup lock 86MDL_SHARED_WRITE Table metadata lock test t3 87unlock tables; 88connection locker; 89unlock tables; 90disconnect purge_control; 91connection default; 92disconnect locker; 93DROP TABLE t1,t3; 94