| 1 |
################################################################################ |
| 2 |
# The intent of this test is to verify if the validations that were |
| 3 |
# created within the Group Replication plugin in the runtime process |
| 4 |
# are fully functional. |
| 5 |
# |
| 6 |
# It will test the invalid scenarios: |
| 7 |
# - Table with no primary key |
| 8 |
# - Table without InnoDB storage engine |
| 9 |
# - Table without both |
| 10 |
# - Table with 'CASCADE' referential key |
| 11 |
# - Table with 'SET NULL' referential key |
| 12 |
# |
| 13 |
# It will cycle all cases in which the runtime validation is verified. |
| 14 |
# |
| 15 |
# Afterwards the plugin will be stopped and one of the cases will be tested in |
| 16 |
# order to assess that the verification is no longer accomplished. |
| 17 |
# |
| 18 |
# One will test in a valid table that dynamic variables that can change in |
| 19 |
# runtime will also cause a failure in validation if they have incorrect values. |
| 20 |
# |
| 21 |
# Table t4 in the test case was added because of BUG#21909427 DMLS ON |
| 22 |
# BLACKHOLE TABLES ARE ALLOWED IN GR, as BLACKHOLE storage engine tables are |
| 23 |
# considered to be transactional. |
| 24 |
|
| 25 |
# Tables t5, t6, t7 and t8 in the test case were added because of |
| 26 |
# BUG#21918361 GR CAN GENERATE INCONSISTENCIES IF THERE ARE MULTIPLE FK |
| 27 |
# DEPENDENCIES. The decision is to block DML updates if the table contains |
| 28 |
# foreign key with 'CASCADE' clause. |
| 29 |
# |
| 30 |
# Test: |
| 31 |
# 0. The test requires one server. |
| 32 |
# |
| 33 |
# 1. With member being ONLINE. |
| 34 |
# - Create table without primary key. |
| 35 |
# - Create table with non-transactional(MyISAM) engine. |
| 36 |
# - Create table without primary key and with non-transactional(MyISAM) engine. |
| 37 |
# - Create table without InnoDB storage engine. |
| 38 |
# - Create auxiliary table with primary key and with InnoDB engine. |
| 39 |
# - Create table with 'ON UPDATE CASCADE' foreign key. |
| 40 |
# - Create table with 'ON DELETE CASCADE' foreign key. |
| 41 |
# - Create table with 'ON DELETE SET NULL' foreign key. |
| 42 |
# - Create table with 'ON UPDATE SET NULL' foreign key. |
| 43 |
# |
| 44 |
# 2. Testing all tables that will fail with following DMLs: |
| 45 |
# INSERT, UPDATE, INSERT...SELECT, DELETE, LOAD DATA, REPLACE, |
| 46 |
# REPLACE...SELECT, DELETE_MULTI, UPDATE_MULTI |
| 47 |
# |
| 48 |
# 3. Test global variables that might change in runtime and which values are |
| 49 |
# not allowed in GR. Following settings are tested with DML operation on |
| 50 |
# auxiliary table: |
| 51 |
# binlog_format=STATEMENT, |
| 52 |
# transaction_write_set_extraction=OFF |
| 53 |
# |
| 54 |
# 4. Stop GR on member. Now, check following must succeed or fail with other |
| 55 |
# errors that not related to GR: |
| 56 |
# INSERT, UPDATE, INSERT...SELECT, DELETE, LOAD DATA, REPLACE, |
| 57 |
# REPLACE...SELECT, DELETE_MULTI, UPDATE_MULTI |
| 58 |
# |
| 59 |
# 5. Clean up. |
| 60 |
################################################################################ |
| 61 |
|
| 62 |
--let $group_replication_group_name= 36236980-4307-11e4-916c-0800200c9a67 |
| 63 |
--source include/have_group_replication_plugin.inc |
| 64 |
|
| 65 |
--source include/start_and_bootstrap_group_replication.inc |
| 66 |
|
| 67 |
#Lets create several tables. They will all breach 1 or more rules |
| 68 |
SET SESSION sql_log_bin= 0; |
| 69 |
call mtr.add_suppression("Table .* does not use the InnoDB storage engine. This is not compatible with Group Replication."); |
| 70 |
SET SESSION sql_log_bin= 1; |
| 71 |
|
| 72 |
#Wrong table without Primary Key |
| 73 |
CREATE TABLE t1 (c1 char(50)) ENGINE=InnoDB; |
| 74 |
|
| 75 |
#Wrong table without Transactional engine |
| 76 |
CREATE TABLE t2 (c1 char(50) NOT NULL PRIMARY KEY) ENGINE=MyISAM; |
| 77 |
|
| 78 |
#Wrong table with both criteria |
| 79 |
CREATE TABLE t3 (c1 char(50)) ENGINE=MyISAM; |
| 80 |
|
| 81 |
#Wrong table without InnoDB storage engine |
| 82 |
CREATE TABLE t4 (c1 char(50) NOT NULL PRIMARY KEY) ENGINE=Blackhole; |
| 83 |
|
| 84 |
#Auxiliary test table |
| 85 |
CREATE TABLE tn (cn char(50) NOT NULL PRIMARY KEY) ENGINE=InnoDB; |
| 86 |
|
| 87 |
# Wrong table with 'ON UPDATE CASCADE' foreign key. |
| 88 |
CREATE TABLE t5 (c1 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES tn(cn) ON UPDATE CASCADE) ENGINE = InnoDB; |
| 89 |
|
| 90 |
# Wrong table with 'ON UPDATE CASCADE' foreign key. |
| 91 |
CREATE TABLE t6 (c1 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES |
| 92 |
tn(cn) ON UPDATE CASCADE ON DELETE RESTRICT) ENGINE = InnoDB; |
| 93 |
|
| 94 |
# Wrong table with 'ON DELETE CASCADE' foreign key. |
| 95 |
CREATE TABLE t7 (c1 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES tn(cn) ON DELETE CASCADE) ENGINE = InnoDB; |
| 96 |
|
| 97 |
# Wrong table with 'ON DELETE CASCADE' foreign key. |
| 98 |
CREATE TABLE t8 (c1 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES |
| 99 |
tn(cn) ON DELETE CASCADE ON UPDATE RESTRICT) ENGINE = InnoDB; |
| 100 |
|
| 101 |
# Wrong table with 'ON DELETE SET NULL' foreign key. |
| 102 |
CREATE TABLE t9 (c1 char(50), c2 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES |
| 103 |
tn(cn) ON DELETE SET NULL) ENGINE = InnoDB; |
| 104 |
|
| 105 |
# Wrong table with 'ON UPDATE SET NULL' foreign key. |
| 106 |
CREATE TABLE t10 (c1 char(50), c2 char(50) NOT NULL PRIMARY KEY, FOREIGN KEY (c1) REFERENCES |
| 107 |
tn(cn) ON UPDATE SET NULL) ENGINE = InnoDB; |
| 108 |
|
| 109 |
# |
| 110 |
# The tests begin here. |
| 111 |
# |
| 112 |
# The use cases to test are the following: |
| 113 |
# - SQLCOM_UPDATE |
| 114 |
# - SQLCOM_INSERT |
| 115 |
# - SQLCOM_INSERT_SELECT |
| 116 |
# - SQLCOM_DELETE |
| 117 |
# - SQLCOM_LOAD |
| 118 |
# - SQLCOM_REPLACE |
| 119 |
# - SQLCOM_REPLACE_SELECT |
| 120 |
# - SQLCOM_DELETE_MULTI |
| 121 |
# - SQLCOM_UPDATE_MULTI |
| 122 |
# |
| 123 |
|
| 124 |
--echo # |
| 125 |
--echo # Testing all tables that will fail. |
| 126 |
--echo # |
| 127 |
|
| 128 |
--let $wrong_tables_count=10 |
| 129 |
while ($wrong_tables_count) |
| 130 |
{ |
| 131 |
#Test the INSERT instruction |
| 132 |
if ($wrong_tables_count >= 9) |
| 133 |
{ |
| 134 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 135 |
--eval INSERT INTO t$wrong_tables_count VALUES('a','a') |
| 136 |
} |
| 137 |
if ($wrong_tables_count < 9) |
| 138 |
{ |
| 139 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 140 |
--eval INSERT INTO t$wrong_tables_count VALUES('a') |
| 141 |
} |
| 142 |
|
| 143 |
#Test the UPDATE instruction |
| 144 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 145 |
--eval UPDATE t$wrong_tables_count SET c1 = 'a' |
| 146 |
|
| 147 |
#Test the INSERT...SELECT instruction |
| 148 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 149 |
--eval UPDATE t$wrong_tables_count SET c1 = 'a' |
| 150 |
|
| 151 |
#Test the INSERT...SELECT instruction |
| 152 |
if ($wrong_tables_count >= 9) |
| 153 |
{ |
| 154 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 155 |
--eval INSERT INTO t$wrong_tables_count (c1,c2) SELECT tn.cn,'a' FROM tn |
| 156 |
} |
| 157 |
if ($wrong_tables_count < 9) |
| 158 |
{ |
| 159 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 160 |
--eval INSERT INTO t$wrong_tables_count (c1) SELECT tn.cn FROM tn |
| 161 |
} |
| 162 |
|
| 163 |
#Test the DELETE instruction |
| 164 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 165 |
--eval DELETE FROM t$wrong_tables_count |
| 166 |
|
| 167 |
#Test the load instruction |
| 168 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 169 |
--eval LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t$wrong_tables_count |
| 170 |
|
| 171 |
#Test the REPLACE instruction |
| 172 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 173 |
--eval REPLACE INTO t$wrong_tables_count(c1) VALUES('a') |
| 174 |
|
| 175 |
#Test the REPLACE...SELECT instruction |
| 176 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 177 |
--eval REPLACE INTO t$wrong_tables_count (c1) SELECT tn.cn FROM tn |
| 178 |
|
| 179 |
#Test the DELETE_MULTI instruction |
| 180 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 181 |
--eval DELETE t$wrong_tables_count, tn FROM t$wrong_tables_count, tn |
| 182 |
|
| 183 |
#Test the UPDATE_MULTI instruction |
| 184 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 185 |
--eval UPDATE t$wrong_tables_count, tn SET c1 = 'a' |
| 186 |
|
| 187 |
#clean up this table |
| 188 |
--eval DROP TABLE t$wrong_tables_count |
| 189 |
|
| 190 |
--dec $wrong_tables_count |
| 191 |
} |
| 192 |
|
| 193 |
--echo # |
| 194 |
--echo # Lets test the global variables that might change in |
| 195 |
--echo # runtime and which values are not allowed. |
| 196 |
--echo # |
| 197 |
|
| 198 |
#Test the value of binlog format. |
| 199 |
|
| 200 |
--let $binlog_format_backup= `SELECT @@GLOBAL.binlog_format` |
| 201 |
|
| 202 |
SET SESSION binlog_format= STATEMENT; |
| 203 |
|
| 204 |
--error ER_BEFORE_DML_VALIDATION_ERROR |
| 205 |
INSERT INTO tn VALUES ('a'); |
| 206 |
|
| 207 |
--eval SET SESSION binlog_format= "$binlog_format_backup" |
| 208 |
|
| 209 |
#Test the value of transaction_write_set_extraction cannot be changed |
| 210 |
|
| 211 |
--let $transaction_write_set_extraction_backup= `SELECT @@GLOBAL.transaction_write_set_extraction` |
| 212 |
|
| 213 |
--error ER_GROUP_REPLICATION_RUNNING |
| 214 |
SET SESSION transaction_write_set_extraction=OFF; |
| 215 |
|
| 216 |
INSERT INTO tn VALUES ('hash'); |
| 217 |
|
| 218 |
--echo # |
| 219 |
--echo # Now, lets repeat all the tests with group replication stopped. |
| 220 |
--echo # They all must succeed or fail with other errors that not the |
| 221 |
--echo # ones from validation. |
| 222 |
--echo # |
| 223 |
--echo # It will be a single table simpler test just to assess that the |
| 224 |
--echo # hook is not longer active. |
| 225 |
--echo # |
| 226 |
--source include/stop_group_replication.inc |
| 227 |
|
| 228 |
#Wrong table without Primary Key |
| 229 |
CREATE TABLE t1 (c1 char(50)) ENGINE=InnoDB; |
| 230 |
|
| 231 |
#Test the UPDATE instruction |
| 232 |
--eval UPDATE t1 SET c1 = 'a' |
| 233 |
|
| 234 |
#Test the INSERT instruction |
| 235 |
--eval INSERT INTO t1 VALUES('a') |
| 236 |
|
| 237 |
#Test the INSERT...SELECT instruction |
| 238 |
--eval INSERT INTO t1 (c1) SELECT tn.cn FROM tn |
| 239 |
|
| 240 |
#Test the DELETE instruction |
| 241 |
--eval DELETE FROM t1 |
| 242 |
|
| 243 |
#Test the load instruction |
| 244 |
--eval LOAD DATA INFILE '../../std_data/words2.dat' INTO TABLE t1 |
| 245 |
|
| 246 |
#Test the REPLACE instruction |
| 247 |
--eval REPLACE INTO t1(c1) VALUES('a') |
| 248 |
|
| 249 |
#Test the REPLACE...SELECT instruction |
| 250 |
--eval REPLACE INTO t1 (c1) SELECT tn.cn FROM tn |
| 251 |
|
| 252 |
#Test the DELETE_MULTI instruction |
| 253 |
--eval DELETE t1, tn FROM t1, tn |
| 254 |
|
| 255 |
#Test the UPDATE_MULTI instruction |
| 256 |
--eval UPDATE t1, tn SET c1 = 'a' |
| 257 |
|
| 258 |
#clean up |
| 259 |
DROP TABLE t1, tn; |
| 260 |
|
| 261 |
call mtr.add_suppression("Table.*is not transactional. This is not compatible with Group Replication"); |
| 262 |
call mtr.add_suppression("Table.*does not have any PRIMARY KEY. This is not compatible with Group Replication."); |
| 263 |
call mtr.add_suppression("Table.*has a foreign key with 'CASCADE', 'SET NULL' or 'SET DEFAULT' clause. This is not compatible with Group Replication."); |
| 264 |
call mtr.add_suppression("Binlog format should be ROW for Group Replication"); |
| 265 |
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); |
| 266 |
|
| 267 |
--source include/gr_clear_configuration.inc |