Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11815

SP variables of temporal data types do not replicate correctly

    Details

      Description

      SP variables in statement binlog are replicated by replacing them to a special function NAME_CONST().
      Substitution is implemented in sp_head.cc in Item_splocal::append_for_log(), which uses a static function sp_get_item_value().

      The implementation of sp_get_item_value() is not correct. It replaces values of temporal variables to string literals, so they are interpreted differently on the master and on the slave.

      This mtr test demonstrates the problem:

      --source include/have_binlog_format_statement.inc
      --source include/master-slave.inc
       
      connection master;
      CREATE TABLE t1(a INT);
      DELIMITER $$;
      CREATE PROCEDURE p1()
      BEGIN
        DECLARE a TIME DEFAULT '01:01:01';
        INSERT INTO t1 VALUES (a=10101);
      END;
      $$
      DELIMITER ;$$
      CALL p1;
      SELECT * FROM t1;
      sync_slave_with_master;
       
      SELECT * FROM t1;
       
      connection master;
      DROP TABLE t1;
      DROP PROCEDURE p1;
      sync_slave_with_master;
       
      --source include/rpl_end.inc
      

      This tests returns the following results:

      include/master-slave.inc
      [connection master]
      connection master;
      CREATE TABLE t1(a INT);
      CREATE PROCEDURE p1()
      BEGIN
      DECLARE a TIME DEFAULT '01:01:01';
      INSERT INTO t1 VALUES (a=10101);
      END;
      $$
      CALL p1;
      SELECT * FROM t1;
      a
      1
      connection slave;
      SELECT * FROM t1;
      a
      0
      connection master;
      DROP TABLE t1;
      DROP PROCEDURE p1;
      connection slave;
      include/rpl_end.inc
      

      Notice, the INSERT statement inserted 1 on the master and 0 on the slave.

      If I look inside mysql-test/var/mysqld.1/data/master-bin.000001, I can see that the INSERT query rewritten as follows:

      INSERT INTO t1 VALUES ( NAME_CONST('a',_latin1'01:01:01' COLLATE 'latin1_swedish_ci')=10101)
      

      This is wrong. It should be rewritten using the temporal literal syntax instead of the string literal syntax:

      INSERT INTO t1 VALUES ( NAME_CONST('a',TIME'01:01:01')=10101)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: