Bug #50614 Default storage_engine not honored when set from within a stored procedure

• Submitted: 26 Jan 2010 4:37
• Modified: 24 Sep 2011 16:59
• Reporter: Roel Van de Paar
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.1.42,5.5.0m2
• OS: Any
• CPU Architecture: Any

[26 Jan 2010 4:37] Roel Van de Paar

Description:
mysql> SET @@GLOBAL.storage_engine="InnoDB";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SESSION.storage_engine="InnoDB";
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS testse//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE testse()
    -> BEGIN
    ->  DROP TABLE IF EXISTS a;
    ->  SET @@GLOBAL.storage_engine="MyISAM";
    ->  SET @@SESSION.storage_engine="MyISAM";
    ->  SHOW GLOBAL VARIABLES LIKE 'storage_engine';
    ->  SHOW SESSION VARIABLES LIKE 'storage_engine';
    ->  CREATE TABLE a (id int);
    ->  SHOW CREATE TABLE a;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL testse;
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.04 sec)

+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.26 sec)

+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.48 sec)

Query OK, 0 rows affected (0.78 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

How to repeat:
SET @@GLOBAL.storage_engine="InnoDB";
SET @@SESSION.storage_engine="InnoDB";
SHOW GLOBAL VARIABLES LIKE 'storage_engine';
SHOW SESSION VARIABLES LIKE 'storage_engine';
DELIMITER //
DROP PROCEDURE IF EXISTS testse//
CREATE PROCEDURE testse()
BEGIN
 DROP TABLE IF EXISTS a;
 SET @@GLOBAL.storage_engine="MyISAM";
 SET @@SESSION.storage_engine="MyISAM"; 
 SHOW GLOBAL VARIABLES LIKE 'storage_engine';
 SHOW SESSION VARIABLES LIKE 'storage_engine';
 CREATE TABLE a (id int);
 SHOW CREATE TABLE a;
END;
//
DELIMITER ;
CALL testse;
SHOW GLOBAL VARIABLES LIKE 'storage_engine';
SHOW SESSION VARIABLES LIKE 'storage_engine';

[26 Jan 2010 4:41] Roel Van de Paar

Verifying as D2.

Workaround is to specify the storage engine at CREATE TABLE time:
CREATE TABLE a (id int) Engine=<MyISAM or InnoDB>;

[24 Sep 2011 16:59] Paul DuBois

Noted in 5.1.59, 5.5.16, 5.6.3 changelogs.

CREATE TABLE without an ENGINE option determined the default engine
at parse rather than execution time. This led to incorrect results if
the statement was executed within a stored program and the default
engine had been changed in the meantime.