Bug #17204 second CALL to procedure crashes Server
Submitted: 7 Feb 2006 21:04 Modified: 31 May 2006 1:07
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.17 nt/5.0BK/5.1BK OS:Microsoft Windows (WinXP/Linux)
Assigned to: Sergei Glukhov CPU Architecture:Any

[7 Feb 2006 21:04] Roland Bouman
Description:
A stored procedure is executed properly when called for the first time. However, when the procedure is called for the second time with the same arguments within the same session, the server crashes.

It was expected that the procedure would behave exactly similar for both calls; not to crash the server.

How to repeat:
login as root  on the commandline tool and issue these statements:

use test;

create procedure bogus (
    p_schema varchar(64)
,   p_table  varchar(64)
)
select   concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenced_column_name
             order by ordinal_position
             )
         )
from   information_schema.key_column_usage kcu
where kcu.referenced_table_schema = p_schema
and kcu.referenced_table_name = p_table
group by constraint_schema
,        constraint_name
,        table_schema
,        table_name
;

mysql> call bogus('oo','object');
+----------------------------------------------------------------
-----------------------------------------------------------------
| concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenced_column_ |
+----------------------------------------------------------------
-----------------------------------------------------------------
| oo.property (property_ibfk_1 )member_object_id -> id

+----------------------------------------------------------------
-----------------------------------------------------------------
1 row in set (0.76 sec)

Query OK, 0 rows affected (0.76 sec)

mysql> call bogus('oo','object');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

The table for wich the proc is called:

CREATE TABLE `object` (
  `id` int(10) unsigned NOT NULL,
  `object_definition_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `object_FKIndex1` (`object_definition_id`),
  CONSTRAINT `object_ibfk_1` FOREIGN KEY (`object_definition_id`) REFERENCES `object_definition` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Suggested fix:
Don't crash the server. Instead, allow the procedure to be called for a second time (and a third time, and...well you get the idea)
[7 Feb 2006 21:41] Markus Popp
I could also produce this bug on MySQL 5.0.18 and I also managed to produce this bug on a Linux server (also with 5.0.18).

My MySQL 5.1.5 server on Windows even crashed the first time when I called the procedure.
[7 Feb 2006 22:47] Miguel Solorzano
I was unable to repeat on 5.0.18 Windows. Could you please provide
the complete script test including the create schema/table which is
used with the procedure.

Thanks in advance.
[7 Feb 2006 23:08] Beat Vontobel
Roland, you should probably give the complete minimal database dump (all schemas and tables, not only those involved in the exact query) that you can use on a clean install to reproduce the bug. I was not yet able to reproduce it on Linux and Mac OS X. But Giuseppe Maxia experienced the same bug on two different servers, but not on a third one. After I told him to drop a few tables the bug disappeared on one of the two servers.

I'd also give it an S1. Such crashing bugs are really complete show stoppers.
[7 Feb 2006 23:40] Roland Bouman
test and oo schema

Attachment: test 20060208 0040.sql (text/plain), 2.75 KiB.

[7 Feb 2006 23:42] Roland Bouman
Ok, i just verified that the crash still occurs when using a proc in the same schema wehre the table is located:

mysql> use test;
Database changed
mysql> create table p(id int not null);
ERROR 1050 (42S01): Table 'p' already exists
mysql> call bogus(schema(),'p');
+---------------------------------------------------------------
----------------------------------------------------------------
| concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenced_column_ |
+---------------------------------------------------------------
----------------------------------------------------------------
| test.c (c_ibfk_1 )name -> name,type -> type

+---------------------------------------------------------------
----------------------------------------------------------------
1 row in set (1.33 sec)

Query OK, 0 rows affected (1.33 sec)

mysql> call bogus(schema(),'p');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

(Accidentally, the file is still marked oo)
[8 Feb 2006 9:53] Giuseppe Maxia
I could reproduce the bug using my normal testing server (MySQL 5.0.18 on Linux Fedora4).
Then Beat Vontobel suggested that I might have some legacy database files (created with previous versions of MySQL 4.1.x and 5.0.x). Indeed I had them, and after I dumped all databases and rebuilt them, that bug disappered.
My initial test case was this:
#---- CUT HERE ----
create database if not exists test;
use test;
drop table if exists tt2;
drop table if exists tt1;
CREATE TABLE tt1 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val INT UNSIGNED NOT NULL) ENGINE=InnoDB;

CREATE TABLE tt2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  id_1 INT UNSIGNED NOT NULL,
  val INT UNSIGNED NOT NULL,
  FOREIGN KEY (id_1) REFERENCES tt1(id)) ENGINE=InnoDB;

delimiter //

drop function if exists test_func //
create function test_func (p_database VARCHAR(50), p_table VARCHAR(50))
RETURNS text
READS SQL DATA
BEGIN
    declare mytext text;
    declare aline text;
    declare done boolean default false;
    declare get_them cursor for
        SELECT
          COLUMN_NAME
          FROM information_schema.KEY_COLUMN_USAGE
          WHERE
            REFERENCED_TABLE_SCHEMA =  p_database
            AND REFERENCED_TABLE_NAME = p_table
          ;
    declare continue handler for not found
        set done = true;
    open get_them;
    set mytext = '';
    /*
    DEPS_LOOP:
    loop
        fetch get_them into aline;
        if (done) then
            leave DEPS_LOOP;
        end if;
        set mytext = concat(mytext, '\n', aline);
    end loop;
    */
    close get_them;
    return mytext;
END//
delimiter ;
#---- CUT HERE ----
As you can see,  I commented out the fetching loop, and the crashing bug was here, with the same error as reported by Roland.
[8 Feb 2006 9:57] Beat Vontobel
Just note that even if the removal of the legacy tables helped, it doesn't seem to be connected only to those. Markus could reproduce it on a server without any legacy tables.
[8 Feb 2006 9:59] Giuseppe Maxia
One more clue, perhaps useful. Here is the stack trace of my server when the crash occurred:

0x817be28 handle_segfault + 356
0xc47420 (?)
(nil)
0x818fd34 _Z21mysql_execute_commandP3THD + 508
0x826f7a4 _Z17mysql_open_cursorP3THDjP13select_resultPP18Server_side_cursor + 332
0x827bd46 _ZN9sp_cursor4openEP3THD + 66
0x8278fca _ZN14sp_instr_copen9exec_coreEP3THDPj + 34
0x8277f6e _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr + 318
0x8278f4d _ZN14sp_instr_copen7executeEP3THDPj + 73
0x8275ae0 _ZN7sp_head7executeEP3THD + 772
0x827608a _ZN7sp_head16execute_functionEP3THDPP4ItemjP5Field + 558
0x813096c _ZN12Item_func_sp12execute_implEP3THDP5Field + 116
0x8133bd4 _ZN12Item_func_sp7val_strEP6String + 48
0x8116cc4 _ZN4Item4sendEP8ProtocolP6String + 160
0x8170fed _ZN11select_send9send_dataER4ListI4ItemE + 201
0x81bc49e _ZN4JOIN4execEv + 658
0x81bdd65
_Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel
+ 305
0x81b9dee _Z13handle_selectP3THDP6st_lexP13select_resultm + 234
0x818fd81 _Z21mysql_execute_commandP3THD + 585
0x8196c06 _Z11mysql_parseP3THDPcj + 306
0x818e6be _Z16dispatch_command19enum_server_commandP3THDPcj + 1178
0x818e1e9 _Z10do_commandP3THD + 129
0x818d6f1 handle_one_connection + 609
0xd46b80 (?)
0xbc8dee (?)
[8 Feb 2006 11:03] Miguel Solorzano
Hi,
Thank you for the feedback.
Still I was unable to repeat with the script provided by Roland:

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> create table p(id int not null);
ERROR 1050 (42S01): Table 'p' already exists
mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> call bogus(schema(),'p')\G
*************************** 1. row ***************************
concat(
             table_schema
         ,   '.'
         ,   table_name
         ,   ' ('
         ,   constraint_name
         ,   ' )'
         ,   group_concat(
                 column_name
             ,   ' -> '
             ,   referenc: test.c (c_ibfk_1 )name -> name,type -> type
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Then I think this issue is repeatable (like another bugs already reported)
when was done an upgrade without to dump/restore the databases
created with earliest version. Could you please try to repeat on fresh
install.

Thanks in advance.
[8 Feb 2006 11:43] Roland Bouman
Ok Miguel, thanks for testing. 

I will do a fresh install and try to repeat it there. But I won't do so right now (sorry bit busy at the moment); Tonight or maybe tomorrow even, I will.

I'll post back as soon as I get the chance.

regards Roland. 

(Anyone else that can reproduce it on a fresh install in the mean time - please don't wait for me - go ahead and fire your comments. TIA)
[8 Feb 2006 16:25] Miguel Solorzano
Thank you. I will left in can't repeat status until someone can provide
a repeatable test case.
[8 Feb 2006 22:17] Beat Vontobel
Miguel, did you test on 5.0.19-bk? Could this be a duplicate of Bug #15533? (I can't tell from the information I see there.) That one would have been already fixed.
[8 Feb 2006 22:35] Miguel Solorzano
Hi Beat,
I tested against the Windows server release 5.0.18, that is my
point that the upgrade issue is a serious candidate of the source
of the crash. Also my co-worker Valeriy tested against on Linux
with 5.0.19 (latest source) without to repeat the crash.

Thanks for the feedback.
[5 Mar 2006 2:46] Markus Popp
This bug occurs on 5.1.7, too.

Here's the stored procedure (which is located in the funcs database):

DELIMITER $$

DROP PROCEDURE IF EXISTS `funcs`.`getDeps` $$
CREATE PROCEDURE `getDeps`(pDatabase VARCHAR(200),
                         pTable VARCHAR(200))
    SQL SECURITY INVOKER
BEGIN

SELECT CONCAT(REFERENCED_COLUMN_NAME, ' -> (',
              TABLE_SCHEMA, '.',
              TABLE_NAME, ') ',
              COLUMN_NAME) AS `references`
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = @pDatabase
  AND REFERENCED_TABLE_NAME = @pTable
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

END $$

DELIMITER ;

And here's my call to the SP:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.7-beta-nt-max

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

mysql> use mpopp
Database changed
mysql> call funcs.getDeps(database(), 'mysqluc');
Empty set (21.53 sec)

Query OK, 0 rows affected (21.53 sec)

mysql> call funcs.getDeps(database(), 'mysqluc');
ERROR 2013 (HY000): Lost connection to MySQL server during query
[5 Mar 2006 3:06] Markus Popp
I could also reproduce this bug on Linux (SuSE Linux 10.0 with MySQL 5.0.18-Max).
[9 Mar 2006 0: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".
[9 Mar 2006 23:29] Markus Popp
This bug still occurs in MySQL 5.0.19 (and also in 5.1.7 and 5.1.8BK).
[9 Mar 2006 23:52] Roland Bouman
I was unable to reproduce it on a clean install of 5.1.7beta but clearly, Markus is still experiencing this problem. How should we go about to find out what the difference is between our environments?
[10 Mar 2006 20:34] Markus Popp
Please try the following:

To emulate relevant parts of my installation, download the my.ini, dump.sql and dump_mysql.sql file, provided in bug report 15072 (in private comments, only visible to developers), then try:

C:\mysql\bin>mysql -u root mysql < C:\dump_mysql.sql

C:\mysql\bin>mysqladmin -u root flush-privileges

C:\mysql\bin>mysql -u mpopp < C:\dump.sql

C:\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.19-nt

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

mysql> CREATE DATABASE funcs;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE mpopp;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL ON funcs.* TO mpopp@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON mpopp.* TO mpopp@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\mysql\bin>mysql -u mpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.19-nt

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

mysql> USE funcs
Database changed
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `funcs`.`getDeps` $$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE PROCEDURE `getDeps`(pDatabase VARCHAR(200),
    ->                          pTable VARCHAR(200))
    ->     SQL SECURITY INVOKER
    -> BEGIN
    ->
    -> SELECT CONCAT(REFERENCED_COLUMN_NAME, ' -> (',
    ->               TABLE_SCHEMA, '.',
    ->               TABLE_NAME, ') ',
    ->               COLUMN_NAME) AS `references`
    -> FROM information_schema.KEY_COLUMN_USAGE
    -> WHERE REFERENCED_TABLE_SCHEMA = pDatabase
    ->   AND REFERENCED_TABLE_NAME = pTable
    -> ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> USE mpopp
Database changed
mysql> DROP TABLE IF EXISTS `mpopp`.`mysqluc`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE  `mpopp`.`mysqluc` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `url` varchar(100) NOT NULL,
    ->   `event_type` varchar(20) NOT NULL,
    ->   `topic` varchar(200) NOT NULL,
    ->   `speaker` varchar(200) NOT NULL,
    ->   `track` varchar(200) NOT NULL,
    ->   `time_from` datetime NOT NULL,
    ->   `time_to` datetime NOT NULL,
    ->   `location` varchar(100) NOT NULL,
    ->   `description` text,
    ->   PRIMARY KEY  (`id`),
    ->   UNIQUE KEY `url` (`url`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> CALL funcs.getDeps(database(), 'mysqluc');
ERROR 2013 (HY000): Lost connection to MySQL server during query

This time, the crash occured even when the procedure was called for the first time - most of the time (at least up to MySQL 5.0.18), the crash occured on its second execution.
[10 Mar 2006 21:06] Markus Popp
I tried the same on 5.0.18 - this time, the procedure crashed the server the second time:

C:\mysql\bin>mysql -u root mysql < C:\dump_mysql.sql

C:\mysql\bin>mysqladmin -u root flush-privileges

C:\mysql\bin>mysql -u mpopp < C:\dump.sql

C:\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.18-nt

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

mysql> CREATE DATABASE funcs;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE mpopp;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL ON funcs.* TO mpopp@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON mpopp.* TO mpopp@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

C:\mysql\bin>mysql -u mpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.18-nt

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

mysql> USE funcs
Database changed
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `funcs`.`getDeps` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `getDeps`(pDatabase VARCHAR(200),
    ->                          pTable VARCHAR(200))
    ->     SQL SECURITY INVOKER
    -> BEGIN
    ->
    -> SELECT CONCAT(REFERENCED_COLUMN_NAME, ' -> (',
    ->               TABLE_SCHEMA, '.',
    ->               TABLE_NAME, ') ',
    ->               COLUMN_NAME) AS `references`
    -> FROM information_schema.KEY_COLUMN_USAGE
    -> WHERE REFERENCED_TABLE_SCHEMA = pDatabase
    ->   AND REFERENCED_TABLE_NAME = pTable
    -> ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> USE mpopp
Database changed
mysql> DROP TABLE IF EXISTS `mpopp`.`mysqluc`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE  `mpopp`.`mysqluc` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `url` varchar(100) NOT NULL,
    ->   `event_type` varchar(20) NOT NULL,
    ->   `topic` varchar(200) NOT NULL,
    ->   `speaker` varchar(200) NOT NULL,
    ->   `track` varchar(200) NOT NULL,
    ->   `time_from` datetime NOT NULL,
    ->   `time_to` datetime NOT NULL,
    ->   `location` varchar(100) NOT NULL,
    ->   `description` text,
    ->   PRIMARY KEY  (`id`),
    ->   UNIQUE KEY `url` (`url`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> CALL funcs.getDeps(database(), 'mysqluc');
Empty set (0.09 sec)

Query OK, 0 rows affected (0.11 sec)

mysql> CALL funcs.getDeps(database(), 'mysqluc');
ERROR 2013 (HY000): Lost connection to MySQL server during query
[20 Mar 2006 11:38] Markus Popp
I'd like to propose the following updates for this bug report:

Severity: S1 (since this bug crashes the server, I would give it the highest priority)
Version: 5.0.17, 5.0.18, 5.0.19, 5.1.7, 5.1.8BK
OS: Windows, Linux
[20 Mar 2006 19:24] Miguel Solorzano
I was able to repeat with Windows:

c:\mysql\bin>mysqld-nt --standalone --console
060320 12:55:46  InnoDB: Started; log sequence number 0 749827247
060320 12:55:47 [Note] mysqld-nt: ready for connections.
Version: '5.0.19-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)

c:\mysql\bin>mysql -u root mysql < C:\markus\dump_mysql.sql

c:\mysql\bin>mysqladmin -u root flush-privileges

c:\mysql\bin>mysql -u mpopp < C:\markus\dump.sql

c:\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13 to server version: 5.0.19-nt

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

mysql> CREATE DATABASE funcs;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE mpopp;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL ON funcs.* TO mpopp@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON mpopp.* TO mpopp@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

c:\mysql\bin>mysql -u mpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.19-nt

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

mysql> USE funcs
Database changed
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `funcs`.`getDeps` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `getDeps`(pDatabase VARCHAR(200),
    ->                          pTable VARCHAR(200))
    ->     SQL SECURITY INVOKER
    -> BEGIN
    ->  SELECT CONCAT(REFERENCED_COLUMN_NAME, ' -> (',
    ->               TABLE_SCHEMA, '.',
    ->                TABLE_NAME, ') ',
    ->                COLUMN_NAME) AS `references`
    ->  FROM information_schema.KEY_COLUMN_USAGE
    ->  WHERE REFERENCED_TABLE_SCHEMA = pDatabase
    ->    AND REFERENCED_TABLE_NAME = pTable
    ->  ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> USE mpopp
Database changed
mysql> DROP TABLE IF EXISTS `mpopp`.`mysqluc`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE  `mpopp`.`mysqluc` (
    ->    `id` int(10) unsigned NOT NULL,
    ->    `url` varchar(100) NOT NULL,
    ->    `event_type` varchar(20) NOT NULL,
    ->    `topic` varchar(200) NOT NULL,
    ->    `speaker` varchar(200) NOT NULL,
    ->   `track` varchar(200) NOT NULL,
    ->   `time_from` datetime NOT NULL,
    ->    `time_to` datetime NOT NULL,
    ->   `location` varchar(100) NOT NULL,
    ->   `description` text,
    ->    PRIMARY KEY  (`id`),
    ->    UNIQUE KEY `url` (`url`)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.14 sec)

mysql> CALL funcs.getDeps(database(), 'mysqluc');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

I will test with latest Windows server and Linux.
[20 Mar 2006 20:43] Miguel Solorzano
Thank you for the bug report. I was able to repeat with latest source
on Linux Suse (see changeset at the bottom):

miguel@hegel:~/dbs/5.0> bin/mysql -uroot mysql < /home/miguel/markus/dump_mysql.sql 
miguel@hegel:~/dbs/5.0> bin/mysqladmin -u root flush-privileges
miguel@hegel:~/dbs/5.0> bin/mysql -u mpopp < /home/miguel/markus/dump.sql 
miguel@hegel:~/dbs/5.0> bin/mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.20-debug

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

mysql> CREATE DATABASE funcs;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE mpopp;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL ON funcs.* TO mpopp@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON mpopp.* TO mpopp@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
Bye

miguel@hegel:~/dbs/5.0> bin/mysql -u mpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.20-debug

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

mysql> USE funcs
Database changed
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `funcs`.`getDeps` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `getDeps`(pDatabase VARCHAR(200),
    ->                           pTable VARCHAR(200))
    ->      SQL SECURITY INVOKER
    ->  BEGIN
    ->  SELECT CONCAT(REFERENCED_COLUMN_NAME, ' -> (',
    ->                TABLE_SCHEMA, '.',
    ->                 TABLE_NAME, ') ',
    ->                COLUMN_NAME) AS `references`
    ->   FROM information_schema.KEY_COLUMN_USAGE
    ->  WHERE REFERENCED_TABLE_SCHEMA = pDatabase
    ->     AND REFERENCED_TABLE_NAME = pTable
    ->   ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
    -> 
    ->  END $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> USE mpopp
Database changed
mysql> DROP TABLE IF EXISTS `mpopp`.`mysqluc`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE  `mpopp`.`mysqluc` (
    -> `id` int(10) unsigned NOT NULL,
    -> `url` varchar(100) NOT NULL,
    -> `event_type` varchar(20) NOT NULL,
    -> `topic` varchar(200) NOT NULL,
    -> `speaker` varchar(200) NOT NULL,
    -> `track` varchar(200) NOT NULL,
    -> `time_from` datetime NOT NULL,
    -> `time_to` datetime NOT NULL,
    -> `location` varchar(100) NOT NULL,
    -> `description` text,
    -> PRIMARY KEY  (`id`),
    -> UNIQUE KEY `url` (`url`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CALL funcs.getDeps(database(), 'mysqluc');
Empty set (0.09 sec)

Query OK, 0 rows affected (0.09 sec)

mysql> CALL funcs.getDeps(database(), 'mysqluc');
ERROR 2013 (HY000): Lost connection to MySQL server during query

[New Thread 1129204656 (LWP 17033)]
[New Thread 1129405360 (LWP 17034)]
060320 17:15:18 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.20-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1129606064 (LWP 17038)]
[Thread 1129606064 (zombie) exited]
[New Thread 1129606064 (LWP 17041)]
[Thread 1129606064 (zombie) exited]
[New Thread 1129606064 (LWP 17044)]
[Thread 1129606064 (zombie) exited]
[New Thread 1129606064 (LWP 17046)]
[Thread 1129606064 (zombie) exited]
[New Thread 1129606064 (LWP 17057)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1129606064 (LWP 17057)]
0x081fbf45 in check_table_access (thd=0x8e65680, want_access=1, tables=0x0, no_errors=false) at sql_parse.cc:5176
5176        if (tables->schema_table && 
(gdb) quit
The program is running.  Exit anyway? (y or n) y
miguel@hegel:~/dbs/5.0> 

miguel@hegel:~/dbs/5.0> bk changes /home/miguel/dbs/mysql-5.0/ | head
ChangeSet@1.2096, 2006-03-20 14:17:47+04:00, gluh@eagle.intranet.mysql.r18.ru
  Fix for bug#18224 VIEW on information_schema crashes the server
    additional check for subselect

ChangeSet@1.2095, 2006-03-20 13:42:02+04:00, gluh@eagle.intranet.mysql.r18.ru
  Fix for bug #18113 "SELECT * FROM information_schema.xxx crashes server"
  
  Crash happened when one selected data from one of INFORMATION_SCHEMA
  tables and in order to build its contents server had to open view which
  used stored function and table or view on which one had not global or
miguel@hegel:~/dbs/5.0>
[29 Mar 2006 12:20] Sergei Glukhov
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4273
[25 Apr 2006 5:49] Sergei Glukhov
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5452
[2 May 2006 4:05] Markus Popp
When will the fix be in the source? The bug still exists in 5.0.21 and 5.1.9.
[18 May 2006 11:01] Sergei Glukhov
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6561
[30 May 2006 5:43] Sergei Glukhov
Fixed in 5.0.23
[31 May 2006 1:07] Paul Dubois
Noted in 5.0.23 changelog.

Multiple calls to a stored procedure that selects from
INFORMATION_SCHEMA could cause a server crash.