From 083a316d1f9020d48a5b9769fafbb3accad3c03a Mon Sep 17 00:00:00 2001 From: Tatjana Azundris Nuernberg Date: Tue, 12 Jul 2011 06:08:52 +0100 Subject: Bug#11758414/Bug#50614: Default storage_engine not honored when set from within a stored procedure When CREATE TABLE wasn't given ENGINE=... it would determine the default ENGINE at parse-time rather than at execution time, leading to incorrect behaviour (namely, later changes to the default engine being ignore) when calling CREATE TABLE from a stored procedure. We now defer working out the default engine till execution of CREATE TABLE. mysql-test/r/sp_trans.result: results! mysql-test/t/sp_trans.test: Show that CREATE TABLE (called from store routine) heeds any changes after CREATE SP / parse-time. Show that explicitly requesting an ENGINE still works. sql/sql_parse.cc: If no ENGINE=... was given at parse-time, determine default engine at execution time of CREATE TABLE. sql/sql_yacc.yy: If CREATE TABLE is not given ENGINE=..., don't bother figuring out the default engine during parsing; we'll do it at execution time instead to be aware of the latest updates. --- mysql-test/t/sp_trans.test | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) (limited to 'mysql-test/t/sp_trans.test') diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 0b04b9d7668..2d59fb20bbd 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -593,6 +593,39 @@ drop function bug13575| drop table t3| +# +# BUG#11758414: Default storage_engine not honored when set +# from within a stored procedure +# +SELECT @@GLOBAL.storage_engine INTO @old_engine| +SET @@GLOBAL.storage_engine=InnoDB| +SET @@SESSION.storage_engine=InnoDB| +# show defaults at define-time +SHOW GLOBAL VARIABLES LIKE 'storage_engine'| +SHOW SESSION VARIABLES LIKE 'storage_engine'| +CREATE PROCEDURE bug11758414() +BEGIN + SET @@GLOBAL.storage_engine="MyISAM"; + SET @@SESSION.storage_engine="MyISAM"; + # show defaults at execution time / that setting them worked + SHOW GLOBAL VARIABLES LIKE 'storage_engine'; + SHOW SESSION VARIABLES LIKE 'storage_engine'; + CREATE TABLE t1 (id int); + CREATE TABLE t2 (id int) ENGINE=InnoDB; + # show we're heeding the default (at run-time, not parse-time!) + SHOW CREATE TABLE t1; + # show that we didn't break explicit override with ENGINE=... + SHOW CREATE TABLE t2; +END; +| +CALL bug11758414| +# show that changing defaults within SP stuck +SHOW GLOBAL VARIABLES LIKE 'storage_engine'| +SHOW SESSION VARIABLES LIKE 'storage_engine'| +DROP PROCEDURE bug11758414| +DROP TABLE t1, t2| +SET @@GLOBAL.storage_engine=@old_engine| + # # BUG#NNNN: New bug synopsis # -- cgit v1.2.1