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

RANDOM_BYTES as a virtual column function makes binlog non-deterministic

    XMLWordPrintable

    Details

      Description

      Apparently binary logging remains oblvious to RANDOM_BYTES when it's used as a virtual column function, which allows its results to be written in the binary log in statement format and thus cause discrepancies between master and slave.

      --source include/master-slave.inc
      --source include/have_binlog_format_mixed.inc
       
      reset master;
       
      create table t (a int, b varbinary(20) as (random_bytes(a)));
      insert into t (a) values (1),(2);
      create table t2 as select * from t;
       
      select a, hex(b) from t2;
       
      --sync_slave_with_master
       
      select a, hex(b) from t2;
       
      --connection master
      show binlog events;
       
      # Cleanup
       
      drop table t, t2;
      --source include/rpl_end.inc
      

      bb-10.10-MDEV-25704 d714285e7

      select a, hex(b) from t2;
      a	hex(b)
      1	53
      2	98A2
      connection slave;
      select a, hex(b) from t2;
      a	hex(b)
      1	DE
      2	C7D7
      

      show binlog events;
      Log_name	Pos	Event_type	Server_id	End_log_pos	Info
      master-bin.000001	4	Format_desc	1	256	Server ver: 10.10.0-MariaDB-debug-log, Binlog ver: 4
      master-bin.000001	256	Gtid_list	1	285	[]
      master-bin.000001	285	Binlog_checkpoint	1	329	master-bin.000001
      master-bin.000001	329	Gtid	1	371	GTID 0-1-1
      master-bin.000001	371	Query	1	507	use `test`; create table t (a int, b varbinary(20) as (random_bytes(a)))
      master-bin.000001	507	Gtid	1	549	BEGIN GTID 0-1-2
      master-bin.000001	549	Query	1	648	use `test`; insert into t (a) values (1),(2)
      master-bin.000001	648	Query	1	721	COMMIT
      master-bin.000001	721	Gtid	1	763	GTID 0-1-3
      master-bin.000001	763	Query	1	864	use `test`; create table t2 as select * from t
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.