MySQL Bugs: #6528: Table that is part of a merge table corrupted when renamed

Bug #6528	Table that is part of a merge table corrupted when renamed
Submitted:	9 Nov 2004 18:42	Modified:	9 Nov 2004 19:30
Reporter:	Gregert Johnson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.17-standard (RPM)	OS:	Linux (Mandrake Linux 9.2)
Assigned to:		CPU Architecture:	Any

Description:
A merge table contained two component tables in its UNION list, p1 and p2.  When p1 was renamed to p1_old, and then p1 was recreated with the same characteristics, it was found to be immediately corrupted.  My suspicion is that cached data for p1 and p1_old is somehow overlapping in the server.

This does not happen if p1 was not originally included in the merge table.

Test dialogue:

mysql> show tables like 'cdr_sip%';
+-----------------------------+
| Tables_in_testdb (cdr_sip%) |
+-----------------------------+
| cdr_sip                     |
| cdr_sip_p1                  |
| cdr_sip_p2                  |
+-----------------------------+
3 rows in set (0.00 sec)

mysql> show create table cdr_sip\G
*************************** 1. row ***************************
       Table: cdr_sip
Create Table: CREATE TABLE `cdr_sip` (
  `call_key` bigint(20) unsigned NOT NULL default '0',
  `seq` smallint(5) unsigned NOT NULL default '0',
  `probe_id` int(10) unsigned NOT NULL default '0',
  `start_time` int(11) NOT NULL default '-1',
  `start_time_ext` int(11) NOT NULL default '-1',
  `time_key` int(11) NOT NULL default '-1',
  `end_time` int(11) NOT NULL default '-1',
  `end_time_ext` int(11) NOT NULL default '-1',
  `file_name` varchar(255) NOT NULL default '',
  `file_offset` int(10) unsigned NOT NULL default '0',
  `file_object_size` int(10) unsigned NOT NULL default '0',
  `call_id` varchar(255) NOT NULL default '',
  `call_src` varchar(255) NOT NULL default '',
  `call_dst` varchar(255) NOT NULL default '',
  `call_length` int(10) unsigned NOT NULL default '0',
  `call_setup_time` int(10) unsigned NOT NULL default '0',
  `timer1` int(11) NOT NULL default '0',
  `timer2` int(11) NOT NULL default '0',
  `timer3` int(11) NOT NULL default '0',
  `timer4` int(11) NOT NULL default '0',
  `media_src_ip` varchar(15) NOT NULL default '',
  `media_src_port` smallint(5) unsigned NOT NULL default '0',
  `media_dst_ip` varchar(15) NOT NULL default '',
  `media_dst_port` smallint(5) unsigned NOT NULL default '0',
  `invite_count` tinyint(3) unsigned NOT NULL default '0',
  `bye_count` tinyint(3) unsigned NOT NULL default '0',
  `register_count` tinyint(3) unsigned NOT NULL default '0',
  `ack_count` tinyint(3) unsigned NOT NULL default '0',
  `flags` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`call_key`,`seq`),
  KEY `ie2` (`probe_id`),
  KEY `ie3` (`start_time`,`start_time_ext`)
) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(cdr_sip_p1,cdr_sip_p2)
1 row in set (0.02 sec)

mysql> select count(*) from cdr_sip_p1;
+----------+
| count(*) |
+----------+
|   161940 |
+----------+
1 row in set (0.08 sec)

mysql> rename table cdr_sip_p1 to cdr_sip_p1_old;
Query OK, 0 rows affected (0.02 sec)

mysql> select count(*) from cdr_sip_p1;
ERROR 1146: Table 'testdb.cdr_sip_p1' doesn't exist

mysql> select count(*) from cdr_sip_p1_old;
+----------+
| count(*) |
+----------+
|   161940 |
+----------+
1 row in set (0.05 sec)

mysql> CREATE TABLE `cdr_sip_p1` (
    ->   `call_key` bigint(20) unsigned NOT NULL default '0',
    ->   `seq` smallint(5) unsigned NOT NULL default '0',
    ->   `probe_id` int(10) unsigned NOT NULL default '0',
    ->   `start_time` int(11) NOT NULL default '-1',
    ->   `start_time_ext` int(11) NOT NULL default '-1',
    ->   `time_key` int(11) NOT NULL default '-1',
    ->   `end_time` int(11) NOT NULL default '-1',
    ->   `end_time_ext` int(11) NOT NULL default '-1',
    ->   `file_name` varchar(255) NOT NULL default '',
    ->   `file_offset` int(10) unsigned NOT NULL default '0',
    ->   `file_object_size` int(10) unsigned NOT NULL default '0',
    ->   `call_id` varchar(255) NOT NULL default '',
    ->   `call_src` varchar(255) NOT NULL default '',
    ->   `call_dst` varchar(255) NOT NULL default '',
    ->   `call_length` int(10) unsigned NOT NULL default '0',
    ->   `call_setup_time` int(10) unsigned NOT NULL default '0',
    ->   `timer1` int(11) NOT NULL default '0',
    ->   `timer2` int(11) NOT NULL default '0',
    ->   `timer3` int(11) NOT NULL default '0',
    ->   `timer4` int(11) NOT NULL default '0',
    ->   `media_src_ip` varchar(15) NOT NULL default '',
    ->   `media_src_port` smallint(5) unsigned NOT NULL default '0',
    ->   `media_dst_ip` varchar(15) NOT NULL default '',
    ->   `media_dst_port` smallint(5) unsigned NOT NULL default '0',
    ->   `invite_count` tinyint(3) unsigned NOT NULL default '0',
    ->   `bye_count` tinyint(3) unsigned NOT NULL default '0',
    ->   `register_count` tinyint(3) unsigned NOT NULL default '0',
    ->   `ack_count` tinyint(3) unsigned NOT NULL default '0',
    ->   `flags` tinyint(3) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`call_key`,`seq`),
    ->   KEY `ie2` (`probe_id`),
    ->   KEY `ie3` (`start_time`,`start_time_ext`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

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

mysql> select * from cdr_sip_p1;
ERROR 1030: Got error 127 from table handler

mysql> check table cdr_sip_p1;
+-------------------+-------+----------+----------------------------------------------------+
| Table             | Op    | Msg_type | Msg_text                                           |
+-------------------+-------+----------+----------------------------------------------------+
| testdb.cdr_sip_p1 | check | error    | Size of datafile is: 0         Should be: 42752156 |
| testdb.cdr_sip_p1 | check | error    | Corrupt                                            |
+-------------------+-------+----------+----------------------------------------------------+
2 rows in set (0.00 sec)

How to repeat:
Repeat the above dialogue.

I just learned that this is a known problem, fixed in 4.1x.  Please disregard.