From 90072e69b3270409fc1caf988db774ab1f4e04a9 Mon Sep 17 00:00:00 2001 From: "kostja@bodhi.local" <> Date: Fri, 8 Dec 2006 02:20:09 +0300 Subject: A fix and test cases for Bug#4968 "Stored procedure crash if cursor opened on altered table" Bug#19733 "Repeated alter, or repeated create/drop, fails" Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from stored procedure." Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing" Bug#22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" Test cases for bugs 4968, 19733, 6895 will be added in 5.0. Re-execution of CREATE DATABASE, CREATE TABLE and ALTER TABLE statements in stored routines or as prepared statements caused incorrect results (and crashes in versions prior to 5.0.25). In 5.1 the problem occured only for CREATE DATABASE, CREATE TABLE SELECT and CREATE TABLE with INDEX/DATA DIRECTOY options). The problem of bugs 4968, 19733, 19282 and 6895 was that functions mysql_prepare_table, mysql_create_table and mysql_alter_table were not re-execution friendly: during their operation they used to modify contents of LEX (members create_info, alter_info, key_list, create_list), thus making the LEX unusable for the next execution. In particular, these functions removed processed columns and keys from create_list, key_list and drop_list. Search the code in sql_table.cc for drop_it.remove() and similar patterns to find evidence. The fix is to supply to these functions a usable copy of each of the above structures at every re-execution of an SQL statement. To simplify memory management, LEX::key_list and LEX::create_list were added to LEX::alter_info, a fresh copy of which is created for every execution. The problem of crashing bug 22060 stemmed from the fact that the above metnioned functions were not only modifying HA_CREATE_INFO structure in LEX, but also were changing it to point to areas in volatile memory of the execution memory root. The patch solves this problem by creating and using an on-stack copy of HA_CREATE_INFO (note that code in 5.1 already creates and uses a copy of this structure in mysql_create_table()/alter_table(), but this approach didn't work well for CREATE TABLE SELECT statement). --- mysql-test/t/ps.test | 72 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 72 insertions(+) (limited to 'mysql-test/t/ps.test') diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index fbeaaa494e0..cd6c0667898 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1068,5 +1068,77 @@ EXECUTE stmt USING @a; DEALLOCATE PREPARE stmt; DROP TABLE t1; +# +# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work +# from stored procedure. +# +# The cause of a bug was that cached LEX::create_list was modified, +# and then together with LEX::key_list was reset. +# +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (i INT); + +PREPARE st_19182 +FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1"; +EXECUTE st_19182; +DESC t2; + +DROP TABLE t2; + +# Check that on second execution we don't loose 'j' column and the keys +# on 'i' and 'j' columns. +EXECUTE st_19182; +DESC t2; + +DEALLOCATE PREPARE st_19182; +DROP TABLE t2, t1; + +# +# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# +# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE +# statement modified HA_CREATE_INFO structure in LEX, making these +# statements PS/SP-unsafe (their re-execution might have resulted +# in incorrect results). +# +--disable_warnings +drop database if exists mysqltest; +drop table if exists t1, t2; +--enable_warnings +# CREATE TABLE and CREATE TABLE ... SELECT +create database mysqltest character set utf8; +prepare stmt1 from "create table mysqltest.t1 (c char(10))"; +prepare stmt2 from "create table mysqltest.t2 select 'test'"; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop table mysqltest.t1; +drop table mysqltest.t2; +alter database mysqltest character set latin1; +execute stmt1; +execute stmt2; +show create table mysqltest.t1; +show create table mysqltest.t2; +drop database mysqltest; +deallocate prepare stmt1; +deallocate prepare stmt2; +# CREATE TABLE with DATA DIRECTORY option +--disable_query_log +eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'"; +--enable_query_log +execute stmt; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +show create table t1; +drop table t1; +execute stmt; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +show create table t1; +drop table t1; +deallocate prepare stmt; +# --echo End of 4.1 tests. -- cgit v1.2.1 From 92f1c7623635adeeab08a58d83179e6b62a0b240 Mon Sep 17 00:00:00 2001 From: "kostja@bodhi.local" <> Date: Tue, 12 Dec 2006 01:50:12 +0300 Subject: Post-merge fixes for Bug#4968 "Stored procedure crash if cursor opened on altered table" and Bug#19733 "Repeated alter, or repeated create/drop, fails" --- mysql-test/t/ps.test | 72 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 72 insertions(+) (limited to 'mysql-test/t/ps.test') diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 9afb5c5ba59..e70ceeec077 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1610,4 +1610,76 @@ execute sq; deallocate prepare no_index; deallocate prepare sq; +# +# Bug#4968 "Stored procedure crash if cursor opened on altered table" +# The bug is not repeatable any more after the fix for +# Bug#15217 "Bug #15217 Using a SP cursor on a table created with PREPARE +# fails with weird error", however ALTER TABLE is not re-execution friendly +# and that caused a valgrind warning. Check that the warning is gone. +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (s1 char(20)); +prepare stmt from "alter table t1 modify s1 int"; +execute stmt; +execute stmt; +drop table t1; +deallocate prepare stmt; + +# +# Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing" +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, b int); +prepare s_6895 from "alter table t1 drop column b"; +execute s_6895; +show columns from t1; +drop table t1; +create table t1 (a int, b int); +execute s_6895; +show columns from t1; +drop table t1; +create table t1 (a int, b int); +execute s_6895; +show columns from t1; +deallocate prepare s_6895; +drop table t1; + +# +# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" +# +# 5.0 part of the test. +# + +# ALTER TABLE +create table t1 (i int primary key auto_increment) comment='comment for table t1'; +create table t2 (i int, j int, k int); +prepare stmt from "alter table t1 auto_increment=100"; +execute stmt; +show create table t1; +# Let us trash table-cache's memory +flush tables; +select * from t2; +execute stmt; +show create table t1; +deallocate prepare stmt; +drop table t1, t2; +# 5.1 part of the test. +# CREATE DATABASE +#set @old_character_set_server= @@character_set_server; +#set @@character_set_server= latin1; +#prepare stmt from "create database mysqltest"; +#execute stmt; +#show create database mysqltest; +#drop database mysqltest; +#set @@character_set_server= utf8; +#execute stmt; +#show create database mysqltest; +#drop database mysqltest; +#deallocate prepare stmt; +#set @@character_set_server= @old_character_set_server; + --echo End of 5.0 tests. -- cgit v1.2.1