MySQL Bugs: #80481: Accesses to new data-dictionary add confusing stages to P_S.EVENTS_STAGES

Bug #80481	Accesses to new data-dictionary add confusing stages to P_S.EVENTS_STAGES_*.
Submitted:	23 Feb 2016 14:02	Modified:	25 Feb 2016 15:29
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.0-git	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
With advent of new data-dictionary DML and DDL statements started to open and lock data-dictionary tables in order to read and/or update table meta-data.

These operations on system tables are reflected in the same way as opening and locking of user tables in P_S.events_stages_* tables and I_S.PROCESSLIST, which
can be pretty confusing.

Here is the excerpt from perfschema.alter_table_progress test result file illustrating the problem:

Dumping ALTER TABLE stages
select EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
from performance_schema.events_stages_history_long
where (thread_id = @con1_thread_id)
and (nesting_event_id = @con1_stmt_id)
order by thread_id, event_id;
EVENT_NAME	WORK_COMPLETED	WORK_ESTIMATED
stage/sql/starting	NULL	NULL
stage/sql/checking permissions	NULL	NULL
stage/sql/checking permissions	NULL	NULL
stage/sql/init	NULL	NULL
stage/sql/Opening tables	NULL	NULL
stage/sql/setup	NULL	NULL
stage/sql/creating table	NULL	NULL
stage/sql/Opening tables	NULL	NULL
stage/sql/System lock	NULL	NULL
stage/sql/After create	NULL	NULL
stage/sql/System lock	NULL	NULL
stage/sql/Opening tables	NULL	NULL
stage/sql/System lock	NULL	NULL
stage/sql/copy to tmp table	5	5
stage/sql/rename result table	NULL	NULL
stage/sql/Opening tables	NULL	NULL
stage/sql/System lock	NULL	NULL
stage/sql/Opening tables	NULL	NULL
stage/sql/System lock	NULL	NULL
stage/sql/Opening tables	NULL	NULL
stage/sql/System lock	NULL	NULL
stage/sql/Opening tables	NULL	NULL
stage/sql/System lock	NULL	NULL
stage/sql/Opening tables	NULL	NULL
stage/sql/System lock	NULL	NULL
stage/sql/end	NULL	NULL
stage/sql/query end	NULL	NULL
stage/sql/closing tables	NULL	NULL
stage/sql/freeing items	NULL	NULL
stage/sql/cleaning up	NULL	NULL

Notice plenty of Opening tables and System lock items in the query result.

How to repeat:
See description.

Suggested fix:
Introduce separate stages/states for opening and locking data-dictionary tables. This way accesses to system tables will be still visible in P_S but can't be mixed up with accesses to normal user tables.

Fixed in 5.8.0.

Bug affects no released version. No changelog entry needed.

Correction, there is a changelog entry because there are user-visible changes.

Noted in 5.8.0 changelog.

To permit accesses to a system tables (for example, data dictionary,
time zone, or log tables) to be distinguished from accesses to
nonsystem tables, the server uses the Locking system tables and
Opening system tables thread states rather than the System lock and
Opening tables thread states.

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0