Bug #19192 CHECK TABLE EXTENDED / REPAIR TABLE show no errors. ALTER TABLE crashes
Submitted: 19 Apr 2006 11:03 Modified: 15 Jun 2006 3:54
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.0.19,5.0-bk OS:Windows (Windows)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[19 Apr 2006 11:03] Shane Bester
Description:
Although CHECK TABLE x EXTENDED and REPAIR TABLE x both report no problems, a sequence of two ALTER TABLE statements causes a repeatable crash.

CREATE TABLE x (
`dirid` bigint(20) unsigned NOT NULL,
`dirname` text NOT NULL,
PRIMARY KEY (`dirid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> CHECK TABLE x  EXTENDED;
+-----------------------+-------+----------+----------+
| Table                 | Op    | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| obs.x    | check | status   | OK       |
+-----------------------+-------+----------+----------+
1 row in set (0.08 sec)

mysql> REPAIR TABLE x ;
+-----------------------+--------+----------+----------+
| Table                 | Op     | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| obs.x  | repair | status   | OK       |
+-----------------------+--------+----------+----------+
1 row in set (0.09 sec)

How to repeat:
Put MYD, MYI, frm files into a 5.0.19 database.  Run

ALTER TABLE x ALTER COLUMN dirid DROP DEFAULT;
ALTER TABLE x ADD COLUMN displayname text NOT NULL;

See attached private files for the tables.

Suggested fix:
not sure.
[19 Apr 2006 11:12] MySQL Verification Team
Stack trace:

LeadUpVec+0x5e [memcpy.asm @ 261]
_mi_rec_pack [mi_dynrec.c @ 725]
_mi_write_blob_record [mi_dynrec.c @ 88]
mi_write [mi_write.c @ 147]
ha_myisam::write_row [ha_myisam.cpp @ 320]
copy_data_between_tables [sql_table.cpp @ 4100]
mysql_alter_table [sql_table.cpp @ 3749]
mysql_execute_command [sql_parse.cpp @ 3021]
mysql_parse [sql_parse.cpp @ 5710]
dispatch_command [sql_parse.cpp @ 1720]
do_command [sql_parse.cpp @ 1516]
handle_one_connection [sql_parse.cpp @ 1159]
pthread_start [my_winthread.c @ 63]
_threadstart [thread.c @ 196]
kernel32!BaseThreadStart+0x34

  if (type == FIELD_BLOB)
      {
	if (!blob->length)
	  flag|=bit;
	else
	{
	  char *temp_pos;
	  size_t tmp_length=length-mi_portable_sizeof_char_ptr;
	  memcpy((byte*) to,from,tmp_length);
	  memcpy_fixed(&temp_pos,from+tmp_length,sizeof(char*)); 
	  memcpy(to+tmp_length,temp_pos,(size_t) blob->length);  <-------CRASH
	  to+=tmp_length+blob->length;
	}
[20 Apr 2006 18:19] John David Duncan
I can reproduce this bug on FreeBSD/i386 but not on Mac OS X/PPC.  (Maybe that means it shows up only on intel?)
[20 Apr 2006 18:29] Sergey Vojtovich
Hi Shane,
I'm working on very similiar bug report: BUG#17001.
Could you please apply following patch and let me know if it solves this problem?
===== sql/sql_table.cc 1.303 vs edited =====
--- 1.303/sql/sql_table.cc      2006-03-30 18:14:51 +05:00
+++ edited/sql/sql_table.cc     2006-04-20 19:45:59 +05:00
@@ -3197,7 +3197,7 @@
   uint db_create_options, used_fields;
   enum db_type old_db_type,new_db_type;
   bool need_copy_table;
-  bool no_table_reopen= FALSE;
+  bool no_table_reopen= FALSE, varchar= FALSE;
   DBUG_ENTER("mysql_alter_table");

   thd->proc_info="init";
@@ -3399,6 +3399,8 @@
   Field **f_ptr,*field;
   for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++)
   {
+    if (field->type() == MYSQL_TYPE_STRING)
+      varchar= TRUE;
     /* Check if field should be dropped */
     Alter_drop *drop;
     drop_it.rewind();
@@ -3665,7 +3667,8 @@
                     ~(ALTER_CHANGE_COLUMN_DEFAULT|ALTER_OPTIONS) ||
                     (create_info->used_fields &
                      ~(HA_CREATE_USED_COMMENT|HA_CREATE_USED_PASSWORD)) ||
-                    table->s->tmp_table);
+                    table->s->tmp_table ||
+                    (table->s->frm_version < FRM_VER_TRUE_VARCHAR && varchar));
   create_info->frm_only= !need_copy_table;

   /*
[20 Apr 2006 21:06] MySQL Verification Team
Sergey, I tried the patch and it crashed in exactly the same place as before - same stack trace also :(
[21 Apr 2006 9:46] Sergey Vojtovich
Shane,

thanks for testing my patch. Indeed I haven't noticed that symptoms are a bit different. But caused by same reason:
ALTER TABLE x ALTER COLUMN dirid DROP DEFAULT;
At this point we have frm created by 5.0 and MYD/MYI created by earlier version.

SELECT * FROM x;
Server crash.

My patch ensures that running ALTER TABLE always recreates MYD/MYI files for tables created
by earlier MySQL version when there is VARCHAR columns.

However your dataset doesn't have VARCHARs. That is why my patch wasn't useful. I have to find
better solution for this problem.
[21 Apr 2006 10:48] Sergey Vojtovich
Shane,

I was able to find better solution for this problem. At least it seems to work well.
This fix keeps frm and MYD/MYI versions in sync. However having frm file created
in 5.0 and MYI/MYD in earlier versions will likely result in server crash.

===== sql/sql_table.cc 1.303 vs edited =====
--- 1.303/sql/sql_table.cc      2006-03-30 18:14:51 +05:00
+++ edited/sql/sql_table.cc     2006-04-21 15:43:30 +05:00
@@ -3665,7 +3665,8 @@
                     ~(ALTER_CHANGE_COLUMN_DEFAULT|ALTER_OPTIONS) ||
                     (create_info->used_fields &
                      ~(HA_CREATE_USED_COMMENT|HA_CREATE_USED_PASSWORD)) ||
-                    table->s->tmp_table);
+                    table->s->tmp_table ||
+                    table->s->mysql_version < 50300);
   create_info->frm_only= !need_copy_table;

   /*
[21 Apr 2006 11:23] MySQL Verification Team
Sergey, with the last patch applied, the server doesn't crash anymore on this ALTER TABLE statement ;-)
[3 May 2006 23:00] Sergei Golubchik
Shane, could you try to find out how this table was created ? What MySQL version, what sequence of DDL statements.
[1 Jun 2006 9:57] Sergey Vojtovich
I was able to repeat reported table layout with 4.0.24. This problem was solved in 4.0.25 with second patch for BUG#6236 (by Monty).
[1 Jun 2006 13:16] Sergey Vojtovich
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7159
[7 Jun 2006 15:02] Sergey Vojtovich
Pushed into tree currently marked as 5.0.23.
[14 Jun 2006 11:42] Sergey Vojtovich
Pushed into tree currently marked as 5.1.12.
[15 Jun 2006 3:54] Paul DuBois
Noted in 5.0.23, 5.1.12 changelogs.

An ALTER TABLE operation that does not need to copy data,
when executed on a table created prior to MySQL 4.0.25,
could result in a server crash for subsequent accesses to the table.