#At file:///home/ja155679/devel/mysql/mysql-6.0-runtime/
2731 Jorgen Austvik 2009-02-26
wl#4343: Add more stress testing variants
added:
mysql-test/suite/ddl_lock/include/exec_with_error.inc
mysql-test/suite/ddl_lock/r/stress_single_user.result
mysql-test/suite/ddl_lock/t/stress_single_user.test
modified:
mysql-test/suite/ddl_lock/include/stress_settings.inc
mysql-test/suite/ddl_lock/r/concurrent_ddl.result
mysql-test/suite/ddl_lock/r/create_stress_tables.result
mysql-test/suite/ddl_lock/r/stress_ddl.result
mysql-test/suite/ddl_lock/stress_tests.txt
mysql-test/suite/ddl_lock/t/concurrent_ddl.test
mysql-test/suite/ddl_lock/t/create_stress_tables.test
mysql-test/suite/ddl_lock/t/stress_ddl.test
=== added file 'mysql-test/suite/ddl_lock/include/exec_with_error.inc'
--- a/mysql-test/suite/ddl_lock/include/exec_with_error.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/ddl_lock/include/exec_with_error.inc 2009-02-26 10:01:50 +0000
@@ -0,0 +1,59 @@
+#
+# SUMMARY
+#
+# Execute some SQL, and allow some errors.
+#
+# USAGE
+#
+# $sql - The SQL to execute, must be set
+#
+# OPTIONAL
+# $errors - The accepted errors, default = 0.
+# $replace_from - What to search for in the result string
+# $replace_to - What to replace it with
+#
+# If you want to do result replace on the SQL string, use the
+# $replace_from and $replace_to, like you would do in
+# --replace_result $replace_from $replace_to.
+#
+# EXAMPLE
+#
+# let $sql=SELECT * FROM foo;
+# let $errors=0, 1050;
+# --source suite/ddl_lock/include/exec_with_error.inc
+#
+
+# Default value
+if (`SELECT LENGTH("$errors") = 0`) {
+ let $errors= 0;
+}
+
+# Default value
+if (`SELECT LENGTH("$sql") = 0`) {
+ die Missing SQL expression;
+}
+
+--disable_abort_on_error
+--disable_result_log
+if (`SELECT LENGTH("$replace_from$replace_to") > 0`)
+{
+--replace_result $replace_from $replace_to
+}
+eval $sql;
+--enable_result_log
+--enable_abort_on_error
+let $my_errno= $mysql_errno;
+if (`SELECT $my_errno NOT IN ($errors)`)
+{
+ --echo # Got unexpected error: $my_errno, allowed error codes: $errors
+ --echo # When executing: $sql
+ if (`SELECT LENGTH("$replace_from$replace_to") > 0`)
+ {
+ --echo # Replacing "$replace_from" with "$replace_to"
+ }
+ --echo # Dumping debug information:
+ SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
+ SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
+ SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='test';
+ --die Unexpected error: $my_error
+}
=== modified file 'mysql-test/suite/ddl_lock/include/stress_settings.inc'
--- a/mysql-test/suite/ddl_lock/include/stress_settings.inc 2008-12-17 10:11:14 +0000
+++ b/mysql-test/suite/ddl_lock/include/stress_settings.inc 2009-02-26 10:01:50 +0000
@@ -2,3 +2,7 @@
## Standard settings (global variables) for the stress testing.
##
let $num_stress_rows= 5000;
+
+if (`SELECT LENGTH("$engine_type") = 0`) {
+ let $engine_type= myisam;
+}
=== modified file 'mysql-test/suite/ddl_lock/r/concurrent_ddl.result'
--- a/mysql-test/suite/ddl_lock/r/concurrent_ddl.result 2009-02-04 09:11:18 +0000
+++ b/mysql-test/suite/ddl_lock/r/concurrent_ddl.result 2009-02-26 10:01:50 +0000
@@ -257,7 +257,7 @@ COUNT(*)
SET DEBUG_SYNC= 'RESET';
DROP TABLE t1;
##
-## 1.6 Tow connections are long create/select, while another is
+## 1.6 Two connections are long create/select, while another is
## altering the table
##
# Connections locker1..locker3 will run different SQL queries, which
=== modified file 'mysql-test/suite/ddl_lock/r/create_stress_tables.result'
--- a/mysql-test/suite/ddl_lock/r/create_stress_tables.result 2008-12-17 10:11:14 +0000
+++ b/mysql-test/suite/ddl_lock/r/create_stress_tables.result 2009-02-26 10:01:50 +0000
@@ -31,9 +31,51 @@ WHERE id + @max <= <num_stress_rows>;
SELECT MAX(id) FROM t1 INTO @max;
INSERT INTO t1(id) SELECT id + @max FROM t1
WHERE id + @max <= <num_stress_rows>;
+CREATE TABLE t2 (id INT PRIMARY KEY)
+ENGINE=<engine_type>;
+INSERT INTO t2(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+CREATE TABLE t3 (id INT PRIMARY KEY)
+ENGINE=<engine_type>;
+CREATE TABLE t4 (id INT PRIMARY KEY)
+ENGINE=<engine_type>;
+INSERT INTO t4(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+SELECT MAX(id) FROM t4 INTO @max;
+INSERT INTO t4(id) SELECT id + @max FROM t4
+WHERE id + @max <= <num_stress_rows>;
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t2;
CREATE PROCEDURE t_proc (OUT b_p CHAR(100))
BEGIN
SELECT b INTO @b_p FROM t1 WHERE Id = 1;
END|
CREATE FUNCTION t_func (s CHAR(20)) RETURNS CHAR(30) DETERMINISTIC
RETURN CONCAT('Hello, ', s, '!');
+CREATE EVENT ev1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO
+SELECT MOD(29,9);
=== modified file 'mysql-test/suite/ddl_lock/r/stress_ddl.result'
--- a/mysql-test/suite/ddl_lock/r/stress_ddl.result 2008-12-17 10:11:14 +0000
+++ b/mysql-test/suite/ddl_lock/r/stress_ddl.result 2009-02-26 10:01:50 +0000
@@ -13,3 +13,32 @@ id
DROP VIEW <view_name>, <all_view_name>;
ALTER PROCEDURE t_proc COMMENT 'Now with a comment from conn <conn_id>';
ALTER FUNCTION t_func COMMENT 'Now with a comment from conn <conn_id>';
+LOCK TABLES t2 READ;
+UNLOCK TABLES;
+ALTER TABLE t2 ADD COLUMN (b_<conn_nr> INT);
+ALTER TABLE t2 DROP COLUMN b_<conn_nr>;
+TRUNCATE TABLE t3;
+HANDLER t2 OPEN;
+HANDLER t2 READ FIRST;
+HANDLER t2 CLOSE;
+CREATE TABLE t5 (id INT PRIMARY KEY) ENGINE=<engine_type> AS SELECT *,
+SLEEP(1) FROM t2;
+ALTER TABLE t5 ADD COLUMN (b CHAR(100));
+DROP TABLE t5;
+CREATE TABLE t2_<conn_nr> ENGINE=<engine_type> AS SELECT *,
+SLEEP(1) FROM t2;
+DROP TABLE t2_<conn_nr>;
+RENAME TABLE t4 TO t4_new;
+RENAME TABLE t4_new TO t4;
+CREATE TABLE t6 (id INT PRIMARY KEY) ENGINE=<engine_type>;
+CREATE TABLE t7 (id INT PRIMARY KEY) ENGINE=<engine_type>;
+CREATE TABLE t8 (id INT PRIMARY KEY) ENGINE=<engine_type>;
+DROP TABLE IF EXISTS t6, t7, t8;
+CREATE FUNCTION t_func2 (s CHAR(20)) RETURNS CHAR(30) DETERMINISTIC
+RETURN CONCAT('Hello, ', s, '!');
+ALTER FUNCTION t_func2 COMMENT 'Now with a comment from conn <conn_nr>';
+DROP FUNCTION t_func2;
+ALTER EVENT ev1 ON SCHEDULE EVERY 12 HOUR
+STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR;
+ALTER EVENT ev1 ON SCHEDULE EVERY 11 HOUR
+STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR;
=== added file 'mysql-test/suite/ddl_lock/r/stress_single_user.result'
--- a/mysql-test/suite/ddl_lock/r/stress_single_user.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/ddl_lock/r/stress_single_user.result 2009-02-26 10:01:50 +0000
@@ -0,0 +1,248 @@
+##
+## 4.1 - Create three tables, which triggers inserts into each others
+## in round robin fashion. (Tables rr_t1 .. rr_t3.)
+##
+Set up
+CREATE TABLE rr_t1_<conn_id> (a INT, b INT, PRIMARY KEY (a))
+ENGINE=<engine_type>;
+CREATE TABLE rr_t2_<conn_id> (c INT, d INT, PRIMARY KEY (c))
+ENGINE=<engine_type>;
+CREATE TABLE rr_t3_<conn_id> (e INT, f INT, PRIMARY KEY (e))
+ENGINE=<engine_type>;
+CREATE TRIGGER ins_t1_t2_<conn_id> BEFORE INSERT ON rr_t1_<conn_id>
+FOR EACH ROW INSERT INTO rr_t2_<conn_id>(c, d) VALUES (NEW.a, NEW.b);
+CREATE TRIGGER ins_t2_t3_<conn_id> BEFORE INSERT ON rr_t2_<conn_id>
+FOR EACH ROW INSERT INTO rr_t3_<conn_id>(e, f) VALUES (NEW.c, NEW.d);
+CREATE TRIGGER ins_t3_t1_<conn_id> BEFORE INSERT ON rr_t3_<conn_id>
+FOR EACH ROW INSERT INTO rr_t1_<conn_id>(a, b) VALUES (NEW.e, NEW.f);
+Run Test
+INSERT INTO rr_t1_<conn_id>(a, b) VALUES (1, 2);
+ERROR HY000: Can't update table 'rr_t1_<conn_id>' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+Clean up
+DROP TRIGGER ins_t1_t2_<conn_id>;
+DROP TRIGGER ins_t2_t3_<conn_id>;
+DROP TRIGGER ins_t3_t1_<conn_id>;
+DROP TABLE rr_t1_<conn_id>;
+DROP TABLE rr_t2_<conn_id>;
+DROP TABLE rr_t3_<conn_id>;
+##
+## 4.2 - Same as 4.1, but with foreign key contraints on the triggers.
+##
+Set up
+CREATE TABLE rr_t1_<conn_id> (a INT, b INT, PRIMARY KEY (a))
+ENGINE=<engine_type>;
+CREATE TABLE rr_t2_<conn_id> (c INT, d INT, PRIMARY KEY (c))
+ENGINE=<engine_type>;
+CREATE TABLE rr_t3_<conn_id> (e INT, f INT, PRIMARY KEY (e))
+ENGINE=<engine_type>;
+INSERT INTO rr_t1_<conn_id>(a, b) VALUES (1, 2);
+INSERT INTO rr_t2_<conn_id>(c, d) VALUES (2, 3), (5, 6);
+INSERT INTO rr_t3_<conn_id>(e, f) VALUES (3, 1), (6, 1);
+ALTER TABLE rr_t1_<conn_id> ADD CONSTRAINT t1_t2_<conn_id> FOREIGN KEY (b)
+REFERENCES rr_t2_<conn_id> (c) ON UPDATE CASCADE;
+ALTER TABLE rr_t2_<conn_id> ADD CONSTRAINT t2_t3_<conn_id> FOREIGN KEY (d)
+REFERENCES rr_t3_<conn_id> (e) ON UPDATE CASCADE;
+ALTER TABLE rr_t3_<conn_id> ADD CONSTRAINT t3_t1_<conn_id> FOREIGN KEY (f)
+REFERENCES rr_t1_<conn_id> (a) ON UPDATE CASCADE;
+Run test
+UPDATE rr_t1_<conn_id> SET b = 5 WHERE a = 1;
+UPDATE rr_t1_<conn_id> SET a = 7 WHERE a = 1;
+UPDATE rr_t2_<conn_id> SET d = 6 WHERE d = 3;
+INSERT INTO rr_t1_<conn_id>(a, b) VALUES (1, 2);
+Verify content
+SELECT * FROM rr_t1_<conn_id> ORDER BY a;
+a b
+1 2
+7 5
+SELECT * FROM rr_t2_<conn_id> ORDER BY c;
+c d
+2 6
+5 6
+SELECT * FROM rr_t3_<conn_id> ORDER BY e;
+e f
+3 1
+6 1
+Clean up
+ALTER TABLE rr_t1_<conn_id> DROP FOREIGN KEY t1_t2_<conn_id>;
+ALTER TABLE rr_t2_<conn_id> DROP FOREIGN KEY t2_t3_<conn_id>;
+ALTER TABLE rr_t3_<conn_id> DROP FOREIGN KEY t3_t1_<conn_id>;
+DROP TABLE rr_t1_<conn_id>, rr_t2_<conn_id>, rr_t3_<conn_id> CASCADE;
+##
+## 4.3 - Sub queries referencing the same table object
+##
+SELECT COUNT(*) FROM t2 AS one
+WHERE id IN (SELECT id FROM t2 WHERE id <= 5)
+OR id IN (SELECT id FROM t2 WHERE id > 5 AND id <= 10);
+COUNT(*)
+8
+##
+## 4.4 - Self joins on table
+##
+SELECT COUNT(one.id), MIN(one.id), MAX(one.id) FROM t2 AS one
+JOIN t2 AS two ON (one.id = two.id);
+COUNT(one.id) MIN(one.id) MAX(one.id)
+8 1 8
+##
+## 4.5 - 4.3 an 4.4 on view.
+##
+SELECT COUNT(*) FROM v2 AS one
+WHERE id IN (SELECT id FROM v2 WHERE id <=5)
+OR id IN (SELECT id FROM v2 WHERE id > 5 AND id <= 10);
+COUNT(*)
+8
+SELECT COUNT(one.id), MIN(one.id), MAX(one.id) FROM v2 AS one
+JOIN v2 AS two ON (one.id = two.id);
+COUNT(one.id) MIN(one.id) MAX(one.id)
+8 1 8
+##
+## 4.6 - 4.3, 4.4 and 4.5 with stored procedures.
+##
+CREATE PROCEDURE t_proc_self_join_<conn_id> (OUT cnt1 INT, OUT cnt2 INT)
+BEGIN
+SELECT COUNT(one.id) INTO cnt1 FROM t2 AS one
+JOIN t2 AS two ON (one.id = two.id);
+SELECT COUNT(one.id) INTO cnt2 FROM v2 AS one
+JOIN v2 AS two ON (one.id = two.id);
+END|
+CALL t_proc_self_join_<conn_id>(@a, @b);
+SELECT @a, @b;
+@a @b
+8 8
+DROP PROCEDURE t_proc_self_join_<conn_id>;
+CREATE PROCEDURE t_proc_sub_select_<conn_id> (OUT cnt1 INT, OUT cnt2 INT)
+BEGIN
+SELECT COUNT(*) INTO cnt1 FROM t2 AS one
+WHERE id IN (SELECT id FROM t2 WHERE id <= 5)
+OR id IN (SELECT id FROM t2 WHERE id > 5 AND id <= 10);
+SELECT COUNT(*) INTO cnt2 FROM v2 AS one
+WHERE id IN (SELECT id FROM v2 WHERE id <=5)
+OR id IN (SELECT id FROM v2 WHERE id > 5 AND id <= 10);
+END|
+CALL t_proc_sub_select_<conn_id>(@a, @b);
+SELECT @a, @b;
+@a @b
+8 8
+DROP PROCEDURE t_proc_sub_select_<conn_id>;
+##
+## 4.7 - DML statements using stored procedures called
+## by triggers on the same table/view
+##
+Set up
+CREATE TABLE tr_t1_<conn_id> (a INT, b INT, PRIMARY KEY (a))
+ENGINE=<engine_type>;
+INSERT INTO tr_t1_<conn_id> (a, b) VALUES (1, 2), (3, 4), (5, 6);
+CREATE PROCEDURE t_doUpdate_<conn_id> (IN aa INT, IN bb INT)
+BEGIN
+UPDATE tr_t1_<conn_id> SET b = bb + 1 WHERE a = aa;
+END|
+CREATE TRIGGER upd_tr_t1_<conn_id> AFTER UPDATE ON tr_t1_<conn_id>
+FOR EACH ROW call t_doUpdate_<conn_id>(NEW.a, NEW.b);
+Run test
+UPDATE tr_t1_<conn_id> SET b = 99 WHERE a = 3;
+ERROR HY000: Can't update table 'tr_t1_<conn_id>' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+Verify
+SELECT * FROM tr_t1_<conn_id> ORDER BY a;
+a b
+1 2
+3 99
+5 6
+Tear down
+DROP TRIGGER upd_tr_t1_<conn_id>;
+DROP PROCEDURE t_doUpdate_<conn_id>;
+DROP TABLE tr_t1_<conn_id>;
+##
+## 4.8 - Using insert select from the same table or view on top of the table
+##
+Set up
+CREATE TABLE is_t1_<conn_id> (a INT, b INT, PRIMARY KEY (a))
+ENGINE=<engine_type>;
+INSERT INTO is_t1_<conn_id> (a, b) VALUES (1, 2), (2, 4), (3, 6);
+CREATE VIEW is_v1_<conn_id> AS SELECT * FROM is_t1_<conn_id>;
+Test table
+SELECT MAX(a) FROM is_t1_<conn_id> INTO @max;
+INSERT INTO is_t1_<conn_id> (a,b)
+SELECT a + @max, b + @max FROM is_t1_<conn_id>;
+Verify table
+SELECT * FROM is_t1_<conn_id>;
+a b
+1 2
+2 4
+3 6
+4 5
+5 7
+6 9
+Test view
+SELECT MAX(a) FROM is_v1_<conn_id> INTO @max;
+INSERT INTO is_t1_<conn_id> (a,b)
+SELECT a + @max, b + @max FROM is_v1_<conn_id>;
+Verify view
+SELECT * FROM is_v1_<conn_id>;
+a b
+1 2
+2 4
+3 6
+4 5
+5 7
+6 9
+7 8
+8 10
+9 12
+10 11
+11 13
+12 15
+Clean up
+DROP VIEW is_v1_<conn_id>;
+DROP TABLE is_t1_<conn_id>;
+##
+## 4.9 - Rename t1 -> t2, t2 -> t3, t3 -> t1 in one statement
+##
+Set up
+CREATE TABLE rt_t1_<conn_id> (a INT, b INT, PRIMARY KEY (a))
+ENGINE=<engine_type>;
+INSERT INTO rt_t1_<conn_id> (a, b) VALUES (1, 2), (3, 4), (5, 6);
+Run test
+RENAME TABLE rt_t1_<conn_id> TO rt_t2_<conn_id>,
+rt_t2_<conn_id> TO rt_t3_<conn_id>,
+rt_t3_<conn_id> TO rt_t1_<conn_id>;
+Verify
+SELECT * FROM rt_t1_<conn_id>;
+a b
+1 2
+3 4
+5 6
+Clean up
+DROP TABLE rt_t1_<conn_id>;
+##
+## 4.10 - Run operations spanning multiple tables, such as
+## CREATE TABLE a SELECT FROM b, c, d
+##
+Set up
+CREATE TABLE mt_t1_<conn_id> (a INT, b INT, PRIMARY KEY (a))
+ENGINE=<engine_type>;
+INSERT INTO mt_t1_<conn_id> (a, b) VALUES (1, 2), (3, 4), (5, 6);
+CREATE TABLE mt_t2_<conn_id> (a INT, b INT, PRIMARY KEY (a))
+ENGINE=<engine_type>;
+INSERT INTO mt_t2_<conn_id> (a, b) VALUES (1, 2), (3, 4), (5, 6);
+Run test
+CREATE TABLE mt_total_<conn_id> (a INT) SELECT a FROM mt_t1_<conn_id>
+UNION SELECT a FROM mt_t2_<conn_id>
+UNION SELECT id FROM t2;
+Verify
+SELECT COUNT(*) FROM mt_total_<conn_id>;
+COUNT(*)
+8
+Clean up
+DROP TABLE mt_t1_<conn_id>;
+DROP TABLE mt_t2_<conn_id>;
+DROP TABLE mt_total_<conn_id>;
+##
+## 4.11 - Create tables using CREATE TABLE a LIKE b
+##
+Run test
+CREATE TABLE t_t1_<conn_id> LIKE t1;
+Verify
+SELECT COUNT(*) FROM t_t1_<conn_id>;
+COUNT(*)
+0
+Clean up
+DROP TABLE t_t1_<conn_id>;
=== modified file 'mysql-test/suite/ddl_lock/stress_tests.txt'
--- a/mysql-test/suite/ddl_lock/stress_tests.txt 2008-12-17 10:11:14 +0000
+++ b/mysql-test/suite/ddl_lock/stress_tests.txt 2009-02-26 10:01:50 +0000
@@ -1,5 +1,6 @@
#
-# 10% DDL transactions
+# 60% DML transactions, 30% DDL, 10% same object multiple times with
+# same user
#
stress_dml
stress_dml
@@ -7,7 +8,7 @@ stress_dml
stress_dml
stress_dml
stress_dml
-stress_dml
-stress_dml
-stress_dml
+stress_single_user
+stress_ddl
+stress_ddl
stress_ddl
=== modified file 'mysql-test/suite/ddl_lock/t/concurrent_ddl.test'
--- a/mysql-test/suite/ddl_lock/t/concurrent_ddl.test 2009-02-04 09:11:18 +0000
+++ b/mysql-test/suite/ddl_lock/t/concurrent_ddl.test 2009-02-26 10:01:50 +0000
@@ -289,7 +289,7 @@ SET DEBUG_SYNC= 'RESET';
DROP TABLE t1;
--echo ##
---echo ## 1.6 Tow connections are long create/select, while another is
+--echo ## 1.6 Two connections are long create/select, while another is
--echo ## altering the table
--echo ##
=== modified file 'mysql-test/suite/ddl_lock/t/create_stress_tables.test'
--- a/mysql-test/suite/ddl_lock/t/create_stress_tables.test 2008-12-17 10:11:14 +0000
+++ b/mysql-test/suite/ddl_lock/t/create_stress_tables.test 2009-02-26 10:01:50 +0000
@@ -8,10 +8,6 @@
--source suite/ddl_lock/include/stress_settings.inc
-if (`SELECT LENGTH("$engine_type") = 0`) {
- let $engine_type= myisam;
-}
-
#
# Create test table with some data
#
@@ -33,6 +29,48 @@ while (`SELECT MAX(id) < $num_stress_row
}
#
+# Test table for locking
+#
+--replace_result $engine_type <engine_type>
+eval CREATE TABLE t2 (id INT PRIMARY KEY)
+ ENGINE=$engine_type;
+
+# Some data for the "long" selects
+INSERT INTO t2(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+#
+# Test table for truncating
+#
+--replace_result $engine_type <engine_type>
+eval CREATE TABLE t3 (id INT PRIMARY KEY)
+ ENGINE=$engine_type;
+
+#
+# Test table for renaming
+#
+--replace_result $engine_type <engine_type>
+eval CREATE TABLE t4 (id INT PRIMARY KEY)
+ ENGINE=$engine_type;
+
+# Need something to start with
+INSERT INTO t4(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+# Bulk load the correct number of rows
+while (`SELECT MAX(id) < $num_stress_rows FROM t4`)
+{
+ SELECT MAX(id) FROM t4 INTO @max;
+--replace_result $num_stress_rows <num_stress_rows>
+ eval INSERT INTO t4(id) SELECT id + @max FROM t4
+ WHERE id + @max <= $num_stress_rows;
+}
+
+#
+# Create views
+#
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t2;
+
+#
# Create stored Procedure
#
delimiter |;
@@ -49,6 +87,13 @@ CREATE FUNCTION t_func (s CHAR(20)) RETU
RETURN CONCAT('Hello, ', s, '!');
#
-# Side effects: this test will create table t1, and routines t_proc
-# and t_func. This is intentional.
+# Create event
+#
+CREATE EVENT ev1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO
+SELECT MOD(29,9);
+
+#
+# Side effects: this test will create tables t1, t2, t3, t3, views v1 and v2,
+# routines t_proc and t_func and event ev1.
+# This is intentional.
#
=== modified file 'mysql-test/suite/ddl_lock/t/stress_ddl.test'
--- a/mysql-test/suite/ddl_lock/t/stress_ddl.test 2008-12-17 10:11:14 +0000
+++ b/mysql-test/suite/ddl_lock/t/stress_ddl.test 2009-02-26 10:01:50 +0000
@@ -6,6 +6,8 @@
## Test case 3: Concurrent system scenarios - DDL load
##
+--source suite/ddl_lock/include/stress_settings.inc
+
# Detect whether or not this test is run from the stress test,
# with the proper setup
let $have_table= `SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
@@ -27,6 +29,7 @@ if (`SELECT ($have_table = 0) OR ($have_
# Use connection id so that the db objects created here get unique names
let $conn_id= `SELECT CONNECTION_ID()`;
+let $conn_nr= conn_$conn_id;
let $col_name= c_$conn_id;
let $idx_name= t1_indx_c_$conn_id;
@@ -63,13 +66,155 @@ eval SELECT id FROM $all_view_name WHERE
eval DROP VIEW $view_name, $all_view_name;
#
-# Stored Procedure
+# Stored Procedure, alse
+# 1.9 - Altering the same stored procedure and function
#
--replace_result $conn_id <conn_id>
eval ALTER PROCEDURE t_proc COMMENT 'Now with a comment from conn $conn_id';
#
-# Function
+# Function, also
+# 1.9 - Altering the same stored procedure and function
#
--replace_result $conn_id <conn_id>
eval ALTER FUNCTION t_func COMMENT 'Now with a comment from conn $conn_id';
+
+##
+## Test case 1: Concurrent users performing same/different DDL
+##
+## This is a stress adoption of this part of the test, where the same
+## queries are run without systematic locking, but in a stress scenario
+## to see that no crashes occure.
+##
+## This test case is covered elsewhere:
+##
+## 1.8a - List of tables in rename is present in stress_single_user
+##
+
+#
+# 1.1 - Lock while alter table, handler drop and truncate, and
+# 1.2 - Simoultanious alter table
+# 1.4 - Select (handler) while altering - select first
+# 1.5 - Select (handler) while altering - alter first
+# 1.11, 1.12 and 1.13 - ALTER TABLE and TRUNCATE TABLE simoultaniously
+#
+
+LOCK TABLES t2 READ;
+UNLOCK TABLES;
+
+--replace_result $conn_nr <conn_nr>
+eval ALTER TABLE t2 ADD COLUMN (b_$conn_nr INT);
+--replace_result $conn_nr <conn_nr>
+eval ALTER TABLE t2 DROP COLUMN b_$conn_nr;
+
+TRUNCATE TABLE t3;
+
+HANDLER t2 OPEN;
+# Could have more coloumns than we expect
+--disable_result_log
+HANDLER t2 READ FIRST;
+--enable_result_log
+HANDLER t2 CLOSE;
+
+##
+## 1.3 - One connection creating a table, another is trying to alter it
+##
+
+# Sleep makes create table take time
+let $sql=CREATE TABLE t5 (id INT PRIMARY KEY) ENGINE=$engine_type AS SELECT *,
+ SLEEP(1) FROM t2;
+# 0 = OK, 1050 = table already exists
+let $errors=0, 1050;
+let $replace_from= $engine_type;
+let $replace_to= <engine_type>;
+--source suite/ddl_lock/include/exec_with_error.inc
+let $replace_from=;
+let $replace_to=;
+
+let $sql=ALTER TABLE t5 ADD COLUMN (b CHAR(100));
+# 0 = OK, 1060 = Duplicate column name, 1146 = Unknown table
+let $errors=0, 1060, 1146;
+--source suite/ddl_lock/include/exec_with_error.inc
+
+let $sql=DROP TABLE t5;
+# 0 = OK, 1051 = Unknown table
+let $errors=0, 1051;
+--source suite/ddl_lock/include/exec_with_error.inc
+
+##
+## 1.6 - Two connections are long create/select, while another is
+## altering the table (alter from part 1.1)
+##
+
+--replace_result $engine_type <engine_type> $conn_nr <conn_nr>
+eval CREATE TABLE t2_$conn_nr ENGINE=$engine_type AS SELECT *,
+ SLEEP(1) FROM t2;
+
+--replace_result $conn_nr <conn_nr>
+eval DROP TABLE t2_$conn_nr;
+
+##
+## 1.7 - Two connections renaming the same table
+##
+
+let $sql=RENAME TABLE t4 TO t4_new;
+# 0 = OK, 1017 = Cant find file, 1050 = table already exists
+let $errors=0, 1017, 1050;
+--source suite/ddl_lock/include/exec_with_error.inc
+
+let $sql=RENAME TABLE t4_new TO t4;
+# 0 = OK, 1017 = Cant find file, 1050 = table already exists
+let $errors=0, 1017, 1050;
+--source suite/ddl_lock/include/exec_with_error.inc
+
+## 1.8a - List of tables in drop table
+
+# 0 = OK, 1050 = table already exists
+let $errors=0, 1050;
+let $replace_from= $engine_type;
+let $replace_to= <engine_type>;
+let $sql=CREATE TABLE t6 (id INT PRIMARY KEY) ENGINE=$engine_type;
+--source suite/ddl_lock/include/exec_with_error.inc
+let $sql=CREATE TABLE t7 (id INT PRIMARY KEY) ENGINE=$engine_type;
+--source suite/ddl_lock/include/exec_with_error.inc
+let $sql=CREATE TABLE t8 (id INT PRIMARY KEY) ENGINE=$engine_type;
+--source suite/ddl_lock/include/exec_with_error.inc
+let $replace_from=;
+let $replace_to=;
+
+let $sql=DROP TABLE IF EXISTS t6, t7, t8;
+# 0 = OK, 1051 = Unknown table
+let $errors=0, 1051;
+--source suite/ddl_lock/include/exec_with_error.inc
+
+## 1.10 - Altering while dropping stored procedure/function
+
+let $sql=CREATE FUNCTION t_func2 (s CHAR(20)) RETURNS CHAR(30) DETERMINISTIC
+RETURN CONCAT('Hello, ', s, '!');
+# 0 = OK, 1304 = function already exists
+let $errors=0, 1304;
+--source suite/ddl_lock/include/exec_with_error.inc
+
+let $sql=ALTER FUNCTION t_func2 COMMENT 'Now with a comment from conn $conn_nr';
+# 0 = OK, 1305 = function does not exist
+let $errors=0, 1305;
+let $replace_from= $conn_nr;
+let $replace_to= <conn_nr>;
+--source suite/ddl_lock/include/exec_with_error.inc
+let $replace_from=;
+let $replace_to=;
+
+let $sql=DROP FUNCTION t_func2;
+# 0 = OK, 1305 = function does not exist
+let $errors=0, 1305;
+--source suite/ddl_lock/include/exec_with_error.inc
+
+##
+## 1.14 Two connections are trying to alter the same event.
+##
+
+ALTER EVENT ev1 ON SCHEDULE EVERY 12 HOUR
+STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR;
+
+ALTER EVENT ev1 ON SCHEDULE EVERY 11 HOUR
+STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR;
=== added file 'mysql-test/suite/ddl_lock/t/stress_single_user.test'
--- a/mysql-test/suite/ddl_lock/t/stress_single_user.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/ddl_lock/t/stress_single_user.test 2009-02-26 10:01:50 +0000
@@ -0,0 +1,400 @@
+##
+## Test for WL #4343 - DDL locking for all metadata objects
+##
+
+##
+## Test case 4: Single user scenarios (object multiple times)
+##
+
+--source suite/ddl_lock/include/stress_settings.inc
+
+# Detect whether or not this test is run from the stress test,
+# with the proper setup
+let $have_table= `SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'`;
+
+let $have_proc= `SELECT (COUNT(*) = 1) FROM INFORMATION_SCHEMA.ROUTINES
+ WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='t_proc'`;
+
+let $have_func= `SELECT (COUNT(*) = 1) FROM INFORMATION_SCHEMA.ROUTINES
+ WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='t_func'`;
+
+if (`SELECT ($have_table = 0) OR ($have_proc = 0) OR ($have_func = 0)`) {
+ --disable_query_log
+ --disable_result_log
+ --source suite/ddl_lock/t/create_stress_tables.test
+ --enable_result_log
+ --enable_query_log
+}
+
+# Use connection id so that the db objects created here get unique names
+let $conn_nr= `SELECT CONNECTION_ID()`;
+let $conn_id= conn_$conn_nr;
+
+--echo ##
+--echo ## 4.1 - Create three tables, which triggers inserts into each others
+--echo ## in round robin fashion. (Tables rr_t1 .. rr_t3.)
+--echo ##
+
+--echo Set up
+
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE rr_t1_$conn_id (a INT, b INT, PRIMARY KEY (a))
+ ENGINE=$engine_type;
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE rr_t2_$conn_id (c INT, d INT, PRIMARY KEY (c))
+ ENGINE=$engine_type;
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE rr_t3_$conn_id (e INT, f INT, PRIMARY KEY (e))
+ ENGINE=$engine_type;
+
+--replace_result $conn_id <conn_id>
+eval CREATE TRIGGER ins_t1_t2_$conn_id BEFORE INSERT ON rr_t1_$conn_id
+ FOR EACH ROW INSERT INTO rr_t2_$conn_id(c, d) VALUES (NEW.a, NEW.b);
+
+--replace_result $conn_id <conn_id>
+eval CREATE TRIGGER ins_t2_t3_$conn_id BEFORE INSERT ON rr_t2_$conn_id
+ FOR EACH ROW INSERT INTO rr_t3_$conn_id(e, f) VALUES (NEW.c, NEW.d);
+
+--replace_result $conn_id <conn_id>
+eval CREATE TRIGGER ins_t3_t1_$conn_id BEFORE INSERT ON rr_t3_$conn_id
+ FOR EACH ROW INSERT INTO rr_t1_$conn_id(a, b) VALUES (NEW.e, NEW.f);
+
+--echo Run Test
+
+--replace_result $conn_id <conn_id>
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+eval INSERT INTO rr_t1_$conn_id(a, b) VALUES (1, 2);
+
+--echo Clean up
+
+--replace_result $conn_id <conn_id>
+eval DROP TRIGGER ins_t1_t2_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP TRIGGER ins_t2_t3_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP TRIGGER ins_t3_t1_$conn_id;
+
+--replace_result $conn_id <conn_id>
+eval DROP TABLE rr_t1_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP TABLE rr_t2_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP TABLE rr_t3_$conn_id;
+
+--echo ##
+--echo ## 4.2 - Same as 4.1, but with foreign key contraints on the triggers.
+--echo ##
+
+--echo Set up
+
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE rr_t1_$conn_id (a INT, b INT, PRIMARY KEY (a))
+ ENGINE=$engine_type;
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE rr_t2_$conn_id (c INT, d INT, PRIMARY KEY (c))
+ ENGINE=$engine_type;
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE rr_t3_$conn_id (e INT, f INT, PRIMARY KEY (e))
+ ENGINE=$engine_type;
+
+--replace_result $conn_id <conn_id>
+eval INSERT INTO rr_t1_$conn_id(a, b) VALUES (1, 2);
+--replace_result $conn_id <conn_id>
+eval INSERT INTO rr_t2_$conn_id(c, d) VALUES (2, 3), (5, 6);
+--replace_result $conn_id <conn_id>
+eval INSERT INTO rr_t3_$conn_id(e, f) VALUES (3, 1), (6, 1);
+
+--replace_result $conn_id <conn_id>
+eval ALTER TABLE rr_t1_$conn_id ADD CONSTRAINT t1_t2_$conn_id FOREIGN KEY (b)
+ REFERENCES rr_t2_$conn_id (c) ON UPDATE CASCADE;
+--replace_result $conn_id <conn_id>
+eval ALTER TABLE rr_t2_$conn_id ADD CONSTRAINT t2_t3_$conn_id FOREIGN KEY (d)
+ REFERENCES rr_t3_$conn_id (e) ON UPDATE CASCADE;
+--replace_result $conn_id <conn_id>
+eval ALTER TABLE rr_t3_$conn_id ADD CONSTRAINT t3_t1_$conn_id FOREIGN KEY (f)
+ REFERENCES rr_t1_$conn_id (a) ON UPDATE CASCADE;
+
+--echo Run test
+
+--replace_result $conn_id <conn_id>
+eval UPDATE rr_t1_$conn_id SET b = 5 WHERE a = 1;
+--replace_result $conn_id <conn_id>
+eval UPDATE rr_t1_$conn_id SET a = 7 WHERE a = 1;
+--replace_result $conn_id <conn_id>
+eval UPDATE rr_t2_$conn_id SET d = 6 WHERE d = 3;
+
+--replace_result $conn_id <conn_id>
+eval INSERT INTO rr_t1_$conn_id(a, b) VALUES (1, 2);
+
+--echo Verify content
+
+--replace_result $conn_id <conn_id>
+eval SELECT * FROM rr_t1_$conn_id ORDER BY a;
+--replace_result $conn_id <conn_id>
+eval SELECT * FROM rr_t2_$conn_id ORDER BY c;
+--replace_result $conn_id <conn_id>
+eval SELECT * FROM rr_t3_$conn_id ORDER BY e;
+
+--echo Clean up
+
+--replace_result $conn_id <conn_id>
+eval ALTER TABLE rr_t1_$conn_id DROP FOREIGN KEY t1_t2_$conn_id;
+--replace_result $conn_id <conn_id>
+eval ALTER TABLE rr_t2_$conn_id DROP FOREIGN KEY t2_t3_$conn_id;
+--replace_result $conn_id <conn_id>
+eval ALTER TABLE rr_t3_$conn_id DROP FOREIGN KEY t3_t1_$conn_id;
+
+--replace_result $conn_id <conn_id>
+eval DROP TABLE rr_t1_$conn_id, rr_t2_$conn_id, rr_t3_$conn_id CASCADE;
+
+--echo ##
+--echo ## 4.3 - Sub queries referencing the same table object
+--echo ##
+
+SELECT COUNT(*) FROM t2 AS one
+WHERE id IN (SELECT id FROM t2 WHERE id <= 5)
+ OR id IN (SELECT id FROM t2 WHERE id > 5 AND id <= 10);
+
+--echo ##
+--echo ## 4.4 - Self joins on table
+--echo ##
+
+SELECT COUNT(one.id), MIN(one.id), MAX(one.id) FROM t2 AS one
+ JOIN t2 AS two ON (one.id = two.id);
+
+--echo ##
+--echo ## 4.5 - 4.3 an 4.4 on view.
+--echo ##
+
+SELECT COUNT(*) FROM v2 AS one
+WHERE id IN (SELECT id FROM v2 WHERE id <=5)
+ OR id IN (SELECT id FROM v2 WHERE id > 5 AND id <= 10);
+
+SELECT COUNT(one.id), MIN(one.id), MAX(one.id) FROM v2 AS one
+ JOIN v2 AS two ON (one.id = two.id);
+
+--echo ##
+--echo ## 4.6 - 4.3, 4.4 and 4.5 with stored procedures.
+--echo ##
+
+delimiter |;
+--replace_result $conn_id <conn_id>
+eval CREATE PROCEDURE t_proc_self_join_$conn_id (OUT cnt1 INT, OUT cnt2 INT)
+BEGIN
+ SELECT COUNT(one.id) INTO cnt1 FROM t2 AS one
+ JOIN t2 AS two ON (one.id = two.id);
+ SELECT COUNT(one.id) INTO cnt2 FROM v2 AS one
+ JOIN v2 AS two ON (one.id = two.id);
+END|
+delimiter ;|
+
+--replace_result $conn_id <conn_id>
+eval CALL t_proc_self_join_$conn_id(@a, @b);
+SELECT @a, @b;
+
+--replace_result $conn_id <conn_id>
+eval DROP PROCEDURE t_proc_self_join_$conn_id;
+
+delimiter |;
+--replace_result $conn_id <conn_id>
+eval CREATE PROCEDURE t_proc_sub_select_$conn_id (OUT cnt1 INT, OUT cnt2 INT)
+BEGIN
+ SELECT COUNT(*) INTO cnt1 FROM t2 AS one
+ WHERE id IN (SELECT id FROM t2 WHERE id <= 5)
+ OR id IN (SELECT id FROM t2 WHERE id > 5 AND id <= 10);
+ SELECT COUNT(*) INTO cnt2 FROM v2 AS one
+ WHERE id IN (SELECT id FROM v2 WHERE id <=5)
+ OR id IN (SELECT id FROM v2 WHERE id > 5 AND id <= 10);
+END|
+delimiter ;|
+
+--replace_result $conn_id <conn_id>
+eval CALL t_proc_sub_select_$conn_id(@a, @b);
+SELECT @a, @b;
+
+--replace_result $conn_id <conn_id>
+eval DROP PROCEDURE t_proc_sub_select_$conn_id;
+
+--echo ##
+--echo ## 4.7 - DML statements using stored procedures called
+--echo ## by triggers on the same table/view
+--echo ##
+
+--echo Set up
+
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE tr_t1_$conn_id (a INT, b INT, PRIMARY KEY (a))
+ ENGINE=$engine_type;
+
+--replace_result $conn_id <conn_id>
+eval INSERT INTO tr_t1_$conn_id (a, b) VALUES (1, 2), (3, 4), (5, 6);
+
+delimiter |;
+--replace_result $conn_id <conn_id>
+eval CREATE PROCEDURE t_doUpdate_$conn_id (IN aa INT, IN bb INT)
+BEGIN
+ UPDATE tr_t1_$conn_id SET b = bb + 1 WHERE a = aa;
+END|
+delimiter ;|
+
+--replace_result $conn_id <conn_id>
+eval CREATE TRIGGER upd_tr_t1_$conn_id AFTER UPDATE ON tr_t1_$conn_id
+ FOR EACH ROW call t_doUpdate_$conn_id(NEW.a, NEW.b);
+
+--echo Run test
+
+--replace_result $conn_id <conn_id>
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+eval UPDATE tr_t1_$conn_id SET b = 99 WHERE a = 3;
+
+--echo Verify
+
+--replace_result $conn_id <conn_id>
+eval SELECT * FROM tr_t1_$conn_id ORDER BY a;
+
+--echo Tear down
+
+--replace_result $conn_id <conn_id>
+eval DROP TRIGGER upd_tr_t1_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP PROCEDURE t_doUpdate_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP TABLE tr_t1_$conn_id;
+
+--echo ##
+--echo ## 4.8 - Using insert select from the same table or view on top of the table
+--echo ##
+
+--echo Set up
+
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE is_t1_$conn_id (a INT, b INT, PRIMARY KEY (a))
+ ENGINE=$engine_type;
+
+--replace_result $conn_id <conn_id>
+eval INSERT INTO is_t1_$conn_id (a, b) VALUES (1, 2), (2, 4), (3, 6);
+--replace_result $conn_id <conn_id>
+eval CREATE VIEW is_v1_$conn_id AS SELECT * FROM is_t1_$conn_id;
+
+--echo Test table
+
+--replace_result $conn_id <conn_id>
+eval SELECT MAX(a) FROM is_t1_$conn_id INTO @max;
+--replace_result $conn_id <conn_id>
+eval INSERT INTO is_t1_$conn_id (a,b)
+ SELECT a + @max, b + @max FROM is_t1_$conn_id;
+
+--echo Verify table
+
+--replace_result $conn_id <conn_id>
+eval SELECT * FROM is_t1_$conn_id;
+
+--echo Test view
+
+--replace_result $conn_id <conn_id>
+eval SELECT MAX(a) FROM is_v1_$conn_id INTO @max;
+--replace_result $conn_id <conn_id>
+eval INSERT INTO is_t1_$conn_id (a,b)
+ SELECT a + @max, b + @max FROM is_v1_$conn_id;
+
+--echo Verify view
+
+--replace_result $conn_id <conn_id>
+eval SELECT * FROM is_v1_$conn_id;
+
+--echo Clean up
+
+--replace_result $conn_id <conn_id>
+eval DROP VIEW is_v1_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP TABLE is_t1_$conn_id;
+
+--echo ##
+--echo ## 4.9 - Rename t1 -> t2, t2 -> t3, t3 -> t1 in one statement
+--echo ##
+
+--echo Set up
+
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE rt_t1_$conn_id (a INT, b INT, PRIMARY KEY (a))
+ ENGINE=$engine_type;
+
+--replace_result $conn_id <conn_id>
+eval INSERT INTO rt_t1_$conn_id (a, b) VALUES (1, 2), (3, 4), (5, 6);
+
+--echo Run test
+
+--replace_result $conn_id <conn_id>
+eval RENAME TABLE rt_t1_$conn_id TO rt_t2_$conn_id,
+ rt_t2_$conn_id TO rt_t3_$conn_id,
+ rt_t3_$conn_id TO rt_t1_$conn_id;
+
+--echo Verify
+
+--replace_result $conn_id <conn_id>
+eval SELECT * FROM rt_t1_$conn_id;
+
+--echo Clean up
+
+--replace_result $conn_id <conn_id>
+eval DROP TABLE rt_t1_$conn_id;
+
+--echo ##
+--echo ## 4.10 - Run operations spanning multiple tables, such as
+--echo ## CREATE TABLE a SELECT FROM b, c, d
+--echo ##
+
+--echo Set up
+
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE mt_t1_$conn_id (a INT, b INT, PRIMARY KEY (a))
+ ENGINE=$engine_type;
+--replace_result $conn_id <conn_id>
+eval INSERT INTO mt_t1_$conn_id (a, b) VALUES (1, 2), (3, 4), (5, 6);
+
+--replace_result $engine_type <engine_type> $conn_id <conn_id>
+eval CREATE TABLE mt_t2_$conn_id (a INT, b INT, PRIMARY KEY (a))
+ ENGINE=$engine_type;
+--replace_result $conn_id <conn_id>
+eval INSERT INTO mt_t2_$conn_id (a, b) VALUES (1, 2), (3, 4), (5, 6);
+
+--echo Run test
+
+--replace_result $conn_id <conn_id>
+eval CREATE TABLE mt_total_$conn_id (a INT) SELECT a FROM mt_t1_$conn_id
+ UNION SELECT a FROM mt_t2_$conn_id
+ UNION SELECT id FROM t2;
+
+--echo Verify
+
+--replace_result $conn_id <conn_id>
+eval SELECT COUNT(*) FROM mt_total_$conn_id;
+
+--echo Clean up
+
+--replace_result $conn_id <conn_id>
+eval DROP TABLE mt_t1_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP TABLE mt_t2_$conn_id;
+--replace_result $conn_id <conn_id>
+eval DROP TABLE mt_total_$conn_id;
+
+--echo ##
+--echo ## 4.11 - Create tables using CREATE TABLE a LIKE b
+--echo ##
+
+--echo Run test
+
+--replace_result $conn_id <conn_id>
+eval CREATE TABLE t_t1_$conn_id LIKE t1;
+
+--echo Verify
+
+--replace_result $conn_id <conn_id>
+eval SELECT COUNT(*) FROM t_t1_$conn_id;
+
+--echo Clean up
+
+--replace_result $conn_id <conn_id>
+eval DROP TABLE t_t1_$conn_id;
| Thread |
|---|
| • bzr commit into mysql-6.0 branch (jorgen.austvik:2731) WL#4343 | Jorgen Austvik | 26 Feb |
