From 06eaf7232e9a920468c0f8d74dcf2fe8b555501c Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 13 Apr 2024 14:24:36 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/suite/rpl/include/rpl_row_sp002.test | 208 ++++++++++++++++++++++++ 1 file changed, 208 insertions(+) create mode 100644 mysql-test/suite/rpl/include/rpl_row_sp002.test (limited to 'mysql-test/suite/rpl/include/rpl_row_sp002.test') diff --git a/mysql-test/suite/rpl/include/rpl_row_sp002.test b/mysql-test/suite/rpl/include/rpl_row_sp002.test new file mode 100644 index 00000000..90c273eb --- /dev/null +++ b/mysql-test/suite/rpl/include/rpl_row_sp002.test @@ -0,0 +1,208 @@ +############################################################################# +# This test is being created to test out the non deterministic items with # +# row based replication. # +############################################################################# +# Test: Contains two stored procedures test one that insert data into tables# +# and use the LAST_INSERTED_ID() on tables with FOREIGN KEY(a) # +# REFERENCES ON DELETE CASCADE. This test also has a delete sp that # +# should cause a delete cascade. # +# The second test has a sp that will either insert rows or delete from# +# the table depending on the CASE outcome. The test uses this SP in a# +# transaction first rolling back and then commiting, # +############################################################################# + + + +# Includes +-- source include/have_binlog_format_row.inc +-- source include/master-slave.inc + +# Begin test section 1 + +eval CREATE TABLE test.t1 (a INT AUTO_INCREMENT KEY, t CHAR(6)) ENGINE=$engine_type; +eval CREATE TABLE test.t2 (a INT AUTO_INCREMENT KEY, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON DELETE CASCADE) ENGINE=$engine_type; + +delimiter |; +create procedure test.p1(IN i CHAR(6)) +begin + INSERT INTO test.t1 (t) VALUES (i); + INSERT INTO test.t2 VALUES (NULL,LAST_INSERT_ID()); +end| +create procedure test.p2(IN i INT) +begin + DELETE FROM test.t1 where a < i; +end| +delimiter ;| + +let $message=< -- test 1 call p1 -- >; +--source include/show_msg.inc +SET FOREIGN_KEY_CHECKS=1; +call test.p1('texas'); +call test.p1('Live'); +call test.p1('next'); +call test.p1('to'); +call test.p1('OK'); +call test.p1('MySQL'); + +let $message=< -- test 1 select master after p1 -- >; +--source include/show_msg.inc + +SELECT * FROM test.t1; +SELECT * FROM test.t2; + +let $message=< -- test 1 select slave after p1 -- >; +--source include/show_msg.inc +sync_slave_with_master; +SELECT * FROM test.t1; +SELECT * FROM test.t2; + +let $message=< -- test 1 call p2 & select master -- >; +--source include/show_msg.inc +connection master; +call test.p2(4); +SELECT * FROM test.t1; +SELECT * FROM test.t2; + +let $message=< -- test 1 select slave after p2 -- >; +--source include/show_msg.inc +sync_slave_with_master; +SELECT * FROM test.t1; +SELECT * FROM test.t2; + +connection master; +#show binlog events; +let $message=< -- End test 1 Begin test 2 -- >; +--source include/show_msg.inc +# End test 1 Begin test 2 + +--disable_warnings +SET FOREIGN_KEY_CHECKS=0; +DROP PROCEDURE IF EXISTS test.p1; +DROP PROCEDURE IF EXISTS test.p2; +DROP TABLE IF EXISTS test.t1; +DROP TABLE IF EXISTS test.t2; +--enable_warnings +# End of cleanup + +eval CREATE TABLE test.t1 (a INT, t CHAR(6), PRIMARY KEY(a)) ENGINE=$engine_type; +eval CREATE TABLE test.t2 (a INT, f INT, FOREIGN KEY(a) REFERENCES test.t1(a) ON UPDATE CASCADE, PRIMARY KEY(a)) ENGINE=$engine_type; + +delimiter |; +CREATE PROCEDURE test.p1(IN nm INT, IN ch CHAR(6)) +BEGIN + INSERT INTO test.t1 (a,t) VALUES (nm, ch); + INSERT INTO test.t2 VALUES (nm, LAST_INSERT_ID()); +END| +CREATE PROCEDURE test.p2(IN i INT) +BEGIN + UPDATE test.t1 SET a = i*10 WHERE a = i; +END| +delimiter ;| +SET FOREIGN_KEY_CHECKS=1; +CALL test.p1(1,'texas'); +CALL test.p1(2,'Live'); +CALL test.p1(3,'next'); +CALL test.p1(4,'to'); +CALL test.p1(5,'OK'); +CALL test.p1(6,'MySQL'); + +let $message=< -- test 2 select Master after p1 -- >; +--source include/show_msg.inc +SELECT * FROM test.t1; +SELECT * FROM test.t2; + +let $message=< -- test 2 select Slave after p1 -- >; +--source include/show_msg.inc +sync_slave_with_master; +SELECT * FROM test.t1; +SELECT * FROM test.t2; + +let $message=< -- test 2 call p2 & select Master -- >; +--source include/show_msg.inc +connection master; +CALL test.p2(2); +CALL test.p2(4); +CALL test.p2(6); +SELECT * FROM test.t1; +SELECT * FROM test.t2; + +let $message=< -- test 1 select Slave after p2 -- >; +--source include/show_msg.inc +sync_slave_with_master; +SELECT * FROM test.t1; +SELECT * FROM test.t2; + +connection master; +#show binlog events; +let $message=< -- End test 2 Begin test 3 -- >; +--source include/show_msg.inc +# End test 2 begin test 3 + +eval CREATE TABLE test.t3 (a INT AUTO_INCREMENT KEY, t CHAR(6))ENGINE=$engine_type; + +delimiter |; +CREATE PROCEDURE test.p3(IN n INT) +begin +CASE n +WHEN 2 THEN + DELETE from test.t3; +ELSE + INSERT INTO test.t3 VALUES (NULL,'NONE'); +END CASE; +end| +delimiter ;| + +SET AUTOCOMMIT=0; +START TRANSACTION; + +-- disable_query_log +-- disable_result_log +let $n=50; +while ($n) +{ + eval call test.p3($n); + dec $n; +} +-- enable_result_log +-- enable_query_log + +ROLLBACK; +select * from test.t3; +sync_slave_with_master; +select * from test.t3; + +connection master; +START TRANSACTION; + +-- disable_query_log +-- disable_result_log +let $n=50; +while ($n) +{ + eval call test.p3($n); + dec $n; +} +-- enable_result_log +-- enable_query_log + +COMMIT; +select * from test.t3; +sync_slave_with_master; +select * from test.t3; + +connection master; +#show binlog events from 1627; + + +# First lets cleanup +SET AUTOCOMMIT=1; +SET FOREIGN_KEY_CHECKS=0; +DROP PROCEDURE test.p3; +DROP PROCEDURE test.p1; +DROP PROCEDURE test.p2; +DROP TABLE test.t1; +DROP TABLE test.t2; +DROP TABLE test.t3; + +# End of 5.0 test case +--source include/rpl_end.inc -- cgit v1.2.3