diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index b3dfe18d4dc..003c71ed3c2 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -239,15 +239,66 @@ SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; SET @old_sql_require_primary_key = @@session.sql_require_primary_key; SET @@session.sql_require_primary_key = 0; -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; + +SET @needs_alter_general_log = ( + SELECT EXISTS ( + SELECT 1 + FROM INFORMATION_SCHEMA.COLUMNS + WHERE TABLE_SCHEMA = 'mysql' + AND TABLE_NAME = 'general_log' + AND COLUMN_NAME IN ( + 'event_time', 'user_host', 'thread_id', + 'server_id', 'command_type', 'argument' + ) + AND NOT ( + -- event_time: check EXTRA contains key description (compatible with DEFAULT_GENERATED) + (COLUMN_NAME = 'event_time' + AND COLUMN_TYPE = 'timestamp(6)' + AND IS_NULLABLE = 'NO' + AND COLUMN_DEFAULT = 'CURRENT_TIMESTAMP(6)' + AND EXTRA LIKE '%on update CURRENT_TIMESTAMP(6)%') + + OR (COLUMN_NAME = 'user_host' + AND COLUMN_TYPE = 'mediumtext' + AND IS_NULLABLE = 'NO') + + -- thread_id: allow display width (such as bigint(20) unsigned) + OR (COLUMN_NAME = 'thread_id' + AND COLUMN_TYPE LIKE 'bigint%unsigned' + AND IS_NULLABLE = 'NO') + + -- server_id: allow display width (such as int(11) unsigned) + OR (COLUMN_NAME = 'server_id' + AND COLUMN_TYPE LIKE 'int%unsigned' + AND IS_NULLABLE = 'NO') + + OR (COLUMN_NAME = 'command_type' + AND COLUMN_TYPE = 'varchar(64)' + AND IS_NULLABLE = 'NO') + + OR (COLUMN_NAME = 'argument' + AND COLUMN_TYPE = 'mediumblob' + AND IS_NULLABLE = 'NO') + ) + ) +); +SET @alter_general_log_sql = IF( + @needs_alter_general_log, + ' + 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 BIGINT UNSIGNED NOT NULL, + MODIFY server_id INTEGER UNSIGNED NOT NULL, + MODIFY command_type VARCHAR(64) NOT NULL, + MODIFY argument MEDIUMBLOB NOT NULL; + ', + 'SELECT "No changes needed" AS Result;' +); +PREPARE alter_general_log_stmt FROM @alter_general_log_sql; +EXECUTE alter_general_log_stmt; +DEALLOCATE PREPARE alter_general_log_stmt; + SET GLOBAL general_log = @old_log_state; SET @old_log_state = @@global.slow_query_log;