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:	8 May 10:22
Reporter:	karry zhang (OCA)	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

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.