Bug #35824 mysql_upgrade does not fix scheduler tables when upgrading from 5.1.23 to 5.1.24
Submitted: 4 Apr 2008 9:46 Modified: 7 May 2008 19:32
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S1 (Critical)
Version:5.1.24, 6.0.5 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Triage: D1 (Critical)

[4 Apr 2008 9:46] Philip Stoev
Description:
When upgrading from 5.1.23 to 5.1.24 while keeping the same vardir and using mysql_upgrade, the scheduler will refuse to start with the following message:

Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_Z
080403 22:19:06 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

How to repeat:
1. Start and stop a 5.1.23 server:

$ cd /build/5.1.23
$ perl mysql-test-run.pl --start-and-exit --skip-ndb --vardir=/build/events_vardir

2. Start a 5.1.24 server using the same vardir

$ cd /build/5.1.24
$ perl mysql-test-run.pl --start-and-exit --skip-ndb --vardir=/build/events_vardir --start-dirty

3. Run mysql_upgrade in 5.1.24

$ ../bin/mysql_upgrade -uroot --socket=/build/events_vardir/tmp/master.sock

4. Restart 5.1.24 server

$ perl mysql-test-run.pl --start-and-exit --skip-ndb --vardir=/build/events_vardir --start-dirty

5. Attempt to work with events

$ ../bin/mysql -uroot --socket=/build/events_vardir/tmp/master.sock -e 'show events' test
ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start

Suggested fix:
Make sure mysql_upgrade brings the tables to the state the event scheduler requires them.
[4 Apr 2008 14:25] Philip Stoev
This regression is probably caused by bug #32633 . THat bug was fixed, however mysql_upgrade was not adjusted.
[4 Apr 2008 15:09] Philip Stoev
Furthermore ../share/mysql_fix_privilege_tables.sql uses ALTER TABLE event ADD sql_mode to change the column, which is bound to fail since this column already exists. Contrast with ALTER TABLE proc MODIFY sql_mode in the same script, which is safer.
[4 Apr 2008 15:15] Davi Arnaut
In a nutshell, this is the result of three problems:

1) mysql_upgrade succeeds even if the SQL update script fails;
2) 5.1 -> 5.1 upgrades where not envisioned for the event table (thus not a regression introduced by Bug#32633);
3) Server does not work with old sql_mode column of the event table.
[4 Apr 2008 16:48] 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/44927

ChangeSet@1.2566, 2008-04-04 13:46:05-03:00, davi@mysql.com +1 -0
  Bug#35824: mysql_upgrade does not fix scheduler tables when upgrading from 5.1.23 to 5.1.24
  
  The problem is that when upgrading the event table, the sql_mode
  column was always being added instead of being updated to list new
  sql_mode values, causing upgrades of the event which already have
  a sql_mode column to not be updated to the new sql_mode values.
  
  The solution is to always add first a stub sql_mode column and
  subsequently update the column to the new sql_mode values.
[4 Apr 2008 22:16] Davi Arnaut
Queued to mysql-5.1-release
[30 Apr 2008 14:57] Philip Stoev
Same situation occurs when upgrading from 6.0.4 to 6.0.5.
[1 May 2008 6:16] Bugs System
Pushed into 5.1.25-rc
[1 May 2008 6:19] Bugs System
Pushed into 6.0.6-alpha
[7 May 2008 19:32] Paul Dubois
Noted in 5.1.25, 6.0.6 changelogs.

mysql_upgrade did not properly update the mysql.event table.
[12 Mar 2009 8:48] Vygintas Krasauskas
Sure it was fixed??

We have upgraded from 5.0.58 to 5.1.31 on SuSE 10: removed all 5.0 RPMs, installed 5.1 and ran mysql_upgrade.

Following error message is still being reported "Cannot proceed because system tables used by Event Scheduler were found damaged at server start".

mysql_upgrade --force reports all "OK".

Server version 5.1.31-enterprise-gpl-pro
[7 Aug 2009 1:01] Chris Hennick
I'm still having this problem, and I'm using 5.1.31-1ubuntu2. I've tried running sudo mysql_upgrade --force, and it doesn't fix it.
[7 Aug 2009 15:08] Davi Arnaut
Server needs to be restarted after running mysql_upgrade.
[29 Sep 2009 16:54] Tomasz Kraus
If you are still having problems.
DROP TABLE event

and then get proper definition from this file:

/usr/local/share/mysql/mysql_system_tables.sql

or find it in other path,

and execute proper CREATE. For me it helped