MySQL Bugs: #17843: Certain stored procedures fail to run at startup

Bug #17843	Certain stored procedures fail to run at startup
Submitted:	1 Mar 2006 21:53	Modified:	17 Aug 2006 9:02
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Any (ALL)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
my.cnf:
[mysqld]
init-file=/tmp/startup.sql

These two options succeed:
(a) CREATE TABLE test.foo (a int);
(b) CALL test.createfoo2();

Where createfoo2 was defined as:
delimiter //
CREATE PROCEDURE createfoo2 ()
BEGIN
CREATE TABLE foo2 (a int);
END
//
delimiter ;

This option will work when using the MySQL client, but will *not* work when called from inside the init-file:

CALL test.createfoo3();

Where createfoo3 was defined as:

CREATE PROCEDURE `createfoo3`()
delimiter //
BEGIN
SET @asql = concat("CREATE TABLE ", "foo3", "(a int)");
PREPARE pst FROM @asql;
EXECUTE pst;
END
//
delimiter ;

How to repeat:
CALL test.createfoo3(); from within an init-file.

Adding a "use test;" before the CALL createfoo3(); in /tmp/startup.sql made no change.

Test case needs to show that this fails even when the table name is qualified with a database name:

CREATE PROCEDURE s1()
BEGIN
 SET @sql = "CREATE TABLE test.t1 (a int)";
 PREPARE pstmt FROM @sql;
 EXECUTE pstmt;
END 

init-file with CALL s1()  will not create the table.

mysql -e"CALL test.s1();" will create the table.

Calling this procedure from the startup file will not work either:

create procedure s3()
  select * from t;

that is, anything that might send result sets will fail (and it's assumed that prepared statements might do that). This is because there is no proper "client", and thus no client capabilities are set.

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/4076

Okay to push (after second review).

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/5295

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/8712

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/8715

This is approved as I actually did a review of the patch by Per-Erik.

Pushed into 5.0-runtime

Pushed into 5.1.12

5.0 version is 5.0.24

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

Documented bugfix in 5.0.24 and 5.1.12 changelogs.

This does not appear to be fixed:

morgo@morguntu:~$ mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.24-pro-gpl-log

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

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| foo            |
| foo2           |
+----------------+

mysql > delimiter //
mysql > CREATE PROCEDURE s1()
    -> BEGIN
    ->  SET @sql = "CREATE TABLE test.t1 (a int)";
    ->  PREPARE pstmt FROM @sql;
    ->  EXECUTE pstmt;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

morgo@morguntu:~$ mysql -e "CALL test.s1()"

<< Creates the table t1 as expected >>

Using an init-file does not create the table as expected.  I know the init-file is working, because it also drops my foo tables:

morgo@morguntu:~$ more ~/init.sql
drop table test.foo;
drop table test.foo2;

call test.s1();

I apologize for the wrong version. This bugfix didn't make it into 5.0.24 but everything works as expected in the latest 5.0 (5.0.25).
Dean, please re-check this bug against the latest 5.0. Team trees make tracking version numbers rather difficult.

Please re-document as fixed in 5.0.25, and remove from 5.0.24 changelogs.  After that it is okay to be closed again.

Moved bugfix to 5.0.25 changelog and closed per Morgan's comment.