# === Purpose ===
#
# This test validates that when enabled in a replica, the option
#  sql_generate_invisible_primary_key
# does not affect the tables being created by the replication threads.
# If a table is created in the source with no primary key it shall remain with
# no primary key in the replica
#
# To get this behavior on the replica the user must use
#  CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE;
# If a table is created in the source with no primary key it shall have a a
# primary key in the replica
# ==== Requirements ====
#
# R1. sql_generate_invisible_primary_key does not affect replication threads
# R2. REQUIRE_TABLE_PRIMARY_KEY_CHECK=GENERATE makes replication applier threads
# generate primary keys for replicated CREATE TABLE statements that don't
# contain one
#
# === Implementation ====
#
# 1. In the replica enable, in the session and globally:
#    - sql_generate_invisible_primary_key
# 2. Create a table without a primary key on the source
#    The table should not have a primary key when replicated
# 3. Create a table without a primary key on the replica
#    The table should have a generated primary key
# 4. Enable the creation of GIPKs in the replica by setting
#    REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE
# 5. Create a table without a primary key on the source
#    The table should have a generated primary key on the replica
#    This still applies if sql_generate_invisible_primary_key = OFF
# 6. Test error scenarios where source changes conflict with the replica schema
#    Case 1: A column is renamed to have the same name as the replica GIPK
#    Case 2: A PK is added in the source for a table that as a GIPK in the replica
#    Case 3: A table is created that already has a my_row_id column
# 7. Cleanup
#
# === References ===
#
# Bug#34092605: sql_generate_invisible_primary_key makes replica diverge
# WL#14639: Row-based replication between tables that differ in the existence of a generated invisible primary key
# WL#15419: Make the replica_generate_invisible_primary_key option settable per channel
#

--echo
--echo ##############################################################
--echo # 1. In the replica enable, in the session and globally:
--echo #    - sql_generate_invisible_primary_key

--source include/not_have_privilege_checks_user.inc
--let $rpl_skip_start_slave = 1
--source include/master-slave.inc

--source include/rpl_connection_slave.inc

--let $sysvars_to_save = [ "GLOBAL.sql_generate_invisible_primary_key" ]
--source include/save_sysvars.inc

SET GLOBAL sql_generate_invisible_primary_key = ON;
SET SESSION sql_generate_invisible_primary_key = ON;

--echo
--echo ##############################################################
--echo # 2. Create a table without a primary key on the source
--echo #    The table should not have a primary key when replicated

--source include/rpl_connection_master.inc

CREATE TABLE t1(a int); # No primary key

--source include/rpl_connection_slave.inc

--source include/start_slave.inc

--source include/rpl_connection_master.inc

--source include/sync_slave_sql_with_master.inc

--let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t1';`
--let $assert_text= The table only contains a column
--let $assert_cond= $column_count = 1
--source include/assert.inc

--let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t1' and column_name='my_row_id';`
--let $assert_text= The table does not contain a generated invisilble primary key
--let $assert_cond= $pk_count = 0
--source include/assert.inc

--echo
--echo ##############################################################
--echo # 3. Create a table without a primary key on the replica
--echo #    The table should have a generated primary key

CREATE TABLE t2(a int); # No primary key

SHOW CREATE TABLE t2;

--let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t2';`
--let $assert_text= The table contains 2 columns
--let $assert_cond= $column_count = 2
--source include/assert.inc

--let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t2' and column_name='my_row_id';`
--let $assert_text= The table contains a generated invisilble primary key
--let $assert_cond= $pk_count = 1
--source include/assert.inc


--echo
--echo ##############################################################
--echo # 4. Enable the creation of GIPKs in the replica by setting
--echo #    REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE

# Store the default value
--let $_pk_key_check_value = `SELECT require_table_primary_key_check FROM performance_schema.replication_applier_configuration WHERE channel_name=""`

 --source include/stop_slave_sql.inc

CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE;

 --source include/start_slave_sql.inc

--echo
--echo ##############################################################
--echo # 5. Create a table without a primary key on the source
--echo #    The table should have a generated primary key on the replica
--echo #    This still applies if sql_generate_invisible_primary_key = OFF

--source include/rpl_connection_master.inc

CREATE TABLE t3(a int); # No primary key

--source include/sync_slave_sql_with_master.inc

--let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t3';`
--let $assert_text= The table only contains an extra column
--let $assert_cond= $column_count = 2
--source include/assert.inc

--let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t3' and column_name='my_row_id';`
--let $assert_text= The table contains a generated invisilble primary key
--let $assert_cond= $pk_count = 1
--source include/assert.inc

# Disable sql_generate_invisible_primary_key

SET GLOBAL sql_generate_invisible_primary_key = OFF;

--source include/restart_slave_sql.inc

--source include/rpl_connection_master.inc

CREATE TABLE t4(a int); # No primary key

--source include/sync_slave_sql_with_master.inc

--let $column_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t4';`
--let $assert_text= The table only contains an extra column
--let $assert_cond= $column_count = 2
--source include/assert.inc

--let $pk_count= `SELECT COUNT(*) FROM information_schema.columns WHERE table_name='t4' and column_name='my_row_id';`
--let $assert_text= The table contains a generated invisilble primary key
--let $assert_cond= $pk_count = 1
--source include/assert.inc

--echo
--echo ##############################################################
--echo # 6. Test error scenarios where source changes conflict with the replica schema
--echo #    Case 1: A column is renamed to have the same name as the replica GIPK
--echo #    Case 2: A PK is added in the source for a table that as a GIPK in the replica

# Case 1: A column is renamed to have the same name as the replica GIPK

--source include/rpl_connection_master.inc

ALTER TABLE t4 RENAME COLUMN a TO my_row_id;

--source include/rpl_connection_slave.inc

--let $slave_sql_errno= convert_error(ER_DUP_FIELDNAME)
--source include/wait_for_slave_sql_error.inc

# Case 2: A PK is added in the source for a table that as a GIPK in the replica

--source include/rpl_connection_master.inc

RESET MASTER;

CREATE TABLE t5(a int); # No primary key

ALTER TABLE t5 ADD PRIMARY KEY `k` (a);

--source include/rpl_connection_slave.inc

--source include/stop_slave.inc
RESET MASTER;
--source include/rpl_reset_slave.inc
START REPLICA;

--let $slave_sql_errno= convert_error(ER_MULTIPLE_PRI_KEY)
--source include/wait_for_slave_sql_error.inc

# Case 3: A table is created that already has a my_row_id column

--source include/rpl_connection_master.inc

RESET MASTER;

CREATE TABLE t6 (my_row_id TEXT);

--source include/rpl_connection_slave.inc

--source include/stop_slave.inc
RESET MASTER;
--source include/rpl_reset_slave.inc
START REPLICA;

--let $slave_sql_errno= convert_error(ER_GIPK_COLUMN_EXISTS)
--source include/wait_for_slave_sql_error.inc

--echo
--echo ##############################################################
--echo # 7. Cleanup

--source include/rpl_connection_master.inc

RESET MASTER;

--source include/rpl_connection_slave.inc

--source include/stop_slave.inc

RESET MASTER;

--source include/rpl_reset_slave.inc

--replace_result $_pk_key_check_value PRIMARY_KEY_CHECK_VALUE
--eval CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = $_pk_key_check_value

--source include/start_slave.inc

--source include/restore_sysvars.inc

DROP TABLE t2;

let $messages =
Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Duplicate column name 'my_row_id''
Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Multiple primary key defined' on query.
Replica SQL for channel .*: Worker .* failed executing transaction .* at .*, end_log_pos .* Error 'Failed to generate invisible primary key. Column 'my_row_id' already exists.'
Replica SQL for channel .*: ... The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state.
Replica SQL for channel '': Error 'Duplicate column name 'my_row_id'' on query.
Replica: Duplicate column name 'my_row_id' Error_code:
Replica SQL for channel '': Error 'Multiple primary key defined' on query.
Replica: Multiple primary key defined Error_code:
Replica SQL for channel '': Error 'Failed to generate invisible primary key. Column 'my_row_id' already exists.'
Replica: Failed to generate invisible primary key. Column 'my_row_id' already exists.
;
--let $suppress_on_current_connection = 1
--source include/suppress_messages.inc

--source include/rpl_connection_master.inc

DROP TABLE t1;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
--source include/disable_binlog.inc
DROP TABLE t6;
--source include/restore_binlog.inc

--source include/rpl_end.inc