4 messages in com.mysql.lists.bugsRe: innodb die on "alter table add key" | From | Sent On | Attachments |
|---|---|---|
| SDiZ (UHome) | 29 Dec 2001 10:00 | |
| Heikki Tuuri | 29 Dec 2001 11:21 | |
| Michael Widenius | 30 Dec 2001 05:12 | |
| Michael Widenius | 01 Apr 2002 06:46 |
| Subject: | Re: innodb die on "alter table add key" |
|---|---|
| From: | Heikki Tuuri (Heik...@innobase.inet.fi) |
| Date: | 12/29/2001 11:21:56 AM |
| List: | com.mysql.lists.bugs |
Hi!
InnoDB: clust index for table akzone/#sql2-297e-26b9 for index PRIMARY not found
The assertion failure means that the table object the query tried to use in the InnoDB dictionary cache did not have any indexes, not even the clustered key which every table should have.
ALTER TABLE did not crash, but a SELECT query you concurrently had running on the table crashed.
Looks like MySQL does not prevent queries to the table being altered. Maybe the old table is dropped when the select query below is still running, and the table object it tries to use in InnoDB dictionary cache is obsolete when the select query finally runs.
Workaround: do
LOCK TABLES yourtablename WRITE; ALTER TABLE yourtablename ...; UNLOCK TABLES;
That seems to block queries in the table being altered, and will probably eliminate the crash. Please test it!
I forward this message to Monty. He should check if MySQL allows queries to the table being dropped in an ALTER TABLE. They should be blocked by the MySQL level of code.
Regards,
Heikki Innobase Oy
mysql/innodb die on "alter table akzone.Forum_Topic add key (Member_ID);" with latest mysql-3.23.47-max. I can repeat this 3 times on my own machine. the table was convected from myisam to innodb today using alter table
How-to-repeat: alter table akzone.Forum_Topic add key (Member_ID);
Table structure included for reference # # Table structure for table `Forum_Topic` #
CREATE TABLE Forum_Topic ( Topic_ID mediumint(8) unsigned NOT NULL auto_increment, Web_Site_ID varchar(20) NOT NULL default '', Forum_Category_ID varchar(20) default NULL, Forum_ID varchar(20) NOT NULL default '', Member_ID varchar(20) NOT NULL default '', Guest varchar(20) default NULL, IP varchar(20) default NULL, Time datetime NOT NULL default '0000-00-00 00:00:00', Icon varchar(20) default NULL, Subject varchar(100) NOT NULL default '', Message text NOT NULL, Reply smallint(5) unsigned default '0', Hit mediumint(8) unsigned NOT NULL default '0', Vote mediumint(8) unsigned NOT NULL default '0', Rate mediumint(8) unsigned NOT NULL default '0', Month_Hit mediumint(8) unsigned NOT NULL default '0', Month_Vote mediumint(8) unsigned NOT NULL default '0', Month_Rate mediumint(8) unsigned NOT NULL default '0', Grade char(1) default NULL, Last_Reply_Time datetime NOT NULL default '0000-00-00 00:00:00', Last_Reply_Member_ID varchar(20) default NULL, Anti_Guest tinyint(1) unsigned NOT NULL default '0', Banned tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (Topic_ID), KEY Forum_Category_ID (Forum_Category_ID), KEY Forum_ID (Forum_ID), KEY Last_Reply_Time (Last_Reply_Time), KEY Time (Time), KEY Banned (Banned), KEY Web_Site_ID (Web_Site_ID), KEY Member_ID (Member_ID), KEY Member_ID_2 (Member_ID) ) TYPE=InnoDB;
/usr/local/mysql/libexec/mysqld: ready for connections InnoDB: clust index for table akzone/#sql2-297e-26b9 for index PRIMARY not found InnoDB: Assertion failure in thread 65888280 in file row0row.c line 474 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mys...@lists.mysql.com mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail
key_buffer_size=8384512 record_buffer=2093056 sort_buffer=4194296 max_used_connections=131 max_connections=512 threads_connected=24 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 3151864 K bytes of memory Hope that's ok, if not, decrease some variables in the equation
InnoDB: Thread 12292 stopped in file os0sync.c line 374 InnoDB: Thread 40636431 stopped in file fil0fil.c line 1125 InnoDB: Thread 8195 stopped in file os0sync.c line 374 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x807bb5f handle_segfault__Fi + 383 0x82a94aa pthread_sighandler + 154 0x819a0d2 row_build_row_ref_in_tuple + 530 0x819e1c5 row_sel_get_clust_rec_for_mysql + 41 0x81a0d99 row_search_for_mysql + 4473 0x80d104f rnd_next__11ha_innobasePc + 103 0x80c1659 rr_sequential__FP14st_read_record + 125 0x80a15c9 sub_select__FP4JOINP13st_join_tableb + 253 0x80a1303 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 395 0x809a7a9 mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4P4ItemT4 UiP13select_result + 5769 0x8082517 mysql_execute_command__Fv + 779 0x8086356 mysql_parse__FP3THDPcUi + 210 0x808188d do_command__FP3THD + 1309 0x8080d5c handle_one_connection__FPv + 548 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0xa27ff18 = SELECT Member_ID FROM Forum_Topic WHERE Member_ID = 'joeyjoey49431143' thd->thread_id=16078
Successfully dumped variables, if you ran with --log, take a look at the details of what thread 16078 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid
The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash
Number of processes running now: 0 011230 01:37:54 mysqld restarted InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 231094585 InnoDB: Doing recovery: scanned up to log sequence number 0 231159808 InnoDB: Doing recovery: scanned up to log sequence number 0 231225344 InnoDB: Doing recovery: scanned up to log sequence number 0 231290880 InnoDB: Doing recovery: scanned up to log sequence number 0 231356416 InnoDB: Doing recovery: scanned up to log sequence number 0 231421952 InnoDB: Doing recovery: scanned up to log sequence number 0 231487488 InnoDB: Doing recovery: scanned up to log sequence number 0 231553024 InnoDB: Doing recovery: scanned up to log sequence number 0 231618560 InnoDB: Doing recovery: scanned up to log sequence number 0 231684096 InnoDB: Doing recovery: scanned up to log sequence number 0 231749632 InnoDB: After this prints a line for every 10th scan sweep: InnoDB: Doing recovery: scanned up to log sequence number 0 232404992 InnoDB: Doing recovery: scanned up to log sequence number 0 233060352 InnoDB: Doing recovery: scanned up to log sequence number 0 233715712 InnoDB: Doing recovery: scanned up to log sequence number 0 234371072 InnoDB: Doing recovery: scanned up to log sequence number 0 235026432 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents:0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 011230 1:38:06 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections
---------------------------------------------------------------------------
-----