Bug #44816 mysqld crashes in Item_func::print on SHOW CREATE TABLE
Submitted: 12 May 2009 7:54 Modified: 25 Jun 2009 12:17
Reporter: Christian Rishoj Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.0.79, 5.1.32 OS:Any (Debian Lenny)
Assigned to: CPU Architecture:Any
Tags: crash

[12 May 2009 7:54] Christian Rishoj
Description:
mysqld consistently crashes when a script I am running tries to get the definition for a view.

The error log shows:

090512  9:39:20 - 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=16777216
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 345917 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xaf7bed8
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_bottom = 0x6d4523b4 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x28) [0x84e6718]
/usr/sbin/mysqld(handle_segfault+0x39e) [0x820101e]
[0xb7f86420]
/usr/sbin/mysqld(Item_func::print(String*, enum_query_type)+0x6d) [0x818176d]
/usr/sbin/mysqld(Item::print_item_w_name(String*, enum_query_type)+0x2b) [0x8152d9b]
/usr/sbin/mysqld(st_select_lex::print(THD*, String*, enum_query_type)+0x113) [0x8273c13]
/usr/sbin/mysqld(subselect_single_select_engine::print(String*, enum_query_type)+0x29) [0x81bcac9]
/usr/sbin/mysqld(Item_subselect::print(String*, enum_query_type)+0x37) [0x81bf0c7]
/usr/sbin/mysqld(Item::print_item_w_name(String*, enum_query_type)+0x2b) [0x8152d9b]
/usr/sbin/mysqld(st_select_lex::print(THD*, String*, enum_query_type)+0x113) [0x8273c13]
/usr/sbin/mysqld(st_select_lex_unit::print(String*, enum_query_type)+0x72) [0x813d982]
/usr/sbin/mysqld(view_store_create_info(THD*, TABLE_LIST*, String*)+0x14d) [0x831dd9d]
/usr/sbin/mysqld(mysqld_show_create(THD*, TABLE_LIST*)+0x18d) [0x8321c0d]
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x2735) [0x8212495]
/usr/sbin/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x211) [0x8219771]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x43a) [0x8219bba]
/usr/sbin/mysqld(do_command(THD*)+0xe0) [0x821ad90]
/usr/sbin/mysqld(handle_one_connection+0x69f) [0x820994f]
/lib/libpthread.so.0 [0xb7f69f3b]
/lib/libc.so.6(clone+0x5e) [0xb7d63bee]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xafa81d0 = SHOW CREATE TABLE `recentMoversWithLocalStock`
thd->thread_id=4
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
090512 09:39:20 mysqld_safe Number of processes running now: 0
090512 09:39:20 mysqld_safe mysqld restarted

The view was defined as:

CREATE ALGORITHM=UNDEFINED DEFINER=`someuser`@`localhost` SQL SECURITY INVOKER VIEW `recentMoversWithLocalStock` AS select `rm`.`itemId` AS `itemId`,`i`.`artist` AS `artist`,`i`.`title` AS `title`,least(`rm`.`sold0`,`rm`.`sold1`,`rm`.`sold2`,`rm`.`sold3`) AS `minSold`,(least(`rm`.`sold0`,`rm`.`sold1`,`rm`.`sold2`,`rm`.`sold3`) * `rm`.`fpr`) AS `minSoldFpr`,(select coalesce(sum(`si`.`stock`),0) AS `COALESCE(SUM(stock),0)` from (`supplierItems` `si` join `suppliers` `s` on((`s`.`id` = `si`.`supplierId`))) where ((`si`.`itemId` = `rm`.`itemId`) and `s`.`isLocalStock` and (`si`.`stock` > 0))) AS `localStock`,`si`.`supplierId` AS `supplierId`,`s`.`name` AS `supplierName` from (((`recentMovers` `rm` join `items` `i` on((`i`.`id` = `rm`.`itemId`))) join `supplierItems` `si` on((`i`.`preferredSupplierItemId` = `si`.`id`))) join `suppliers` `s` on((`si`.`supplierId` = `s`.`id`)))

How to repeat:

$ mysql dbname
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.32-1-log (Debian)

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

mysql> SHOW CREATE TABLE `recentMoversWithLocalStock`\G
ERROR 2013 (HY000): Lost connection to MySQL server during query

The crash only occurs on a single box here. You might not be able to reproduce the problem without our data files.
[12 May 2009 8:01] Christian Rishoj
Possible reason: The view definition refers to another view (`recentMovers`), which does not exist (it has been deleted).
[12 May 2009 8:33] Sveta Smirnova
Thank you for the report.

Yes, we are not able to reproduce the problem using generic view. Please provide output of SHOW CREATE TABLE for all underlying tables and views.
[12 May 2009 14:24] Christian Rishoj
I have attached a structural dump.

A quick trial of loading the structure, dropping the referred view and inspecting the referring view on my local box did not reproduce the problem, though.
[13 May 2009 10:26] Sveta Smirnova
Thank you for the feedback.

Problem still not repeatable for me. Additionally there is a chance this was fixed after 5.1.32. Could you please try current version 5.1.34 and if problem still exists provide more information related to the problem: dump of tables or output of SHOW TABLE STATUS for all underlying tables and your configuration file.
[13 May 2009 11:45] Christian Rishoj
The tables have since been dropped and re-created, so I am afraid I can no longer reproduce this error or provide TABLE STATUS output.
[19 May 2009 14:29] MySQL Verification Team
I couldn't repeat too.
[20 May 2009 6:30] Sveta Smirnova
Thank you for the feedback.

As 2 of us can not repeat the problem and problem is not repeatable on you side I close the report as "Can't repeat".

If you meet this again and would be able to provide more details how to repeat feel free to open the report.
[30 Oct 2009 8:23] MySQL Verification Team
repeatable in 5.0.77, and 5.0.79, but not repeatable in 5.0.82..

mysqld-debug.exe!Item_sum::print()[item_sum.cc:449]
mysqld-debug.exe!Item::print_item_w_name()[item.cc:443]
mysqld-debug.exe!st_select_lex::print()[sql_select.cc:15847]
mysqld-debug.exe!st_select_lex_unit::print()[sql_lex.cc:1625]
mysqld-debug.exe!view_store_create_info()[sql_show.cc:1288]
mysqld-debug.exe!mysqld_show_create()[sql_show.cc:433]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:3526]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:6313]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1938]
mysqld-debug.exe!do_command()[sql_parse.cc:1628]
mysqld-debug.exe!handle_one_connection()[sql_parse.cc:1234]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_threadstart()[thread.c:196]
kernel32.dll!FlsSetValue()
[4 May 2010 5:12] MySQL Verification Team
the fix for bug #43354 might have fixed this too