MySQL Bugs: #22830: Events: crash with procedure which alters events with function

Bug #22830	Events: crash with procedure which alters events with function
Submitted:	29 Sep 2006 15:43	Modified:	4 Dec 2006 6:05
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Andrey Hristov	CPU Architecture:	Any

Description:
I create a procedure which alters an event.
The alteration refers to a function.
Crash.

The test case is an alteration of the one in
Bug #22397 Events: crash with procedure which alters events
Since bug#22397 is now fixed, I report this as a separate bug.

How to repeat:
mysql> /* first let's show that bug#22397 is fixed */
mysql> create event e_16 on schedule every 1 second do set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p_16 () alter event e_16 on schedule every @a second;
Query OK, 0 rows affected (0.01 sec)

mysql> set @a = null;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_16();
ERROR 1512 (HY000): Incorrect INTERVAL value: 'NULL'
mysql>
mysql> /* now let's try with a function instead of a variable */
mysql> create event e_17 on schedule every 1 second do set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> create function f_17 () returns int return 5;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p_17 () alter event e_17 on schedule every f_17() second;
Query OK, 0 rows affected (0.01 sec)

mysql> call p_17();
ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for the bug report. Verified on Suse 10 32-bit.

mysql> call p_17();
ERROR 2013 (HY000): Lost connection to MySQL server during query

It's actually an assert, will investigate more
(gdb) bt
#0  0xffffe410 in __kernel_vsyscall ()
#1  0x400f22c1 in raise () from /lib/tls/libc.so.6
#2  0x400f3b75 in abort () from /lib/tls/libc.so.6
#3  0x400eb903 in __assert_fail () from /lib/tls/libc.so.6
#4  0x0824383b in open_tables (thd=0x8e03170, start=0x42005c84, counter=0x42005c58, flags=0) at sql_base.cc:2864
#5  0x08243d80 in simple_open_n_lock_tables (thd=0x8e03170, tables=0x42005ca0) at sql_base.cc:3160
#6  0x08375e03 in Event_db_repository::open_event_table (this=0x8783c60, thd=0x8e03170, lock_type=TL_UNLOCK, table=0x0) at event_db_repository.cc:442
#7  0x083764d9 in Event_db_repository::update_event (this=0x8783c60, thd=0x8e03170, parse_data=0x8e01198, new_dbname=0x0, new_name=0x0) at event_db_repository.cc:663
#8  0x083779dd in Events::update_event (this=0x8783c80, thd=0x8e03170, parse_data=0x8e01198, rename_to=0x0) at events.cc:388
#9  0x08219c77 in mysql_execute_command (thd=0x8e03170) at sql_parse.cc:3900
#10 0x08362a92 in sp_instr_stmt::exec_core (this=0x6, thd=0x8e03170, nextp=0x6) at sp_head.cc:2551
#11 0x08362789 in sp_lex_keeper::reset_lex_and_exec_core (this=0x8df5984, thd=0x8e03170, nextp=0x4200653c, open_tables=false, instr=0x8df5958) at sp_head.cc:2422
#12 0x08362951 in sp_instr_stmt::execute (this=0x8df5958, thd=0x8e03170, nextp=0x4200653c) at sp_head.cc:2502
#13 0x0835f8f9 in sp_head::execute (this=0x8e0a2e0, thd=0x8e03170) at sp_head.cc:1096
#14 0x08360c85 in sp_head::execute_procedure (this=0x8e0a2e0, thd=0x8e03170, args=0x8e0370c) at sp_head.cc:1725
#15 0x0821b734 in mysql_execute_command (thd=0x8e03170) at sql_parse.cc:4618
#16 0x0821e881 in mysql_parse (thd=0x8e03170, inBuf=0x8e0a5e8 "call p_17()", length=11) at sql_parse.cc:6079
#17 0x08215394 in dispatch_command (command=COM_QUERY, thd=0x8e03170, packet=0x8df44d1 "call p_17()", packet_length=12) at sql_parse.cc:1828
#18 0x08214d2c in do_command (thd=0x8e03170) at sql_parse.cc:1612
#19 0x08213ffa in handle_one_connection (arg=0x0) at sql_parse.cc:1227
#20 0x40053aa7 in start_thread () from /lib/tls/libpthread.so.0
#21 0x40184c2e in clone () from /lib/tls/libc.so.6

Ok, this is a known problem, which exists because ha_commit() was moved too close to the write to mysql.event

There is a simpler example of this problem :

mysql> create function f_17 () returns int return 5;
Query OK, 0 rows affected (0.07 sec)

mysql> create event e_18 on schedule every f_17() second do select 123;
ERROR 1305 (42000): FUNCTION db2.f_17 does not exist
WTH??

mysql> show function status\G
*************************** 1. row ***************************
           Db: db2
         Name: f_17
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2006-10-04 15:37:45
      Created: 2006-10-04 15:37:45
Security_type: DEFINER
      Comment:
1 row in set (0.01 sec)
mysql> select db2.f_17();
+------------+
| db2.f_17() |
+------------+
|          5 |
+------------+
1 row in set (0.04 sec)

mysql> create event e_18 on schedule every f_17() second do select 123;
ERROR 2013 (HY000): Lost connection to MySQL server during query

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

ChangeSet@1.2338, 2006-10-05 16:03:33+02:00, andrey@example.com +7 -0
  Fix for bug#22830
  Events: crash with procedure which alters events with function
  
  Stored routine call in CREATE|ALTER EVENT is unsafe now. Their usage
  will complicate the code too much and could possible open a can of
  worms. Therefore, their usage is forbidden and only standard MySQL
  functions could be used as part of the expressions. This fix also fixes
  a similar problem in the KILL statement (kill some_user_func())
  
  To be documented: CREATE|ALTER allow expressions as part of the statement.
  These expessions can include invocations to functions but only to
  standard ones and not to stored functions from mysql.proc and neither
  UDF. Because KILL is not also safe called with a routine name as
  parameter, user supplied routines are also disabled in this case.

Sent review comments by email, changing to in progress.

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

ChangeSet@1.2331, 2006-10-31 18:22:56+01:00, andrey@example.com +8 -0
  Better fix for bug#22830
  Events: crash with procedure which alters events with function
  
  This fix also changes the handling of KILL command combined with
  subquery. It changes the error message given back to "not supported",
  from parse error. The error for CREATE|ALTER EVENT has also been changed
  to generate "not supported yet" instead of parse error.
  In case of a SP call, the error is "not supported yet". This change
  cleans the parser from code which should not belong to there. Still
  LEX::expr_allows_subselect is existant because it simplifies the handling
   of SQLCOM_HA_READ which forbids subselects.

Patch approved (the second version),
sent minor comments by email.

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

ChangeSet@1.2331, 2006-11-02 13:51:43+01:00, andrey@example.com +10 -0
  Better fix for bug#22830
  Events: crash with procedure which alters events with function
  
  Post-review CS
  
  This fix also changes the handling of KILL command combined with
  subquery. It changes the error message given back to "not supported",
  from parse error. The error for CREATE|ALTER EVENT has also been changed
  to generate "not supported yet" instead of parse error.
  In case of a SP call, the error is "not supported yet". This change
  cleans the parser from code which should not belong to there. Still
  LEX::expr_allows_subselect is existant because it simplifies the handling
   of SQLCOM_HA_READ which forbids subselects.

Will appear in 5.1.14

Direct and indirect usage of functions in CREATE/ALTER EVENT is prohibited. Led to crash before. Usage of subqueries is less restricted. It was forbidden before, now it's allowed if no tables are used like in:
CREATE EVENT xyz ON SCHEDULE EVERY (SELECT 1 FROM DUAL) SECOND...

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

ChangeSet@1.2374, 2006-11-28 20:40:16+03:00, kostja@bodhi.local +1 -0
  A fix for a memory leak itroduced by a fix for Bug#22830,
  reported as Bug#24486 "Valgrind warnings: sp_head(), 
  deadlock_innodb:events_grant".

If you're not saying that an event can't invoke a stored routine, then please look at the syntax as shown on http://dev.mysql.com/doc/refman/5.1/en/create-event.html and http://dev.mysql.com/doc/refman/5.1/en/alter-event.html and then tell me exactly which values/clauses don't admit stored function calls and/or references to values returned by stored functions. Also it's not clear whether or not built-functions or variables holding results from same are included in the prohibition.

Finally, where exactly are subqueries permitted, and which types remain prohibited? Are you saying that SELECT ... FROM DUAL is prohibited?

Thanks!

SELECT FROM DUAL is allowed as subquery as it does not touch any real table
Variables and builtin functions are allowed, because they don't touch any real tables (in theory).
SP calls are not allowed because this implies lookup in mysql.proc. However, the following event:
create event xyz on schedule at now() + interval 15 minute do alter event xyz on schedule at now() + func() minute;
will be created, but will emit an error when executed. The ALTER EVENT statement from the body will trigger an error.
It's forbidden in the 'schedule' as well as for STARTS and ENDS

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented feature/restriction change in 5.1.13 changelog and relevant sections of Events chapter in Manual.