Bug #117459 Cross DDL operations on MySQL partitioned tables, parallel COUNT(*) does not report errors.
Submitted: 13 Feb 3:02 Modified: 21 Mar 11:05
Reporter: YuChen Jiang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[13 Feb 3:02] YuChen Jiang
Description:
Using a snapshot (vision) captured before the table structure modification, a parallel COUNT(*) query on the modified table structure executes without errors.

We think the problematic code lies in the logic of ha_innopart::records handling parallel COUNT(*) operations.

Because ha_innobase::records handles the case where !index->is_usable, while ha_innopart::records appears to overlook this scenario when processing parallel COUNT(*) operations.

How to repeat:
The following COUNT(*) queries are all parallel reads:

session 1:
begin;
select count(*) from t1;

session 2:
alter table t2 add column c int;

session 1:
select count(*) from t2;

If t2 is a non-partitioned table, it throws an error: Table definition has changed, please retry transaction.
If t2 is a partitioned table, the query can still be executed, which is considered unexpected behavior.

For the final query in Session 1, we also tested non-parallel read operations, such as SELECT * FROM t2;

Results as follows:
If t2 is a non-partitioned table, it throws an error: Table definition has changed, please retry transaction.
If t2 is a partitioned table, it throws an error: Table definition has changed, please retry transaction.

In a word, among the four scenarios, only the partitioned table executing a parallel COUNT(*) did not report errors, which seems to be a bug.

Suggested fix:
In the logic of ha_innopart::records handling parallel COUNT(*) operations,add code to handle the HA_ERR_TABLE_DEF_CHANGED scenario.
[13 Feb 7:30] MySQL Verification Team
Hi,

Thanks for the report.
[21 Mar 11:05] Edward Gilmore
Posted by developer:
 
Added the following note to the MySQL Server 8.0.42, 8.4.5, and 9.3.0 release notes:
	
Partition table indexes were not checked when retrieving a record count while that table's 
definition was being altered by another client session. The record count was executed without error.

As of this release, the index is checked to ensure it is usable when retrieving a record count.