From b0a1ae09ae12bb3eefda03ff91263a3c860fd49b Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Fri, 26 Feb 2010 09:22:48 -0300 Subject: Bug#49823: mysql_upgrade fatal error due to general_log / slow_low CSV NULL The problem was that the CSV storage engine does not support NULL fields, yet in some early 5.1 version the log tables (general_log and slow_log) were created with null fields. On top of this, when altering a CSV table column, all fields of the table must be NOT NULL otherwise the alteration fails. The solution is to ensure that during upgrade all columns of the log tables are NOT NULL. mysql-test/r/log_tables_upgrade.result: Add test case result for Bug#49823. mysql-test/std_data/bug49823.CSV: Sample data for test. mysql-test/std_data/bug49823.frm: Add a CSV table which mimics the general_log table, except that it contains a nullable column. mysql-test/t/log_tables_upgrade.test: Add test case for Bug#49823. scripts/mysql_system_tables_fix.sql: Ensure that all columns of the log tables are NOT NULL. --- scripts/mysql_system_tables_fix.sql | 21 +++++++++++++++++++-- 1 file changed, 19 insertions(+), 2 deletions(-) (limited to 'scripts/mysql_system_tables_fix.sql') diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index 4260aee9142..deeb4d4de82 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -221,12 +221,29 @@ ALTER TABLE func SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; -ALTER TABLE general_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL; +ALTER TABLE general_log + MODIFY event_time TIMESTAMP NOT NULL, + 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 MEDIUMTEXT NOT NULL; SET GLOBAL general_log = @old_log_state; SET @old_log_state = @@global.slow_query_log; SET GLOBAL slow_query_log = 'OFF'; -ALTER TABLE slow_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL; +ALTER TABLE slow_log + MODIFY start_time TIMESTAMP NOT NULL, + MODIFY user_host MEDIUMTEXT NOT NULL, + MODIFY query_time TIME NOT NULL, + MODIFY lock_time TIME NOT NULL, + MODIFY rows_sent INTEGER NOT NULL, + MODIFY rows_examined INTEGER NOT NULL, + MODIFY db VARCHAR(512) NOT NULL, + MODIFY last_insert_id INTEGER NOT NULL, + MODIFY insert_id INTEGER NOT NULL, + MODIFY server_id INTEGER UNSIGNED NOT NULL, + MODIFY sql_text MEDIUMTEXT NOT NULL; SET GLOBAL slow_query_log = @old_log_state; # -- cgit v1.2.1