From 284c72eacf2074bf50c1930d5e8eb333367eba2f Mon Sep 17 00:00:00 2001 From: Sachin Date: Wed, 17 Jul 2019 15:56:29 +0530 Subject: MDEV-17614 INSERT on dup key update is replication unsafe 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 --- sql/sql_class.h | 40 ++++++++++++++++++++++++++-------------- 1 file changed, 26 insertions(+), 14 deletions(-) (limited to 'sql/sql_class.h') diff --git a/sql/sql_class.h b/sql/sql_class.h index e3bc572ed0c..8a4d8ff06a3 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2208,6 +2208,20 @@ public: /* container for handler's private per-connection data */ Ha_data ha_data[MAX_HA]; + /** + Bit field for the state of binlog warnings. + + The first Lex::BINLOG_STMT_UNSAFE_COUNT bits list all types of + unsafeness that the current statement has. + + This must be a member of THD and not of LEX, because warnings are + detected and issued in different places (@c + decide_logging_format() and @c binlog_query(), respectively). + Between these calls, the THD->lex object may change; e.g., if a + stored routine is invoked. Only THD persists between the calls. + */ + uint32 binlog_unsafe_warning_flags; + #ifndef MYSQL_CLIENT binlog_cache_mngr * binlog_setup_trx_data(); @@ -2317,20 +2331,6 @@ private: */ enum_binlog_format current_stmt_binlog_format; - /** - Bit field for the state of binlog warnings. - - The first Lex::BINLOG_STMT_UNSAFE_COUNT bits list all types of - unsafeness that the current statement has. - - This must be a member of THD and not of LEX, because warnings are - detected and issued in different places (@c - decide_logging_format() and @c binlog_query(), respectively). - Between these calls, the THD->lex object may change; e.g., if a - stored routine is invoked. Only THD persists between the calls. - */ - uint32 binlog_unsafe_warning_flags; - /* Number of outstanding table maps, i.e., table maps in the transaction cache. @@ -3939,6 +3939,18 @@ public: } void leave_locked_tables_mode(); int decide_logging_format(TABLE_LIST *tables); + /* + In Some cases when decide_logging_format is called it does not have all + information to decide the logging format. So that cases we call decide_logging_format_2 + at later stages in execution. + One example would be binlog format for IODKU but column with unique key is not inserted. + We dont have inserted columns info when we call decide_logging_format so on later stage we call + decide_logging_format_low + + @returns 0 if no format is changed + 1 if there is change in binlog format + */ + int decide_logging_format_low(TABLE *table); enum need_invoker { INVOKER_NONE=0, INVOKER_USER, INVOKER_ROLE}; void binlog_invoker(bool role) { m_binlog_invoker= role ? INVOKER_ROLE : INVOKER_USER; } -- cgit v1.2.1