| 1 |
############################################################################ |
| 2 |
# WL#9053 : Group Replication: Push Group Replication Plugin to mysql-trunk |
| 3 |
# |
| 4 |
# This test does the basic check of prepared statements in |
| 5 |
# group replication environment |
| 6 |
# |
| 7 |
# 0. This test requires two servers |
| 8 |
# 1. Start Group Replication on two servers |
| 9 |
# 2. Execute the following prepared statements on the members of the group |
| 10 |
# a) "CREATE TABLE.." |
| 11 |
# b) "INSERT.." |
| 12 |
# c) "SELECT.." |
| 13 |
# d) "UPDATE.." |
| 14 |
# e) "DELETE.." |
| 15 |
# f) "DROP.." |
| 16 |
# 3. Verify the consistency of data across the group |
| 17 |
# 4. Clean-up |
| 18 |
############################################################################ |
| 19 |
|
| 20 |
# Start GR with two servers |
| 21 |
--source include/have_group_replication_plugin.inc |
| 22 |
--source include/group_replication.inc |
| 23 |
|
| 24 |
# On server1 |
| 25 |
# Create a prepared statement to create table |
| 26 |
PREPARE stmt1 FROM "CREATE TABLE t1 (a INT PRIMARY KEY)"; |
| 27 |
EXECUTE stmt1; |
| 28 |
# Create a prepared statement to insert into table |
| 29 |
PREPARE stmt2 FROM "INSERT INTO t1(a) value (?)"; |
| 30 |
# Insert data |
| 31 |
SET @val=1; |
| 32 |
EXECUTE stmt2 USING @val; |
| 33 |
SET @val=2; |
| 34 |
EXECUTE stmt2 USING @val; |
| 35 |
SET @val=3; |
| 36 |
EXECUTE stmt2 USING @val; |
| 37 |
# Create a prepared statement to select |
| 38 |
PREPARE stmt3 FROM "SELECT * FROM t1 WHERE a>?"; |
| 39 |
SET @val=0; |
| 40 |
EXECUTE stmt3 USING @val; |
| 41 |
DEALLOCATE PREPARE stmt1; |
| 42 |
DEALLOCATE PREPARE stmt2; |
| 43 |
DEALLOCATE PREPARE stmt3; |
| 44 |
|
| 45 |
--source include/rpl_sync.inc |
| 46 |
|
| 47 |
# Connect to server2 |
| 48 |
--echo |
| 49 |
--let $rpl_connection_name= server2 |
| 50 |
--source include/rpl_connection.inc |
| 51 |
|
| 52 |
--let $assert_text= 'Check table t1 exist on server 2' |
| 53 |
--let $assert_cond= "[SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA=\"test\" AND TABLE_NAME=\"t1\"]" = 1 |
| 54 |
--source include/assert.inc |
| 55 |
|
| 56 |
--let $assert_text= 'Check all records exists on server 2' |
| 57 |
--let $assert_cond= "[SELECT COUNT(*) FROM test.t1]" = 3 |
| 58 |
--source include/assert.inc |
| 59 |
|
| 60 |
# Create a prepared statement to insert |
| 61 |
PREPARE stmt4 FROM "INSERT INTO t1(a) value (?)"; |
| 62 |
# Insert data |
| 63 |
SET @val=4; |
| 64 |
EXECUTE stmt4 USING @val; |
| 65 |
SET @val=5; |
| 66 |
EXECUTE stmt4 USING @val; |
| 67 |
SET @val=6; |
| 68 |
EXECUTE stmt4 USING @val; |
| 69 |
# Conflict testing |
| 70 |
--error ER_DUP_ENTRY |
| 71 |
EXECUTE stmt4 USING @val; |
| 72 |
# Create a prepared statement to update |
| 73 |
PREPARE stmt5 FROM "UPDATE t1 SET a=a+?"; |
| 74 |
SET @val=10; |
| 75 |
EXECUTE stmt5 USING@val; |
| 76 |
# Conflict testing |
| 77 |
PREPARE stmt FROM "UPDATE t1 SET a=11 WHERE a=?"; |
| 78 |
SET @val=12; |
| 79 |
--error ER_DUP_ENTRY |
| 80 |
EXECUTE stmt USING@val; |
| 81 |
|
| 82 |
# Create a prepared statement to delete |
| 83 |
PREPARE stmt6 FROM "DELETE from t1 WHERE a=?"; |
| 84 |
SET @val=11; |
| 85 |
EXECUTE stmt6 USING @val; |
| 86 |
SET @val=12; |
| 87 |
EXECUTE stmt6 USING @val; |
| 88 |
# Create a prepared statement to select |
| 89 |
PREPARE stmt7 FROM "SELECT * FROM t1 WHERE a>?"; |
| 90 |
SET @val=10; |
| 91 |
EXECUTE stmt7 USING @val; |
| 92 |
DEALLOCATE PREPARE stmt4; |
| 93 |
DEALLOCATE PREPARE stmt5; |
| 94 |
DEALLOCATE PREPARE stmt6; |
| 95 |
DEALLOCATE PREPARE stmt7; |
| 96 |
|
| 97 |
--source include/rpl_sync.inc |
| 98 |
|
| 99 |
# Connect to server1 and verify the data with server2 |
| 100 |
--echo |
| 101 |
--let $rpl_connection_name= server1 |
| 102 |
--source include/rpl_connection.inc |
| 103 |
|
| 104 |
--let $assert_text= 'Check all records exists on server 1' |
| 105 |
--let $assert_cond= "[SELECT COUNT(*) FROM test.t1 WHERE a>10]" = 4 |
| 106 |
--source include/assert.inc |
| 107 |
|
| 108 |
# Create a prepared statement to select |
| 109 |
PREPARE stmt8 FROM "SELECT * FROM t1"; |
| 110 |
EXECUTE stmt8; |
| 111 |
DEALLOCATE PREPARE stmt8; |
| 112 |
|
| 113 |
# Clean-up |
| 114 |
# Drop table |
| 115 |
PREPARE stmt9 FROM "DROP TABLE t1"; |
| 116 |
EXECUTE stmt9; |
| 117 |
DEALLOCATE PREPARE stmt9; |
| 118 |
|
| 119 |
--source include/group_replication_end.inc |