• unknown's avatar
    BUG#33029 5.0 to 5.1 replication fails on dup key when inserting · d5f09a13
    unknown authored
    using a trig in SP
    
    For all 5.0 and up to 5.1.12 exclusive, when a stored routine or
    trigger caused an INSERT into an AUTO_INCREMENT column, the
    generated AUTO_INCREMENT value should not be written into the
    binary log, which means if a statement does not generate
    AUTO_INCREMENT value itself, there will be no Intvar event (SET
    INSERT_ID) associated with it even if one of the stored routine
    or trigger caused generation of such a value. And meanwhile, when
    executing a stored routine or trigger, it would ignore the
    INSERT_ID value even if there is a INSERT_ID value available set
    by a SET INSERT_ID statement.
    
    Starting from MySQL 5.1.12, the generated AUTO_INCREMENT value is
    written into the binary log, and the value will be used if
    available when executing the stored routine or trigger.
    
    Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
    (referenced as the buggy versions in the text below), when a
    statement that generates AUTO_INCREMENT value by the top
    statement was executed in the body of a SP, all statements in the
    SP after this statement would be treated as if they had generated
    AUTO_INCREMENT by the top statement.  When a statement that did
    not generate AUTO_INCREMENT value by the top statement but by a
    function/trigger called by it, an erroneous Intvar event would be
    associated with the statement, this erroneous INSERT_ID value
    wouldn't cause problem when replicating between masters and
    slaves of 5.0.x or prior 5.1.12, because the erroneous INSERT_ID
    value was not used when executing functions/triggers. But when
    replicating from buggy versions to 5.1.12 or newer, which will
    use the INSERT_ID value in functions/triggers, the erroneous
    value will be used, which would cause duplicate entry error and
    cause the slave to stop.
    
    The patch for 5.0 fixed it not to generate the erroneous Intvar
    event, another patch for 5.1 fixed it to ignore the SET INSERT_ID
    value when executing functions/triggers if it is replicating from
    a master of buggy versions.
    
    
    mysql-test/include/show_binlog_events.inc:
      add $binlog_start parameter to set the start position when show binlog events, if not set a default value will be used.
      
      mask out column 2(Pos), 4(Server_id), table_id, and file_id
    sql/sql_class.cc:
      Reset insert_id_used after each query in SP
    mysql-test/r/rpl_auto_increment_bug33029.result:
      Add test for bug33029, test if the master generate the erroneous event or not
    mysql-test/t/rpl_auto_increment_bug33029.test:
      Add test for bug33029, test if the master generate the erroneous event or not
    d5f09a13
rpl_auto_increment_bug33029.test 2.53 KB