Bug #28487 during alter table .. add partition temporary tables are visible to users
Submitted: 17 May 2007 5:14 Modified: 5 Jun 2007 11:06
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.19 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: partition

[17 May 2007 5:14] Shane Bester
Description:
During an ALTER TABLE .. ADD PARTITION operation, the temporary table
is visible to a user that executes SHOW TABLE STATUS.

See here:

mysql> show table status;
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+--------------------------------------------------------------+
| Name              | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation         | Checksum | Create_options | Comment                                                      |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+--------------------------------------------------------------+
| #mysql50#t1_test# | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL              |     NULL | NULL           | Out of memory; restart server and try again (needed 2 bytes) |
| t1_test           | InnoDB |      10 | Compact    |  337 |            194 |       65536 |               0 |        65536 |         0 |           NULL | NULL        | NULL        | NULL       | latin1_swedish_ci |     NULL | partitioned    |                                                              |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+--------------------------------------------------------------+
2 rows in set (0.00 sec)

How to repeat:
Will add a testcase later.

Suggested fix:
Don't show these short-lived tables.
[17 May 2007 5:57] Shane Bester
TESTCASE:
---------------------------

in connection 1:
-------------------

delimiter ;
drop table if exists t1;
create table t1_test(id int not null,name varchar(25),data
tinyblob,key(name),key(id)) engine=innodb partition by range(id) ( partition
p00
values less than (5000));
drop procedure if exists p1;
delimiter //
create procedure p1()
begin
        declare counter int default 0;
        declare continue handler for sqlexception begin end;
        repeat
                insert into t1_test values
(floor(10000*rand()),'aaaaaaa','bbbbbbb');
                alter table t1_test add partition (partition `p08` values less
than (200000));
                alter table t1_test drop partition p08;
                set counter=counter+1;
        until counter > 1000000
        end repeat;

end //

delimiter ;

call p1();

in connection 2:
----------------------
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS;
<or more... > until you see that temp table in the output...
[5 Jun 2007 11:06] Sergey Vojtovich
A duplicate of BUG#28488.