| Bug #116035 | with parallel instant ddl and select INFORMATION_SCHEMA will show Duplicate row | ||
|---|---|---|---|
| Submitted: | 9 Sep 2024 1:55 | Modified: | 11 Sep 2024 12:55 |
| Reporter: | zongyi chen (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | instant ddl | ||
[9 Sep 2024 17:21]
MySQL Verification Team
Hi, Not sure why is this a bug, the entries have same table_id so the values are not wrong.. the fact that in some edge case i_s table with do this I would not consider a bug. I_S tables are made to be as fast as possible and as light on the system as possible. How would this behavior affect you badly?
[10 Sep 2024 2:54]
zongyi chen
Stable reproduction：
change two files:
1.
diff --git a/sql/dd/impl/raw/raw_record.cc b/sql/dd/impl/raw/raw_record.cc
index a773e18..c66543f 100644
--- a/sql/dd/impl/raw/raw_record.cc
+++ b/sql/dd/impl/raw/raw_record.cc
@@ -40,6 +40,8 @@
#include "sql/tztime.h" // Time_zone_offset
#include "sql_string.h"
#include "template_utils.h"
+#include "sql/debug_sync.h"
+#include "sql/current_thd.h"
namespace dd {
@@ -98,6 +100,7 @@ bool Raw_record::update() {
bool Raw_record::drop() {
DBUG_TRACE;
+ DEBUG_SYNC(current_thd, "before_delete_dd");
int rc = m_table->file->ha_delete_row(m_table->record[1]);
if (rc) {
2.
diff --git a/storage/innobase/dict/dict0dd.cc b/storage/innobase/dict/dict0dd.cc
index ca5312a..a7a95e1 100644
--- a/storage/innobase/dict/dict0dd.cc
+++ b/storage/innobase/dict/dict0dd.cc
@@ -5551,6 +5551,17 @@ const char *dd_process_dd_tables_rec_and_mtr_commit(
mtr_commit(mtr);
THD *thd = current_thd;
+ dict_table_t *ib_table;
+ const auto hash_value = ut::hash_uint64(table_id);
+
+ HASH_SEARCH(id_hash, dict_sys->table_id_hash, hash_value, dict_table_t *,
+ ib_table, ut_ad(ib_table->cached), ib_table->id == table_id);
+
+ if (ib_table != nullptr && ib_table->name.m_name[0] == 't' && ib_table->name.m_name[5] == 't') {
+ DEBUG_SYNC(thd, "before_first_open");
+ sleep(5);
+ }
+
*table = dd_table_open_on_id(table_id, thd, mdl, true, false);
if (!(*table)) {
two conn
1.
use test;
drop table t1;
CREATE TABLE t1 (a int);
set debug_sync = 'before_delete_dd wait_for go';
alter table t1 add column b int;
2.
set debug_sync = 'before_first_open SIGNAL go';
SELECT TABLE_ID,NAME,N_COLS,ROW_FORMAT,INSTANT_COLS,TOTAL_ROW_VERSIONS,64-TOTAL_ROW_VERSIONS AS "REMAINING_copy_DDLs" FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name like "%t1%";
[10 Sep 2024 3:02]
zongyi chen
it is not affect me yet,but maybe someone who use select count(*) from i_s will be affect. maybe we can use a set to deal this issue.
Description: as topics say How to repeat: 1.sql in mysql: drop table t1; CREATE TABLE IF NOT EXISTS `t1` (`a` int); 2.write sql in bash file(vim instant_simple.sh): /home/czy/open_8037/mysql/bin/mysql -u root -h 127.0.0.1 -P 8037 -f -Dtest -e" alter table t1 add column b int,algorithm=instant; SELECT TABLE_ID,NAME,N_COLS,ROW_FORMAT,INSTANT_COLS,TOTAL_ROW_VERSIONS,64-TOTAL_ROW_VERSIONS AS \"REMAINING_copy_DDLs\" FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name like \"%t1%\" ORDER BY TOTAL_ROW_VERSIONS DESC;" >>tmp5.log 2>&1 & /home/czy/open_8037/mysql/bin/mysql -u root -h 127.0.0.1 -P 8037 -f -Dtest -e" alter table t1 drop column b,algorithm=instant; SELECT TABLE_ID,NAME,N_COLS,ROW_FORMAT,INSTANT_COLS,TOTAL_ROW_VERSIONS,64-TOTAL_ROW_VERSIONS AS \"REMAINING_copy_DDLs\" FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name like \"%t1%\" ORDER BY TOTAL_ROW_VERSIONS DESC;" >>tmp5.log 2>&1 & 3.sh instant_simple.sh 4.cat tmp5.log will show TABLE_ID NAME N_COLS ROW_FORMAT INSTANT_COLS TOTAL_ROW_VERSIONS REMAINING_copy_DDLs 2707 test/t1 4 Dynamic 0 2 62 2707 test/t1 4 Dynamic 0 2 62 TABLE_ID NAME N_COLS ROW_FORMAT INSTANT_COLS TOTAL_ROW_VERSIONS REMAINING_copy_DDLs 2707 test/t1 4 Dynamic 0 2 62 in first select i_s.innodb_tables show duplicate row.