| 1 |
# ==== Purpose ==== |
| 2 |
# |
| 3 |
# The purpose of this script is to test how `Q_SQL_REQUIRE_PRIMARY_KEY` is being |
| 4 |
# logged within a `Query_log` event. |
| 5 |
# |
| 6 |
# ==== Requirements ==== |
| 7 |
# |
| 8 |
# R1. `Q_SQL_REQUIRE_PRIMARY_KEY` should only be logged for `Query_log` events |
| 9 |
# holding `CREATE TABLE` or `ALTER TABLE` statements. |
| 10 |
# |
| 11 |
# ==== Implementation ==== |
| 12 |
# |
| 13 |
# TC1. Check `CREATE TABLE` has `Q_SQL_REQUIRE_PRIMARY_KEY` flag set |
| 14 |
# ------------------------------------------------------------------ |
| 15 |
# 1) Set `sql_require_primary_key` to 0. |
| 16 |
# 2) Create a table with no primary key and check the flag is set to 0. |
| 17 |
# 3) Set `sql_require_primary_key` to 1. |
| 18 |
# 4) Create a table with a primary key and check the flag is set to 1. |
| 19 |
# |
| 20 |
# TC2. Check DML `INSERT`, `UPDATE` and `DELETE` do not have the flag set |
| 21 |
# ----------------------------------------------------------------------- |
| 22 |
# 1) Execute `INSERT` for both tables and check that the flag is not set for |
| 23 |
# both statements. |
| 24 |
# 2) Execute `UPDATE` for both tables and check that the flag is not set for |
| 25 |
# both statements. |
| 26 |
# 3) Execute `DELETE` for both tables and check that the flag is not set for |
| 27 |
# both statements. |
| 28 |
# |
| 29 |
# TC3. Check `ALTER TABLE` has `Q_SQL_REQUIRE_PRIMARY_KEY` flag set |
| 30 |
# ------------------------------------------------------------------ |
| 31 |
# 1) Set `sql_require_primary_key` to 0. |
| 32 |
# 2) Alter the table with no primary key and check the flag is set to 0. |
| 33 |
# 3) Set `sql_require_primary_key` to 1. |
| 34 |
# 4) Alter the table with a primary key and check the flag is set to 1. |
| 35 |
# |
| 36 |
# TC4. Check `DROP TABLE` do not has `Q_SQL_REQUIRE_PRIMARY_KEY` flag set |
| 37 |
# ----------------------------------------------------------------------- |
| 38 |
# 1) Set `sql_require_primary_key` to 0. |
| 39 |
# 2) Drop the table with no primary key and check that the flag is not set. |
| 40 |
# 3) Set `sql_require_primary_key` to 1. |
| 41 |
# 4) Drop the table with a primary key and check that the flag is not set. |
| 42 |
# |
| 43 |
# ==== References ==== |
| 44 |
# |
| 45 |
# BUG#28524803 'SET @@SESSION.SQL_REQUIRE_PRIMARY_KEY=1' IS ADDED FOR DMLS IN |
| 46 |
# MYSQLBINLOG O/P |
| 47 |
# |
| 48 |
|
| 49 |
# Asserts the expected count of `SET @@SESSION.sql_require_primary_key` for a |
| 50 |
# given sql_expression |
| 51 |
# |
| 52 |
|
| 53 |
--source include/have_log_bin.inc |
| 54 |
|
| 55 |
--write_file $MYSQL_TMP_DIR/assert_count_flag_occurences.inc END_OF_PROCEDURE |
| 56 |
if ($target_sql_expression == '') |
| 57 |
{ |
| 58 |
--die !!!ERROR IN TEST: you must set $target_sql_expression |
| 59 |
} |
| 60 |
if ($expected_statements_match_count == '') |
| 61 |
{ |
| 62 |
--die !!!ERROR IN TEST: you must set $expected_statements_match_count |
| 63 |
} |
| 64 |
if ($assert_success_text == '') |
| 65 |
{ |
| 66 |
--die !!!ERROR IN TEST: you must set $assert_success_text |
| 67 |
} |
| 68 |
|
| 69 |
--let $before_include = $include_silent |
| 70 |
--let $include_silent = 1 |
| 71 |
--source include/save_binlog_position.inc |
| 72 |
--let $include_silent = $before_include |
| 73 |
--let $assert_binlog_file = `SELECT CONCAT('$MYSQLTEST_VARDIR/tmp/mysqlbinlog_set_require_primary_key_', UUID(), '.binlog')` |
| 74 |
|
| 75 |
--eval $target_sql_expression |
| 76 |
|
| 77 |
--exec $MYSQL_BINLOG --start-position=$binlog_position --force-if-open -vv $binlog_fullpath > $assert_binlog_file |
| 78 |
|
| 79 |
--let $assert_select = sql_require_primary_key=$require_primary_key_target_value[^;]*; |
| 80 |
--let $assert_file = $assert_binlog_file |
| 81 |
--let $assert_text = $assert_success_text |
| 82 |
--let $assert_count = $expected_statements_match_count |
| 83 |
--source include/assert_grep.inc |
| 84 |
|
| 85 |
--remove_file $assert_binlog_file |
| 86 |
END_OF_PROCEDURE |
| 87 |
|
| 88 |
FLUSH BINARY LOGS; |
| 89 |
|
| 90 |
--echo # |
| 91 |
--echo # TC1. Check `CREATE TABLE` has `Q_SQL_REQUIRE_PRIMARY_KEY` flag set |
| 92 |
--echo # ------------------------------------------------------------------ |
| 93 |
|
| 94 |
# 1) Set `sql_require_primary_key` to 0. |
| 95 |
SET @@session.sql_require_primary_key=0; |
| 96 |
|
| 97 |
# 2) Create a table with no primary key and check the flag is set to 0. |
| 98 |
--let $target_sql_expression = CREATE TABLE tnopk (col1 INT, col2 INT) |
| 99 |
--let $require_primary_key_target_value = 0 |
| 100 |
--let $expected_statements_match_count = 1 |
| 101 |
--let $assert_success_text = 'CREATE TABLE' with no PK has 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set and with value '0' |
| 102 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 103 |
|
| 104 |
# 3) Set `sql_require_primary_key` to 1. |
| 105 |
SET @@session.sql_require_primary_key=1; |
| 106 |
|
| 107 |
# 4) Create a table with a primary key and check the flag is set to 1. |
| 108 |
--let $target_sql_expression = CREATE TABLE tpk (col1 INT PRIMARY KEY, col2 INT) |
| 109 |
--let $require_primary_key_target_value = 1 |
| 110 |
--let $expected_statements_match_count = 1 |
| 111 |
--let $assert_success_text = 'CREATE TABLE' with PK has 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set and with value '1' |
| 112 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 113 |
|
| 114 |
--echo # |
| 115 |
--echo # TC2. Check DML `INSERT`, `UPDATE` and `DELETE` do not have the flag set |
| 116 |
--echo # ----------------------------------------------------------------------- |
| 117 |
|
| 118 |
# 1) Execute `INSERT` for both tables and check that the flag is not set for |
| 119 |
# both statements. |
| 120 |
--let $target_sql_expression = INSERT INTO tnopk VALUES (1, 0), (2, 0); INSERT INTO tpk VALUES (1, 0), (2, 0) |
| 121 |
--let $expected_statements_match_count = 0 |
| 122 |
--let $assert_success_text = 'INSERT' statements do not have 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set |
| 123 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 124 |
|
| 125 |
# 2) Execute `UPDATE` for both tables and check that the flag is not set for |
| 126 |
# both statements. |
| 127 |
--let $target_sql_expression = UPDATE tnopk SET col2 = 1 WHERE col1 = 1; UPDATE tpk SET col2 = 1 WHERE col1 = 1 |
| 128 |
--let $expected_statements_match_count = 0 |
| 129 |
--let $assert_success_text = 'UPDATE' statements do not have 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set |
| 130 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 131 |
|
| 132 |
# 3) Execute `DELETE` for both tables and check that the flag is not set for |
| 133 |
# both statements. |
| 134 |
--let $target_sql_expression = DELETE FROM tnopk where col1 = 2; DELETE FROM tpk where col1 = 2 |
| 135 |
--let $expected_statements_match_count = 0 |
| 136 |
--let $assert_success_text = 'DELETE' statements do not have 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set |
| 137 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 138 |
|
| 139 |
--echo # |
| 140 |
--echo # TC3. Check `ALTER TABLE` has `Q_SQL_REQUIRE_PRIMARY_KEY` flag set |
| 141 |
--echo # ------------------------------------------------------------------ |
| 142 |
|
| 143 |
# 1) Set `sql_require_primary_key` to 0. |
| 144 |
SET @@session.sql_require_primary_key=0; |
| 145 |
|
| 146 |
# 2) Alter the table with no primary key and check the flag is set to 0. |
| 147 |
--let $target_sql_expression = ALTER TABLE tnopk ADD COLUMN (col3 INT) |
| 148 |
--let $require_primary_key_target_value = 0 |
| 149 |
--let $expected_statements_match_count = 1 |
| 150 |
--let $assert_success_text = 'ALTER TABLE' in table with no PK has 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set and with value '0' |
| 151 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 152 |
|
| 153 |
# 3) Set `sql_require_primary_key` to 1. |
| 154 |
SET @@session.sql_require_primary_key=1; |
| 155 |
|
| 156 |
# 4) Alter the table with a primary key and check the flag is set to 1. |
| 157 |
--let $target_sql_expression = ALTER TABLE tpk ADD COLUMN (col3 INT) |
| 158 |
--let $require_primary_key_target_value = 1 |
| 159 |
--let $expected_statements_match_count = 1 |
| 160 |
--let $assert_success_text = 'ALTER TABLE' in table with PK has 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set and with value '1' |
| 161 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 162 |
|
| 163 |
--echo # |
| 164 |
--echo # TC4. Check `DROP TABLE` do not has `Q_SQL_REQUIRE_PRIMARY_KEY` flag set |
| 165 |
--echo # ----------------------------------------------------------------------- |
| 166 |
|
| 167 |
# 1) Set `sql_require_primary_key` to 0. |
| 168 |
SET @@session.sql_require_primary_key=0; |
| 169 |
|
| 170 |
# 2) Drop the table with no primary key and check that the flag is not set. |
| 171 |
--let $target_sql_expression = DROP TABLE tnopk |
| 172 |
--let $expected_statements_match_count = 0 |
| 173 |
--let $assert_success_text = 'DROP TABLE' statements, of table with no PK, do not have 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set |
| 174 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 175 |
|
| 176 |
# 3) Set `sql_require_primary_key` to 1. |
| 177 |
SET @@session.sql_require_primary_key=1; |
| 178 |
|
| 179 |
# 4) Drop the table with a primary key and check that the flag is not set. |
| 180 |
--let $target_sql_expression = DROP TABLE tpk |
| 181 |
--let $expected_statements_match_count = 0 |
| 182 |
--let $assert_success_text = 'DROP TABLE' statements, of table with PK, do not have 'Q_SQL_REQUIRE_PRIMARY_KEY' flag set |
| 183 |
--source $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |
| 184 |
|
| 185 |
# Clean up |
| 186 |
--remove_file $MYSQL_TMP_DIR/assert_count_flag_occurences.inc |