rpl_row_img_sanity.test

#
#  Description
#  ===========
#
#  This test case checks whether binlog files contain Before and After
#  image values as expected. Configuration is done using the
#  --binlog-row-image variable.
#
#  How it works
#  ============
#
#  The test case is implemented such that master and slave basically
#  hold the same table but sometimes differ in indexes, number of
#  columns and data types constraints (autoinc or NOT NULL). Then some
#  statements are executed and the output of mysqlbinlog is parsed for
#  the given event to check if the columns in the before and after
#  image match expectations according to the binlog-row-image value on
#  master and on slave.
#
#  See also WL#5096.
#
--source include/not_group_replication_plugin.inc
-- source include/have_binlog_format_row.inc
-- source include/no_valgrind_without_big.inc
-- source include/master-slave.inc

-- connection slave
call mtr.add_suppression("Slave: Can\'t find record in \'t\' Error_code: MY-001032");
call mtr.add_suppression("Slave SQL for channel '': .*Could not execute Update_rows event on table test.t; Can.t find record in .t.* Error_code: MY-001032");
call mtr.add_suppression("The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state");

-- connection master

## assertion: check that default value for binlog-row-image == 'FULL'
SHOW VARIABLES LIKE 'binlog_row_image';

## save original 
-- connection master
SET @old_binlog_row_image= @@binlog_row_image;
-- connection slave
SET @old_binlog_row_image= @@binlog_row_image;
-- connection master

-- echo #####################################################
-- echo # basic assertion that binlog_row_image='FULL' is the
-- echo # default 
-- echo #####################################################

-- connection master
-- let $row_img_set=master:FULL:N,slave:FULL:Y
-- source include/rpl_row_img_set.inc

CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1));

-- let $row_img_query= INSERT INTO t(c1,c3) VALUES (1, 'a')
-- let $row_img_expected_master= | 1:1 2:NULL 3:'a'
-- let $row_img_expected_slave = | 1:1 2:NULL 3:'a'
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c1=2 WHERE c1=1;
-- let $row_img_expected_master= 1:1 2:NULL 3:'a' | 1:2 2:NULL 3:'a'
-- let $row_img_expected_slave = 1:1 2:NULL 3:'a' | 1:2 2:NULL 3:'a'
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= DELETE FROM t;
-- let $row_img_expected_master= 1:2 2:NULL 3:'a' |
-- let $row_img_expected_slave = 1:2 2:NULL 3:'a' |
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

#
# Assertions: combines img_types with different index types. 
#             The checks that rows are logged with expected
#             image contents, depending on the img_type.
#

-- connection master
SET @img_types= 'MINIMAL NOBLOB FULL COMPLETE';
while (`SELECT HEX(@img_types) != HEX('')`)
{
  -- disable_query_log
  let $img_type= `SELECT SUBSTRING_INDEX(@img_types, ' ', 1)`;
  -- let $row_img_set=master:$img_type:N,slave:$img_type:Y
  -- source include/rpl_row_img_set.inc

  SET @index_types= 'UK-NOT-NULL PK UK K NONE';
  while (`SELECT HEX(@index_types) != HEX('')`)
  {
    -- disable_query_log
    let $index_type= `SELECT SUBSTRING_INDEX(@index_types, ' ', 1)`;
    -- enable_query_log

    -- echo ITERATIONS: row_img: $img_type, indexes: $index_type

    -- source include/rpl_reset.inc
    -- connection master

    # create the table
    -- echo CREATING TABLE IN $CURRENT_CONNECTION WITH INDEX '$index_type'

    if (`SELECT HEX('$index_type') = HEX('NONE')`)
    {
      CREATE TABLE t (c1 int, c2 int, c3 blob);
    }

    if (`SELECT HEX('$index_type') = HEX('K')`)
    {
      CREATE TABLE t (c1 int, c2 int, c3 blob, key(c1));
    }

    if (`SELECT HEX('$index_type') = HEX('UK')`)
    {
      CREATE TABLE t (c1 int, c2 int, c3 blob, unique key(c1));
    }

    if (`SELECT HEX('$index_type') = HEX('PK')`)
    {
      CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1));
    }

    if (`SELECT HEX('$index_type') = HEX('UK-NOT-NULL')`)
    {
      CREATE TABLE t (c1 int NOT NULL, c2 int, c3 blob, unique key(c1));
    }

    --source include/sync_slave_sql_with_master.inc
    -- connection master

    # Issue some statements

    -- let $row_img_query= INSERT INTO t VALUES (1,2,"a")
    -- let $row_img_expected_master= | 1:1 2:2 3:'a'
    -- let $row_img_expected_slave = | 1:1 2:2 3:'a'
    -- source include/rpl_row_img_parts_master_slave.inc

    -- let $row_img_query= INSERT INTO t(c1,c3) VALUES (10,"a")
    if (`SELECT @@binlog_row_image = "FULL" or @@binlog_row_image = "NOBLOB" or @@binlog_row_image = "COMPLETE"`)
    {
      -- let $row_img_expected_master= | 1:10 2:NULL 3:'a'
    }
    if  (`SELECT @@binlog_row_image = "MINIMAL"`)
    {
      -- let $row_img_expected_master= | 1:10 3:'a'
    }
    -- let $row_img_expected_slave= $row_img_expected_master
    -- source include/rpl_row_img_parts_master_slave.inc

    -- let $row_img_query= INSERT INTO t(c1) VALUES (1000)
    if (`SELECT @@binlog_row_image = "FULL" or @@binlog_row_image = "COMPLETE"`)
    {
      -- let $row_img_expected_master= | 1:1000 2:NULL 3:NULL
    } 
    if  (`SELECT @@binlog_row_image = "NOBLOB"`)
    { 
      -- let $row_img_expected_master= | 1:1000 2:NULL
    }
    if  (`SELECT @@binlog_row_image = "MINIMAL"`)
    { 
      -- let $row_img_expected_master= | 1:1000
    }
    -- let $row_img_expected_slave= $row_img_expected_master
    -- source include/rpl_row_img_parts_master_slave.inc

    -- let $row_img_query= UPDATE t SET c1=2 WHERE c1=1
    if (`SELECT @@binlog_row_image = "FULL" or @@binlog_row_image = "COMPLETE" OR (SELECT '$index_type' IN ('NONE', 'K', 'UK'))`)
    {
      -- let $bi= 1:1 2:2 3:'a'
    }

    # noblob with pk + uk not nullable
    if (`SELECT @@binlog_row_image = "NOBLOB" AND (SELECT '$index_type' IN ('PK', 'UK-NOT-NULL'))`)
    {
      -- let $bi= 1:1 2:2
    }

    if (`SELECT @@binlog_row_image = "MINIMAL" AND (SELECT '$index_type' IN ('PK', 'UK-NOT-NULL'))`)
    {
      -- let $bi= 1:1
    }

    if (`SELECT @@binlog_row_image = "FULL"`)
    {
      -- let $ai= 1:2 2:2 3:'a'
    }

    if (`SELECT @@binlog_row_image = "NOBLOB"`)
    {
      -- let $ai= 1:2 2:2
    }

    if (`SELECT @@binlog_row_image = "MINIMAL" or @@binlog_row_image = "COMPLETE"`)
    {
      -- let $ai= 1:2
    }
    -- let $row_img_expected_master= $bi | $ai
    -- let $row_img_expected_slave = $bi | $ai
    -- let $ai=
    -- let $bi=
    -- source include/rpl_row_img_parts_master_slave.inc

    -- let $row_img_query= DELETE FROM t WHERE c2=2
    # no key, simple key or unique key nullable
    if (`SELECT @@binlog_row_image = "FULL" OR (SELECT @@binlog_row_image = "COMPLETE") OR (SELECT '$index_type' IN ('NONE', 'K', 'UK'))`)
    {
      -- let $row_img_expected_master= 1:2 2:2 3:'a' |
    }

    # noblob with pk + uk not nullable
    if (`SELECT @@binlog_row_image = "NOBLOB" AND (SELECT '$index_type' IN ('PK', 'UK-NOT-NULL'))`)
    {
      -- let $row_img_expected_master= 1:2 2:2 |
    }

    if (`SELECT @@binlog_row_image = "MINIMAL" AND (SELECT '$index_type' IN ('PK', 'UK-NOT-NULL'))`)
    {
      -- let $row_img_expected_master= 1:2 |
    }
    -- let $row_img_expected_slave= $row_img_expected_master
    -- source include/rpl_row_img_parts_master_slave.inc

    DROP TABLE t;

    --source include/sync_slave_sql_with_master.inc

    -- disable_query_log
    -- connection master
    -- eval SET @index_types= LTRIM(SUBSTRING(@index_types, LENGTH('$index_type') + 1))
    -- enable_query_log

  }

  -- disable_query_log
  -- connection master
  -- eval SET @img_types= LTRIM(SUBSTRING(@img_types, LENGTH('$img_type') + 1))
  -- enable_query_log
}

### Some more scenarios
###  These relate to different constraints on PKE and its impact 
###  on logged images. Further description is inline.

-- connection master
SET @img_types= 'MINIMAL NOBLOB FULL COMPLETE';
while (`SELECT HEX(@img_types) != HEX('')`)
{
  -- disable_query_log
  let $img_type= `SELECT SUBSTRING_INDEX(@img_types, ' ', 1)`;
  -- let $row_img_set=master:$img_type:N,slave:$img_type:Y
  -- source include/rpl_row_img_set.inc

  -- echo ITERATIONS: row_img: $img_type

  -- source include/rpl_reset.inc
  -- connection master

  # ASSERTIONS:
  # UPDATE (MINIMAL,NOBLOB,FULL,COMPLETE)
  #  - on slave, columns that are not in master's BI but are in slave's
  #    PKE are in slave's BI

  -- connection master
  SET SQL_LOG_BIN=0;
  CREATE TABLE t (a INT);
  SET SQL_LOG_BIN=1;

  -- connection slave
  SET SQL_LOG_BIN=0;
  CREATE TABLE t (a INT, b INT DEFAULT 1000);
  SET SQL_LOG_BIN=1;

  -- connection master
  INSERT INTO t VALUES (1);
  --source include/sync_slave_sql_with_master.inc
  -- connection master

  -- let $row_img_query= UPDATE t SET a=2 WHERE a=1;
  #   1. columns that are set to default value are in AI
  -- let $row_img_expected_master= 1:1 | 1:2
  if (`SELECT @@binlog_row_image = "NOBLOB" OR (SELECT @@binlog_row_image = "FULL")`)
  {
    -- let $row_img_expected_slave = 1:1 2:1000 | 1:2 2:1000
  }
  if (`SELECT @@binlog_row_image = "MINIMAL" OR (SELECT @@binlog_row_image = "COMPLETE")`)
  {
    -- let $row_img_expected_slave = 1:1 2:1000 | 1:2
  }
  -- source include/rpl_row_img_parts_master_slave.inc

  -- connection master
  DROP TABLE IF EXISTS t;
  --source include/sync_slave_sql_with_master.inc

  -- source include/rpl_reset.inc

  -- connection master
  if (`SELECT @@binlog_row_image = "MINIMAL"`)
  {
    -- echo ####### MINIMAL PARTICULAR SCENARIO ######
  
    # ASSERTIONS: 
    #   INSERT/MINIMAL
    #      1. columns that are set to default value are in AI
    #
    #    UPDATE/MINIMAL:
    #      2. columns that are set to the same value are not in AI
    #      3. columns that are not in WHERE clause but in PKE are in BI
    #      4. on slave, columns that are not in master's BI but are in slave's
    #         PKE are in slave's BI
    #    
    #    DELETE/MINIMAL:
    #      5. on slave, columns that are in master's BI but not in slave's PKE are
    #         not in slave's BI
    #      6. columns that are NOT NULL UK but not in PK are not in BI
    #
    -- connection master
    SET SQL_LOG_BIN=0;
    CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT DEFAULT 100, c3 INT);
    SET SQL_LOG_BIN=1;

    -- connection slave
    SET SQL_LOG_BIN=0;
    CREATE TABLE t (c1 INT NOT NULL UNIQUE KEY, c2 INT DEFAULT 100, c3 INT, c4 INT, PRIMARY KEY(c2,c3));

    -- let $row_img_query= INSERT INTO t VALUES (1, 100, 1);
    #   1. columns that are set to default value are in AI
    -- let $row_img_expected_master= | 1:1 2:100 3:1
    -- let $row_img_expected_slave = | 1:1 2:100 3:1
    -- source include/rpl_row_img_parts_master_slave.inc

    -- let $row_img_query= UPDATE t SET c2=100, c3=1000 WHERE c2=100;
    #   2. columns that are set to the same value are not in AI
    #   3. columns that are not in WHERE clause but in PKE are in BI
    #   4. on slave, columns that are not in master's BI but are in slave's PKE are in slave's BI
    -- let $row_img_expected_master= 1:1 | 3:1000
    -- let $row_img_expected_slave = 2:100 3:1 | 3:1000
    -- source include/rpl_row_img_parts_master_slave.inc

    -- let $row_img_query= DELETE FROM t WHERE c1=1
    # asserts:
    #   5. on slave, columns that are not in master's BI but are in slave's PKE are in slave's BI
    #   6. columns that are NOT NULL UK but not in PK are not in BI
    -- let $row_img_expected_master= 1:1 |
    -- let $row_img_expected_slave= 2:100 3:1000 | 
    -- source include/rpl_row_img_parts_master_slave.inc

    -- connection master
    DROP TABLE t;
    --source include/sync_slave_sql_with_master.inc

    -- echo ####### MINIMAL OTHER PARTICULAR SCENARIO ######
  
    # ASSERTIONS: 
    #    UPDATE/MINIMAL:
    #      1. all columns needed to identify a row are in BI; as the
    #      index is not unique, this means all columns of the table
    #      2. columns that are in SET changed are in AI
    #      3. columns that are not in SET are not in AI
    #      4. table on slave has the same definition as table on
    #      master, so points 1-2-3 apply to images in the slave's
    #      binlog too.
    #
    -- connection master
    CREATE TABLE t (c1 INT NOT NULL, c2 INT NOT NULL, KEY (c1));
    INSERT INTO t VALUES (30,40);

    -- connection slave

    -- let $row_img_query= UPDATE t SET c2=100 ORDER BY c1;
    #   1. all columns needed to identify a row are in BI
    #   2. columns that are in SET changed are in AI
    #   3. columns that are not in SET are not in AI
    #   4. slave is as master as tables' definitions are identical
    -- let $row_img_expected_master= 1:30 2:40 | 2:100
    -- let $row_img_expected_slave = 1:30 2:40 | 2:100
    -- source include/rpl_row_img_parts_master_slave.inc

    -- connection master
    DROP TABLE t;
    --source include/sync_slave_sql_with_master.inc
  }

  if (`SELECT @@binlog_row_image = "MINIMAL" OR (SELECT @@binlog_row_image = "COMPLETE")`)
  {
    -- echo ####### MINIMAL AND COMPLETE PARTICULAR SCENARIO SETTING SAME VALUE ######

    # ASSERTIONS:
    #    1. columns that are set to the same value are not in AI
    #    2. columns that are set to the same value by INSERT ... DUPLICATE KEY
    #       are not in AI
    #

    -- connection master
    CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1));
    INSERT INTO t VALUES (1,2,"a");
    INSERT INTO t VALUES (2,4,NULL);


    --source include/sync_slave_sql_with_master.inc
    -- connection master

    # Issue some statements
    -- let $row_img_query= UPDATE t SET c1 = 1, c2 = 4 WHERE c3 = "a";
    if (`SELECT @@binlog_row_image = "MINIMAL"`)
    {
      -- let $row_img_expected_master= 1:1 | 2:4
    }
    if (`SELECT @@binlog_row_image = "COMPLETE"`)
    {
      -- let $row_img_expected_master= 1:1 2:2 3:'a' | 2:4
    }
    -- let $row_img_expected_slave = $row_img_expected_master
    -- source include/rpl_row_img_parts_master_slave.inc

    -- let $row_img_query= INSERT INTO t VALUES (1, 4, "a") AS alias ON DUPLICATE KEY UPDATE c2 = alias.c2 + 2, c3 = alias.c3;
    if (`SELECT @@binlog_row_image = "MINIMAL"`)
    {
      -- let $row_img_expected_master= 1:1 | 2:6
    }
    if (`SELECT @@binlog_row_image = "COMPLETE"`)
    {
      -- let $row_img_expected_master= 1:1 2:4 3:'a' | 2:6
    }
    -- let $row_img_expected_slave = $row_img_expected_master
    -- source include/rpl_row_img_parts_master_slave.inc

    -- let $row_img_query= INSERT INTO t VALUES (2, 4, "") AS alias ON DUPLICATE KEY UPDATE c2 = alias.c2, c3 = alias.c3;
    if (`SELECT @@binlog_row_image = "MINIMAL"`)
    {
      -- let $row_img_expected_master= 1:2 | 3:''
    }
    if (`SELECT @@binlog_row_image = "COMPLETE"`)
    {
      -- let $row_img_expected_master= 1:2 2:4 3:NULL | 3:''
    }
    -- let $row_img_expected_slave = $row_img_expected_master
    -- source include/rpl_row_img_parts_master_slave.inc

    -- connection master
    DROP TABLE t;
    --source include/sync_slave_sql_with_master.inc
  }

  if (`SELECT @@binlog_row_image = "NOBLOB"`)
  {
    -- echo ####### NOBLOB PARTICULAR SCENARIO ######

    #    ASSERTIONS: 
    #    INSERT/NOBLOB:
    #      1 non-blob columns that are set to default value are in AI
    #    
    #    UPDATE/NOBLOB:
    #      2 blob columns that are in WHERE clause but not in PKE are not in BI
    #      3 blob columns that are in NON-NULL UK but not in PK are not in BI
    #      4 on slave, blob columns that are in master's BI clause but not in
    #        slave's PKE are not in BI
    #
    #    DELETE
    #      5 non-blob columns that are not in WHERE clause but in PKE are in BI
    #      6 blob columns that are used in WHERE clause but not in PKE are not
    #        in BI
    #      7 blob columns that are NOT NULL UK but not in PK are not in BI
    #      8 on slave, blob columns that are in master's BI but not in slave's PKE
    #        are not in slave's BI

    -- connection master
    SET SQL_LOG_BIN=0;
    CREATE TABLE t (c1 INT, c2 BLOB, c3 INT DEFAULT 1000, c4 BLOB NOT NULL, c5 INT, UNIQUE KEY(c4(512)), PRIMARY KEy(c1, c2(512)));
    SET SQL_LOG_BIN=1;

    -- connection slave
    SET SQL_LOG_BIN=0;
    CREATE TABLE t (c1 INT PRIMARY KEY, c2 BLOB, c3 INT DEFAULT 1000, c4 BLOB, c5 INT);
    SET SQL_LOG_BIN=1;

    -- connection master
    -- let $row_img_query= INSERT INTO t VALUES (1,'aaa', 1000, 'bbb', 1)
    #   1. non-blob columns that are set to default value are in AI
    -- let $row_img_expected_master= | 1:1 2:'aaa' 3:1000 4:'bbb' 5:1
    -- let $row_img_expected_slave = | 1:1 2:'aaa' 3:1000 4:'bbb' 5:1
    -- source include/rpl_row_img_parts_master_slave.inc

    -- connection master
    -- let $row_img_query= UPDATE t SET c2='aaa', c3=1000, c5=10000 WHERE c1=1 AND c4='bbb'
    # asserts that:
    #  2. blob columns that are in WHERE clause but not in PKE are not in BI
    #  3. blob columns that are in NON-NULL UK but not in PK are not in BI
    #  4. on slave, blob columns that are in master's BI clause but not in
    #     slave's PKE are not in BI
    -- let $row_img_expected_master= 1:1 2:'aaa' 3:1000 5:1 | 1:1 3:1000 5:10000
    -- let $row_img_expected_slave = 1:1 3:1000 5:1 | 1:1 3:1000 5:10000
    -- source include/rpl_row_img_parts_master_slave.inc

    -- connection master
    -- let $row_img_query= DELETE FROM t WHERE c1=1 AND c4='bbb'
    # asserts that:
    #   5. non-blob columns that are not in WHERE clause but in PKE are in BI
    #   6. blob columns that are used in WHERE clause but not in PKE are not n BI
    #   7. blob columns that are NOT NULL UK but not in PK are not in BI
    #   8. on slave, blob columns that are in master's BI but not in slave's PKE are not in slave's BI
    -- let $row_img_expected_master= 1:1 2:'aaa' 3:1000 5:10000 | 
    -- let $row_img_expected_slave = 1:1 3:1000 5:10000 | 
    -- source include/rpl_row_img_parts_master_slave.inc

    -- connection master
    DROP TABLE t;
    --source include/sync_slave_sql_with_master.inc

  }

  -- disable_query_log
  -- connection master
  -- eval SET @img_types= LTRIM(SUBSTRING(@img_types, LENGTH('$img_type') + 1))
  -- enable_query_log
}

-- echo ################## SPECIAL CASES #########################

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:NOBLOB:N,slave:NOBLOB:Y
-- source include/rpl_row_img_set.inc

-- echo ###################################
-- echo # PK (contains blob)
-- echo ###################################

-- connection master

CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1,c3(512)));

-- let $row_img_query= INSERT INTO t VALUES (1,2,"a")
-- let $row_img_expected_master= | 1:1 2:2 3:'a'
-- let $row_img_expected_slave = | 1:1 2:2 3:'a'
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= INSERT INTO t(c1,c3) VALUES (10,"a")
-- let $row_img_expected_master= | 1:10 2:NULL 3:'a'
-- let $row_img_expected_slave = | 1:10 2:NULL 3:'a'
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= INSERT INTO t(c1) VALUES (1000)
-- let $row_img_expected_master= | 1:1000 2:NULL
-- let $row_img_expected_slave = | 1:1000 2:NULL
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c1=2 WHERE c1=1
-- let $row_img_expected_master= 1:1 2:2 3:'a' | 1:2 2:2
-- let $row_img_expected_slave = 1:1 2:2 3:'a' | 1:2 2:2
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= DELETE FROM t WHERE c2=2
-- let $row_img_expected_master= 1:2 2:2 3:'a' | 
-- let $row_img_expected_slave = 1:2 2:2 3:'a' | 
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;

--source include/sync_slave_sql_with_master.inc

-- echo ###################################
-- echo # PK (does not contain blob, but blob is updated)
-- echo ###################################

-- source include/rpl_reset.inc
-- connection master

CREATE TABLE t (c1 int, c2 int, c3 blob, primary key(c1,c2));

-- let $row_img_query= INSERT INTO t VALUES (1,2,"a")
-- let $row_img_expected_master= | 1:1 2:2 3:'a'
-- let $row_img_expected_slave = | 1:1 2:2 3:'a'
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= INSERT INTO t(c1,c3) VALUES (10,"a")
-- let $row_img_expected_master= | 1:10 2:0 3:'a'
-- let $row_img_expected_slave = | 1:10 2:0 3:'a'
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= INSERT INTO t(c1) VALUES (1000)
-- let $row_img_expected_master= | 1:1000 2:0
-- let $row_img_expected_slave = | 1:1000 2:0
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c3='b' WHERE c1=1
-- let $row_img_expected_master= 1:1 2:2 | 1:1 2:2 3:'b'
-- let $row_img_expected_slave = 1:1 2:2 | 1:1 2:2 3:'b'
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= DELETE FROM t WHERE c2=2
-- let $row_img_expected_master= 1:1 2:2 | 
-- let $row_img_expected_slave = 1:1 2:2 | 
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- echo ###################################
-- echo # AUTOINC columns
-- echo ###################################

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y
-- source include/rpl_row_img_set.inc

CREATE TABLE t (c1 int NOT NULL AUTO_INCREMENT, c2 int, c3 blob, primary key(c1,c2));

-- let $row_img_query= INSERT INTO t(c2) VALUES (2)
-- let $row_img_expected_master= | 1:1 2:2
-- let $row_img_expected_slave = | 1:1 2:2
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:COMPLETE:N,slave:COMPLETE:Y
-- source include/rpl_row_img_set.inc

CREATE TABLE t (c1 int NOT NULL AUTO_INCREMENT, c2 int, c3 blob, primary key(c1,c2));

-- let $row_img_query= INSERT INTO t(c2) VALUES (2)
-- let $row_img_expected_master= | 1:1 2:2 3:NULL
-- let $row_img_expected_slave = | 1:1 2:2 3:NULL
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- echo ##################################################################
-- echo # Test that slave does not write more columns than the ones it has 
-- echo ##################################################################

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y
-- source include/rpl_row_img_set.inc

SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int NOT NULL AUTO_INCREMENT, c2 int, c3 blob, primary key(c1,c2));
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int, c2 int, primary key(c1));

-- connection master

-- let $row_img_query= INSERT INTO t(c2,c3) VALUES (2,'aaaaa')
-- let $row_img_expected_master= | 1:1 2:2 3:'aaaaa'
-- let $row_img_expected_slave = | 1:1 2:2
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c2=3, c3='bbbbb' WHERE c2=2
-- let $row_img_expected_master= 1:1 2:2 | 2:3 3:'bbbbb'
-- let $row_img_expected_slave = 1:1 | 2:3
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:COMPLETE:N,slave:COMPLETE:Y
-- source include/rpl_row_img_set.inc

SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int NOT NULL AUTO_INCREMENT, c2 int, c3 blob, primary key(c1,c2));
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int, c2 int, primary key(c1));

-- connection master

-- let $row_img_query= INSERT INTO t(c2,c3) VALUES (2,'aaaaa')
-- let $row_img_expected_master= | 1:1 2:2 3:'aaaaa'
-- let $row_img_expected_slave = | 1:1 2:2
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c2=3, c3='bbbbb' WHERE c2=2
-- let $row_img_expected_master= 1:1 2:2 3:'aaaaa' | 2:3 3:'bbbbb'
-- let $row_img_expected_slave = 1:1 2:2 | 2:3
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- echo ##################################################################
-- echo # Test that slave fills default columns in its own columns
-- echo ##################################################################

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:FULL:N,slave:FULL:Y
-- source include/rpl_row_img_set.inc

SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int, c2 int);
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int, c2 int, c3 int DEFAULT 2005);

-- connection master

-- let $row_img_query= INSERT INTO t(c1) VALUES (1)
-- let $row_img_expected_master= | 1:1 2:NULL
-- let $row_img_expected_slave = | 1:1 2:NULL 3:2005
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= INSERT INTO t(c1) VALUES (2)
-- let $row_img_expected_master= | 1:2 2:NULL
-- let $row_img_expected_slave = | 1:2 2:NULL 3:2005
-- source include/rpl_row_img_parts_master_slave.inc

-- connection slave
SELECT * FROM t;
-- connection master
SELECT * FROM t;

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:COMPLETE:N,slave:COMPLETE:Y
-- source include/rpl_row_img_set.inc

SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int, c2 int);
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int, c2 int, c3 int DEFAULT 2005);

-- connection master

-- let $row_img_query= INSERT INTO t(c1) VALUES (1)
-- let $row_img_expected_master= | 1:1 2:NULL
-- let $row_img_expected_slave = | 1:1 2:NULL 3:2005
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= INSERT INTO t(c1) VALUES (2)
-- let $row_img_expected_master= | 1:2 2:NULL
-- let $row_img_expected_slave = | 1:2 2:NULL 3:2005
-- source include/rpl_row_img_parts_master_slave.inc

-- connection slave
SELECT * FROM t;
-- connection master
SELECT * FROM t;

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- echo ##################################################################
-- echo # Test that slave uses partial BI when master contains more columns
-- echo ##################################################################

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y
-- source include/rpl_row_img_set.inc


SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c1, c3), unique key(c1));
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int NOT NULL, c2 int, unique key(c1));

-- connection master

-- let $row_img_query= INSERT INTO t VALUES (1, 2, 3)
-- let $row_img_expected_master= | 1:1 2:2 3:3
-- let $row_img_expected_slave = | 1:1 2:2
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c2= 4 WHERE c1=1
-- let $row_img_expected_master= 1:1 3:3 | 2:4
-- let $row_img_expected_slave = 1:1 | 2:4
-- source include/rpl_row_img_parts_master_slave.inc

-- connection slave
SELECT * FROM t;
-- connection master
SELECT * FROM t;

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:COMPLETE:N,slave:COMPLETE:Y
-- source include/rpl_row_img_set.inc


SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c1, c3), unique key(c1));
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int NOT NULL, c2 int, unique key(c1));

-- connection master

-- let $row_img_query= INSERT INTO t VALUES (1, 2, 3)
-- let $row_img_expected_master= | 1:1 2:2 3:3
-- let $row_img_expected_slave = | 1:1 2:2
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c2= 4 WHERE c1=1
-- let $row_img_expected_master= 1:1 2:2 3:3 | 2:4
-- let $row_img_expected_slave = 1:1 2:2 | 2:4
-- source include/rpl_row_img_parts_master_slave.inc

-- connection slave
SELECT * FROM t;
-- connection master
SELECT * FROM t;

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- echo ##################################################################
-- echo # Test that if master has binlog_row_image=MINIMAL and slave has 
-- echo # NOBLOB, FULL or COMPLETE, it will log the expected columns
-- echo ##################################################################

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:MINIMAL:N,slave:FULL:Y
-- source include/rpl_row_img_set.inc

SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c1));
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, c4 blob, unique key(c1));

-- connection master
-- let $row_img_query= INSERT INTO t VALUES (1, 2, 3)
-- let $row_img_expected_master= | 1:1 2:2 3:3
-- let $row_img_expected_slave = | 1:1 2:2 3:3 4:NULL
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c2= 4 WHERE c1=1
-- let $row_img_expected_master=  1:1 | 2:4
-- let $row_img_expected_slave = 1:1 2:2 3:3 4:NULL | 1:1 2:4 3:3 4:NULL
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= DELETE FROM t WHERE c2=4
-- let $row_img_expected_master= 1:1 |
-- let $row_img_expected_slave = 1:1 2:4 3:3 4:NULL | 
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:MINIMAL:N,slave:NOBLOB:Y
-- source include/rpl_row_img_set.inc

SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c1));
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, c4 blob, unique key(c1));

-- connection master
-- let $row_img_query= INSERT INTO t VALUES (1, 2, 3)
-- let $row_img_expected_master= | 1:1 2:2 3:3
-- let $row_img_expected_slave = | 1:1 2:2 3:3
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c2= 4 WHERE c1=1
-- let $row_img_expected_master= 1:1 | 2:4
-- let $row_img_expected_slave = 1:1 2:2 3:3 | 1:1 2:4 3:3
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= DELETE FROM t WHERE c2=4
-- let $row_img_expected_master= 1:1 |
-- let $row_img_expected_slave = 1:1 2:4 3:3 | 
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:MINIMAL:N,slave:COMPLETE:Y
-- source include/rpl_row_img_set.inc

SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c1));
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, c4 blob, unique key(c1));

-- connection master
-- let $row_img_query= INSERT INTO t VALUES (1, 2, 3)
-- let $row_img_expected_master= | 1:1 2:2 3:3
-- let $row_img_expected_slave = | 1:1 2:2 3:3 4:NULL
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= UPDATE t SET c2= 4 WHERE c1=1
-- let $row_img_expected_master= 1:1 | 2:4
-- let $row_img_expected_slave = 1:1 2:2 3:3 4:NULL | 2:4
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_query= DELETE FROM t WHERE c2=4
-- let $row_img_expected_master= 1:1 |
-- let $row_img_expected_slave = 1:1 2:4 3:3 4:NULL | 
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t;
--source include/sync_slave_sql_with_master.inc

-- echo ################################################################
-- echo # Test that the slave stop with error if no usable data is on BI
-- echo ################################################################

-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:MINIMAL:N,slave:NOBLOB:Y
-- source include/rpl_row_img_set.inc

SET SQL_LOG_BIN=0;
CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, primary key(c3));
SET SQL_LOG_BIN=1;

-- connection slave
CREATE TABLE t (c1 int NOT NULL, c2 int, primary key(c1));

-- connection master

INSERT INTO t VALUES (1,2,3);
UPDATE t SET c2=4 WHERE c2=2;
DROP TABLE t;

-- connection slave
# 1032==ER_KEY_NOT_FOUND (handler returns HA_ERR_END_OF_FILE)
let $slave_sql_errno= 1032;
source include/wait_for_slave_sql_error.inc;
DROP TABLE t;
--let $rpl_only_running_threads= 1

# ==== Purpose ====
#
# Check that when binlog_row_image= FULL 'UPDATE' query should not using
# temporary if the PRIMARY KEY not being modified as part of the query.
#
# ==== Implementation ====
#
# Set binlog_row_image= FULL. Create a table which has both a primary key and
# a regular int field which is not a key. Execute an UPDATE statement in such
# a way that it doesn't update the primary key field. See the 'EXPLAIN' output
# it should not use a temporary table. Repeat the same test in case of
# binlog_row_image= NOBLOB as well. No temporary table should be used in this
# case as well.
#
# ==== References ====
#
# Bug#22510353: UNNECESSARY USING TEMPORARY FOR UPDATE
#
###############################################################################
-- source include/rpl_reset.inc
-- connection master
-- let $row_img_set=master:FULL:N,slave:FULL:Y
-- source include/rpl_row_img_set.inc
CREATE TABLE t1(id INT PRIMARY KEY, a INT) ENGINE = INNODB;

--source include/sync_slave_sql_with_master.inc

-- connection master
-- let $row_img_query= INSERT INTO t1 (id, a) VALUES (1, 1)
-- let $row_img_expected_master= | 1:1 2:1
-- let $row_img_expected_slave = | 1:1 2:1
-- source include/rpl_row_img_parts_master_slave.inc

-- echo "Case: FULL - EXPLAIN output should not display Using temporary"
EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2;

-- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2
-- let $row_img_expected_master=  1:1 2:1 | 1:1 2:2
-- let $row_img_expected_slave = 1:1 2:1 | 1:1 2:2
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_set=master:NOBLOB:N,slave:NOBLOB:Y
-- source include/rpl_row_img_set.inc

-- echo "Case: NOBLOB - EXPLAIN output should not display Using temporary"
EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2;

-- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2
-- let $row_img_expected_master=  1:1 2:2 | 1:1 2:3
-- let $row_img_expected_slave = 1:1 2:2 | 1:1 2:3
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y
-- source include/rpl_row_img_set.inc

EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2;

-- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2
-- let $row_img_expected_master= 1:1 | 2:4
-- let $row_img_expected_slave = 1:1 | 2:4
-- source include/rpl_row_img_parts_master_slave.inc

-- let $row_img_set=master:COMPLETE:N,slave:COMPLETE:Y
-- source include/rpl_row_img_set.inc

-- echo "Case: COMPLETE - EXPLAIN output should not display Using temporary"
EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2;

-- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2
-- let $row_img_expected_master=  1:1 2:4 | 2:5
-- let $row_img_expected_slave = 1:1 2:4 | 2:5
-- source include/rpl_row_img_parts_master_slave.inc

DROP TABLE t1;
--source include/sync_slave_sql_with_master.inc

## CLEAN UP

-- connection master
SET GLOBAL binlog_row_image= @old_binlog_row_image;
SET SESSION binlog_row_image= @old_binlog_row_image;
-- connection slave
SET GLOBAL binlog_row_image= @old_binlog_row_image;
SET SESSION binlog_row_image= @old_binlog_row_image;

--source include/rpl_end.inc