MySQL Bugs: #27818: a comedy of errors related to events in 5.1.17

Bug #27818	a comedy of errors related to events in 5.1.17
Submitted:	13 Apr 2007 22:27	Modified:	8 May 2007 1:16
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	5.1.17	OS:	Windows
Assigned to:	Daniel Fischer	CPU Architecture:	Any
Tags:	events, mysql_fix_privilege_tables, mysql_upgrade, qc

Description:
After an upgrade from 5.1.16 to 5.1.17, selecting from information_schema.events elicits an error report suggesting that the user run scripts/mysql_fix_privilege_tables. 

Under Windows that is now impossible, since the only script of that name in that folder is a bash script for *nix; mysql_fix_privilege_tables.sql has been moved to the SHARE folder! 

After running share/mysql_fix_privilege_tables.sql, information_schema.events has 20 columns, not 17.

How to repeat:
SELECT version();
+------------------------------------+
| version()                          |
+------------------------------------+
| 5.1.17-beta-community-nt-debug-log |
+------------------------------------+

SELECT * FROM information_schema.events;
ERROR:
Error Column count of mysql.event is wrong. Expected 17, found 16. Created with MySQL 50116, now running 50117. Please use scripts/mysql_fix_privilege_tables

\. ../scripts/mysql_fix_privilege_tables.sql
ERROR:
Failed to open file '..\scripts\mysql_fix_privilege_tables.sql', error: 2

-- Ha ha it's been move to the SHARE folder

\. ../share/mysql_fix_privilege_tables.sql
-- as usual, many spurious, irritating unnecessary error reports and beeps ensue
 
DESC information_schema.events;
+------------------+--------------+------+-----+---------------------+-------+
| Field            | Type         | Null | Key | Default             | Extra |
+------------------+--------------+------+-----+---------------------+-------+
| EVENT_CATALOG    | varchar(64)  | YES  |     | NULL                |       |
| EVENT_SCHEMA     | varchar(64)  | NO   |     |                     |       |
| EVENT_NAME       | varchar(64)  | NO   |     |                     |       |
| DEFINER          | varchar(77)  | NO   |     |                     |       |
| TIME_ZONE        | varchar(64)  | NO   |     |                     |       |
| EVENT_BODY       | varchar(8)   | NO   |     |                     |       |
| EVENT_DEFINITION | longtext     | NO   |     |                     |       |
| EVENT_TYPE       | varchar(9)   | NO   |     |                     |       |
| EXECUTE_AT       | datetime     | YES  |     | NULL                |       |
| INTERVAL_VALUE   | varchar(256) | YES  |     | NULL                |       |
| INTERVAL_FIELD   | varchar(18)  | YES  |     | NULL                |       |
| SQL_MODE         | longtext     | NO   |     |                     |       |
| STARTS           | datetime     | YES  |     | NULL                |       |
| ENDS             | datetime     | YES  |     | NULL                |       |
| STATUS           | varchar(8)   | NO   |     |                     |       |
| ON_COMPLETION    | varchar(12)  | NO   |     |                     |       |
| CREATED          | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| LAST_ALTERED     | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| LAST_EXECUTED    | datetime     | YES  |     | NULL                |       |
| EVENT_COMMENT    | varchar(64)  | NO   |     |                     |       |
+------------------+--------------+------+-----+---------------------+-------+

Suggested fix:
1. Clean up the documentation & error messages for this script.

2. Clean up the script itself so it no longer emits so many irritating and useless beeps and error messages.

The changes in Event Scheduler metadata in 5.1.17 were already documented here:

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-17.html
http://dev.mysql.com/doc/refman/5.1/en/create-event.html
http://bugs.mysql.com/16420

I have updated the following portions of the Manual to remove any inconsistencies:

http://dev.mysql.com/doc/refman/5.1/en/events-table.html
http://dev.mysql.com/doc/refman/5.1/en/show-events.html

See http://lists.mysql.com/commits/24565 for my commit with these changes.

According to http://dev.mysql.com/doc/refman/5.1/en/mysql-fix-privilege-tables.html when upgrading to 5.1.10 or later on Windows, you should instead run mysql_upgrade, which is discussed here: http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

If error or warning messages issued by the server say to do something else, that's a server issue AFAIK. If mysql_upgrade doesn't perform correctly, that's also a server issue.

If the location of mysql_fix_privilege_tables has been changed, then Docs needs to be informed of this, and that it's intentional.

Removing myself and Stefan from this bug, and changing status/category/severity to Open/Server:Installing/Serious.

This is a verified server problem. Error message is incorrect for Windows in any case.

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

ChangeSet@1.2596, 2007-04-19 18:58:32+02:00, df@pippilotta.erinye.com +1 -0
  BUG#27818 comedy of errors

The above patch changes the error message to be slightly more helpful (i.e. recommends the correct tool to use now).

I verified that 5.1.16 -> 5.1.17 upgrade works with mysql_upgrade (bin/mysql_upgrade.exe on windows).

queued in 5.1-build

Pushed into 5.1.18-beta

Noted in 5.1.18 changelog.

An error message suggested the use of mysql_fix_privilege_tables
after an upgrade, but the recommended program is now mysql_upgrade.