Skip to content
  • Sujatha Sivakumar's avatar
    0b5b1aed
    Bug#21435502: DROP TABLE IF EXISTS MAY BRAKE REPLICATION IF · 0b5b1aed
    Sujatha Sivakumar authored
    SLAVE HAS REPLICATION FILTERS.
    
    Problem:
    =======
    If a replica has replication filters on a given database and
    DROP TABLE IF EXISTS is issued on a table that holds a key
    being used as Foreign Key by another table the statement
    will fail as expected with error:
    
    ERROR 1217 (23000): Cannot delete or update a parent row: a
    foreign key constraint fails
    
    however this will break replication on the replica with:
    
    Last_SQL_Error: Query caused different errors on master and
    slave. Error on master: message (format)='Cannot delete or
    update a parent row: a foreign key constraint fails' error
    code=1217 ; Error on slave: actual message='no error',
      error code=0. Default database: 'db1'. Query:
    'DROP TABLE IF EXISTS `table1` /* generated by server */'
    
    Two issues were reported in this bug.
    
    1) Failed query logged to binary log.
    
    2) When a query with expected error is received on slave
    side and it was skipped due to replication filters, then
    the error checking should not happen on slave side. i.e
    the comparison of expected error with actual error must
    not happen.
    
    Analysis and fix:
    =================
    
    Issue 1:
    =======
    
    We do binlog failed queries when the query can generate
    'partial results'.  When a list of tables are dropped as
    part of a single 'DROP TABLE' command and if it fails, it
    can generate partial results and in such cases we log it
    with an expected error into the binary log so that slave's
    state is also the same as that of the master.
    
    But when the DROP TABLE command is used to DROP a single
    table and if that command fails then the query cannot
    generate 'partial results'. In that case the query will
    not be written to the binary log.
    
    Issue 2:
    =======
    This was fixed as part of Bug#20797764 fix in MySQL-5.6.27.
    0b5b1aed
    Bug#21435502: DROP TABLE IF EXISTS MAY BRAKE REPLICATION IF
    Sujatha Sivakumar authored
    SLAVE HAS REPLICATION FILTERS.
    
    Problem:
    =======
    If a replica has replication filters on a given database and
    DROP TABLE IF EXISTS is issued on a table that holds a key
    being used as Foreign Key by another table the statement
    will fail as expected with error:
    
    ERROR 1217 (23000): Cannot delete or update a parent row: a
    foreign key constraint fails
    
    however this will break replication on the replica with:
    
    Last_SQL_Error: Query caused different errors on master and
    slave. Error on master: message (format)='Cannot delete or
    update a parent row: a foreign key constraint fails' error
    code=1217 ; Error on slave: actual message='no error',
      error code=0. Default database: 'db1'. Query:
    'DROP TABLE IF EXISTS `table1` /* generated by server */'
    
    Two issues were reported in this bug.
    
    1) Failed query logged to binary log.
    
    2) When a query with expected error is received on slave
    side and it was skipped due to replication filters, then
    the error checking should not happen on slave side. i.e
    the comparison of expected error with actual error must
    not happen.
    
    Analysis and fix:
    =================
    
    Issue 1:
    =======
    
    We do binlog failed queries when the query can generate
    'partial results'.  When a list of tables are dropped as
    part of a single 'DROP TABLE' command and if it fails, it
    can generate partial results and in such cases we log it
    with an expected error into the binary log so that slave's
    state is also the same as that of the master.
    
    But when the DROP TABLE command is used to DROP a single
    table and if that command fails then the query cannot
    generate 'partial results'. In that case the query will
    not be written to the binary log.
    
    Issue 2:
    =======
    This was fixed as part of Bug#20797764 fix in MySQL-5.6.27.
Loading