Bug #49823 mysql_upgrade fatal error due to general_log / slow_low CSV NULL

• Submitted: 19 Dec 2009 12:27
• Modified: 18 Jun 2010 2:07
• Reporter: Eric Thunberg
• Status: Closed
• Category: MySQL Server: Installing
• Severity: S3 (Non-critical)
• Version: 5.1.41
• OS: Linux (2.6.18-53 x86_64)
• Assigned to: Davi Arnaut
• CPU Architecture: Any
• Tags: csv, general_log, mysql_fix_privilege_tables, mysql_upgrade, null, slow_log

[19 Dec 2009 12:27] Eric Thunberg

Description:
When mysql_upgrade is run after upgrading from 5.1.30 to 5.1.33 or 5.1.34 the following error occurs:

Running 'mysql_fix_privilege_tables'...
ERROR 1178 (42000) at line 311: The storage engine for the table doesn't support nullable columns
ERROR 1178 (42000) at line 316: The storage engine for the table doesn't support nullable columns
FATAL ERROR: Upgrade failed

Seems to be related to the following data in mysql_upgrade:

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE general_log MODIFY COLUMN server_id INTEGER UNSIGNED 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;
SET GLOBAL slow_query_log = @old_log_state;

I thought it was because I was jumping from 5.1.30 to 5.1.34 so I instead performed mysql_upgrade from 5.1.30 -> 5.1.31 -> 5.1.32 -> 5.1.33 where I encountered the same error.

To get around it I hex edited mysql_upgrade and commented out the two ALTER statements pertaining to general_log and slow_low.

How to repeat:
Attempt to upgrade (from source and or binary) from 5.1.3[012] to 5.1.3[34] and run mysql_upgrade.

Suggested fix:
Do not have one.

[21 Dec 2009 10:20] Sveta Smirnova

Thank you for the report.

But version 5.1.34 is old. Additionally I can not repeat described behavior with current development sources. Please try current version 5.1.41 and if problem still exists provide full error log file in new version and output of SHOW VARIABLES LIKE 'sql_mode'

[29 Dec 2009 14:50] Eric Thunberg

Just attempted to upgrade to 5.1.41 and the same error occurred.

[29 Dec 2009 14:53] Eric Thunberg

Running 'mysql_fix_privilege_tables'...
ERROR 1178 (42000) at line 311: The storage engine for the table doesn't support nullable columns
ERROR 1178 (42000) at line 316: The storage engine for the table doesn't support nullable columns
FATAL ERROR: Upgrade failed
[root@db003 bin]# 

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

Please let me know what other information you require.

[29 Dec 2009 14:58] Sveta Smirnova

Thank you for the feedback.

Do you have data in tables general_log and slow_log tables? Please check if there is a row where server_id is null.

[29 Dec 2009 15:08] Eric Thunberg

mysql> select * from mysql.general_log;
Empty set (0.00 sec)

mysql> select server_id from mysql.slow_log;
+-----------+
| server_id |
+-----------+
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
|         1 |
+-----------+
20 rows in set (0.00 sec)

[30 Dec 2009 23:50] Sveta Smirnova

Thank you for the feedback.

Could you please send us your general_log and slow_log tables? Probably we can guess something from data.

[31 Dec 2009 17:50] Eric Thunberg

mysqldump of the general log

Attachment: mysql.general_log.sql (application/octet-stream, text), 2.00 KiB.

[31 Dec 2009 17:51] Eric Thunberg

mysqldump of the slow log

Attachment: mysql.slow_log.sql (application/octet-stream, text), 24.50 KiB.

[7 Jan 2010 0:05] m k

I've been seeing this same message for several upgrades.  I looked at the table definitions for general_log and slow_log in /usr/local/share/mysql/fix_privilege_tables.sql and compared to existing tables using show create table general_log, etc.  My existing tables had columns allowing nulls.  The fix_*.sql file defined columns as not null.  After verifying tables had no data, I ran an alter table statement and modified column for each column in the table to match the fix_*.sql script.  The errors have now stopped.  I'm on FreeBSD 7.2 and upgraded from mysql 5.1.38 to 5.1.41 via ports.  HTH

[11 Jan 2010 7:03] Sveta Smirnova

Thank you for the feedback.

Verified as described. Problem is general_log and slow_log tables were created in very early 5.1 versions. For example, upgrade 5.1.11->5.1.43 has this issue.

Suggested fix:

ALTER TABLE general_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL, MODIFY COLUMN thread_id INTEGER UNSIGNED NOT NULL, MODIFY COLUMN command_type varchar(64) NOT NULL, MODIFY COLUMN user_host mediumtext NOT NULL, MODIFY COLUMN argument mediumtext NOT NULL;

Same for slow_log.

[11 Jan 2010 16:15] Eric Thunberg

I ran the query provided for general_log and ran the following for slow_log:

SET GLOBAL log_slow_queries = 'OFF';

ALTER TABLE slow_log MODIFY COLUMN server_id INTEGER UNSIGNED NOT NULL, MODIFY COLUMN insert_id INTEGER UNSIGNED NOT NULL, MODIFY COLUMN last_insert_id INTEGER UNSIGNED NOT NULL, MODIFY COLUMN db varchar(512) NOT NULL;

SET GLOBAL log_slow_queries = 'ON';

Then I ran mysql_upgrade and everything worked perfectly.

Thanks for the help,
Eric

[26 Feb 2010 12:23] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101611

3358 Davi Arnaut	2010-02-26
      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.

[26 Feb 2010 20:00] Davi Arnaut

Queued to mysql-5.1-bugteam and up.

[2 Mar 2010 14:35] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:[e-mail]-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)

[3 Mar 2010 1:17] Paul DuBois

Noted in 6.0.14 changelog.

mysql_upgrade did not detect when CSV log tables incorrectly
contained columns that could be NULL. Now these columns are altered
to be NOT NULL. 

Setting report to Need Merge pending push to 5.1.x, Celosia.

[26 Mar 2010 8:20] Bugs System

Pushed into 5.5.4-m3 (revid:[e-mail]-20100326080914-2pz8ns984e0spu03) (version source revid:[e-mail]-20100307164059-cri8typa32cypq0l) (merge vers: 5.5.3-m2) (pib:16)

[26 Mar 2010 8:24] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100326081116-m3v4l34yhr43mtsv) (version source revid:[e-mail]-20100325072612-4sds00ix8ajo1e84) (pib:16)

[26 Mar 2010 15:53] Paul DuBois

Noted in 5.5.4 changelog.

Setting report to Need Merge pending push to 5.1.x.

[31 Mar 2010 16:13] Paul DuBois

5.5.4 changelog entry was moved to 5.5.5.

[6 Apr 2010 7:57] Bugs System

Pushed into 5.1.46 (revid:[e-mail]-20100405111026-7kz1p8qlzglqgfmu) (version source revid:[e-mail]-20100301084434-ytctk3ceebjvqo7a) (merge vers: 5.1.45) (pib:16)

[6 Apr 2010 14:17] Paul DuBois

Noted in 5.1.46 changelog.

[17 Jun 2010 12:09] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:56] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

[17 Jun 2010 13:36] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)