MySQL Bugs: #33002: Event Scheduler: Unnecessarily disabled after 5.1 -> 6.0.4 upgrade

Bug #33002	Event Scheduler: Unnecessarily disabled after 5.1 -> 6.0.4 upgrade
Submitted:	5 Dec 2007 14:50	Modified:	14 Aug 2008 3:31
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any

Description:
In 6.0.4, the 3-byte Unicode character set formerly known as utf8 is now known as utf8mb3, so utf8 columns from tables created before 6.0.4 show up as utf8mb3.

When the server starts, for backward compatibility, it allows privilege table columns that should be utf8 to be utf8mb3. It issues an error message for each column, but continues nonetheless, and evaluates privileges correctly.

Example warnings:

071203 13:30:54 [ERROR] Incorrect definition of table mysql.db: expected the type of column 'Select_priv' at position 3 to have character set 'utf8' but found character set 'utf8mb3'.
071203 13:30:54 [ERROR] Incorrect definition of table mysql.db: expected the type of column 'Insert_priv' at position 4 to have character set 'utf8' but found character set 'utf8mb3'.

The same does not happen for the event scheduler. Similar messages are issued for columns in the mysql.event table:

071203 13:30:54 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'definer' at position 3 to have character set 'utf8' but found character set 'utf8mb3'.
071203 13:30:54 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'comment' at position 15 to have character set 'utf8' but found character set 'utf8mb3'.

However, the event scheduler does not work, even with event_scheduler=1 specified in my.cnf:

071203 13:30:54 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

Please note that this is an upgrade INCOMPATIBILITY. It's an effect of upgrading from 5.1 to 6.0.4 that the user surely will not appreciate: Updating causes the event scheduler to be turned off even if previously it was enabled.

If the server can issue messages for the privilege tables but still use them correctly, it should also be able to issue messages for the event table but still leave the event scheduler working.

How to repeat:
Upgrade a 5.1 installation to 6.0.4 or higher. (Or copy a 5.1 data directory to a 6.0.4 installation)

Add event_scheduler=1 to my.cnf if it is not there already.

Start the server.

Check the error log. It will indicate that the event scheduler is disabled.

Suggested fix:
Don't disable the event scheduler if the utf8 columns are utf8mb3. (Note: utf8mb3 is the *only* character set for which the errors should be ignored.)

Thank you for the report.

Verified as described.

Another problem:

mysql_upgrade will convert the grant tables from utf8mb3 to utf8, but it does not do so for the mysql.event table.  It should. Otherwise, users will need to alter the columns to utf8 manually.

The problem of the event table retaining traces of utf8mb3 after mysql_upgrade turns out to be a more general problem affecting several mysql database tables. Consequently, I have opened Bug#33053 to report the issue as a separate problem.

Additional note. It appears that the event scheduler refuses to start if the server detects that utf8mb3 is used in ANY of the mysql database tables that the server checks during startup. Consequently, a workaround for this problem does not consist of converting the event table to use utf8 rather than utf8mb3. The grant tables also must be converted, although that can be done with mysql_upgrade after upgrading to 6.0.

Since mysql_upgrade converts the grant tables properly, even if it fails
to do so for the event table, this sequence appears to work:

1. In MySQL 5.1, before upgrading, create a dump file
   containing your mysql.event table:

shell> mysqldump -uroot -p mysql event > event.sql

2. Upgrade to MySQL 6.0 and start the server.

3. Recreate the mysql.event table using the dump file:

shell> mysql -uroot -p mysql < event.sql

4. Run mysql_upgrade to upgrade the other system tables in
   the mysql database:

shell> mysql_upgrade -uroot -p

5. Restart the server. The event scheduler should run
   properly.

Users should not have to go through this, of course.

The dump gets the user a file with drop table + create table that includes
"utf8" in the column definitions. Reloading the dump after upgrading
gives the user an event table with utf8 rather than utfmb3. Running
mysql_upgrade converts the grant tables from utf8mb3 to utf8.

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/50963

2745 Davi Arnaut	2008-08-05
      Bug#33053: mysql_upgrade: Does not convert all mysql db tables from utf8mb3 to utf8
      Bug#33002: Event Scheduler: Unnecessarily disabled after 5.1 -> 6.0.4 upgrade
      
      The 3-byte Unicode character set formerly known as utf8 is
      now known in 6.0 as utf8mb3 and this cause problems if system
      tables created in older versions of MySQL are used as the code
      that check table compatibility won't accept a different charset
      and this was happening for the event table that wasn't being
      properly upgraded.
      
      The solution is to ensure that all system tables are upgraded
      to use the new utf8 character set rather than the old utf8mb3.
      In the future it would be nice if the server could be capable of
      handling the character set mishap, but for now we just upgrade
      the table as the event subsystem is the only one that checks
      if a table is compatible.

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/51001

2745 Davi Arnaut	2008-08-05
      Bug#33053: mysql_upgrade: Does not convert all mysql db tables from utf8mb3 to utf8
      Bug#33002: Event Scheduler: Unnecessarily disabled after 5.1 -> 6.0.4 upgrade
      
      The 3-byte Unicode character set formerly known as utf8 is
      now known in 6.0 as utf8mb3 and this cause problems if system
      tables created in older versions of MySQL are used as the code
      that check table compatibility won't accept a different charset
      and this was happening for the event table that wasn't being
      properly upgraded.
      
      The solution is to ensure that all system tables are upgraded
      to use the new utf8 character set rather than the old utf8mb3.
      In the future it would be nice if the server could be capable of
      handling the character set mishap, but for now we just upgrade
      the table as the event subsystem is the only one that checks
      if a table is compatible.

Queued to 6.0-bugteam

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/51016

2746 Kristofer Pettersson	2008-08-06 [merge]
      Auto merge

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/51138

2744 Marc Alff	2008-08-07 [merge]
      Merge mysql-6.0-bugteam -> local bugfix branch

Pushed into 6.0.7-alpha  (revid:davi.arnaut@sun.com-20080805213614-shm7jp3xhd4b00v6) (version source revid:davi.arnaut@sun.com-20080812141852-8e6knbqclpfd8irn) (pib:3)

Noted in 6.0.7 changelog.

After an upgrade to MySQL 6.0.4 or higher, columns that used the old
3-byte Unicode utf8 character set are treated as having the utf8mb3 
character set. mysql_upgrade did not convert all system tables in the
mysql database to use the new 4-byte Unicode utf8 character set
rather than utf8mb3. This caused problems such as that the event
scheduler would not start. mysql_upgrade now performs the utf8mb3 to
utf8 conversion for system tables.

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/51602

2684 He Zhenxing	2008-08-14 [merge]
      Merge 6.0 -> 6.0-rpl-testfixes

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/51603

2684 He Zhenxing	2008-08-14 [merge]
      Merge 6.0 -> 6.0-rpl-testfixes

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)

Pushed into 6.0.7-alpha  (revid:davi.arnaut@sun.com-20080805213614-shm7jp3xhd4b00v6) (version source revid:vvaintroub@mysql.com-20080804094710-jb2qpqxpf2ir2gf3) (pib:3)