MySQL Bugs: #20789: Merge Subtable Rename Causes Crash

Bug #20789	Merge Subtable Rename Causes Crash
Submitted:	30 Jun 2006 10:17	Modified:	4 Oct 2006 2:01
Reporter:	Frank Osterberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22, 5.1.9beta	OS:	Windows (Win32)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any

Description:
I created two MyISAM tables, t1 and t2, then created a Merge table "mt" based on t1 and t2. Then i renamed t2 to t and tried to insert into mt. The renamed table was the last table that the merge table should have inserted into... but of course it was renamed.

result was a complete server crash, no error message or log entry of any kind, manually had to restart the server, the merge table was corrupt and had to be dropped and recreated.

obviously this is provoking an error of some kind, but i would expect to not be allowed to rename, drop or alter subtables of a merge table... or that the merge table automatically drops the table from its union if any of the above happends (sort of like an update cascade)
or in the simplist case an appropriate error message for a failed insert..
not a complete server crash.. it didn't even make an error log entry!

How to repeat:
CREATE TABLE `t1` (
  `TIM` datetime NOT NULL,
  `VAL` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `t2` (
  `TIM` datetime NOT NULL,
  `VAL` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `mt` (
  `TIM` datetime NOT NULL,
  `VAL` double default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`t1`,`t2`);

INSERT INTO mt VALUES ('2006-01-01',0) -- should work
;

ALTER TABLE `t2` RENAME TO `t`;

INSERT INTO mt VALUES ('2006-01-01',0) -- bad BAD idead
;

Suggested fix:
a:
simply throw a usable query error, i.e. "subtable xxxxx not found"

b:
don't allow: alter, rename or drop on tables that are part of a merge union

c:
automatically drop tables from a merge table union if they are:
altered,
renamed or
dropped from the schema

i forgot finish the Synopsis before sending the bur report, it's supposed to read:
Merge Subtable Rename Causes Crash.. or somethig like that :(

Thank you for a problem report. Please, specify the exact MySQL version used, 5.0.x. Please, check, is there anything for this crash in the error log (there should be something).

I can reproduce this on two different servers, the first is running:
5.0.22-community-max-nt

and the second:
5.1.9-beta

here are the relevant .err entries since the last server start (on 060626) before the this crash happend (on 060630):

060626 13:56:31 InnoDB: Started; log sequence number 0 1055147274
060626 13:56:31 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=terminal-relay-bin' to avoid this problem.
060626 13:56:31 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-max-nt: ready for connections.
Version: '5.0.22-community-max-nt' socket: '' port: 3306 MySQL Community Edition - Max (GPL)
060630 11:43:30 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060630 11:43:33 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1080663756.
InnoDB: Doing recovery: scanned up to log sequence number 0 1080663756
060630 11:43:34 InnoDB: Started; log sequence number 0 1080663756
060630 11:43:34 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=terminal-relay-bin' to avoid this problem.
060630 11:43:34 [Note] C:\Programme\MySQL\MySQL Server 5.0\bin\mysqld-max-nt: ready for connections.
Version: '5.0.22-community-max-nt' socket: '' port: 3306 MySQL Community Edition - Max (GPL)

i hope this helps

Thank you for the bug report. I was able to repeat on Windows (on Linux
not crashes):
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `t1` (
    ->   `TIM` datetime NOT NULL,
    ->   `VAL` double default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> CREATE TABLE `t2` (
    ->   `TIM` datetime NOT NULL,
    ->   `VAL` double default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> CREATE TABLE `mt` (
    ->   `TIM` datetime NOT NULL,
    ->   `VAL` double default NULL
    -> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST
    -> UNION=(`t1`,`t2`);
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> INSERT INTO mt VALUES ('2006-01-01',0) -- should work
    -> ;
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> ALTER TABLE `t2` RENAME TO `t`;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO mt VALUES ('2006-01-01',0) -- bad BAD idead
    -> ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

 	ntdll.dll!7c918fea() 	
 	ntdll.dll!7c90104b() 	
 	mysqld-nt.exe!_my_pwrite()  + 0x46	C
 	mysqld-nt.exe!__mi_mark_file_changed()  + 0x7c	C
 	mysqld-nt.exe!_mi_write()  + 0xea	C
>	mysqld-nt.exe!ha_myisammrg::write_row(unsigned char * buf=0x00dfcab0)  Line 139 + 0xd	C++
 	mysqld-nt.exe!write_record(THD * thd=0x00e08268, st_table * table=0x00dfc318, st_copy_info * info=0x02e1f074)  Line 1115 + 0xf	C++
 	mysqld-nt.exe!mysql_insert(THD * thd=0x00e08268, st_table_list * table_list=0x00e22660, List<Item> & fields={...}, List<List<Item> > & values_list={...}, List<Item> & update_fields={...}, List<Item> & update_values={...}, enum_duplicates duplic=DUP_ERROR, int ignore=0)  Line 505 + 0xc	C++
 	mysqld-nt.exe!mysql_execute_command(THD * thd=0x003a49c0)  Line 3302	C++
 	mysqld-nt.exe!mysql_parse(THD * thd=0x00e08268, char * inBuf=0x00e225d8, unsigned int length=38)  Line 5739	C++
 	mysqld-nt.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x00e08268, char * packet=0x00e1a5a9, unsigned int packet_length=39)  Line 1747	C++
 	mysqld-nt.exe!do_command(THD * thd=0x00e08268)  Line 1531 + 0xd	C++
 	mysqld-nt.exe!handle_one_connection(void * arg=0x00e08268)  Line 1174 + 0xa	C++
 	mysqld-nt.exe!_pthread_start()  + 0x3b	C
 	mswsock.dll!71a15ccc() 	
 	mysqld-nt.exe!_threadstart(void * ptd=0x00dfd198)  Line 196 + 0x6	C
 	kernel32.dll!7c80b50b() 	
 	mswsock.dll!71a15ccc() 	
 	kernel32.dll!7c8399f3() 

int ha_myisammrg::write_row(byte * buf)
{
  statistic_increment(table->in_use->status_var.ha_write_count,&LOCK_status);
  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
    table->timestamp_field->set_time();
  if (table->next_number_field && buf == table->record[0])
      update_auto_increment();
  return myrg_write(file,buf); <<<<<<<<<<<<< crash here
}

Sorry, should have been on S2 from the start, an easy workaround is to just make sure not to alter tables that are in a merge set.

It seems like this works on Linux but causes a crash on windows. Thus we need to figure out how it works in linux. i.e if an error code is returned or if it just works by chance.

The windows version should then be fixed to work the same way.

Couldn't get it to crash on Linux, even if _both_ subtables where renamed. Seems strange, my guess is that this works just by luck, but that needs to be checked.

UPDATE: There should be a validation of the merge table when ever it is opened to make sure that the underlying tables are conistent with the view (merge is a kind of view) definition. I'm working with creating such a validation in "int ha_myisammrg::open(const char *name, int mode, uint test_if_locked)".

UPDATE: More specifically, the system crashes because of differences in how windows and linux performs a rename. In windows the original file is deleted, but in linux the file is renamed using hard links. A patch would be to perform a check in the mrg_write to see if the files of the underlying tables still exists.

UPDATE: adding a check to ha_myisammrg::external_lock and verifying that (MI_INFO*)info->s->kfile != -1 prevents the system from crashing on windows. It will leave the merge table in a confused state though, and a FLUSH TABLES is needed to be able to resume operations.

ChangeSet@1.2248, 2006-09-08 12:16:23+02:00, Kristofer.Pettersson@naruto. +3 -0
  Bug#20789 Merge Subtable Rename Causes Crash
   - Added test for bad file descriptor which prevents windows version to crash.

  myisam/mi_locking.c@1.40, 2006-09-08 12:13:57+02:00, Kristofer.Pettersson@naruto. +10 -1
    This patch prevents the windos built to crash if the file is closed.

  mysql-test/r/windows.result@1.2, 2006-09-08 12:13:58+02:00, Kristofer.Pettersson@naruto. +28 -0
    Added test case for the windows built.

  mysql-test/t/windows.test@1.2, 2006-09-08 12:13:58+02:00, Kristofer.Pettersson@naruto. +39 -0
    Added test case for the windows built.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	Kristofer.Pettersson
# Host:	naruto.
# Root:	C:/cpp/bug20789/my50-bug20789

--- 1.39/myisam/mi_locking.c	2006-09-08 13:12:19 +02:00
+++ 1.40/myisam/mi_locking.c	2006-09-08 13:12:19 +02:00
@@ -223,7 +223,16 @@
     default:
       break;				/* Impossible */
     }
-  }
+  } 
+#ifdef __WIN__
+  /*
+     The file has been closed and kfile is -1.
+     See mi_extra.c about implementation of
+     HA_EXTRA_PREPARE_FOR_DELETE. 
+  */
+ else 
+    error=HA_ERR_NO_SUCH_TABLE; 
+#endif                                               
   pthread_mutex_unlock(&share->intern_lock);
 #if defined(FULL_LOG) || defined(_lint)
   lock_type|=(int) (flag << 8);		/* Set bit to set if real lock */

--- 1.1/mysql-test/r/windows.result	2006-09-08 13:12:19 +02:00
+++ 1.2/mysql-test/r/windows.result	2006-09-08 13:12:19 +02:00
@@ -6,3 +6,31 @@
 ERROR 42000: Unknown database 'prn'
 create table nu (a int);
 drop table nu;
+CREATE TABLE `t1` (
+`TIM` datetime NOT NULL,
+`VAL` double default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE `t2` (
+`TIM` datetime NOT NULL,
+`VAL` double default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE `mt` (
+`TIM` datetime NOT NULL,
+`VAL` double default NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST
+UNION=(`t1`,`t2`);
+INSERT INTO mt VALUES ('2006-01-01',0);
+ALTER TABLE `t2` RENAME TO `t`;
+INSERT INTO mt VALUES ('2006-01-01',0);
+ERROR HY000: Can't lock file (errno: 155)
+select * from mt;
+ERROR HY000: Can't lock file (errno: 155)
+FLUSH TABLES;
+select * from mt;
+ERROR HY000: Can't find file: 'mt' (errno: 2)
+ALTER TABLE `t` RENAME TO `t2`;
+INSERT INTO mt VALUES ('2006-01-01',0);
+select * from mt;
+TIM	VAL
+2006-01-01 00:00:00	0
+2006-01-01 00:00:00	0

--- 1.1/mysql-test/t/windows.test	2006-09-08 13:12:19 +02:00
+++ 1.2/mysql-test/t/windows.test	2006-09-08 13:12:19 +02:00
@@ -18,3 +18,42 @@
 drop table nu;
 
 # End of 4.1 tests
+
+#
+# Bug #20789: Merge Subtable Rename Causes Crash
+#
+CREATE TABLE `t1` (
+  `TIM` datetime NOT NULL,
+  `VAL` double default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE `t2` (
+  `TIM` datetime NOT NULL,
+  `VAL` double default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE `mt` (
+  `TIM` datetime NOT NULL,
+  `VAL` double default NULL
+) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST
+UNION=(`t1`,`t2`);
+
+# insert into the merge table and thus open it.
+INSERT INTO mt VALUES ('2006-01-01',0);
+
+# Alter one of the tables that are part of the merge table
+ALTER TABLE `t2` RENAME TO `t`;
+
+# Insert into the merge table that has just been altered
+--error 1015
+INSERT INTO mt VALUES ('2006-01-01',0); 
+--error 1015
+select * from mt;
+
+FLUSH TABLES;
+--error 1017
+select * from mt;
+
+# Alter one of the tables that are part of the merge table
+ALTER TABLE `t` RENAME TO `t2`;
+INSERT INTO mt VALUES ('2006-01-01',0);
+select * from mt; 
+

Approved with small changes.

A new patch has been committed. The first patched caused a test case (multi_update) to fail. The revisted patch will do the same checks but in code directly related to the merge table instead.

Noted in 5.1.12 changelog.

On Windows, inserting into a MERGE table after renaming an underlying MyISAM table caused a server crash.

Setting back to In Progress pending decision on what to do for 5.0.

Approved with small changes. See email.

Bug#22339 is resolved by this patch as well.

Available in 5.0.26.

Available in 5.1.12-beta.

Noted in 5.0.26 changelog. (Was already noted in
5.1.12 changelog earlier.)