| Bug #30754 | mysql crash | ||
|---|---|---|---|
| Submitted: | 1 Sep 2007 5:36 | Modified: | 19 Apr 2008 16:01 |
| Reporter: | Ken Chen | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.45-debug-log | OS: | Linux (2.6.18-8.el5 #1 SMP) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | crash | ||
[1 Sep 2007 5:36]
Ken Chen
[1 Sep 2007 5:40]
Ken Chen
Create Table: CREATE TABLE `boardCache` (
`uid` bigint(10) unsigned NOT NULL,
`memberID` bigint(10) unsigned NOT NULL default '0',
`fromID` int(10) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`mood` tinyint(3) unsigned NOT NULL default '0',
`attrib` tinyint(3) unsigned NOT NULL default '0',
`text` text,
`reply_text` text NOT NULL,
`reply_date` datetime NOT NULL,
PRIMARY KEY (`uid`),
KEY `memberID_2` (`memberID`,`date`,`attrib`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Create Table: CREATE TABLE `board2007` (
`uid` bigint(10) unsigned NOT NULL,
`memberID` bigint(10) unsigned NOT NULL default '0',
`fromID` int(10) unsigned NOT NULL default '0',
`fromIP` varchar(18) default NULL,
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`mood` tinyint(3) unsigned NOT NULL default '0',
`attrib` tinyint(3) unsigned NOT NULL default '0',
`reply` int(10) unsigned NOT NULL default '0',
`text` text,
PRIMARY KEY (`uid`),
KEY `reply` (`reply`,`attrib`),
KEY `fromID` (`fromID`,`date`,`memberID`),
KEY `memberID_2` (`memberID`,`date`,`attrib`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Create Table: CREATE TABLE `boardreply2007` (
`reply` bigint(10) unsigned NOT NULL,
`memberID` bigint(10) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`text` text,
PRIMARY KEY (`reply`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 4. row ***************************
Name: board2007
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1592665
Avg_row_length: 272
Data_length: 434094080
Max_data_length: 0
Index_length: 226574336
Data_free: 0
Auto_increment: NULL
Create_time: 2007-08-28 09:34:09
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 7168 kB
*************************** 6. row ***************************
Name: boardCache
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 69397
Avg_row_length: 549
Data_length: 38125568
Max_data_length: 0
Index_length: 4325376
Data_free: 0
Auto_increment: NULL
Create_time: 2007-08-28 09:40:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 72704 kB
*************************** 10. row ***************************
Name: boardreply2007
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1359862
Avg_row_length: 241
Data_length: 328056832
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2007-08-28 09:47:24
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 4096 kB
[2 Sep 2007 1:27]
Ken Chen
070902 6:44:10 - 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 against 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=268435456 read_buffer_size=1044480 max_used_connections=151 max_connections=160 threads_connected=93 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 589182 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0xaa230d8 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... Cannot determine thread, fp=0x357d010, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8220bb7 0xae11534 0x8311ff9 0x8312041 0x82a3765 0x82a3c5f 0x8297b60 0x83c8967 0x82980e0 0x829062b 0x8241fbb 0x8249ff7 0x823c35e 0x823b7bb 0x823a538 0x4f8532db 0x4f7ad14e New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Res 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 0x94c49a78 is invalid pointer thd->thread_id=33299 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Writing a core file Number of processes running now: 0 [root@db1 /tmp]# /usr/local/mysql/bin/resolve_stack_dump -s /tmp/mysqld.sym -n q 0x8220bb7 handle_segfault + 623 0xae11534 _end + 38618628 0x8311ff9 _ZN10SQL_SELECTD9Ev + 15 0x8312041 _ZN10SQL_SELECTD1Ev + 25 0x82a3765 _ZN13st_join_table7cleanupEv + 29 0x82a3c5f _ZN4JOIN7cleanupEb + 219 0x8297b60 _ZN4JOIN7destroyEv + 314 0x83c8967 _ZN13st_select_lex7cleanupEv + 171 0x82980e0 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sel + 1142 0x829062b _Z13handle_selectP3THDP6st_lexP13select_resultm + 443 0x8241fbb _Z21mysql_execute_commandP3THD + 15885 0x8249ff7 _Z11mysql_parseP3THDPKcjPS2_ + 505 0x823c35e _Z16dispatch_command19enum_server_commandP3THDPcj + 2968 0x823b7bb _Z10do_commandP3THD + 695 0x823a538 handle_one_connection + 1326 0x4f8532db _end + 1190224811 0x4f7ad14e _end + 1189544478
[2 Sep 2007 19:42]
Valeriy Kravchuk
Thank you for a problem report. Please, send your my.cnf file content and the results of: EXPLAIN SELECT B.uid,'117985',B.fromID,B.date,B.mood,B.attrib,B.text, R.text AS reply_text,R.date AS reply_date FROM board2007 B LEFT JOIN boardreply2007 R ON R.reply=B.uid WHERE B.date= (SELECT MAX(date) FROM board2007 WHERE date<(SELECT MIN(date) FROM boardCache WHERE memberID='117985') and memberID='117985') and B.memberID='117985'\G (part of that REPLACE that lead to crash originally).
[3 Sep 2007 2:20]
Ken Chen
mysql> EXPLAIN SELECT
B.uid,'117985',B.fromID,B.date,B.mood,B.attrib,B.text,
-> B.uid,'117985',B.fromID,B.date,B.mood,B.attrib,B.text,
-> R.text AS reply_text,R.date AS reply_date FROM board2007 B LEFT JOIN boardreply2007 R ON
-> R.reply=B.uid WHERE B.date=
-> (SELECT MAX(date) FROM board2007 WHERE date<(SELECT MIN(date) FROM boardCache WHERE
-> memberID='117985') and memberID='117985')
-> and B.memberID='117985'\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: B
type: ref
possible_keys: memberID_2
key: memberID_2
key_len: 16
ref: const,const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: R
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: friend.B.uid
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
*************************** 4. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
4 rows in set (0.13 sec)
[17 Feb 2008 15:20]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.51a, and inform about the results.
[7 Mar 2008 2:00]
Ken Chen
sorry for delay. The platform now is FreeBSD 7.0
mysql> EXPLAIN SELECT
-> B.uid,'117985',B.fromID,B.date,B.mood,B.attrib,B.text,
-> R.text AS reply_text,R.date AS reply_date FROM board2007 B LEFT JOIN boardreply2007 R ON
-> R.reply=B.uid WHERE B.date=
-> (SELECT MAX(date) FROM board2007 WHERE date<(SELECT MIN(date) FROM boardCache WHERE
-> memberID='117985') and memberID='117985')
-> and B.memberID='117985'\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: B
type: ref
possible_keys: memberID_2
key: memberID_2
key_len: 16
ref: const,const
rows: 7
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: R
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: friend.B.uid
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
*************************** 4. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
4 rows in set (0.00 sec)
[8 Mar 2008 10:20]
Valeriy Kravchuk
What version of MySQL do you use on FreeBSD 7.0? 5.0.51 or newer?
[8 Mar 2008 16:01]
Ken Chen
Version: '5.0.51a-log' Binary distribution from MySQL.
[19 Mar 2008 16:01]
Susanne Ebrecht
The binaries for FreeBSD that you can download from http://dev.mysql.com/downloads/mysql/5.0.html#freebsd are only for FreeBSD 6.x. Because there are two much changes between FreeBSD 6 and FreeBSD 7 they won't work as expected with FreeBSD 7. We don't provide binaries for FreeBSD 7 at the moment. Please take the source from http://dev.mysql.com/downloads/mysql/5.0.html#source compile it and try if you still get this weird behaviour.
[19 Apr 2008 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
