#################################################################################### # WL#13784 - Create auto_increment PK on table creation when no PK is specified # # # # Test cases in this file verifies feature requirements, # # a) In GIPK mode if primary key is generated for a non-PK table then # # query string CREATE TABLE must be re-written to include generated # # primary key definition in the binlog event to replicate table # # correctly. # # # # b) DML operation on table for which primary key is generated at source only, # # should be replicated correctly. # # # # Case 1: Verify replication of a table with generated invisible primary key. # # Source: @sql_generate_invisible_primary_key = ON. # # Create table t1 without explicit primary key and Insert rows. # # Replica: Verify that table t1 has same table structure and column values.# # # # # # Case 2: Verify replication of a table with generated invisible primary key when # # system variable show_gipk_in_create_table_and_information_schema = OFF. # # Source: @sql_generate_invisible_primary_key = ON. # # @show_gipk_in_create_table_and_information_schema = OFF # # Create table t1 without explicit primary key and Insert rows. # # Replica: Verify that table t1 has same table structure and column values.# # show_gipk_in_create_table_and_information_schema should not # # query re-writing to log an event. # # # # # # Case 3: Verify replication of a tables created using stored procedure and # # prepared statement. # # Source: @sql_generate_invisible_primary_key = ON. # # Create table t1 without explicit primary key using stored # # procedure and insert a row. # # Create table t2 without explicit primary key using prepared # # statement and insert a row. # # Verify that table t1 and t2 has generated invisible primary key and same # # column values in source and replica. # # Source: DROP TABLE t1 and t2. # # Create table t1 without explicit primary key using stored # # procedure and insert a row. # # Create table t2 without explicit primary key using prepared # # statement and insert a row. # # Verify that table t1 and t2 has generated invisible primary key and same # # column values in source and replica. # # # # # # Case 4: Verify replication of CREATE TABLE ... SELECT with an explicitly # # specified primary key similar to generated invisible primary key. # # Source: @sql_generate_invisible_primary_key = ON. # # Create table t2 using CREATE TABLE ... SELECT with explicit # # primary key having same attributes as generated invisible primary# # key. # # Replica: Verify that table t2 is created. # # # # # # Case 5: Verify replication of CREATE TABLE ... SELECT using temporary source # # table with GIPK. # # Source: @sql_generate_invisible_primary_key=ON. # # Create temporary table t3 without explicit primary key. # # Create table t4 using CREATE TABLE ... SELECT my_row_id, t3.* # # from t3 with explicit primary key without auto_increment # # attributes # # Replica: Verify that table t4 is replicated and has same column values # # as table in source. # #################################################################################### --source include/not_have_privilege_checks_user.inc --source include/master-slave.inc CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format'); SET @saved_session_sql_generate_invisible_primary_key = @@session.sql_generate_invisible_primary_key; SET SESSION sql_generate_invisible_primary_key = ON; --echo # Case 1: Test case to verify replication of a table with generated --echo # invisible primary key. CREATE TABLE t1(f1 INT, f2 INT); INSERT INTO t1 VALUES (1, 10), (2, 20); --echo # Check that t1 exists and has generated invisible primary key definition in --echo # source and replica. --let $rpl_diff_statement= SHOW CREATE TABLE t1; --source include/rpl_diff.inc --echo # Check that t1 has same column values in source and replica. --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1 --source include/rpl_diff.inc --source include/rpl_connection_master.inc DROP TABLE t1; --source include/sync_slave_sql_with_master.inc --echo # Case 2: Test case to verify replication of a table with generated --echo # invisible primary key when susyem variable --echo # show_gipk_in_create_table_and_information_schema = OFF. --source include/rpl_connection_master.inc SET @saved_session_show_gipk_in_create_table_and_information_schema = @@session.show_gipk_in_create_table_and_information_schema; SET SESSION show_gipk_in_create_table_and_information_schema = OFF; CREATE TABLE t1(f1 INT, f2 INT); INSERT INTO t1 VALUES (1, 10), (2, 20); --echo # Check that t1 exists and has generated invisible primary key definition in --echo # source and replica. --let $rpl_diff_statement= SHOW CREATE TABLE t1; --source include/rpl_diff.inc --echo # Check that t1 has same column values in source and replica. --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1 --source include/rpl_diff.inc --source include/rpl_connection_master.inc DROP TABLE t1; SET SESSION show_gipk_in_create_table_and_information_schema = @saved_session_show_gipk_in_create_table_and_information_schema; --source include/sync_slave_sql_with_master.inc --echo # Case 3: Test case to verify replication of a table created with --echo # generated invisible primary key using stored procedure and --echo # prepared statement. --source include/rpl_connection_master.inc DELIMITER $; CREATE PROCEDURE p1() BEGIN CREATE TABLE t1(f1 INT, f2 INT); INSERT INTO t1 VALUES(1, 3); end $ DELIMITER ;$ PREPARE stmt1 FROM 'CREATE TABLE t2(f1 INT, f2 INT)'; CALL p1(); EXECUTE stmt1; INSERT INTO t2 VALUES(8, 4); --echo # Check that t1 exists and has generated invisible primary key definition --echo # in source and replica. --let $rpl_diff_statement= SHOW CREATE TABLE t1; --source include/rpl_diff.inc --echo # Check that t1 hase same column values in source and replica. --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1 --source include/rpl_diff.inc --echo # Check that t2 exists and has generated invisible primary key definition --echo # in source and replica. --let $rpl_diff_statement= SHOW CREATE TABLE t2; --source include/rpl_diff.inc --echo # Check that t2 hase same column values in source and replica. --let $rpl_diff_statement= SELECT my_row_id, t2.* FROM t2 --source include/rpl_diff.inc --source include/rpl_connection_master.inc DROP TABLE t1, t2; --source include/sync_slave_sql_with_master.inc --source include/rpl_connection_master.inc CALL p1(); EXECUTE stmt1; INSERT INTO t2 VALUES(8, 4); --echo # Check that t1 exists and has generated invisible primary key definition --echo # in source and replica. --let $rpl_diff_statement= SHOW CREATE TABLE t1; --source include/rpl_diff.inc --echo # Check that t1 hase same column values in source and replica. --let $rpl_diff_statement= SELECT my_row_id, t1.* FROM t1 --source include/rpl_diff.inc --echo # Check that t2 exists and has generated invisible primary key definition --echo # in source and replica. --let $rpl_diff_statement= SHOW CREATE TABLE t2; --source include/rpl_diff.inc --echo # Check that t2 hase same column values in source and replica. --let $rpl_diff_statement= SELECT my_row_id, t2.* FROM t2 --source include/rpl_diff.inc --source include/rpl_connection_master.inc DROP TABLE t1, t2; DROP PROCEDURE p1; DROP PREPARE stmt1; --source include/sync_slave_sql_with_master.inc --echo # Case 4: Verify replication of CREATE TABLE ... SELECT with explicitly --echo # specified primary key similar to generated invisible primary key. --echo # Table t2 is created as primary key is explicitly specified. --source include/rpl_connection_master.inc CREATE TABLE t1(f1 INT, f2 INT); INSERT INTO t1 VALUES (1, 10), (2, 20); # Following statement is marked as unsafe statement when BINLOG_FORMAT = STATEMENT. # So when BINLOG_FORMAT = STATEMENT, additional warnings are reported. But no warnings # reported in other BINLOG_FORMAT types. Hence disable printing warnings here so that # test works with all BINLOG_FORMAT types. --disable_warnings CREATE TABLE t2 (my_row_id BIGINT UNSIGNED INVISIBLE AUTO_INCREMENT PRIMARY KEY) AS SELECT t1.my_row_id, t1.* FROM t1; --enable_warnings --echo # check that t2 exists in source and replica. --let $rpl_diff_statement= SHOW CREATE TABLE t2; --source include/rpl_diff.inc --echo # Case 5: Verify replication of CREATE TABLE ... SELECT using temporary source --echo # table with GIPK. CREATE TEMPORARY TABLE t3 (f1 INT); INSERT INTO t3 VALUES (1),(3),(7),(8),(4); # Following statement is marked as unsafe statement when BINLOG_FORMAT = STATEMENT. # So when BINLOG_FORMAT = STATEMENT, additional warnings are reported. But no warnings # reported in other BINLOG_FORMAT types. Hence disable printing warnings here so that # test works with all BINLOG_FORMAT types. --disable_warnings CREATE TABLE t4(my_row_id BIGINT UNSIGNED INVISIBLE AUTO_INCREMENT PRIMARY KEY) AS SELECT my_row_id, f1 FROM t3; --enable_warnings --echo # check that t4 exists in source and replica. --source include/sync_slave_sql_with_master.inc --let $rpl_diff_statement= SHOW CREATE TABLE t4 --source include/rpl_diff.inc --let $rpl_diff_statement= SELECT * FROM t4; --source include/rpl_diff.inc --source include/rpl_connection_master.inc DROP TABLE t1, t2, t3, t4; SET SESSION sql_generate_invisible_primary_key = @saved_session_sql_generate_invisible_primary_key; --source include/rpl_end.inc