| 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 |