Bug #50192 Strange effect in replication test, trigger, auto_increment
Submitted: 8 Jan 2010 19:21 Modified: 8 Mar 2010 19:59
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.99-m3 OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any

[8 Jan 2010 19:21] Matthias Leich
Description:
My script:
==========
--source include/master-slave.inc
SET SESSION BINLOG_FORMAT = STATEMENT ;

CREATE TABLE t1 (f1 INTEGER, pk INTEGER               , PRIMARY KEY (pk));
CREATE TABLE t2 (f1 INTEGER, pk INTEGER AUTO_INCREMENT, PRIMARY KEY (pk));

DROP TRIGGER IF EXISTS tr1;
CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW INSERT INTO t2 (f1) VALUES (1) ;
DELETE FROM t1 WHERE f1 BETWEEN 76 AND 76;

DROP TRIGGER IF EXISTS tr1;
CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE t2 SET f1 = f1 + 1;
DELETE FROM t1 WHERE f1 BETWEEN 76 AND 76;

INSERT INTO t2 (f1) VALUES (1);
INSERT INTO t2 (f1) SELECT 1 UNION ALL SELECT 1 ;

Result on mysql-5.1-rep+3 revno: 3130 2010-01-05
================================================
...
SET SESSION BINLOG_FORMAT = STATEMENT ;
CREATE TABLE t1 (f1 INTEGER, pk INTEGER               , PRIMARY KEY (pk));
CREATE TABLE t2 (f1 INTEGER, pk INTEGER AUTO_INCREMENT, PRIMARY KEY (pk));
DROP TRIGGER IF EXISTS tr1;
Warnings:
Note	1360	Trigger does not exist
CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW INSERT INTO t2 (f1) VALUES (1) ;
DELETE FROM t1 WHERE f1 BETWEEN 76 AND 76;
Warnings:
Note	1592	Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statement updates two AUTO_INCREMENT columns. This is unsafe because the generated value cannot be predicted by slave.
   <== Why this?
       The DELETE might cause an execution of the DELETE trigger.
       This trigger INSERTs maybe several rows into a table with one 
       AUTOINCREMENT column.
          
DROP TRIGGER IF EXISTS tr1;
CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW UPDATE t2 SET f1 = f1 + 1;
DELETE FROM t1 WHERE f1 BETWEEN 76 AND 76;
Warnings:
Note	1592	Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Statement updates two AUTO_INCREMENT columns. This is unsafe because the generated value cannot be predicted by slave.
   <== "More" wrong.
       The DELETE trigger UPDATEs a non AUTOINCREMENT column.
       There is IMHO no reason to "generate" any AUTOINCREMENT values.

INSERT INTO t2 (f1) VALUES (1);
INSERT INTO t2 (f1) SELECT 1 UNION ALL SELECT 1 ;
   <== Interesting. Even an INSERT of two rows where
       the AUTOINCREMENT value has to be generated does
       not get criticized by the server. 

I will come up with additional information (affected releases) soon.

How to repeat:
See above

Suggested fix:
Please
- fix the server
  The behavior I met seems to be very inconsistent.
  Insert via trigger   versus   direct insert
  Update via trigger where no autoinc column is changed
or
- fix at least the server error message so that it
  is no more misleading
[11 Jan 2010 14:40] Matthias Leich
The reported effect does not exist in:
mysql-5.1-bugteam revno: 3309 2010-01-08
   5.1.42

The effect exists in:
mysql-next-mr revno: 2955 2010-01-06
   5.5.99-m3  
mysql-6.0-codebase-bugfixing revno: 3817 2010-01-08
   6.0.14-alpha

This means some important and not unlikely statements
which get executed without error or warning in
5.1.42 (mysql-5.1-bugteam) will earn a warning in future
MySQL releases.
In case these statements are really unsafe than we have
to live with this warning, but in case these statements
are not unsafe than the change in behavior is IMHO not
acceptable.
[28 Jan 2010 13:55] Andrei Elkin
Matthias, Omer.

Confirming that the warning makes sense even though it's just
one auto-inc table could be involved via a trigger or a stored
function.
Not in the particular case of the reported test though. Alas, to make
safety analysis to be utmost precise does not seem to be feasible
to implement. That's why such a pessimistic reaction.

This bug should be fixed with updating the warning message that
somehow left unattended at time of bug#45677 fixing.

Notice, that 5.1 does not issue the warning due to lack of fixes
incl ones of bug#45827 that are in 5.5.
[28 Jan 2010 15:19] Matthias Leich
Hi Andrei,
assuming that the statement is most probably unsafe
under the conditions it is executed than a warning
is a great improvement compared to older releases
without any warning.
I only insist in some non misleading explanation.
This means the text of the warning and most probably
also the manual should be adjusted.
[28 Jan 2010 17:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/98496

2977 Andrei Elkin	2010-01-28
      Bug #50192   Strange effect in replication test, trigger, auto_increment  
      
      The auto-inc unsafe warning makes sense even though it's just
      one auto-inc table could be involved via a trigger or a stored
      function.
      However its content was not updated by bug@45677 fixes that refined
      replication of auto_increment in stored routine semantics.
      
      Fixed with updating the error message, renaming the error and an internal unsafe-condition 
      constants.
      
      A documentation notice
      ======================
      
            Inserting into an autoincrement column in a stored function or a trigger
            is unsafe for replication.
            Even with just one autoincrement column, if the routine is invoked more than 
            once slave is not guranteed to execute the statement graph same way as 
            the master.
            And since it's impossible to estimate how many times a rotine can be invoked at 
            the query pre-execution phase (see lock_tables), the statement is marked
            pessimistically unsafe. 
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        results updated to include the expected unsafe warning.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        regression test for bug#50192 to diplaying the unsafe warning comes out to the user warning stack.
     @ sql/share/errmsg-utf8.txt
        Updating the auto-inc unsafe message to correspond to bug@45677 fixes' new sematics.
     @ sql/share/errmsg.txt
        Updating the auto-inc unsafe message to correspond to bug@45677 fixes' new sematics.
     @ sql/sql_base.cc
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics.
     @ sql/sql_lex.cc
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics.
     @ sql/sql_lex.h
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics
        and description comments.
[29 Jan 2010 11:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/98594

2977 Andrei Elkin	2010-01-29
      Bug #50192   Strange effect in replication test, trigger, auto_increment  
      
      The auto-inc unsafe warning makes sense even though it's just
      one auto-inc table could be involved via a trigger or a stored
      function.
      However its content was not updated by bug@45677 fixes continuing to mention
      two tables whereas the fixes refined semantics of replication of auto_increment 
      in stored routine.
      
      Fixed with updating the error message, renaming the error and an internal unsafe-condition 
      constants.
      
      A documentation notice
      ======================
      
            Inserting into an autoincrement column in a stored function or a trigger
            is unsafe for replication.
            Even with just one autoincrement column, if the routine is invoked more than 
            once slave is not guaranteed to execute the statement graph same way as 
            the master.
            And since it's impossible to estimate how many times a routine can be invoked at 
            the query pre-execution phase (see lock_tables), the statement is marked
            pessimistically unsafe. 
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        results updated to include the expected unsafe warning.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        regression test for bug#50192 to diplaying the unsafe warning comes out to the user warning stack.
     @ sql/share/errmsg-utf8.txt
        Updating the auto-inc unsafe message to correspond to bug@45677 fixes' new sematics.
     @ sql/share/errmsg.txt
        Updating the auto-inc unsafe message to correspond to bug@45677 fixes' new sematics.
     @ sql/sql_base.cc
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics.
     @ sql/sql_lex.cc
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics.
     @ sql/sql_lex.h
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics
        and description comments.
[29 Jan 2010 13:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/98624

2977 Andrei Elkin	2010-01-29
      Bug #50192   Strange effect in replication test, trigger, auto_increment  
      
      The auto-inc unsafe warning makes sense even though it's just
      one auto-inc table could be involved via a trigger or a stored
      function.
      However its content was not updated by bug@45677 fixes continuing to mention
      two tables whereas the fixes refined semantics of replication of auto_increment 
      in stored routine.
      
      Fixed with updating the error message, renaming the error and an internal unsafe-condition 
      constants.
      
      A documentation notice
      ======================
      
            Inserting into an autoincrement column in a stored function or a trigger
            is unsafe for replication.
            Even with just one autoincrement column, if the routine is invoked more than 
            once slave is not guaranteed to execute the statement graph same way as 
            the master.
            And since it's impossible to estimate how many times a routine can be invoked at 
            the query pre-execution phase (see lock_tables), the statement is marked
            pessimistically unsafe. 
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        results updated to include the expected unsafe warning.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        regression test for bug#50192 to diplaying the unsafe warning comes out to the user warning stack.
     @ sql/share/errmsg-utf8.txt
        Updating the auto-inc unsafe message to correspond to bug@45677 fixes' new sematics.
     @ sql/share/errmsg.txt
        Updating the auto-inc unsafe message to correspond to bug@45677 fixes' new sematics.
     @ sql/sql_base.cc
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics.
     @ sql/sql_lex.cc
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics.
     @ sql/sql_lex.h
        changing a symbolic name to correspond to updated by bug@45677 fixes new sematics
        and description comments.
[29 Jan 2010 19:30] Andrei Elkin
Pushed to next-mr-bugfixing.
[4 Feb 2010 1:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99189

2982 Luis Soares	2010-02-04
      Fixes result file for binlog_stm_mix_innodb_myisam which was
      left unchanged on patch for BUG#50192.
[13 Feb 2010 8:37] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100211135109-t63avry9fqpgyh78) (merge vers: 6.0.14-alpha) (pib:16)
[13 Feb 2010 8:39] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100213083327-cee4ao3jpg33eggv) (version source revid:luis.soares@sun.com-20100211135018-1f9dbghg0itszigo) (pib:16)
[13 Feb 2010 10:00] Jon Stephens
Documented in the 6.0.14 changelog as follows:

      The error message given when trying to replicate (using 
      statement-based mode) insertions into an autoincrement column by 
      a stored function or a trigger was improved.

Waiting for merge to 5.5.
[6 Mar 2010 11:06] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100213160132-nx1vlocxuta76txh) (merge vers: 5.5.99-m3) (pib:16)
[8 Mar 2010 19:59] Jon Stephens
Also documented in the 5.5.3 changelog. Closed.