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

MySQL Bug#56184 Rolled back transaction without non-trasactional table updated was binlogged

        Details

        • Type: Bug
        • Status: Stalled
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.0.10
        • Fix Version/s: 10.0
        • Component/s: None
        • Labels:
          None

          Description

          Found while analyzing main.innodb_read_only result difference. The following revision is either not fully merged or not merged at all. Please check if it is something we want to have:

          revno: 2876.438.1
committer: <anders.song@greatopensource.com>
branch nick: mysql-trunk
timestamp: Wed 2011-01-05 13:21:07 +0800
message:
  Bug#56184 Rolled back transaction without non-trasactional table updated was binlogged
  Bug#55798 Slave SQL retry on transaction inserts extra data into non-transaction table

  The transaction modified non-transactional table will be binlogged with ROLLBACK if it
  rolls back on master. It includes the case that all statements which modified
  non-transactional table are binlogged outside(before) the transaction.
  Example:
    BEGIN
    INSERT INTO trans-table;
    INSERT INOT non-trans-table;
    ROLLBACK
  it will be binlogged as:
    BEGIN
    INSERT INTO non-trans-table;
    COMMIT
    BEGIN
    INSERT INTO trans-table;
    ROLLBACK;
  All statements in the second binlogged transaction modify only transactional tables and
  are rolled back safely on master. So the second transaction should not be binlogged.

  After 5.5, there are two caches for binary logs, a transactional cache
  and a statement cache. When executing a transaction, statements that
  modified only transactional tables are always put in transactional
  cache. Statements that modified non-transactional tables can be put in
  either transactional or non-transactional cache depending on different
  situations. In this patch, a flag is added to mark if there is any
  statement that modified non-transactional table in transactional cache.
  When rolling back a transaction on master, transactional cache should
  not be flushed to binary log, if there is no statement in it that
  modified a non-transactional table. Otherwise, it should be flushed into
  binary log followed by 'ROLLBACK' statement.

  BUG#55798
  When a temporary error(eg. Lock timeout) happens, Slave SQL thread will rollback the
  transaction and retry it again. But it is possible that the transaction cannot be
  rolled back safely. For example a non-transactional table has been modified by the
  transaction. It will make master and slave diversely.

  After this patch, SQL thread will not retry to execute a transaction which can not be rolled
  back safely if temporary error is encountered.

  It also did some refactoring on code related to THD_TRANS::modified_non_trans_table and
  binlog_cache_data.

            Gliffy Diagrams

              Attachments

                Issue Links

                is part of

                Task - A task that needs to be done. MDEV-4784 merge test cases from 5.6

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Stalled

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  jplindst Jan Lindström added a comment - - edited

                  Tested with a new testcase attached and indeed non-trasactional table insert is binlogged. But in my opinion this is correct, you can't assume transactional behavior on non-transactional table. If we can't roll back non-transactional update on master (warning is shown) we must execute it also on slave (to avoid inconsistent master-slave databases). I do not see rolled back transaction on binlog:

                  include/show_binlog_events.inc
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Gtid	#	#	GTID #-#-#
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1(c1 INT KEY) ENGINE=MyISAM
master-bin.000001	#	Gtid	#	#	GTID #-#-#
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2(c1 INT NOT NULL PRIMARY KEY) ENGINE=innodb
master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES(1)
master-bin.000001	#	Query	#	#	COMMIT
include/diff_tables.inc [master:test.t1, slave:test.t1]
                  Show
                  jplindst Jan Lindström added a comment - - edited Tested with a new testcase attached and indeed non-trasactional table insert is binlogged. But in my opinion this is correct, you can't assume transactional behavior on non-transactional table. If we can't roll back non-transactional update on master (warning is shown) we must execute it also on slave (to avoid inconsistent master-slave databases). I do not see rolled back transaction on binlog: include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t1(c1 INT KEY) ENGINE=MyISAM master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `test`; CREATE TABLE t2(c1 INT NOT NULL PRIMARY KEY) ENGINE=innodb master-bin.000001 # Gtid # # BEGIN GTID #-#-# master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES(1) master-bin.000001 # Query # # COMMIT include/diff_tables.inc [master:test.t1, slave:test.t1]
                  Hide
                  Permalink
                  jplindst Jan Lindström added a comment -

                  Assigning to Kristian for second opinion. Attached also test case changes on mentioned fix.

                  Show
                  jplindst Jan Lindström added a comment - Assigning to Kristian for second opinion. Attached also test case changes on mentioned fix.

                    People

                    • Assignee:
                      knielsen Kristian Nielsen
                      Reporter:
                      svoj Sergey Vojtovich
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      4 Start watching this issue

                      Dates

                      • Created:
                        Updated: