Bug #118132 Alter general log takes a lot of time during upgrade
Submitted: 8 May 8:30 Modified: 13 May 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

[8 May 8:30] karry zhang
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.
[12 May 6:04] karry zhang
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.

[13 May 16:01] MySQL Verification Team
Hello karry zhang,

Thank you for the report and contribution.

regards,
Umesh