Debian Codegraph @

mysql-test/suite/rpl/t/rpl_row_generated_invisible_primary_key.test

raw
1 ####################################################################################
2 # WL#13784 - Create auto_increment PK on table creation when no PK is specified #
3 # #
4 # When all the following hold: #
5 # * sql_generate_invisible_primary_key=ON, and #
6 # * binlog_format=ROW or MIXED, and #
7 # * user executes CREATE TABLE ... SELECT, and #
8 # * the source table does not have a primary key, #
9 # then the rewritten CREATE TABLE query used for the binary log (which does not #
10 # have the SELECT part) shall include the generated primary key definition #
11 # explicitly. show_gipk_in_create_table_and_information_schema should not affect #
12 # query re-writing. #
13 ####################################################################################
14
15 --source include/have_binlog_format_mixed_or_row.inc
16 --source include/master-slave.inc
17
18 SET @saved_session_sql_generate_invisible_primary_key = @@session.sql_generate_invisible_primary_key;
19 SET SESSION sql_generate_invisible_primary_key = ON;
20
21 CREATE TABLE t1(f1 INT, f2 INT);
22 INSERT INTO t1 VALUES (1, 10);
23
24 SET @saved_session_show_gipk_in_create_table_and_information_schema =
25 @@session.show_gipk_in_create_table_and_information_schema;
26 --echo # CREATE TABLE ... SELECT with show_gipk_in_create_table_and_information_schema = ON.
27 SET show_gipk_in_create_table_and_information_schema = ON;
28 CREATE TABLE t2 AS SELECT * FROM t1;
29
30 --echo # CREATE TABLE ... SELECT with show_gipk_in_create_table_and_information_schema = OFF.
31 SET show_gipk_in_create_table_and_information_schema = OFF;
32 CREATE TABLE t3 AS SELECT * FROM t1;
33 SET SESSION show_gipk_in_create_table_and_information_schema =
34 @saved_session_show_gipk_in_create_table_and_information_schema;
35
36 --echo # Check that t1 exists and has generated invisible primary key definition
37 --echo # in source and replica.
38 --let $rpl_diff_statement= SHOW CREATE TABLE t1;
39 --source include/rpl_diff.inc
40 --echo # Check if t1 has same column values in source and replica.
41 --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1
42 --source include/rpl_diff.inc
43
44 --echo # Check that t2 exists and has generated invisible primary key definition
45 --echo # in source and replica.
46 --let $rpl_diff_statement= SHOW CREATE TABLE t2;
47 --source include/rpl_diff.inc
48 --echo # Check if t2 has same column values in source and replica.
49 --let $rpl_diff_statement= SELECT my_row_id, t2.* FROM t2;
50 --source include/rpl_diff.inc
51
52 --echo # Check that t3 exists and has generated invisible primary key definition
53 --echo # in source and replica. show_gipk_in_create_table_and_information_schema=OFF
54 --echo # should not effect generating query string to binlog.
55 --let $rpl_diff_statement= SHOW CREATE TABLE t3;
56 --source include/rpl_diff.inc
57 --echo # Check if t3 has same column values in source and replica.
58 --let $rpl_diff_statement= SELECT my_row_id, t3.* FROM t3
59 --source include/rpl_diff.inc
60
61 --source include/rpl_connection_master.inc
62 DROP TABLE t1, t2, t3;
63
64 SET SESSION sql_generate_invisible_primary_key =
65 @saved_session_sql_generate_invisible_primary_key;
66 --source include/rpl_end.inc