| Bug #73936 | If the storage engine supports RBR, unsafe SQL statementes end up in binlog | ||
|---|---|---|---|
| Submitted: | 16 Sep 2014 16:50 | Modified: | 22 Jan 2019 15:35 |
| Reporter: | Santosh Praneeth Banda | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.6.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 Jul 2018 9:34]
MySQL Verification Team
Hi, Yes, it works just like this. Verifying the bug. Thanks for reporting it. If we are going to change the behavior or just better document it I can't say at this point. all best Bogdan
[22 Jan 2019 15:35]
Margaret Fisher
Posted by developer: Changelog entry added for MySQL 8.0.15: If a storage engine has the capability to log in STATEMENT format but not in ROW format, when binlog_format is set to STATEMENT, an unsafe SQL statement should be logged and a warning message should be written to the error log. However, such statements were instead not executed and an error message was written to the error log, which is the correct behavior when binlog_format is set to MIXED or ROW. The issue has now been corrected so that unsafe statements are logged with a warning as expected when binlog_format is set to STATEMENT.
Description: If the storage engine supports RBR, we continue to inject unsafe SQL statements in to the binlog. In these cases a warning is logged in the error log, but the behavior is completely different when storage engine doesn't support RBR. In these cases, mysql hits the following when running unsafe SQL statements "Error 'Cannot execute statement: impossible to write to binary log since statement is unsafe, storage engine is limited to statement-based logging, and BINLOG_FORMAT = MIXED. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe' on query". This is basically confusing and i except both the cases behave similarly. I am using this mtr test case. source include/master-slave.inc; source include/have_binlog_format_statement.inc; connection master; create table t1 (a text) engine=myisam; insert into t1 (a) values(uuid()); drop table t1; sync_slave_with_master; source include/rpl_end.inc; and a small patch to test --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -641,7 +641,7 @@ void _mi_report_crashed(MI_INFO *file, const char *message, ha_myisam::ha_myisam(handlerton *hton, TABLE_SHARE *table_arg) :handler(hton, table_arg), file(0), int_table_flags(HA_NULL_IN_KEY | HA_CAN_FULLTEXT | HA_CAN_SQL_HANDLER | - HA_BINLOG_ROW_CAPABLE | HA_BINLOG_STMT_CAPABLE | + HA_BINLOG_STMT_CAPABLE | HA_DUPLICATE_POS | HA_CAN_INDEX_BLOBS | HA_AUTO_PART_KEY | HA_FILE_BASED | HA_CAN_GEOMETRY | HA_NO_TRANSACTIONS | HA_CAN_INSERT_DELAYED | HA_CAN_BIT_FIELD | HA_CAN_RTREEKEYS | Change the storage engine used in the test case to innodb and the test fails with a different error (a warning in the error log) How to repeat: see description Suggested fix: don't know