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

[1 Mar 2006 21:53] Morgan Tocker
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.
[1 Mar 2006 22:27] Morgan Tocker
Adding a "use test;" before the CALL createfoo3(); in /tmp/startup.sql made no change.
[1 Mar 2006 22:38] Dean Ellis
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.
[22 Mar 2006 10:56] Per-Erik Martin
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.
[23 Mar 2006 15:32] Bugs System
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
[10 Apr 2006 18:18] Jim Winstead
Okay to push (after second review).
[21 Apr 2006 12:03] Bugs System
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
[4 Jul 2006 19:27] Bugs System
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
[4 Jul 2006 19:48] Bugs System
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
[4 Jul 2006 20:14] Konstantin Osipov
This is approved as I actually did a review of the patch by Per-Erik.
[4 Jul 2006 20:15] Konstantin Osipov
Pushed into 5.0-runtime
[2 Aug 2006 12:00] Konstantin Osipov
Pushed into 5.1.12
[2 Aug 2006 12:07] Konstantin Osipov
5.0 version is 5.0.24
[7 Aug 2006 7:28] Jon Stephens
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.
[10 Aug 2006 6:16] Morgan Tocker
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();
[16 Aug 2006 19:21] Konstantin Osipov
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.
[17 Aug 2006 2:07] Morgan Tocker
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.
[17 Aug 2006 9:02] Jon Stephens
Moved bugfix to 5.0.25 changelog and closed per Morgan's comment.