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 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

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