Bug #2385 CREATE TABLE LIKE lacks locking on source and destination table

• Submitted: 14 Jan 2004 7:16
• Modified: 2 Apr 2004 11:01
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1
• OS: Any (all)
• Assigned to: Bugs System
• CPU Architecture: Any

[14 Jan 2004 7:16] Guilhem Bichot

Description:
This is a superset of BUG#2376 "CREATE TABLE LIKE not blocked by FLUSH TABLES WITH READ LOCK" which I'll mark as a duplicate of this one.

Looking in code of mysql_create_like_table(), no lock is taken.
* No lock on source:
I added a 'sleep(10);' in this function just before my_copy().
I did
CREATE TABLE t (a int);
CREATE TABLE u LIKE t;
and while it was sleeping, in another connection I did:
ALTER TABLE t add key(a);
Result that u was created with an index on a, why not.
But now imagine that the ALTER comes *during* the my_copy() (not just before, like I did, but during; it's as possible, as my_copy() is just a file copy which takes no locks). Then t.frm will be changed while my_copy() is copying it, resulting in a corrupted u.frm.
So we need CREATE TABLE u LIKE t to lock t, like CREATE TABLE u SELECT * from t does (with mysql_lock_tables()).
* No lock on destination:
Keeping the same sleep(10), I did
CREATE TABLE t (a int);
CREATE TABLE u LIKE t;
and while it was sleeping, in another connection I did:
CREATE TABLE u (b int);
Result was: both "CREATE TABLE u..." apparently succeeded ("query ok"), but "SHOW CREATE TABLE u" proves that in fact "CREATE TABLE u LIKE t" overrode "CREATE TABLE u (b int)", so you get this impressive output:
MASTER> create table u (b int);
Query OK, 0 rows affected (0.01 sec)

MASTER> show create table u;
+-------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------+
| u     | CREATE TABLE `u` (
  `a` int(11) default NULL,
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------+
So we need locking on the destination, like CREATE TABLE not-LIKE not-SELECT does (does it by holding a lock on LOCK_open, apparently) (CREATE TABLE SELECT does some locking too but it's faulty (that's commented in code, but I'll still file a bug report for this)).

How to repeat:
Add some sleep() in code like explained above

Suggested fix:
suggestions in "description".

[31 Mar 2004 9:04] Victor Vagin

subj: bk commit - 4.1 tree (vva:1.1746) BUG#2385

ChangeSet
  1.1746 04/04/01 00:50:29 [e-mail] +1 -0
  fixed bug #2385 "CREATE TABLE LIKE lacks locking on source and destination table"
  changed function mysql_create_like_table in sql/sql_table.cc:
  1. added name locking of source table
  2. added flag MY_DONT_OVERWRITE_FILE for my_copy

[1 Apr 2004 7:29] Victor Vagin

new patch is:

subj: bk commit - 4.1 tree (vva:1.1746) BUG#2385

ChangeSet
  1.1746 04/04/01 23:13:25 [e-mail] +7 -0
  added synchronization in mysql_create_like_table
  (
  fixed BUG #2385 CREATE TABLE LIKE lacks locking on source and destination table
  and added tests for it
  )

[2 Apr 2004 11:01] Victor Vagin

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix will be in the mysql-4.1.2