diff --git a/mysql-test/r/improved_dup_key_error.result b/mysql-test/r/improved_dup_key_error.result new file mode 100644 index 00000000000..7afc1b704ca --- /dev/null +++ b/mysql-test/r/improved_dup_key_error.result @@ -0,0 +1,11 @@ +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(pk INT PRIMARY KEY); +FLUSH LOGS; +INSERT INTO t1 VALUES(1); +FLUSH LOGS; +SET @start_improved_dup_key_error = @@global.improved_dup_key_error; +ERROR 1062 (23000) at line 29: Duplicate entry '1' for key 'PRIMARY' +SET @@global.improved_dup_key_error = 1; +ERROR 1062 (23000) at line 29: Duplicate entry '1' for key 't1.PRIMARY' [INSERT INTO t1 VALUES(1)] +SET @@global.improved_dup_key_error = @start_improved_dup_key_error; +DROP TABLE t1; diff --git a/mysql-test/r/mysqld--help-notwin-profiling.result b/mysql-test/r/mysqld--help-notwin-profiling.result index 4dde477ffe3..5dadbcc281d 100644 --- a/mysql-test/r/mysqld--help-notwin-profiling.result +++ b/mysql-test/r/mysqld--help-notwin-profiling.result @@ -382,6 +382,10 @@ The following options may be given as the first argument: Specifies a directory to add to the ignore list when collecting database names from the datadir. Put a blank argument to reset the list accumulated so far. + --improved-dup-key-error + Include the table name in the error text when receiving a + duplicate key error and log the query into a new + duplicate key query log file. --init-connect=name Command(s) that are executed for each new connection --init-file=name Read SQL commands from this file at startup --init-slave=name Command(s) that are executed by a slave server each time @@ -1784,6 +1788,7 @@ histogram-step-size-update-command 16ms hll-data-size-log2 14 host-cache-size 279 ignore-builtin-innodb FALSE +improved-dup-key-error FALSE init-connect init-file (No default value) init-slave diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index 7bb6947d6d0..029b3c95346 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -382,6 +382,10 @@ The following options may be given as the first argument: Specifies a directory to add to the ignore list when collecting database names from the datadir. Put a blank argument to reset the list accumulated so far. + --improved-dup-key-error + Include the table name in the error text when receiving a + duplicate key error and log the query into a new + duplicate key query log file. --init-connect=name Command(s) that are executed for each new connection --init-file=name Read SQL commands from this file at startup --init-slave=name Command(s) that are executed by a slave server each time @@ -1782,6 +1786,7 @@ histogram-step-size-update-command 16ms hll-data-size-log2 14 host-cache-size 279 ignore-builtin-innodb FALSE +improved-dup-key-error FALSE init-connect init-file (No default value) init-slave diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index 9dd2b633ea0..2900db7bc0b 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -336,5 +336,15 @@ SHOW WARNINGS; Level Code Message Error 1062 Duplicate entry '11' for key 'a' +set @save_improved_dup_key_error = @@global.improved_dup_key_error; +set @@global.improved_dup_key_error = 1; +SHOW TABLES WHERE f1(11) = 11; +ERROR 23000: Duplicate entry '11' for key 't1.a' [INSERT INTO t1 VALUES( NAME_CONST('x',11))] + +SHOW WARNINGS; +Level Code Message +Error 1062 Duplicate entry '11' for key 't1.a' [INSERT INTO t1 VALUES( NAME_CONST('x',11))] + +set @@global.improved_dup_key_error = @save_improved_dup_key_error; DROP TABLE t1; DROP FUNCTION f1; diff --git a/mysql-test/suite/sys_vars/r/improved_dup_key_error_basic.result b/mysql-test/suite/sys_vars/r/improved_dup_key_error_basic.result new file mode 100644 index 00000000000..a2cb55ed41c --- /dev/null +++ b/mysql-test/suite/sys_vars/r/improved_dup_key_error_basic.result @@ -0,0 +1,42 @@ +SET @start_improved_dup_key_error = @@global.improved_dup_key_error; +SELECT @start_improved_dup_key_error; +@start_improved_dup_key_error +0 +SET @@global.improved_dup_key_error = DEFAULT; +SELECT @@global.improved_dup_key_error; +@@global.improved_dup_key_error +0 +SET @@global.improved_dup_key_error = false; +SELECT @@global.improved_dup_key_error; +@@global.improved_dup_key_error +0 +SET @@global.improved_dup_key_error = true; +SELECT @@global.improved_dup_key_error; +@@global.improved_dup_key_error +1 +SET @@global.improved_dup_key_error = 1; +SELECT @@global.improved_dup_key_error; +@@global.improved_dup_key_error +1 +SET @@global.improved_dup_key_error = 0; +SELECT @@global.improved_dup_key_error; +@@global.improved_dup_key_error +0 +SET @@global.improved_dup_key_error = -1; +ERROR 42000: Variable 'improved_dup_key_error' can't be set to the value of '-1' +SELECT @@global.improved_dup_key_error; +@@global.improved_dup_key_error +0 +SET @@global.improved_dup_key_error = 100; +ERROR 42000: Variable 'improved_dup_key_error' can't be set to the value of '100' +SELECT @@global.improved_dup_key_error; +@@global.improved_dup_key_error +0 +SET @@session.improved_dup_key_error = 10; +ERROR HY000: Variable 'improved_dup_key_error' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@session.improved_dup_key_error; +ERROR HY000: Variable 'improved_dup_key_error' is a GLOBAL variable +SET @@global.improved_dup_key_error = @start_improved_dup_key_error; +SELECT @@global.improved_dup_key_error; +@@global.improved_dup_key_error +0 diff --git a/mysql-test/suite/sys_vars/t/improved_dup_key_error_basic.test b/mysql-test/suite/sys_vars/t/improved_dup_key_error_basic.test new file mode 100644 index 00000000000..4c3ae7a5979 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/improved_dup_key_error_basic.test @@ -0,0 +1,34 @@ +--source include/not_embedded.inc + +SET @start_improved_dup_key_error = @@global.improved_dup_key_error; +SELECT @start_improved_dup_key_error; + +SET @@global.improved_dup_key_error = DEFAULT; +SELECT @@global.improved_dup_key_error; + +SET @@global.improved_dup_key_error = false; +SELECT @@global.improved_dup_key_error; + +SET @@global.improved_dup_key_error = true; +SELECT @@global.improved_dup_key_error; + +SET @@global.improved_dup_key_error = 1; +SELECT @@global.improved_dup_key_error; + +SET @@global.improved_dup_key_error = 0; +SELECT @@global.improved_dup_key_error; + +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.improved_dup_key_error = -1; +SELECT @@global.improved_dup_key_error; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.improved_dup_key_error = 100; +SELECT @@global.improved_dup_key_error; + +--ERROR ER_GLOBAL_VARIABLE +SET @@session.improved_dup_key_error = 10; +--ERROR ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.improved_dup_key_error; + +SET @@global.improved_dup_key_error = @start_improved_dup_key_error; +SELECT @@global.improved_dup_key_error; diff --git a/mysql-test/t/improved_dup_key_error.test b/mysql-test/t/improved_dup_key_error.test new file mode 100644 index 00000000000..6801831c338 --- /dev/null +++ b/mysql-test/t/improved_dup_key_error.test @@ -0,0 +1,37 @@ +--source include/have_log_bin.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +# Create a table +CREATE TABLE t1(pk INT PRIMARY KEY); +# Flush CREATE TABLE to binlog and rotate to a new binlog +FLUSH LOGS; + +# Insert some data and flush that to the binlog +INSERT INTO t1 VALUES(1); +--let $BINLOG= query_get_value(SHOW MASTER STATUS, File, 1) +FLUSH LOGS; + +# Save current value of variable +SET @start_improved_dup_key_error = @@global.improved_dup_key_error; + +# Attempt to insert duplicate keys via piping from mysqlbinlog +# without the variable set +--let $MYSQLD_DATADIR = `select @@datadir` +--let $OUTPUT_FILE = $MYSQLD_DATADIR/errors.txt +--error 1 +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG | $MYSQL 2>$OUTPUT_FILE +--exec cat $OUTPUT_FILE + +# Now turn on the variable and try again +SET @@global.improved_dup_key_error = 1; +--error 1 +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$BINLOG | $MYSQL 2>$OUTPUT_FILE +--exec cat $OUTPUT_FILE +--exec rm $OUTPUT_FILE + +SET @@global.improved_dup_key_error = @start_improved_dup_key_error; + +DROP TABLE t1; diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index 5569776904f..553dc05df6a 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -266,5 +266,19 @@ SHOW WARNINGS; --echo +set @save_improved_dup_key_error = @@global.improved_dup_key_error; +set @@global.improved_dup_key_error = 1; + +--error ER_DUP_ENTRY +SHOW TABLES WHERE f1(11) = 11; + +--echo + +SHOW WARNINGS; + +--echo + +set @@global.improved_dup_key_error = @save_improved_dup_key_error; + DROP TABLE t1; DROP FUNCTION f1; diff --git a/sql/handler.cc b/sql/handler.cc index f953580d1fd..da98667b00f 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3836,19 +3836,23 @@ void handler::ha_release_auto_increment() @param msg Error message template to which key value should be added. @param errflag Flags for my_error() call. + @param query The query that caused the error + @param org_table_name The original table name (if any) */ -void print_keydup_error(TABLE *table, KEY *key, const char *msg, myf errflag) +void print_keydup_error(TABLE *table, KEY *key, const char *msg, myf errflag, + const THD *thd, const char *org_table_name) { /* Write the duplicated key in the error message */ char key_buff[MAX_KEY_LENGTH]; + std::string key_name; String str(key_buff,sizeof(key_buff),system_charset_info); if (key == NULL) { /* Key is unknown */ + key_name = "*UNKNOWN*"; str.copy("", 0, system_charset_info); - my_printf_error(ER_DUP_ENTRY, msg, errflag, str.c_ptr(), "*UNKNOWN*"); } else { @@ -3860,8 +3864,40 @@ void print_keydup_error(TABLE *table, KEY *key, const char *msg, myf errflag) str.length(max_length-4); str.append(STRING_WITH_LEN("...")); } - my_printf_error(ER_DUP_ENTRY, msg, errflag, str.c_ptr_safe(), key->name); + + if (opt_improved_dup_key_error) + { + if (org_table_name != nullptr) + key_name = org_table_name; + else + key_name = table->s->table_name.str; + + key_name += "."; + } + + key_name += key->name; } + + if (opt_improved_dup_key_error) + { + // Replace any newlines in the query with spaces + const char *query_str = thd->query(); + std::string query = query_str != nullptr ? query_str : ""; + std::transform(query.begin(), query.end(), query.begin(), + [](std::string::value_type ch) { return ch == '\n' ? ' ' : ch; }); + + // Add " []" (or at least the first 512 bytes) to the format + // Note: The total error message buffer size is only 512 bytes, so some + // of the query string could be cut off. We don't really care - + // we are just trying to get as much as the query string as + // we can. + std::string msg_fmt = std::string(msg) + " [%-.512s]"; + + my_printf_error(ER_DUP_ENTRY, msg_fmt.c_str(), errflag, str.c_ptr(), + key_name.c_str(), query.c_str()); + } + else + my_printf_error(ER_DUP_ENTRY, msg, errflag, str.c_ptr(), key_name.c_str()); } @@ -3869,12 +3905,14 @@ void print_keydup_error(TABLE *table, KEY *key, const char *msg, myf errflag) Construct and emit duplicate key error message using information from table's record buffer. - @sa print_keydup_error(table, key, msg, errflag). + @sa print_keydup_error(table, key, msg, errflag, thd, org_table_name). */ -void print_keydup_error(TABLE *table, KEY *key, myf errflag) +void print_keydup_error(TABLE *table, KEY *key, myf errflag, + const THD *thd, const char *org_table_name) { - print_keydup_error(table, key, ER(ER_DUP_ENTRY_WITH_KEY_NAME), errflag); + print_keydup_error(table, key, ER(ER_DUP_ENTRY_WITH_KEY_NAME), errflag, + thd, org_table_name); } @@ -3924,7 +3962,7 @@ void handler::print_error(int error, myf errflag) { print_keydup_error(table, key_nr == MAX_KEY ? NULL : &table->key_info[key_nr], - errflag); + errflag, ha_thd()); DBUG_VOID_RETURN; } textno=ER_DUP_KEY; diff --git a/sql/handler.h b/sql/handler.h index a74ece4dbb1..84fbd205683 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -3763,8 +3763,10 @@ inline const char *table_case_name(HA_CREATE_INFO *info, const char *name) return ((lower_case_table_names == 2 && info->alias) ? info->alias : name); } -void print_keydup_error(TABLE *table, KEY *key, const char *msg, myf errflag); -void print_keydup_error(TABLE *table, KEY *key, myf errflag); +void print_keydup_error(TABLE *table, KEY *key, const char *msg, myf errflag, + const THD *thd, const char *org_table_name = nullptr); +void print_keydup_error(TABLE *table, KEY *key, myf errflag, + const THD *thd, const char *org_table_name = nullptr); void warn_fk_constraint_violation(THD *thd, TABLE *table, int error); #endif /* HANDLER_INCLUDED */ diff --git a/sql/mysqld.cc b/sql/mysqld.cc index c1802e80b89..5c333373077 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -464,6 +464,7 @@ my_bool opt_log_queries_not_using_indexes= 0; my_bool opt_disable_working_set_size = 0; ulong opt_log_throttle_queries_not_using_indexes= 0; ulong opt_log_throttle_legacy_user= 0; +bool opt_improved_dup_key_error= 0; bool opt_error_log= IF_WIN(1,0); bool opt_disable_networking=0, opt_skip_show_db=0; bool opt_skip_name_resolve=0; diff --git a/sql/mysqld.h b/sql/mysqld.h index a55871f2499..28412e2ebfb 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -272,6 +272,7 @@ extern const char* mysql_compression_lib_names[3]; extern MY_BITMAP temp_pool; extern bool opt_large_files, server_id_supplied; extern bool opt_update_log, opt_bin_log, opt_error_log; +extern bool opt_improved_dup_key_error; extern my_bool opt_log, opt_slow_log, opt_log_raw; extern char* opt_gap_lock_logname; extern my_bool opt_allow_multiple_engines; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 2170e83e618..f90310103af 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -9615,7 +9615,7 @@ copy_data_between_tables(TABLE *from,TABLE *to, err_msg= ER(ER_DUP_ENTRY_AUTOINCREMENT_CASE); print_keydup_error(to, key_nr == MAX_KEY ? NULL : &to->key_info[key_nr], - err_msg, MYF(0)); + err_msg, MYF(0), thd, from->s->table_name.str); } else to->file->print_error(error, MYF(0)); diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 05f1af222be..498bdd12344 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -5839,3 +5839,10 @@ static Sys_var_mybool Sys_session_track_state_change( NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(update_session_track_state_change)); + +static Sys_var_mybool Sys_improved_dup_key_error( + "improved_dup_key_error", + "Include the table name in the error text when receiving a duplicate " + "key error and log the query into a new duplicate key query log file.", + GLOBAL_VAR(opt_improved_dup_key_error), + CMD_LINE(OPT_ARG), DEFAULT(FALSE)); diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h index fdedcd0390d..37bf76d1387 100644 --- a/storage/innobase/handler/ha_innodb.h +++ b/storage/innobase/handler/ha_innodb.h @@ -56,6 +56,8 @@ typedef struct st_innobase_share { /** Prebuilt structures in an InnoDB table handle used within MySQL */ struct row_prebuilt_t; +struct ha_innobase_inplace_ctx; + /** The class defining a handle to an Innodb table */ class ha_innobase: public handler { @@ -304,6 +306,14 @@ class ha_innobase: public handler TABLE* altered_table, Alter_inplace_info* ha_alter_info, bool commit); + inline MY_ATTRIBUTE((nonnull, warn_unused_result)) + bool commit_try_rebuild( + Alter_inplace_info* ha_alter_info, + ha_innobase_inplace_ctx*ctx, + TABLE* altered_table, + const TABLE* old_table, + trx_t* trx, + const char* table_name); /** @} */ bool check_if_incompatible_data(HA_CREATE_INFO *info, uint table_changes); diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 2e919ba41d0..21b407e2d88 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -4028,7 +4028,7 @@ oom: dup_key = &ha_alter_info->key_info_buffer[ prebuilt->trx->error_key_num]; } - print_keydup_error(altered_table, dup_key, MYF(0)); + print_keydup_error(altered_table, dup_key, MYF(0), user_thd); break; case DB_ONLINE_LOG_TOO_BIG: DBUG_ASSERT(ctx->online); @@ -4877,7 +4877,7 @@ when rebuilding the table. */ inline MY_ATTRIBUTE((nonnull, warn_unused_result)) bool -commit_try_rebuild( +ha_innobase::commit_try_rebuild( /*===============*/ Alter_inplace_info* ha_alter_info, ha_innobase_inplace_ctx*ctx, @@ -4953,7 +4953,9 @@ commit_try_rebuild( dup_key = &ha_alter_info ->key_info_buffer[err_key]; } - print_keydup_error(altered_table, dup_key, MYF(0)); + print_keydup_error(altered_table, dup_key, MYF(0), + user_thd, + old_table->s->table_name.str); DBUG_RETURN(true); case DB_ONLINE_LOG_TOO_BIG: my_error(ER_INNODB_ONLINE_LOG_TOO_BIG, MYF(0), diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 64600aeeeb3..6b7860e81f3 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -10260,7 +10260,7 @@ int ha_rocksdb::inplace_populate_sk( print_keydup_error(new_table_arg, &new_table_arg->key_info[index->get_keyno()], - MYF(0)); + MYF(0), ha_thd()); DBUG_RETURN(ER_DUP_ENTRY); } }