MySQL Bugs: #118132: Alter general log takes a lot of time during upgrade

Bug #118132	Alter general log takes a lot of time during upgrade
Submitted:	8 May 2025 8:30	Modified:	13 May 2025 16:01
Reporter:	karry zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
When the general log is large, it takes a lot of time to upgrade.

How to repeat:
1 . open general log by:
set global general_log = on;
set global log_output='table';

2. run sysbench to create a big general log.

3. upgrage mysql.

Suggested fix:
The reason for this problem is that we need to execute the following SQL when upgrading:
ALTER TABLE general_log
  MODIFY event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  MODIFY user_host MEDIUMTEXT NOT NULL,
  MODIFY thread_id INTEGER NOT NULL,
  MODIFY server_id INTEGER UNSIGNED NOT NULL,
  MODIFY command_type VARCHAR(64) NOT NULL,
  MODIFY argument MEDIUMBLOB NOT NULL;
ALTER TABLE general_log
  MODIFY thread_id BIGINT UNSIGNED NOT NULL;

We can see it in mysql_fix_privilege_tables.sql.

General log only supports copy ddl, so it takes a lot of time.

It is recommended to combine the two alter operations into one. At the same time, before executing the alter operation, determine whether the current table structure is different from the target table structure. If they are the same, do not perform the alter operation. If they are different, perform the alter operation.

Hello, Verification Team, this is my patch. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix.txt (text/plain), 2.98 KiB.

Hello karry zhang,

Thank you for the report and contribution.

regards,
Umesh

Feasibility Review:
The upgrade script alters mysql.general_log twice in a row (scripts/mysql_system_tables_fix.sql:239) while the table is defined as a CSV log table (scripts/mysql_system_tables.sql:355). CSV forces every ALTER TABLE to copy the full file, so with gigabyte-scale logs we copy the data twice and stall the upgrade; that's the core problem. From first principles, the fastest possible upgrade path is to:
(a) avoid touching the table unless its shape diverges from the desired definition.
(b) if we must touch it, do the entire transformation in one pass so we pay for at most one copy.

Patch Assessment:
The diff collapses the two ALTER TABLE statements into one and introduces a metadata check that inspects the six columns we care about in INFORMATION_SCHEMA.COLUMNS; only if any column deviates do we prepare and execute the ALTER.

TL;DR:
Logically correct. We'll merge if it shows performance gains during the
upgrade process.