MySQL Bugs: #13256: MySQL crashes when trying to SELECT ROUTINES table

Bug #13256	MySQL crashes when trying to SELECT ROUTINES table
Submitted:	16 Sep 2005 6:46	Modified:	16 Sep 2005 11:53
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.14-rc	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

When I try to use SELECT * FROM ROUTINES in information_schema db, MySQL crashes.

How to repeat:
use information_schema;
SELECT * FROM ROUTINES;
ERROR 2013 (HY000): Lost connection to MySQL server during query
Stack trace :

0x80d9ccd handle_segfault + 653
0x83806fa __pthread_sighandler + 122
0x8122df5 get_field(st_mem_root*, Field*) + 69
0x81c3444 store_schema_proc(THD*, st_table*, st_table*, char const*, bool, char const*) + 148
0x81c3c37 fill_schema_proc(THD*, st_table_list*, Item*) + 407
0x81c6847 get_schema_tables_result(JOIN*) + 295
0x8140355 JOIN::exec() + 2549
0x81421a1 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 177
0x814285a handle_select(THD*, st_lex*, select_result*, unsigned long) + 266
0x80f3650 mysql_execute_command(THD*) + 9248
0x80fb54c mysql_parse(THD*, char*, unsigned int) + 508
0x80fc0c3 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 2595
0x80fd7a1 handle_one_connection + 1617
0x837c1fe pthread_start_thread + 206
0x83a9b1a __clone + 106

Suggested fix:
Fix the crash :)

Regards,
  Jocelyn

Thank you for your bug report.

I tried to repeat the problem you described both on officially released 5.0.12-beta-nt, and on relatively new 5.0.13-BK build (ChangeSet@1.1941.1.1, 2005-09-13 02:31:47+02:00, kent@mysql.com) on Linux.

select * from routines just works, returning my 13 routines on Windows and 21 on Linux, as expected. So, please, give the additional information on how to repeat the behaviour you described (starting form the version number and where did you get it...).

Have you upgraded from previous version of MySQL (and what one)?

How many routines do you have?

What hardware you used? Your my.cnf content would be of some use too.

Hi,

I'm using a bktree fetched and built yesterday.
I've upgraded from 4.1.14 to 5.0.14-rc, and since I've never defined any
routines, this table should be empty (or at least in the state mysql created it
when launched).
I'm running it on an Athlon MP + sarge debian with 3GB of memory.
When kind of informations do you need from the my.cnf ?

Regards,
  Jocelyn

OK, thank you. Two more questions:

1. Have you executed mysql_fix_privilege_tables script (as described in http://dev.mysql.com/doc/mysql/en/upgrading-from-4-1.html)?

2. Have you tried to install fresh copy, with new data directory?

I was interested in any non-default settings in my.cnf, but the two questions above are more important.

Hi,

I've just try to run the mysql_fix_privilege_tables, but the crash still occurs.
SHOW CREATE TABLE ROUTINES reports :

CREATE TEMPORARY TABLE `ROUTINES` (
  `SPECIFIC_NAME` varchar(64) NOT NULL default '',
  `ROUTINE_CATALOG` varchar(4096) default NULL,
  `ROUTINE_SCHEMA` varchar(64) NOT NULL default '',
  `ROUTINE_NAME` varchar(64) NOT NULL default '',
  `ROUTINE_TYPE` varchar(9) NOT NULL default '',
  `DTD_IDENTIFIER` varchar(64) default NULL,
  `ROUTINE_BODY` varchar(8) NOT NULL default '',
  `ROUTINE_DEFINITION` longtext NOT NULL,
  `EXTERNAL_NAME` varchar(64) default NULL,
  `EXTERNAL_LANGUAGE` varchar(64) default NULL,
  `PARAMETER_STYLE` varchar(8) NOT NULL default '',
  `IS_DETERMINISTIC` varchar(3) NOT NULL default '',
  `SQL_DATA_ACCESS` varchar(64) NOT NULL default '',
  `SQL_PATH` varchar(64) default NULL,
  `SECURITY_TYPE` varchar(7) NOT NULL default '',
  `CREATED` datetime NOT NULL default '0000-00-00 00:00:00',
  `LAST_ALTERED` datetime NOT NULL default '0000-00-00 00:00:00',
  `SQL_MODE` longtext NOT NULL,
  `ROUTINE_COMMENT` varchar(64) NOT NULL default '',
  `DEFINER` varchar(77) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8

I've not tried with a fresh copy yet.

  Jocelyn

Hi,

I made a mistake, I forgot I have some stored procs created a long time ago which was not refering anymore to existing db/table:

mysql> SELECT * FROM proc;
+------+-----------+----------------------------------------------------------------------------------------------------------------+
| name | type      | body                                                                                                           |
+------+-----------+----------------------------------------------------------------------------------------------------------------+
| u    | procedure | create procedure u(y int)
 begin
   declare z1 int;
   set z1 = y;
   insert into taist.procs values(z1);
 end |
| foo1 | procedure | CREATE PROCEDURE foo1(x int) CREATE TABLE pouet SELECT 1                                                       |
+------+-----------+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

Regards,
  Jocelyn

Well I think you can close this bug since the crash seems to be caused by incorrect old stored procs.

Thanks,
  Jocelyn

Thank you for your comments. I am closing it as "Not a bug".