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)
• Status: Duplicate
• 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] MySQL Verification Team

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.