MySQL Bugs: #61645: external locking corrupted temporary table after alter

Bug #61645	external locking corrupted temporary table after alter
Submitted:	26 Jun 2011 8:16	Modified:	28 Jun 2011 7:31
Reporter:	Veerabahu Subramanian	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.13, 5.5.15	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, temporary table

Description:
In MySQL 5.5 altering a temporary table results in losing the data after one select query. This happens only for temporary table. 

What is Expected :-
   Values should be returned. Able to retrieve data by using further select query 
  i.e. select * from test should return values  

Test Environment: 
  Linux RHEL 5 update 4
  MySQL 5.5 (mysql  Ver 14.14 Distrib 5.5.10, for Linux (x86_64)) 
  All queries are made using mysql command line 

How to repeat:
1. Create temporary table 
      CREATE TEMPORARY TABLE `test` ( `ci` int(11) DEFAULT NULL ); 
2. Insert some values in to it 
       insert into test values (1); 
       insert into test values (1); 
3. Select values 
       select * from test; 
Here values will be returned properly 
4. Alter the table 
       alter table test add column ACB varchar (255); 
5. Now select 
       select * from test; 
  Here values will be returned properly 
6. Now again select 
        select * from test; Also select count(*) from test will return 0 
The values won't be returned

Please, check with a newer version, 5.5.13 at least. I do not see the problem with current code:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  CREATE TEMPORARY TABLE `test` ( `ci` int(11) DEFAULT NULL ); 
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test values (1); 
Query OK, 1 row affected (0.04 sec)

mysql>        insert into test values (1); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from test; 
+------+
| ci   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.02 sec)

mysql> alter table test add column ACB varchar (255); 
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test; 
+------+------+
| ci   | ACB  |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select count(*) from test; 
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.03 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TEMPORARY TABLE `test` (
  `ci` int(11) DEFAULT NULL,
  `ACB` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I see the problem with 5.5.13 too. Here you go, 

[veechand@Veerabahu ~]$ mysql -uroot -piltwat test --socket=/var/lib/mysql/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>   CREATE TEMPORARY TABLE `test` ( `ci` int(11) DEFAULT NULL );
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into test values (1);
Query OK, 1 row affected (0.00 sec)

mysql>        insert into test values (1);
Query OK, 1 row affected (0.00 sec)

mysql>        select * from test;
+------+
| ci   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql>        alter table test add column ACB varchar (255);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>        select * from test;
+------+------+
| ci   | ACB  |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql>        select * from test;
Empty set (0.00 sec)

mysql>        select count(*) from test;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Some difference that I observe are (from Valeriy Kravchuk) 
   1. My Server is Linux RHEL 5 update 4 64 bit version  
   2. Version of MySQL is 5.5.13 
Let me know if you look for any further details to reproduce the issue.

It's not repeatable on Window with current code too:

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >CREATE TEMPORARY TABLE `test` ( `ci` int(11) DEFAULT NULL );
Query OK, 0 rows affected (0.07 sec)

mysql 5.5 >insert into test values (1);
Query OK, 1 row affected (0.02 sec)

mysql 5.5 > insert into test values (1);
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >select * from test;
+------+
| ci   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.02 sec)

mysql 5.5 >alter table test add column ACB varchar (255);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.5 >select * from test;
+------+------+
| ci   | ACB  |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql 5.5 >select count(*) from test;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql 5.5 >

I couldn't repeat on Ubuntu too:

miguel@lempa:~$ dbs/5.5/bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.13-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TEMPORARY TABLE `test` ( `ci` int(11) DEFAULT NULL );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+------+
| ci   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> alter table test add column ACB varchar (255);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+------+
| ci   | ACB  |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

not repeatable for me either. can we get the my.cnf file ?

my.cnf

Attachment: my.cnf (application/octet-stream, text), 2.44 KiB.

Attached my.cnf 
Miguel Solorzano, Is Ubuntu 64 bit? Mine is 64 bit.

the bug is repeatable with the external-locking option enabled.

drop temporary table if exists `test`;
drop table if exists `test`;
create temporary table `test` (`ci` int)engine=myisam;
insert into test values (1),(1);
select * from test;
alter table test add column acb varchar(1);
select * from test;
select * from test;
select count(*) from test;
check table test;

Verified as described by Shane (server started with --external-locking) on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop temporary table if exists `test`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> drop table if exists `test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create temporary table `test` (`ci` int)engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1),(1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+
| ci   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> alter table test add column acb varchar(1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+------+
| ci   | acb  |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from test;
Empty set (0.04 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql> check table test;
+-----------+-------+----------+----------------------------------------------------------+
| Table     | Op    | Msg_type | Msg_text                                                 |
+-----------+-------+----------+----------------------------------------------------------+
| test.test | check | warning  | 0 clients are using or haven't closed the table properly |
| test.test | check | warning  | Size of datafile is: 40       Should be: 0               |
| test.test | check | error    | Record-count is not ok; is 2   Should be: 0              |
| test.test | check | warning  | Found 2 key parts. Should be: 0                          |
| test.test | check | error    | Corrupt                                                  |
+-----------+-------+----------+----------------------------------------------------------+
5 rows in set (0.02 sec)