diff --git a/mysql-test/suite/rpl/r/bug93917.result b/mysql-test/suite/rpl/r/bug93917.result new file mode 100644 index 00000000000..7730383f38d --- /dev/null +++ b/mysql-test/suite/rpl/r/bug93917.result @@ -0,0 +1,172 @@ +# +# Bug #93917 "Wrong binlog entry for BLOB on a blackhole intermediary master" +# (https://bugs.mysql.com/bug.php?id=93917) +# +include/master-slave.inc +Warnings: +Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. +Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. +[connection master] +[connection slave] +[connection master] +******************************************************************************** +Testing 'TINYBLOB' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col TINYBLOB) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: TINYBLOB)] +DROP TABLE t1; +******************************************************************************** +Testing 'BLOB' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col BLOB) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: BLOB)] +DROP TABLE t1; +******************************************************************************** +Testing 'MEDIUMBLOB' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col MEDIUMBLOB) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: MEDIUMBLOB)] +DROP TABLE t1; +******************************************************************************** +Testing 'LONGBLOB' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col LONGBLOB) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: LONGBLOB)] +DROP TABLE t1; +******************************************************************************** +Testing 'TINYTEXT' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col TINYTEXT) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: TINYTEXT)] +DROP TABLE t1; +******************************************************************************** +Testing 'TEXT' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col TEXT) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: TEXT)] +DROP TABLE t1; +******************************************************************************** +Testing 'MEDIUMTEXT' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col MEDIUMTEXT) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: MEDIUMTEXT)] +DROP TABLE t1; +******************************************************************************** +Testing 'LONGTEXT' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col LONGTEXT) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: LONGTEXT)] +DROP TABLE t1; +******************************************************************************** +Testing 'VARCHAR(64)' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col VARCHAR(64)) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: VARCHAR(64))] +DROP TABLE t1; +******************************************************************************** +Testing 'VARBINARY(64)' data type +******************************************************************************** + +CREATE TABLE t1(id BIGINT, col VARBINARY(64)) ENGINE=InnoDB; +include/sync_slave_sql_with_master.inc +ALTER TABLE t1 ENGINE=Blackhole; +[connection master] +INSERT INTO t1 VALUES(1, 'testblob_1'); +include/sync_slave_sql_with_master.inc +[connection master] +UPDATE t1 SET col = 'blb' where ID = 1; +include/sync_slave_sql_with_master.inc +[connection master] +include/assert.inc [Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: VARBINARY(64))] +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/bug93917.test b/mysql-test/suite/rpl/t/bug93917.test new file mode 100644 index 00000000000..e4155708d50 --- /dev/null +++ b/mysql-test/suite/rpl/t/bug93917.test @@ -0,0 +1,71 @@ +--echo # +--echo # Bug #93917 "Wrong binlog entry for BLOB on a blackhole intermediary master" +--echo # (https://bugs.mysql.com/bug.php?id=93917) +--echo # + +--source include/have_innodb.inc +--source include/have_blackhole.inc +--source include/have_binlog_format_row.inc + +--source include/master-slave.inc + +--let $safe_load_data_dir = `SELECT @@global.secure_file_priv` +--let $server_1_binlog_record_file = $safe_load_data_dir/bug93917_server_1.txt +--let $server_2_binlog_record_file = $safe_load_data_dir/bug93917_server_2.txt + +--let $data_type_list = 'TINYBLOB', 'BLOB', 'MEDIUMBLOB', 'LONGBLOB', 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'VARCHAR(64)', 'VARBINARY(64)' +--let $list_sentinel = 'ABCDEF' +--let $number_of_data_types = `SELECT FIND_IN_SET($list_sentinel, CONCAT_WS(',', $data_type_list, $list_sentinel)) - 1` +--let $data_type_index = 1 + +--let $server_1_log_file = query_get_value(SHOW MASTER STATUS, File, 1) +--source include/rpl_connection_slave.inc +--let $server_2_log_file = query_get_value(SHOW MASTER STATUS, File, 1) +--source include/rpl_connection_master.inc + +while($data_type_index <= $number_of_data_types) +{ + --let $data_type = `SELECT ELT($data_type_index, $data_type_list)` + --echo ******************************************************************************** + --echo Testing '$data_type' data type + --echo ******************************************************************************** + --echo + + --eval CREATE TABLE t1(id BIGINT, col $data_type) ENGINE=InnoDB + + --source include/sync_slave_sql_with_master.inc + ALTER TABLE t1 ENGINE=Blackhole; + + --source include/rpl_connection_master.inc + INSERT INTO t1 VALUES(1, 'testblob_1'); + --let $server_1_start_pos = query_get_value(SHOW MASTER STATUS, Position, 1) + + --source include/sync_slave_sql_with_master.inc + --let $server_2_start_pos = query_get_value(SHOW MASTER STATUS, Position, 1) + + --source include/rpl_connection_master.inc + UPDATE t1 SET col = 'blb' where ID = 1; + --let $server_1_stop_pos = query_get_value(SHOW MASTER STATUS, Position, 1) + + --source include/sync_slave_sql_with_master.inc + --let $server_2_stop_pos = query_get_value(SHOW MASTER STATUS, Position, 1) + + --source include/rpl_connection_master.inc + + # there should be no overwritten "blbtblob_1" value in the Blackhole update rows binlog record + --exec $MYSQL_BINLOG -vv --base64-output=decode-rows $server_1_datadir/$server_1_log_file --start-position=$server_1_start_pos --stop-position=$server_1_stop_pos | grep ^### > $server_1_binlog_record_file + --exec $MYSQL_BINLOG -vv --base64-output=decode-rows $server_2_datadir/$server_2_log_file --start-position=$server_2_start_pos --stop-position=$server_2_stop_pos | grep ^### > $server_2_binlog_record_file + + --let $assert_text = Rows Update event records for both InnoDB (on server_1) and Blackhole (on server_2) should be identical (column type: $data_type) + --let $assert_cond = LOAD_FILE("$server_1_binlog_record_file") = LOAD_FILE("$server_2_binlog_record_file") + --source include/assert.inc + + --remove_file $server_1_binlog_record_file + --remove_file $server_2_binlog_record_file + + DROP TABLE t1; + + --inc $data_type_index +} + +--source include/rpl_end.inc diff --git a/sql/field.h b/sql/field.h index f2d00dee8e5..2e30482531b 100644 --- a/sql/field.h +++ b/sql/field.h @@ -3683,7 +3683,8 @@ protected: private: /** - In order to support update of virtual generated columns of blob type, + In order to support update of virtual generated columns and columns in + a Blackhole table of BLOB type, we need to allocate the space blob needs on server for old_row and new_row respectively. This variable is used to record the allocated blob space for old_row. @@ -3895,9 +3896,10 @@ public: /** Mark that the BLOB stored in value should be copied before updating it. - When updating virtual generated columns we need to keep the old - 'value' for BLOBs since this can be needed when the storage engine - does the update. During read of the record the old 'value' for the + When updating virtual generated columns or columns in a Blackhole table + we need to keep the old 'value' for BLOBs since this can be needed when + the storage engine does the update. + During read of the record the old 'value' for the BLOB is evaluated and stored in 'value'. This function is to be used to specify that we need to copy this BLOB 'value' into 'old_value' before we compute the new BLOB 'value'. For more information @see @@ -3905,12 +3907,6 @@ public: */ void set_keep_old_value(bool old_value_flag) { - /* - We should only need to keep a copy of the blob 'value' in the case - where this is a virtual genarated column (that is indexed). - */ - DBUG_ASSERT(is_virtual_gcol()); - /* If set to true, ensure that 'value' is copied to 'old_value' when keep_old_value() is called. @@ -3921,8 +3917,9 @@ public: /** Save the current BLOB value to avoid that it gets overwritten. - This is used when updating virtual generated columns that are - BLOBs. Some storage engines require that we have both the old and + This is used when updating virtual generated columns or columns in a + Blackhole table that are BLOBs. + Some storage engines require that we have both the old and new BLOB value for virtual generated columns that are indexed in order for the storage engine to be able to maintain the index. This function will transfer the buffer storing the current BLOB value @@ -3947,17 +3944,16 @@ public: old value for the BLOB and use table->record[0] to read the new value. + Similarly, in case of Blackhole "old" BLOB values are not read by + the storage engine and therefore 'Field_blob' is not made to point to the + engine's internal buffer. Therefore, in order to avoid "old" BLOB data + corruption, it also needs to be saved in 'old_value'. + This function must be called before we store the new BLOB value in this field object. */ void keep_old_value() { - /* - We should only need to keep a copy of the blob value in the case - where this is a virtual genarated column (that is indexed). - */ - DBUG_ASSERT(is_virtual_gcol()); - // Transfer ownership of the current BLOB value to old_value if (m_keep_old_value) { diff --git a/storage/blackhole/ha_blackhole.cc b/storage/blackhole/ha_blackhole.cc index acf85acf482..25e0b12d7eb 100644 --- a/storage/blackhole/ha_blackhole.cc +++ b/storage/blackhole/ha_blackhole.cc @@ -149,7 +149,53 @@ int ha_blackhole::rnd_next(uchar *buf) TRUE); THD *thd= ha_thd(); if (is_slave_applier(thd) && thd->query().str == NULL) + { + /* + Unlike a normal storage engine (e.g. 'InnoDB') in which + 'rnd_next()' overload actually reads all data in BLOB fields + from real storage into internal SE memory (like 'mem_heap' in InnoDB) + and updates packed representation ('table->record[0]') of the BLOB + field values with pointers to this internal SE memory, 'Blackhole' + engine does not do this. + + In case when a database with Blackhole tables serves just as an + intermediate binlog server in a replication chain, this may cause + data corruption. + + In particular, when 'Update_rows_log_event' is processed on a + Blackhole table, calling 'Update_rows_log_event::do_exec_row()' will + first copy Before Image (BI) found in 'record[0]' into 'record[1]' and + then unpack After Image (AI) into 'record[0]'. The problem is that this + record copying is shallow (just 'memcpy()') and for packed BLOB fields + it just copies pointer values. In other words, before calling + 'unpack_current_row()' we end up in a situation when packed BLOB field + values in 'record[0]' (AI) and 'record[1]' (BI) point to exactly the + same memory location and calling 'unpack_current_row()' for AI + overwrites BLOB data in BI. + + To prevent this we do the same trick as for virtual generated columns + in 5.7 - keeping old BLOB value inside 'old_value' field in + 'Field_blob' class by calling 'keep_old_value()' for all BLOB fields + currently marked for update in 'table->write_set'.. + */ + + if (table_share->blob_fields != 0) + for (Field **field_ptr= table->field; *field_ptr != NULL; ++field_ptr) + { + Field *current_field= *field_ptr; + if ((current_field->flags & BLOB_FLAG) != 0) + { + Field_blob* bfield= down_cast(current_field); + if (bfield->in_write_set()) + { + bfield->set_keep_old_value(true); + bfield->keep_old_value(); + } + } + } + rc= 0; + } else rc= HA_ERR_END_OF_FILE; MYSQL_READ_ROW_DONE(rc);