MySQL Bugs: #35824: mysql_upgrade does not fix scheduler tables when upgrading from 5.1.23 to 5.1.24

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

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.

This regression is probably caused by bug #32633 . THat bug was fixed, however mysql_upgrade was not adjusted.

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.

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.

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.

Queued to mysql-5.1-release

Same situation occurs when upgrading from 6.0.4 to 6.0.5.

Pushed into 5.1.25-rc

Pushed into 6.0.6-alpha

Noted in 5.1.25, 6.0.6 changelogs.

mysql_upgrade did not properly update the mysql.event table.

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

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.

Server needs to be restarted after running mysql_upgrade.

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