#################################################################################### # WL#13784 - Create auto_increment PK on table creation when no PK is specified # # # # When all the following hold: # # * sql_generate_invisible_primary_key=ON, and # # * binlog_format=ROW or MIXED, and # # * user executes CREATE TABLE ... SELECT, and # # * the source table does not have a primary key, # # then the rewritten CREATE TABLE query used for the binary log (which does not # # have the SELECT part) shall include the generated primary key definition # # explicitly. show_gipk_in_create_table_and_information_schema should not affect # # query re-writing. # #################################################################################### --source include/have_binlog_format_mixed_or_row.inc --source include/master-slave.inc SET @saved_session_sql_generate_invisible_primary_key = @@session.sql_generate_invisible_primary_key; SET SESSION sql_generate_invisible_primary_key = ON; CREATE TABLE t1(f1 INT, f2 INT); INSERT INTO t1 VALUES (1, 10); SET @saved_session_show_gipk_in_create_table_and_information_schema = @@session.show_gipk_in_create_table_and_information_schema; --echo # CREATE TABLE ... SELECT with show_gipk_in_create_table_and_information_schema = ON. SET show_gipk_in_create_table_and_information_schema = ON; CREATE TABLE t2 AS SELECT * FROM t1; --echo # CREATE TABLE ... SELECT with show_gipk_in_create_table_and_information_schema = OFF. SET show_gipk_in_create_table_and_information_schema = OFF; CREATE TABLE t3 AS SELECT * FROM t1; SET SESSION show_gipk_in_create_table_and_information_schema = @saved_session_show_gipk_in_create_table_and_information_schema; --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 if 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 --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 if t2 has same column values in source and replica. --let $rpl_diff_statement= SELECT my_row_id, t2.* FROM t2; --source include/rpl_diff.inc --echo # Check that t3 exists and has generated invisible primary key definition --echo # in source and replica. show_gipk_in_create_table_and_information_schema=OFF --echo # should not effect generating query string to binlog. --let $rpl_diff_statement= SHOW CREATE TABLE t3; --source include/rpl_diff.inc --echo # Check if t3 has same column values in source and replica. --let $rpl_diff_statement= SELECT my_row_id, t3.* FROM t3 --source include/rpl_diff.inc --source include/rpl_connection_master.inc DROP TABLE t1, t2, t3; SET SESSION sql_generate_invisible_primary_key = @saved_session_sql_generate_invisible_primary_key; --source include/rpl_end.inc