Percona Server moved to https://jira.percona.com/projects/PS

CREATE TABLE for a table that already exists does not fail immediately

Bug #1127008 reported by Ovais Tariq
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
High
Laurynas Biveinis
Percona Server moved to https://jira.percona.com/projects/PS 5.6.11-60.3
5.1
Invalid
Undecided
Unassigned
5.5
Fix Released
High
Sergei Glushchenko
Percona Server moved to https://jira.percona.com/projects/PS 5.5.30-30.2
5.6
Fix Released
High
Laurynas Biveinis
Percona Server moved to https://jira.percona.com/projects/PS 5.6.11-60.3

Bug Description

A CREATE TABLE statement containing the name of the table that already exists does not fail immediately, and instead waits on table meta_data_lock if there is a transaction that executed a query that opened that table. This is only evident in 5.5 because this has to do with how Meta Data Locking is implemented.

-- Create a table and insert some data
CREATE TABLE `create_mdl_test` (
`ID` bigint(20) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into create_mdl_test(ID) values (1);
insert into create_mdl_test(ID) values (2);
insert into create_mdl_test(ID) values (3);
insert into create_mdl_test(ID) values (4);
insert into create_mdl_test(ID) values (5);

-- Execute the statements in different sessions as shown below:
session 1 (test) > start transaction;
Query OK, 0 rows affected (0.00 sec)

session 1 (test) > select * from create_mdl_test;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)

session 2 (test) > CREATE TABLE `create_mdl_test` (
    -> `ID` bigint(20) NOT NULL,
    -> PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; <-- this blocks as can be seen from the process list checked on session 3 below

session 3 (test) > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 4 | msandbox | localhost | test | Sleep | 45 | | NULL |
| 5 | msandbox | localhost | test | Query | 21 | Waiting for table metadata lock | CREATE TABLE `create_mdl_test` (
`ID` bigint(20) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAUL |
| 6 | msandbox | localhost | test | Query | 0 | NULL | show processlist |
+----+----------+-----------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

This bug has been fixed in MariaDB 5.5.29 (https://mariadb.atlassian.net/browse/MDEV-3941), and I have tested it. I have also tested MariaDB 5.5.29 for other MDL related use cases, and except for this case where the CREATE TABLE fails immediately when the table already exists, I have found the rest of the behaviour with respect to MDL, to be the same.

This is a request to port the fix from MariaDB 5.5.29 to Percona Server

Related branches

lp:~sergei.glushchenko/percona-server/ST-28169-Bug1127008
Alexey Kopytov (community): Approve
Diff: 405 lines (+214/-21)
11 files modified
Percona-Server/mysql-test/r/create_delayed.result (+3/-0)
Percona-Server/mysql-test/r/percona_bug1127008.result (+28/-0)
Percona-Server/mysql-test/t/create_delayed.test (+34/-0)
Percona-Server/mysql-test/t/percona_bug1127008.test (+28/-0)
Percona-Server/sql/mdl.cc (+5/-1)
Percona-Server/sql/sql_base.cc (+96/-16)
Percona-Server/sql/sql_base.h (+2/-1)
Percona-Server/sql/sql_db.cc (+1/-1)
Percona-Server/sql/sql_insert.cc (+4/-0)
Percona-Server/sql/sql_parse.cc (+8/-1)
Percona-Server/sql/sql_table.cc (+5/-1)
lp:~sergei.glushchenko/percona-server/ST-28169-Bug1127008-5.6
On hold for merging into lp:percona-server/5.6
Alexey Kopytov (community): Needs Fixing
Diff: 396 lines (+207/-21)
11 files modified
Percona-Server/mysql-test/r/create.result (+28/-0)
Percona-Server/mysql-test/r/create_delayed.result (+3/-0)
Percona-Server/mysql-test/t/create.test (+25/-0)
Percona-Server/mysql-test/t/create_delayed.test (+34/-0)
Percona-Server/sql/mdl.cc (+5/-1)
Percona-Server/sql/sql_base.cc (+92/-16)
Percona-Server/sql/sql_base.h (+2/-1)
Percona-Server/sql/sql_db.cc (+1/-1)
Percona-Server/sql/sql_insert.cc (+4/-0)
Percona-Server/sql/sql_parse.cc (+8/-1)
Percona-Server/sql/sql_table.cc (+5/-1)
lp:~laurynas-biveinis/percona-server/5.6-merge-3
Alexey Kopytov (community): Approve
Percona core: Pending requested
Diff: 19408 lines (+2919/-15492)
66 files modified
Percona-Server/client/mysqldump.c (+35/-4)
Percona-Server/mysql-test/r/create_delayed.result (+3/-0)
Percona-Server/mysql-test/r/percona_bug1127008.result (+28/-0)
Percona-Server/mysql-test/r/percona_innodb_version.result (+1/-1)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+0/-443)
Percona-Server/mysql-test/suite/innodb/r/percona_bug_1133926.result (+4/-0)
Percona-Server/mysql-test/suite/innodb/t/percona_bug_999147-master.opt (+0/-1)
Percona-Server/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result (+6/-0)
Percona-Server/mysql-test/suite/sys_vars/r/innodb_corrupt_table_action_basic.result (+49/-0)
Percona-Server/mysql-test/t/create_delayed.test (+34/-0)
Percona-Server/mysql-test/t/percona_bug1127008.test (+28/-0)
Percona-Server/mysql-test/t/percona_innodb_version.test (+1/-1)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+0/-227)
Percona-Server/scripts/fill_help_tables.sql (+2177/-9)
Percona-Server/sql/mdl.cc (+5/-1)
Percona-Server/sql/sql_base.cc (+92/-16)
Percona-Server/sql/sql_base.h (+2/-1)
Percona-Server/sql/sql_db.cc (+1/-1)
Percona-Server/sql/sql_insert.cc (+4/-0)
Percona-Server/sql/sql_parse.cc (+8/-1)
Percona-Server/sql/sql_table.cc (+5/-1)
Percona-Server/storage/innobase/btr/btr0btr.cc (+19/-23)
Percona-Server/storage/innobase/btr/btr0cur.cc (+52/-61)
Percona-Server/storage/innobase/btr/btr0pcur.cc (+6/-7)
Percona-Server/storage/innobase/btr/btr0sea.cc (+1/-4)
Percona-Server/storage/innobase/buf/buf0buf.cc (+31/-16)
Percona-Server/storage/innobase/buf/buf0rea.cc (+2/-4)
Percona-Server/storage/innobase/fil/fil0fil.cc (+26/-32)
Percona-Server/storage/innobase/fsp/fsp0fsp.cc (+15/-33)
Percona-Server/storage/innobase/handler/ha_innodb.cc (+4/-2)
Percona-Server/storage/innobase/include/btr0btr.ic (+1/-1)
Percona-Server/storage/innobase/include/buf0buf.ic (+1/-7)
Percona-Server/storage/innobase/include/fut0fut.ic (+1/-4)
Percona-Server/storage/innobase/include/srv0srv.h (+16/-0)
Percona-Server/storage/innobase/row/row0ins.cc (+5/-4)
Percona-Server/storage/innobase/row/row0merge.cc (+3/-2)
Percona-Server/storage/innobase/row/row0sel.cc (+6/-5)
build/build-binary.sh (+38/-1)
build/debian/additions/innotop/InnoDBParser.pm (+0/-1089)
build/debian/additions/innotop/changelog.innotop (+0/-318)
build/debian/additions/innotop/innotop (+0/-9485)
build/debian/additions/innotop/innotop.1 (+0/-2086)
build/debian/percona-server-client-5.6.dirs (+0/-1)
build/debian/percona-server-client-5.6.docs (+0/-1)
build/debian/percona-server-client-5.6.install (+0/-3)
build/debian/percona-server-client-5.6.lintian-overrides (+0/-2)
build/debian/percona-server-server-5.6.postinst (+9/-1)
build/percona-server.spec (+26/-27)
build/percona-shared-compat.spec (+4/-0)
doc/Makefile (+0/-145)
doc/source/diagnostics/innodb_deadlock_count.rst (+0/-57)
doc/source/diagnostics/innodb_show_lock_names.rst (+0/-24)
doc/source/diagnostics/innodb_show_status.rst (+0/-475)
doc/source/diagnostics/misc_info_schema_tables.rst (+0/-48)
doc/source/diagnostics/mysql_syslog.rst (+0/-43)
doc/source/diagnostics/process_list.rst (+0/-53)
doc/source/diagnostics/show_engines.rst (+0/-40)
doc/source/diagnostics/slow_extended_55.rst (+0/-377)
doc/source/diagnostics/thread_based_profiling.rst (+0/-17)
doc/source/diagnostics/user_stats.rst (+0/-242)
doc/source/installation/apt_repo.rst (+65/-0)
doc/source/installation/yum_repo.rst (+81/-0)
doc/source/release-notes/Percona-Server-5.6.5-60.0.rst (+0/-26)
doc/source/release-notes/Percona-Server-5.6.6-60.1.rst (+0/-18)
doc/source/release-notes/release-notes_index.rst (+11/-0)
doc/source/upstream-bug-fixes.rst (+13/-1)
Ovais Tariq (ovais-tariq)
tags: added: i29581
Revision history for this message
Alexey Kopytov (akopytov) wrote :

The upstream bug has been marked as a duplicate of http://bugs.mysql.com/bug.php?id=63144. We should refer to that one in docs when describing the fix.

Changed in percona-server:
status: New → Triaged
importance: Undecided → High
Sergei Glushchenko (sergei.glushchenko)
Changed in percona-server:
assignee: nobody → Sergei Glushchenko (sergei.glushchenko)
status: Triaged → In Progress
Laurynas Biveinis (laurynas-biveinis)
tags: added: upstream
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-40

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.