| 1 |
################################################################################ |
| 2 |
# The aim of this test is to verify that Group Replication successfully |
| 3 |
# blocks DML's on non-transactional tables. |
| 4 |
# |
| 5 |
# 0. Start a server with Group Replication |
| 6 |
# 1. Create tables with different storage engines, this should not be blocked |
| 7 |
# (INNODB, CSV, MyISAM, MEMORY, ARCHIVE, BLACKHOLE, MRG_MyISAM) |
| 8 |
# 2. Perform DML's on non-transactional tables, the instructions should FAIL. |
| 9 |
# 3. Execute a DML(INSERT..SELECT) on innodb table where select is from |
| 10 |
# non-transactional table. The instruction should FAIL. |
| 11 |
# 4. Alter the non-transactional table's engine to INNODB and execute |
| 12 |
# DML(INSERT..SELECT) again. This time instruction should SUCCEED. |
| 13 |
# 5. Clean-up |
| 14 |
# |
| 15 |
################################################################################ |
| 16 |
|
| 17 |
--source include/have_group_replication_plugin.inc |
| 18 |
|
| 19 |
--source include/group_replication.inc |
| 20 |
|
| 21 |
--connection server1 |
| 22 |
--echo Create tables with different storage engines, Group Replication should not block this. |
| 23 |
|
| 24 |
USE test; |
| 25 |
# Create table with INNODB |
| 26 |
CREATE TABLE tn (c1 int PRIMARY KEY, c2 int) ENGINE=INNODB; |
| 27 |
|
| 28 |
# Create table with CSV |
| 29 |
CREATE TABLE t1 (c1 int not null, c2 int not null) ENGINE=CSV; |
| 30 |
|
| 31 |
# Create table with MyISAM |
| 32 |
CREATE TABLE t2 (c1 int PRIMARY KEY, c2 int) ENGINE=MyISAM; |
| 33 |
|
| 34 |
# Create table with MEMORY |
| 35 |
CREATE TABLE t3 (c1 int PRIMARY KEY, c2 int) ENGINE=MEMORY; |
| 36 |
|
| 37 |
# Create table with ARCHIVE |
| 38 |
CREATE TABLE t4 (c1 int, c2 int) ENGINE=ARCHIVE; |
| 39 |
|
| 40 |
# Create table with BLACKHOLE |
| 41 |
CREATE TABLE t5 (c1 int PRIMARY KEY, c2 int) ENGINE=BLACKHOLE; |
| 42 |
|
| 43 |
# Create table with MRG_MyISAM |
| 44 |
CREATE TABLE t6 (C1 INT PRIMARY KEY, c2 int) ENGINE=MERGE UNION=(t2) INSERT_METHOD=LAST; |
| 45 |
|
| 46 |
--echo |
| 47 |
--echo Group Replication Should block DML's on non-transactional tables |
| 48 |
--echo |
| 49 |
|
| 50 |
--let $tables_count= 6 |
| 51 |
while ($tables_count) |
| 52 |
{ |
| 53 |
# These statements are expected to fail with ER_BEFORE_DML_VALIDATION_ERROR. |
| 54 |
# The ARCHIVE storage engine is an exception, as it rejects UPDATE and DELETE |
| 55 |
# statements with ER_ILLEGAL_HA during resolving. |
| 56 |
--let $update_delete_error = ER_BEFORE_DML_VALIDATION_ERROR |
| 57 |
if ($tables_count == 4) { |
| 58 |
--let $update_delete_error = ER_ILLEGAL_HA |
| 59 |
} |
| 60 |
|
| 61 |
# Test the INSERT instruction |
| 62 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 63 |
--eval INSERT INTO t$tables_count (c1,c2) VALUES(1, 2) |
| 64 |
--eval INSERT INTO tn (c1) VALUES ($tables_count+10) |
| 65 |
|
| 66 |
# Test the UPDATE instruction |
| 67 |
--error $update_delete_error |
| 68 |
--eval UPDATE t$tables_count SET c2= 100 |
| 69 |
|
| 70 |
# Test the REPLACE instructions |
| 71 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 72 |
--eval REPLACE INTO t$tables_count(c1) VALUES(2) |
| 73 |
|
| 74 |
# Test the REPLACE...SELECT instruction |
| 75 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 76 |
--eval REPLACE INTO t$tables_count (c1) SELECT tn.c1 FROM tn |
| 77 |
|
| 78 |
# Test the UPDATE_MULTI instruction |
| 79 |
--error $update_delete_error |
| 80 |
--eval UPDATE t$tables_count, tn SET t$tables_count.c1= 100, tn.c1= 100 |
| 81 |
|
| 82 |
# Test the DELETE instruction |
| 83 |
--error $update_delete_error |
| 84 |
--eval DELETE FROM t$tables_count |
| 85 |
|
| 86 |
--eval DELETE FROM tn |
| 87 |
--dec $tables_count |
| 88 |
} |
| 89 |
|
| 90 |
--echo Check that INSERT...SELECT statement on InnoDB table(tn) from a non-transactional table(Eg:MyISAM t2) fails |
| 91 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 92 |
INSERT INTO tn (c1) SELECT (t2.c1) FROM t2; |
| 93 |
|
| 94 |
--echo change storage engine to "InnoDB" on t2 and check INSERT...SELECT on tn succeeds |
| 95 |
ALTER TABLE t2 ENGINE=INNODB; |
| 96 |
INSERT INTO tn (c1) SELECT (t2.c1) FROM t2; |
| 97 |
|
| 98 |
# clean up |
| 99 |
DROP TABLE tn; |
| 100 |
DROP TABLE t1; |
| 101 |
DROP TABLE t2; |
| 102 |
DROP TABLE t3; |
| 103 |
DROP TABLE t4; |
| 104 |
DROP TABLE t5; |
| 105 |
DROP TABLE t6; |
| 106 |
|
| 107 |
SET SESSION sql_log_bin= 0; |
| 108 |
call mtr.add_suppression("Table.*does not have any PRIMARY KEY. This is not compatible with Group Replication."); |
| 109 |
call mtr.add_suppression("Table.*does not use the InnoDB storage engine. This is not compatible with Group Replication."); |
| 110 |
SET SESSION sql_log_bin= 1; |
| 111 |
|
| 112 |
--source include/group_replication_end.inc |