MySQL Bugs: #63149: Unhandled exception when assigning certain privileges

Bug #63149	Unhandled exception when assigning certain privileges
Submitted:	8 Nov 2011 14:32	Modified:	27 Mar 2012 4:23
Reporter:	William Schneider	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Administration	Severity:	S3 (Non-critical)
Version:	5.2.35, 5.2.36	OS:	MacOS (10.7.2)
Assigned to:		CPU Architecture:	Any

Description:
In the OSX version of Workbench, the following error is thrown when adding certain roles through the 'Users and Privileges' panel with MySQL 5.0.67

Unhandled exception: Error executing 'Table 'mysql.event' doesn't exist'
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE mysql.event TO 'root'@'wiki-dev-db.core.umbc.edu'.
SQL Error: 1146

In somewhat haphazard debugging, I noticed that this seems to only occur when adding roles which include the "* Modify Routines" global privilege. This problem does not occur for another mysql server we have running 5.5.15.

How to repeat:
As a administratively privileged database user, open the 'Users and Privileges' tab under the 'Security' section when in the 'Server Administration' for mysql 5.0.67 (or maybe 5.0.x?). Select any user account, check the 'DBA' checkbox, and click 'Apply'. The above error should appear.

Suggested fix:
On line 126 of 'wb_admin_security_be.py' (in MySQLWorkbench.app/Contents/Plugins), within "Routine_manage_attr", "event" is included in an array of tables to grant privileges. Removing it stops the error from appearing, but would likely produce problems for mysql servers which rely on it. Would adding a mysql version check before determining whether to include the "event" table work?

I couldn't repeat on Windows 7 64-bit.

Included the OS I was using in the report details

Please, send the output of:

show create table mysql.event;

statement from your server. This is how it looks like in my case:

mysql> show create table mysql.event\G
*************************** 1. row ***************************
       Table: event
Create Table: CREATE TABLE `event` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` int(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','S
ECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MIN
UTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_
MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMES
TAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENA
BLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE',
'NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','PO
STGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO
_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLAS
H_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')
 NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) unsigned NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NU
LL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NU
LL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events'
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.16-log |
+------------+
1 row in set (0.00 sec)

Output for `show create table mysql.event;` is an error:

"Error Code: 1146. Table 'mysql.event' doesn't exist"

Isn't this expected of MySQL 5.0.x?

William is correct. The 'event' table dows not exist on MySQL 5.0.  

I think supporters missed the point here: ".. error is thrown when adding certain roles through the 'Users and Privileges' panel with MySQL 5.0.67 ... This problem does not occur for another mysql server we have running 5.5.15."

I don't know if WB is supposed to work seamlessly with MySQL 5.0 in every respect.

Peter
(not a MySQL person)

Workbench does not support 5.0 entirely. Still, unhandled exceptions should not happen. Please, check if new version, 5.2.36, solves the problem for you (it should have some fixes to better work on 5.0).

Thanks for the update, but the problem still occurs under the same circumstances in 5.2.36 but throws a different error message:

Unhandled exception: Error executing 'INSERT,UPDATE,DE command denied to user '<my_username_here>'@'<connection_client_hostname_here>' for table 'event''
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE mysql.event TO '<my_username_here>'@'%'.
SQL Error: 1142

(I have root privileges, so it is not a permission problem but the problem is that the mysql.event table does not exist in the mysql version I am using)

Then, after I click 'revert' in order to be able to select other users to apply new permissions to, I receive the message:

Unhandled exception: 'NoneType' object has no attribute 'nextRow'

and am unable to do any additional tasks until I physically quit and reopen Workbench, since after this error is thrown Workbench indicates that mySQL has been stopped (though it is really still running).

Thanks for the continued effort to squash these unknown exceptions.

Verified just as described initially that one can NOT assign DBA role to any user while connected to 5.0.x, as Workbench still tries to access mysql.events table. This is a bug.

MySQL introduced the mysql.event table in server version 5.1.6 according to http://dev.mysql.com/doc/refman/5.1/en/events-privileges.html. Workbench should not try to access this table for older MySQL Server versions.

Fixed as of 5.2.39, and here's the changelog entry:

While using Workbench to adjust a "Users and Privileges" role with a MySQL Server version prior to 5.1.6, an unhandled exception would result from Workbench
attempting to access the "mysql.event" table.