MySQL Bugs: #27828: SELECT from information schema in an event crashes server.

Bug #27828	SELECT from information schema in an event crashes server.
Submitted:	14 Apr 2007 20:06	Modified:	24 May 2007 15:39
Reporter:	Tobias Asplund	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.17	OS:	MacOS
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
SELECT from an information_schema table seems to crash the server if done from an event.

How to repeat:
mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.01 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE bug (a INT );
Query OK, 0 rows affected (0.46 sec)

mysql> CREATE EVENT bug ON SCHEDULE EVERY 10 SECOND STARTS CURRENT_TIMESTAMP ENABLE DO INSERT INTO bug SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'uptime';
Query OK, 0 rows affected (0.00 sec)

-- Watch the server crash :)
-- 070414 14:05:57 [Note] SCHEDULER: [test.bug of flupps@localhost] executing in thread 3. 
-- 070414 14:05:57 - mysqld got signal 10;

Doesn't crash here, latest 5.1-maint, 5.1.18-beta-valgrind-max-debug (i386)

070415 22:45:20 [Note] Event Scheduler: [odbctest.bug of root@localhost] executing in thread 11.
070415 22:45:20 [Note] Event Scheduler: [root@localhost].[odbctest.bug] executed successfully in thread 11.
070415 22:45:30 [Note] Event Scheduler: [odbctest.bug of root@localhost] executing in thread 12.
070415 22:45:30 [Note] Event Scheduler: [root@localhost].[odbctest.bug] executed successfully in thread 12.

The server is executing the event since 2:30h, and still no crash.
mysql> select count(*) from bug;
+----------+
| count(*) |
+----------+
|      484 |
+----------+
1 row in set (0.00 sec)

Is it a fresh 5.1.17 or it was isnatlled over previous 5.1.x version? See bug #27818 in later case.

It was installed over an installation that has been upgraded a few times, and I did get the problem of bug#27818.
I solved that by running mysql_upgrade and it doesn't give any errors on startup anymore.
I'm currently cloning a 5.1.18 tree from bk and seeing if I still get an error once building from there.

Using newest pull from bk and building with ./BUILD/compile-pentium64-debug-max I get another issue.

(from IRC)

22:39:13 <flupps|US> mysql> set global event_scheduler = 0;
22:39:13 <flupps|US> Query OK, 0 rows affected (0.00 sec)
22:39:13 <flupps|US> mysql> CREATE EVENT bug ON SCHEDULE EVERY 10 SECOND STARTS CURRENT_TIMESTAMP ENABLE DO SELECT 1;
22:39:13 <flupps|US> Query OK, 0 rows affected (0.00 sec)
22:39:13 <flupps|US> mysql> \r
22:39:17 <flupps|US> -- hangs indefinitely

The hang happens when I issue a CREATE EVENT command, nothing works after that until I kill -9.
The fun part is that when the server starts up again, and I turn on the scheduler, everything works, so the crash in 5.1.17 is not there anymore.

I haven't found any other commands where this happens, so I think it's tied to the event creation. It *does* make it into the event table just fine, however.

Me and Andrey found that the upgrade scripts doesn't work properly to add PK/time_zones etc to the mysql.event table, so my table structure was not correct, however, manually fixing this didn't seem to correct the problem (Maybe we missed some alter command or you have to flush privileges or restart or something simple like that that we missed, wouldn't rule this out yet).

To rule out that my table structure is bad again, this is what the clean install gave me:

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','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `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 'ENABLED',
  `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','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') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events'
1 row in set (0.00 sec)

Thank you for the report.

I can not repeat described behaviour on 32-bit Intel Mac and 64-bit PPC.

Please indicate accurate platform of yours.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".