Debian Codegraph @

mysql-test/suite/binlog/t/binlog_set_require_primary_key_only_on_ddl.test

raw
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