diff --git a/mysql-test/suite/rpl/r/rpl_row_img_sanity.result b/mysql-test/suite/rpl/r/rpl_row_img_sanity.result index 00ee2d0..e2f4c1c 100644 --- a/mysql-test/suite/rpl/r/rpl_row_img_sanity.result +++ b/mysql-test/suite/rpl/r/rpl_row_img_sanity.result @@ -639,6 +639,73 @@ DROP TABLE t; include/wait_for_slave_sql_error.inc [errno=1032] DROP TABLE t; include/rpl_reset.inc +CON: 'master', IMG: 'FULL', RESTART SLAVE: 'N' +SET SESSION binlog_row_image= 'FULL'; +SET GLOBAL binlog_row_image= 'FULL'; +FLUSH TABLES; +SHOW VARIABLES LIKE 'binlog_row_image'; +Variable_name Value +binlog_row_image FULL +CON: 'slave', IMG: 'FULL', RESTART SLAVE: 'Y' +SET SESSION binlog_row_image= 'FULL'; +SET GLOBAL binlog_row_image= 'FULL'; +include/stop_slave.inc +include/start_slave.inc +FLUSH TABLES; +SHOW VARIABLES LIKE 'binlog_row_image'; +Variable_name Value +binlog_row_image FULL +CREATE TABLE t1(id INT PRIMARY KEY, a INT) ENGINE = INNODB; +include/sync_slave_sql_with_master.inc +INSERT INTO t1 (id, a) VALUES (1, 1); +"Case: FULL - EXPLAIN output should not display Using temporary" +EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 const 1 Using where +UPDATE t1 SET a=a+1 WHERE id < 2; +CON: 'master', IMG: 'NOBLOB', RESTART SLAVE: 'N' +SET SESSION binlog_row_image= 'NOBLOB'; +SET GLOBAL binlog_row_image= 'NOBLOB'; +FLUSH TABLES; +SHOW VARIABLES LIKE 'binlog_row_image'; +Variable_name Value +binlog_row_image NOBLOB +CON: 'slave', IMG: 'NOBLOB', RESTART SLAVE: 'Y' +SET SESSION binlog_row_image= 'NOBLOB'; +SET GLOBAL binlog_row_image= 'NOBLOB'; +include/stop_slave.inc +include/start_slave.inc +FLUSH TABLES; +SHOW VARIABLES LIKE 'binlog_row_image'; +Variable_name Value +binlog_row_image NOBLOB +"Case: NOBLOB - EXPLAIN output should not display Using temporary" +EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 const 1 Using where +UPDATE t1 SET a=a+1 WHERE id < 2; +CON: 'master', IMG: 'MINIMAL', RESTART SLAVE: 'N' +SET SESSION binlog_row_image= 'MINIMAL'; +SET GLOBAL binlog_row_image= 'MINIMAL'; +FLUSH TABLES; +SHOW VARIABLES LIKE 'binlog_row_image'; +Variable_name Value +binlog_row_image MINIMAL +CON: 'slave', IMG: 'MINIMAL', RESTART SLAVE: 'Y' +SET SESSION binlog_row_image= 'MINIMAL'; +SET GLOBAL binlog_row_image= 'MINIMAL'; +include/stop_slave.inc +include/start_slave.inc +FLUSH TABLES; +SHOW VARIABLES LIKE 'binlog_row_image'; +Variable_name Value +binlog_row_image MINIMAL +EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 const 1 Using where +UPDATE t1 SET a=a+1 WHERE id < 2; +DROP TABLE t1; +include/sync_slave_sql_with_master.inc SET GLOBAL binlog_row_image= @old_binlog_row_image; SET SESSION binlog_row_image= @old_binlog_row_image; SET GLOBAL binlog_row_image= @old_binlog_row_image; diff --git a/mysql-test/suite/rpl/t/rpl_row_img_sanity.test b/mysql-test/suite/rpl/t/rpl_row_img_sanity.test index af60b0f..25dfde1 100644 --- a/mysql-test/suite/rpl/t/rpl_row_img_sanity.test +++ b/mysql-test/suite/rpl/t/rpl_row_img_sanity.test @@ -806,10 +806,73 @@ let $slave_sql_errno= 1032; source include/wait_for_slave_sql_error.inc; DROP TABLE t; --let $rpl_only_running_threads= 1 + +# ==== Purpose ==== +# +# Check that when binlog_row_image= FULL 'UPDATE' query should not using +# temporary if the PRIMARY KEY not being modified as part of the query. +# +# ==== Implementation ==== +# +# Set binlog_row_image= FULL. Create a table which has both a primary key and +# a regular int field which is not a key. Execute an UPDATE statement in such +# a way that it doesn't update the primary key field. See the 'EXPLAIN' output +# it should not use a temporary table. Repeat the same test in case of +# binlog_row_image= NOBLOB as well. No temporary table should be used in this +# case as well. +# +# ==== References ==== +# +# Bug#79867: UNNECESSARY USING TEMPORARY FOR UPDATE +# +############################################################################### -- source include/rpl_reset.inc +-- connection master +-- let $row_img_set=master:FULL:N,slave:FULL:Y +-- source include/rpl_row_img_set.inc +CREATE TABLE t1(id INT PRIMARY KEY, a INT) ENGINE = INNODB; -## CLEAN UP +--source include/sync_slave_sql_with_master.inc + +-- connection master +-- let $row_img_query= INSERT INTO t1 (id, a) VALUES (1, 1) +-- let $row_img_expected_master= | 1:1 2:1 +-- let $row_img_expected_slave = | 1:1 2:1 +-- source include/rpl_row_img_parts_master_slave.inc + +-- echo "Case: FULL - EXPLAIN output should not display Using temporary" +EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2; + +-- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2 +-- let $row_img_expected_master= 1:1 2:1 | 1:1 2:2 +-- let $row_img_expected_slave = 1:1 2:1 | 1:1 2:2 +-- source include/rpl_row_img_parts_master_slave.inc +-- let $row_img_set=master:NOBLOB:N,slave:NOBLOB:Y +-- source include/rpl_row_img_set.inc + +-- echo "Case: NOBLOB - EXPLAIN output should not display Using temporary" +EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2; + +-- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2 +-- let $row_img_expected_master= 1:1 2:2 | 1:1 2:3 +-- let $row_img_expected_slave = 1:1 2:2 | 1:1 2:3 +-- source include/rpl_row_img_parts_master_slave.inc + +-- let $row_img_set=master:MINIMAL:N,slave:MINIMAL:Y +-- source include/rpl_row_img_set.inc + +EXPLAIN UPDATE t1 SET a=a+1 WHERE id < 2; + +-- let $row_img_query= UPDATE t1 SET a=a+1 WHERE id < 2 +-- let $row_img_expected_master= 1:1 | 2:4 +-- let $row_img_expected_slave = 1:1 | 2:4 +-- source include/rpl_row_img_parts_master_slave.inc + +DROP TABLE t1; +--source include/sync_slave_sql_with_master.inc + +## CLEAN UP -- connection master SET GLOBAL binlog_row_image= @old_binlog_row_image; SET SESSION binlog_row_image= @old_binlog_row_image; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index dcda626..fd6780a 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -452,7 +452,7 @@ int mysql_update(THD *thd, /* Update the table->file->stats.records number */ table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); - table->mark_columns_needed_for_update(); + table->mark_columns_needed_for_update(false/*mark_binlog_columns=false*/); select= make_select(table, 0, 0, conds, 0, &error); { // Enter scope for optimizer trace wrapper @@ -530,7 +530,7 @@ int mysql_update(THD *thd, #ifdef WITH_PARTITION_STORAGE_ENGINE used_key_is_modified|= partition_key_modified(table, table->write_set); #endif - + table->mark_columns_per_binlog_row_image(); using_filesort= order && (need_sort||used_key_is_modified); if (thd->lex->describe) { @@ -1859,12 +1859,12 @@ multi_update::initialize_tables(JOIN *join) { if (safe_update_on_fly(thd, join->join_tab, table_ref, all_tables)) { - table->mark_columns_needed_for_update(); + table->mark_columns_needed_for_update(true/*mark_binlog_columns=true*/); table_to_update= table; // Update table on the fly continue; } } - table->mark_columns_needed_for_update(); + table->mark_columns_needed_for_update(true/*mark_binlog_columns=true*/); /* enable uncacheable flag if we update a view with check option diff --git a/sql/table.cc b/sql/table.cc index 3d87ee9..bdd176b 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5344,12 +5344,14 @@ void TABLE::mark_columns_needed_for_delete() @brief Mark columns needed for doing an update of a row + @param mark_binlog_columns if true, mark columns as per binlog_row_image + requirements. @details Some engines needs to have all columns in an update (to be able to build a complete row). If this is the case, we mark all not updated columns to be read. - If this is no the case, we do like in the delete case and mark + If this is not the case, we do like in the delete case and mark if neeed, either the primary key column or all columns to be read. (see mark_columns_needed_for_delete() for details) @@ -5357,17 +5359,30 @@ void TABLE::mark_columns_needed_for_delete() mark all USED key columns as 'to-be-read'. This allows the engine to loop over the given record to find all changed keys and doesn't have to retrieve the row again. - + Unlike other similar methods, it doesn't mark fields used by triggers, that is the responsibility of the caller to do, by using Table_triggers_list::mark_used_fields(TRG_EVENT_UPDATE)! + + Note: Marking additional columns as per binlog_row_image requirements will + influence query execution plan. For example in the case of + binlog_row_image=FULL the entire read_set and write_set needs to be flagged. + This will influence update query to think that 'used key is being modified' + and query will create a temporary table to process the update operation. + Which will result in performance degradation. Hence callers who don't want + their query execution to be influenced as per binlog_row_image requirements + can skip marking binlog specific columns here and they should make an + explicit call to 'mark_columns_per_binlog_row_image()' function to mark + binlog_row_image specific columns. */ -void TABLE::mark_columns_needed_for_update() +void TABLE::mark_columns_needed_for_update(bool mark_binlog_columns) { DBUG_ENTER("mark_columns_needed_for_update"); - mark_columns_per_binlog_row_image(); + + if (mark_binlog_columns) + mark_columns_per_binlog_row_image(); if (file->ha_table_flags() & HA_REQUIRES_KEY_COLUMNS_FOR_DELETE) { /* Mark all used key columns for read */ diff --git a/sql/table.h b/sql/table.h index ea790ec..43e9124 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1,7 +1,7 @@ #ifndef TABLE_INCLUDED #define TABLE_INCLUDED -/* Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. +/* Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -1216,7 +1216,7 @@ public: void mark_columns_used_by_index_no_reset(uint index, MY_BITMAP *map); void mark_columns_used_by_index(uint index); void mark_auto_increment_column(void); - void mark_columns_needed_for_update(void); + void mark_columns_needed_for_update(bool mark_binlog_columns); void mark_columns_needed_for_delete(void); void mark_columns_needed_for_insert(void); void mark_columns_per_binlog_row_image(void);