| 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: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.0.17 nt/5.0BK/5.1BK | OS: | Windows (WinXP/Linux) |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
[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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
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)