• Sachin's avatar
    MDEV-17614 INSERT on dup key update is replication unsafe · 284c72ea
    Sachin authored
    Problem:-
    When mysql executes INSERT ON DUPLICATE KEY INSERT, the storage engine checks
    if the inserted row would generate a duplicate key error. If yes, it returns
    the existing row to mysql, mysql updates it and sends it back to the storage
    engine.When the table has more than one unique or primary key, this statement
    is sensitive to the order in which the storage engines checks the keys.
    Depending on this order, the storage engine may determine different rows
    to mysql, and hence mysql can update different rows.The order that the
    storage engine checks keys is not deterministic. For example, InnoDB checks
    keys in an order that depends on the order in which indexes were added to
    the table. The first added index is checked first. So if master and slave
    have added indexes in different orders, then slave may go out of sync.
    
    Solution:-
    Make INSERT...ON DUPLICATE KEY UPDATE unsafe while using stmt or mixed format
    When there is more then one unique key.
    Although there is two exception.
      1. Auto Increment key is not counted because Innodb will get gap lock for
        failed Insert and concurrent insert will get a next increment value. But if
        user supplies auto inc value it can be unsafe.
      2. Count only unique keys for which insertion is performed.
    
    So this patch also addresses the bug id #72921
    284c72ea
rpl_unsafe_statements.test 6.13 KB