Debian Codegraph @

mysql-test/suite/rpl/r/rpl_generated_invisible_primary_key.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format');
7 SET @saved_session_sql_generate_invisible_primary_key = @@session.sql_generate_invisible_primary_key;
8 SET SESSION sql_generate_invisible_primary_key = ON;
9 # Case 1: Test case to verify replication of a table with generated
10 # invisible primary key.
11 CREATE TABLE t1(f1 INT, f2 INT);
12 INSERT INTO t1 VALUES (1, 10), (2, 20);
13 # Check that t1 exists and has generated invisible primary key definition in
14 # source and replica.
15 include/rpl_diff.inc
16 # Check that t1 has same column values in source and replica.
17 include/rpl_diff.inc
18 [connection master]
19 DROP TABLE t1;
20 include/sync_slave_sql_with_master.inc
21 # Case 2: Test case to verify replication of a table with generated
22 # invisible primary key when susyem variable
23 # show_gipk_in_create_table_and_information_schema = OFF.
24 [connection master]
25 SET @saved_session_show_gipk_in_create_table_and_information_schema =
26 @@session.show_gipk_in_create_table_and_information_schema;
27 SET SESSION show_gipk_in_create_table_and_information_schema = OFF;
28 CREATE TABLE t1(f1 INT, f2 INT);
29 INSERT INTO t1 VALUES (1, 10), (2, 20);
30 # Check that t1 exists and has generated invisible primary key definition in
31 # source and replica.
32 include/rpl_diff.inc
33 # Check that t1 has same column values in source and replica.
34 include/rpl_diff.inc
35 [connection master]
36 DROP TABLE t1;
37 SET SESSION show_gipk_in_create_table_and_information_schema =
38 @saved_session_show_gipk_in_create_table_and_information_schema;
39 include/sync_slave_sql_with_master.inc
40 # Case 3: Test case to verify replication of a table created with
41 # generated invisible primary key using stored procedure and
42 # prepared statement.
43 [connection master]
44 CREATE PROCEDURE p1() BEGIN
45 CREATE TABLE t1(f1 INT, f2 INT);
46 INSERT INTO t1 VALUES(1, 3);
47 end
48 $
49 PREPARE stmt1 FROM 'CREATE TABLE t2(f1 INT, f2 INT)';
50 CALL p1();
51 EXECUTE stmt1;
52 INSERT INTO t2 VALUES(8, 4);
53 # Check that t1 exists and has generated invisible primary key definition
54 # in source and replica.
55 include/rpl_diff.inc
56 # Check that t1 hase same column values in source and replica.
57 include/rpl_diff.inc
58 # Check that t2 exists and has generated invisible primary key definition
59 # in source and replica.
60 include/rpl_diff.inc
61 # Check that t2 hase same column values in source and replica.
62 include/rpl_diff.inc
63 [connection master]
64 DROP TABLE t1, t2;
65 include/sync_slave_sql_with_master.inc
66 [connection master]
67 CALL p1();
68 EXECUTE stmt1;
69 INSERT INTO t2 VALUES(8, 4);
70 # Check that t1 exists and has generated invisible primary key definition
71 # in source and replica.
72 include/rpl_diff.inc
73 # Check that t1 hase same column values in source and replica.
74 include/rpl_diff.inc
75 # Check that t2 exists and has generated invisible primary key definition
76 # in source and replica.
77 include/rpl_diff.inc
78 # Check that t2 hase same column values in source and replica.
79 include/rpl_diff.inc
80 [connection master]
81 DROP TABLE t1, t2;
82 DROP PROCEDURE p1;
83 DROP PREPARE stmt1;
84 include/sync_slave_sql_with_master.inc
85 # Case 4: Verify replication of CREATE TABLE ... SELECT with explicitly
86 # specified primary key similar to generated invisible primary key.
87 # Table t2 is created as primary key is explicitly specified.
88 [connection master]
89 CREATE TABLE t1(f1 INT, f2 INT);
90 INSERT INTO t1 VALUES (1, 10), (2, 20);
91 CREATE TABLE t2 (my_row_id BIGINT UNSIGNED INVISIBLE AUTO_INCREMENT PRIMARY KEY)
92 AS SELECT t1.my_row_id, t1.* FROM t1;
93 # check that t2 exists in source and replica.
94 include/rpl_diff.inc
95 # Case 5: Verify replication of CREATE TABLE ... SELECT using temporary source
96 # table with GIPK.
97 CREATE TEMPORARY TABLE t3 (f1 INT);
98 INSERT INTO t3 VALUES (1),(3),(7),(8),(4);
99 CREATE TABLE t4(my_row_id BIGINT UNSIGNED INVISIBLE AUTO_INCREMENT PRIMARY KEY)
100 AS SELECT my_row_id, f1 FROM t3;
101 # check that t4 exists in source and replica.
102 include/sync_slave_sql_with_master.inc
103 include/rpl_diff.inc
104 include/rpl_diff.inc
105 [connection master]
106 DROP TABLE t1, t2, t3, t4;
107 SET SESSION sql_generate_invisible_primary_key =
108 @saved_session_sql_generate_invisible_primary_key;
109 include/rpl_end.inc