Bug #2387 CREATE TABLE SELECT has imperfect locking on destination table
Submitted: 14 Jan 2004 7:23 Modified: 3 Feb 2004 11:20
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.0 OS:Any (all)
Assigned to: Dmitry Lenev CPU Architecture:Any

[14 Jan 2004 7:23] Guilhem Bichot
It's documented in code, but it's still a bug. Here's the code of
create_table_from_items() which is called by CREATE SELECT:
  /* create and lock table */
  /* QQ: This should be done atomic ! */
  if (mysql_create_table(thd,db,name,create_info,*extra_fields,
			 *keys,0,1,select_field_count)) // no logging
  if (!(table=open_table(thd,db,name,name,(bool*) 0)))

Indeed, I added a sleep(10) between the two if(),
then I did
CREATE TABLE t (a int);
INSERT INTO t values(1);
and while it was sleeping I did
ALTER TABLE u add (b int);
All commands succeeded ("query ok") (ALTER terminated before CREATE), but results show the confusion:
MASTER> select * from t;
| a    |
|    1 |
1 row in set (0.01 sec)

MASTER> show create table u;
| Table | Create Table                                                                                                     |
| u     | CREATE TABLE `u` (
  `a` int(11) default NULL,
  `b` int(11) default NULL
1 row in set (0.00 sec)

MASTER> select * from u;
| a    | b    |
| NULL |    1 |
See: 'b' was added, but the record 'a=1' of 't' got copied to 'a=NULL,b=1' in 'u', whereas we would expect 'a=1,b=NULL' if there was proper locking. It shows that ALTER confused CREATE TABLE u.

How to repeat:
See description.

Suggested fix:
I'm not discovering anything as the fault is already documented in code, I'm just entering it here so that it gets fixed.
[3 Feb 2004 11:20] Michael Widenius
This is not a bug issue but a worklog issue. I have now created a worklog entry for this instead.