Crash on simple "SELECT a FROM b WHERE c="d" LIMIT 1"

Crash on simple "SELECT a FROM b WHERE c="d" LIMIT 1"

am 30.11.2005 15:52:37 von Beat Vontobel

Hello to everybody on the bugs list!

I just subscribed to this list as maybe somebody can help me here.
Since we run MySQL 5.0 in production we had a few repeatable crashes.
Most of them are now isolated and either resolved to existing bugs or
posted as new ones.

But there still remains one being very nasty, as it's not fully
repeatable: We run a MediaWiki (default install) for some
documentation on our intranet. The following simple query from
MediaWiki to determine a user id for a user name (on login)
_sometimes_ crashes the server (as I said it's not fully repeatable -
but it still happens about three times a week on exactly the same
query with probably around 2 to 3 logins per day):
------------------------------------------------------------ ------------
/* User::idFromName */ SELECT user_id FROM `user` WHERE
user_name='username' LIMIT 1
------------------------------------------------------------ ------------

Thats the output from SHOW CREATE TABLE:
------------------------------------------------------------ ------------
CREATE TABLE `user` (
`user_id` int(5) unsigned NOT NULL auto_increment,
`user_name` varchar(255) character set latin1 collate latin1_bin
NOT NULL default '',
`user_real_name` varchar(255) character set latin1 collate
latin1_bin NOT NULL default '',
`user_password` tinyblob NOT NULL,
`user_newpassword` tinyblob NOT NULL,
`user_email` tinytext NOT NULL,
`user_options` blob NOT NULL,
`user_touched` varchar(14) character set latin1 collate latin1_bin
NOT NULL default '',
`user_token` varchar(32) character set latin1 collate latin1_bin
NOT NULL default '',
PRIMARY KEY (`user_id`),
KEY `user_name` (`user_name`(10))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
------------------------------------------------------------ ------------

A table check returns no problems, the table contains only 5 rows
(internal users only).

That's the crash info with a resolved stack trace:
------------------------------------------------------------ ------------
key_buffer_size=536870912
read_buffer_size=2093056
max_used_connections=60
max_connections=300
threads_connected=19
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)
*max_connections = 1751885 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x6947a628
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=0x66018c48, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x814e7f8 _ZN6String19set_or_copy_alignedEPKcjP15charset_info_st + 64
0x4004d96c _end + 934313532
0x813f76b
_Z10make_fieldPcjPhhj16enum_field_typesP15charset_info_stN5F ield13geomet
ry_typeENS4_5utypeEP10st_typelibPKcP8st_table + 1875
0x819ee4d _Z21add_ref_to_table_condP3THDP13st_join_table + 265
0x819eaea _Z24change_to_use_tmp_fieldsP3THDPP4ItemR4ListIS1_ES6_jS6_
+ 234
0x819562b
_Z23create_myisam_from_heapP3THDP8st_tableP15TMP_TABLE_PARAM ib + 39
0x818d1f7 _ZN13st_table_list10prep_whereEP3THDPP4Itemb + 871
0x819031e _ZN4JOIN8optimizeEv + 3762
0x818c6ba _Z15rename_file_extPKcS0_S0_ + 66
0x81621ad handle_bootstrap + 1069
0x8168d82 _Z21mysql_execute_commandP3THD + 20070
0x8160aa6 _ZN7set_varD1Ev + 14
0x81605d1 _ZN17sys_var_thd_ulongD0Ev + 1
0x815fab1 __tcf_39 + 9
0x40047f60 _end + 934290480
0x401da327 _end + 935938039
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. 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 0x8c6e7e8 = /* User::idFromName */ SELECT user_id FROM
`user` WHERE user_name='Bvontob' LIMIT 1
thd->thread_id=50403
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.

Number of processes running now: 0
------------------------------------------------------------ ------------

Maybe somebody familiar with MySQL's internals can determine from the
stack trace what's going on here and at least point me into the right
direction. I'd be very happy to finally make a repeatable test case
out of this to post to the bugs database to finally get rid of this!

Thank you!

Regards,
Beat


-----

"I may have invented [control-alt-delete], but I think Bill made it
famous." (IBM engineer David J. Bradley)

MeteoNews GmbH, Beat Vontobel (IT, Research & Development)

E-Mail: b.vontobel@meteonews.ch
IT Department: +41 (0)43 288 40 54
Main phone: +41 (0)43 288 40 50
Mobile: +41 (0)79 738 79 70




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Crash on simple "SELECT a FROM b WHERE c="d" LIMIT 1"

am 01.12.2005 11:02:05 von Beat Vontobel

Hi

When I looked again at the stack trace in my last mail I realized
that this couldn't make sense: Of course I did a stupid thing and ran
an older trace output still from 5.0.15 through the symbols from
5.0.16. So here's the stack trace again with a more recent crash that
happened on 5.0.16. Sorry about that!

Regards,
Beat

mysqld got signal 11;
....

key_buffer_size=536870912
read_buffer_size=2093056
max_used_connections=64
max_connections=300
threads_connected=11
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)
*max_connections = 1751885 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x665ac628
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=0x661f2768, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8150650 handle_segfault + 356
0x4003b96c _end + 934239804
0x814155d _ZNK12Field_string4typeEv + 13
0x81e4d6c
_Z11get_mm_leafP13st_qsel_paramP4ItemP5FieldP11st_key_partN9 Item_func8Fu
nctypeES2_ + 916
0x81e2fde
_Z12get_mm_partsP13st_qsel_paramP4ItemP5FieldN9Item_func8Fun ctypeES2_11I
tem_result + 286
0x81e14b3 _Z11get_mm_treeP13st_qsel_paramP4Item + 1339
0x81db127 _ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyyb +
1283
0x81a107f
_Z22get_quick_record_countP3THDP10SQL_SELECTP8st_tablePK6Bit mapILj64EEy
+ 59
0x8198546
_Z20make_join_statisticsP4JOINP13st_table_listP4ItemP16st_dy namic_array
+ 2550
0x818f75a _ZN4JOIN8optimizeEv + 750
0x8192946
_Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_ jP8st_orderS
B_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 130
0x818ebd2 _Z13handle_selectP3THDP6st_lexP13select_resultm + 234
0x8164175 _Z21mysql_execute_commandP3THD + 601
0x816ae7a _Z11mysql_parseP3THDPcj + 306
0x8162aa2 _Z16dispatch_command19enum_server_commandP3THDPcj + 1178
0x81625cd _Z10do_commandP3THD + 129
0x8161aad handle_one_connection + 569
0x40035f60 _end + 934216752
0x401cb327 _end + 935876599
New value of fp=(nil) failed sanity check, terminating stack trace!
....
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8cf51f0 = /* User::idFromName */ SELECT user_id FROM
`user` WHERE user_name='Bastienne' LIMIT 1
thd->thread_id=178439
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.

Number of processes running now: 0



Am 30.11.2005 um 15:52 schrieb Beat Vontobel:

> Hello to everybody on the bugs list!
>
> I just subscribed to this list as maybe somebody can help me here.
> Since we run MySQL 5.0 in production we had a few repeatable
> crashes. Most of them are now isolated and either resolved to
> existing bugs or posted as new ones.
>
> But there still remains one being very nasty, as it's not fully
> repeatable: We run a MediaWiki (default install) for some
> documentation on our intranet. The following simple query from
> MediaWiki to determine a user id for a user name (on login)
> _sometimes_ crashes the server (as I said it's not fully repeatable
> - but it still happens about three times a week on exactly the same
> query with probably around 2 to 3 logins per day):
> ------------------------------------------------------------ ----------
> --
> /* User::idFromName */ SELECT user_id FROM `user` WHERE
> user_name='username' LIMIT 1
> ------------------------------------------------------------ ----------
> --
>
> Thats the output from SHOW CREATE TABLE:
> ------------------------------------------------------------ ----------
> --
> CREATE TABLE `user` (
> `user_id` int(5) unsigned NOT NULL auto_increment,
> `user_name` varchar(255) character set latin1 collate latin1_bin
> NOT NULL default '',
> `user_real_name` varchar(255) character set latin1 collate
> latin1_bin NOT NULL default '',
> `user_password` tinyblob NOT NULL,
> `user_newpassword` tinyblob NOT NULL,
> `user_email` tinytext NOT NULL,
> `user_options` blob NOT NULL,
> `user_touched` varchar(14) character set latin1 collate
> latin1_bin NOT NULL default '',
> `user_token` varchar(32) character set latin1 collate latin1_bin
> NOT NULL default '',
> PRIMARY KEY (`user_id`),
> KEY `user_name` (`user_name`(10))
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> ------------------------------------------------------------ ----------
> --
>
> A table check returns no problems, the table contains only 5 rows
> (internal users only).
>
> That's the crash info with a resolved stack trace:
> ------------------------------------------------------------ ----------
> --
> key_buffer_size=536870912
> read_buffer_size=2093056
> max_used_connections=60
> max_connections=300
> threads_connected=19
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size + sort_buffer_size)
> *max_connections = 1751885 K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
>
> thd=0x6947a628
> 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=0x66018c48, backtrace may not be correct.
> Stack range sanity check OK, backtrace follows:
> 0x814e7f8 _ZN6String19set_or_copy_alignedEPKcjP15charset_info_st + 64
> 0x4004d96c _end + 934313532
> 0x813f76b
> _Z10make_fieldPcjPhhj16enum_field_typesP15charset_info_stN5F ield13geom
> etry_typeENS4_5utypeEP10st_typelibPKcP8st_table + 1875
> 0x819ee4d _Z21add_ref_to_table_condP3THDP13st_join_table + 265
> 0x819eaea
> _Z24change_to_use_tmp_fieldsP3THDPP4ItemR4ListIS1_ES6_jS6_ + 234
> 0x819562b
> _Z23create_myisam_from_heapP3THDP8st_tableP15TMP_TABLE_PARAM ib + 39
> 0x818d1f7 _ZN13st_table_list10prep_whereEP3THDPP4Itemb + 871
> 0x819031e _ZN4JOIN8optimizeEv + 3762
> 0x818c6ba _Z15rename_file_extPKcS0_S0_ + 66
> 0x81621ad handle_bootstrap + 1069
> 0x8168d82 _Z21mysql_execute_commandP3THD + 20070
> 0x8160aa6 _ZN7set_varD1Ev + 14
> 0x81605d1 _ZN17sys_var_thd_ulongD0Ev + 1
> 0x815fab1 __tcf_39 + 9
> 0x40047f60 _end + 934290480
> 0x401da327 _end + 935938039
> 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. 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 0x8c6e7e8 = /* User::idFromName */ SELECT user_id
> FROM `user` WHERE user_name='Bvontob' LIMIT 1
> thd->thread_id=50403
> 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.
>
> Number of processes running now: 0
> ------------------------------------------------------------ ----------
> --
>
> Maybe somebody familiar with MySQL's internals can determine from
> the stack trace what's going on here and at least point me into the
> right direction. I'd be very happy to finally make a repeatable
> test case out of this to post to the bugs database to finally get
> rid of this!
>
> Thank you!
>
> Regards,
> Beat
>
>
> -----
>
> "I may have invented [control-alt-delete], but I think Bill made it
> famous." (IBM engineer David J. Bradley)
>
> MeteoNews GmbH, Beat Vontobel (IT, Research & Development)
>
> E-Mail: b.vontobel@meteonews.ch
> IT Department: +41 (0)43 288 40 54
> Main phone: +41 (0)43 288 40 50
> Mobile: +41 (0)79 738 79 70
>
>
>
>
> --
> MySQL Bugs Mailing List
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe: http://lists.mysql.com/bugs?
> unsub=b.vontobel@meteonews.ch
>
>



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org