Description:
MySQL has long been missing some pretty essential commands in its prepared statement lexicon. I know we've all been whining for years, but now that 5.7 is mature and still nothing, this needs to be addressed.
Picked the three biggest complaints for this report. These go back a long ways (i.e. http://bugs.mysql.com/bug.php?id=42337) but are poorly documented.
The following commands need to be added to PREPARE support, and therefore stored procedure support (wouldn't make sense in other routine types, in any way I can think of).
{LOCK|UNLOCK} TABLES
in procs: no
in prepare: no
This is documented as separate from the basic prepare<->procedure syntax sharing, though it is missing from both. See top line item:
http://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html
{CREATE|DROP} TRIGGER
in procs: no
in prepare: no
Complained about the loudest, as you can do a lot of neat stuff with dynamic triggers from procedures. Generally also requires lock/unlock support mentioned above for change atomicity.
{CREATE|ALTER|DROP} EVENT
in procs: yes
in prepare: no
As this is supported in procs but not events, there's a hacky way to do it dynamically! I'll plop that in 'how to repeat' - don't disable it as a risk (I'm sure many people are using the workaround), just fix the missing non-hacky support!
Support list for reference:
https://github.com/mysql/mysql-server/blob/e0e0ae2ea27c9bb76577664845507ef224d362e4/sql/sq...
http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
How to repeat:
example of hacky workaround here:
use test;
CREATE DEFINER=`root`@`localhost` EVENT `test_event` ON SCHEDULE EVERY 10 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION PRESERVE ENABLE DO select 1;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `event_hack_proc`()
SQL SECURITY INVOKER
begin
insert into mysql.proc
set `db` ='test',
`name`='tmp_proc',
`type`='PROCEDURE',
`specific_name`='tmp_proc',
`language`='SQL',
`sql_data_access`='CONTAINS_SQL',
`is_deterministic`='NO',
`security_type`='DEFINER',
`param_list`='',
`returns`='',
`body`="alter definer='root'@'localhost' event test_event ON SCHEDULE EVERY 40 SECOND",
`definer`='root@localhost',
`created`=NOW(),
`modified`=NOW(),
`sql_mode`='NO_AUTO_VALUE_ON_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION',
`comment`='',
`character_set_client`='utf8',
`collation_connection`='utf8_general_ci',
`db_collation`='utf8_general_ci',
`body_utf8`="alter definer='root'@'localhost' event test_event ON SCHEDULE EVERY 40 SECOND";
call tmp_proc();
delete from mysql.proc where db='test' and name='tmp_proc' and type='PROCEDURE';
end;;
delimiter ;
call event_hack_proc;
show create event test_event;
Suggested fix:
add the support already! it's widely desired, and needed to be compatible in 2016, with stack reduction and automation as industry wide goals. DB-layer admin procs are very popular and getting moreso.
it might not even be that hard to add! seems like most of the work here would be testing, not really a lot of coding.
