Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
LINUX
Description
We've see a server crash due to long semaphore wait. The innodb stats update thread updates stats(dict_stats_save_index_stat()) on a table using InnoDB internal parser, holding dict_operation_lock and dict_sys->mutext, but the plan is full table scan(innodb_index_stats) other than clust index scan. There are more than 200 thousands records in stats table, and there are multiple udpates for the table stats. So it takes more than 900s to finish.
The reason why stats updates use full table scan is that there is a running SQL: ALTER TABLE mysql.innodb_index_stats ENGINE=InnoDB STATS_PERSISTENT=0. The online DDL set cluster index of innodb_index_stats to ONLINE_INDEX_CREATION.
callstack:
prepare_inplace_alter_table_dict
--row_log_allocate
----dict_index_set_online_status
In function opt_calc_index_goodness():
343 /* At least for now we don't support using FTS indexes for queries
344 done through InnoDB's own SQL parser. */
345 if (dict_index_is_online_ddl(index) || (index->type & DICT_FTS))
In this case we should use the online index.
Attachments
Issue Links
- relates to
-
MDEV-33575 Assertion `rec_get_trx_id(rec, clust_index) < trx->id' failed in row_merge_read_clustered_index
-
- Confirmed
-
-
MDEV-33462 Server aborts while altering an InnoDB statistics table
-
- Closed
-
It turns out that I had introduced this condition back in MySQL 5.6 when implementing WL#5526 Online ADD INDEX. The condition was almost correct at that point of time, but I forgot to revise it later that year when implementing WL#6255 online table rebuild.
I believe that the condition dict_index_is_online_ddl(index) needs to be replaced with index->is_committed(). That would also fix a corner case where a secondary index has been fully built, but the ALTER TABLE or CREATE INDEX operation has not been committed yet, possibly because an upgrade of a metadata lock (MDL) to exclusive is in progress. In particular, if this happened during CREATE UNIQUE INDEX, it is possible that the DDL operation will need to be rolled back because some DML operation would introduce a duplicate entry in the not-yet-committed unique index.
Correcting the condition to index->is_committed() also has the benefit that the condition will never hold on the clustered index of a table, that is, the clustered index will never be disqualified from use in the InnoDB internal SQL interpreter.
Also the condition on index->type is inaccurate. index->is_btree() would properly disqualify SPATIAL INDEX, which were introduced for InnoDB in MySQL 5.7 and MariaDB Server 10.2.