MySQL Bugs: #17211: QueryBrowser crashes if SP is called under certain circumstances

Bug #17211	QueryBrowser crashes if SP is called under certain circumstances
Submitted:	7 Feb 2006 22:56	Modified:	8 Dec 2006 7:40
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.1.19, 1.1.20	OS:	Windows (Windows XP)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Result Set

Description:
If a SP is created under user root in database A and this procedure is called by a normal user from database B (in QueryBrowser) for the second time (!), QueryBrowser crashes. This could eventually relate to bug report http://bugs.mysql.com/bug.php?id=17204 where the MySQL server crashes when a SP is executed for the second time.

How to repeat:
Create one root and one normal user, one funcs database and one test database and grant the normal user access to both databases:

CREATE USER root@localhost;
GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION;
(this is probably the current state)
CREATE USER user@localhost;
CREATE DATABASE funcs;
CREATE DATABASE test;
GRANT ALL ON funcs.* TO user@localhost;
GRANT ALL ON test.* TO user@localhost;

Log in as user root and create this SP in database funcs:

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 ;

Then log in as user 'user' and access database test - create following tables:

CREATE TABLE tt1 (
  id int(10) unsigned NOT NULL auto_increment,
  val int(10) unsigned NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE tt2 (
  id int(10) unsigned NOT NULL auto_increment,
  id_1 int(10) unsigned NOT NULL,
  val int(10) unsigned NOT NULL,
  PRIMARY KEY  (id),
  KEY id_1 (id_1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE tt3 (
  id int(10) unsigned NOT NULL auto_increment,
  id_1 int(10) unsigned NOT NULL,
  val int(10) unsigned NOT NULL,
  PRIMARY KEY  (id),
  KEY id_1 (id_1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `tt2`
  ADD CONSTRAINT tt2_ibfk_1 FOREIGN KEY (id_1) REFERENCES tt1 (id);

ALTER TABLE `tt3`
  ADD CONSTRAINT tt3_ibfk_1 FOREIGN KEY (id_1) REFERENCES tt1 (id);

Then execute following Stored Procedure twice from MySQL QueryBrowser, using user 'user' in database test:

CALL funcs.getDeps(database(), 'tt1');

After the second execution, MySQL QueryBrowser used to crash.

Thank you for a problem report. Please, specify the exact version of MySQL server used. As it can be a server bug, please, check the server error log also. Is there something unusual there?

The server version is 5.0.18-max-nt. However, the error log doesn't indicate anything and also the uptime is not interrupted, so the server continues to operate - seems to be a QueryBrowser problem (now also tested with 1.1.20).

But I have discovered that it's not always the 2nd time when QueryBrowser crashes. Sometimes it has worked several times in a row (before it crashed again), but later it fell back to the behaviour that it used to crash when the procedure is called the 2nd time. So if QueryBrowser doesn't crash on the 2nd try, keep on trying a few times more.

Verified just as described with QB 1.1.20 and MySQL server 5.0.18 on XP. Query Browser crashes upon 2nd or 3rd repeatable execution of that CALL statement.

The problem seems to be a lot more general than in the original post. I don't need to change the user. Just create a procedure and CALL with the same user (or other user). QueryBrowser 1.1.20 crashes randomly (most of the time second or third CALL, but sometimes a bit later).

I have up to date WinXP with QueryBrowser 1.1.20

I've tried with server versions: 
5.0.17-nt-max
5.0.20a-nt-max

Servers keep running normally.

Simpliest SP that I was using:

DELIMITER $$
CREATE PROCEDURE `testProc`()
BEGIN
  SELECT 1;
END $$
DELIMITER ;

There might be a difference that more complicated procedures crash sooner than the very basic one above.

Same thing is happening to me:

 (Win XP-SP2, QB 1.1.20)

If you have one QB open that you're using to Create SPs and another QB to test/execute the SP, after the 2nd or 3rd time of running procedures QB will crash.

The funny thing is, is that although you get the notice, if you move it off to the side you can continue working.  During a TSQL to MySQL conversion process, I'd rack up a ton of those error message windows and would just ignore them.

1.1.20 WindowsXP Pro,

Your MySQL connection id is 231 to server version: 5.0.20-nt-max

DROP TABLE IF EXISTS `test`.`test`;
CREATE TABLE  `test`.`test` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `Data` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`test_data` $$
CREATE PROCEDURE `test`.`test_data` ()
BEGIN
  SELECT * FROM test;
END $$

DELIMITER ;

Query Browser is zombie, but server does not crash
Logs following
060609 12:54:43 [Warning] Aborted connection 225 to db: 'test' user: 'root' host: 'localhost' (Got an error reading communication packets)
060609 13:02:56 [Warning] Aborted connection 247 to db: 'test' user: 'root' host: 'localhost' (Got an error reading communication packets)
060609 13:02:56 [Warning] Aborted connection 248 to db: 'test' user: 'root' host: 'localhost' (Got an error reading communication packets)
060609 13:02:56 [Warning] Aborted connection 227 to db: 'test' user: 'root' host: 'localhost' (Got an error reading communication packets)

hope it helps to resolve the problem

This problem can no longer be reproduced and is likely already fixed. Please check again with the latest version and let me know the outcome.

I also could not reproduce it with the latest version, so I assume that the problem is fixed.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/