Bug #75608 | Event scheduler does not allow subsecond scheduling and gives incorrect error | ||
---|---|---|---|
Submitted: | 23 Jan 2015 20:07 | Modified: | 4 Feb 2015 19:37 |
Reporter: | Justin Swanhart | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
Version: | 5.6.24, 5.7.6 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[23 Jan 2015 20:07]
Justin Swanhart
[25 Jan 2015 7:11]
MySQL Verification Team
Hello Justin Swanhart, Thank you for the report and test case. Thanks, Umesh
[25 Jan 2015 7:12]
MySQL Verification Team
// 5.6.24 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.24 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.24-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS percona; Query OK, 1 row affected (0.00 sec) mysql> mysql> CREATE TABLE IF NOT EXISTS percona.innodb_trx_history -> ( ts timestamp, -> key key_ts(ts), -> key key_trx_id(trx_id) -> ) -> as -> SELECT *, null as ts -> FROM INFORMATION_SCHEMA.INNODB_TRX -> LIMIT 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE EVENT IF NOT EXISTS percona.collect_innodb_trx -> ON SCHEDULE EVERY .5 SECOND -> DO -> INSERT INTO percona.innodb_trx_history -> SELECT *, NOW() -> FROM INFORMATION_SCHEMA.INNODB_TRX; ERROR 1542 (HY000): INTERVAL is either not positive or too big
[25 Jan 2015 7:12]
MySQL Verification Team
// 5.7.6 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> CREATE DATABASE IF NOT EXISTS percona; Query OK, 1 row affected (0.00 sec) mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS percona.innodb_trx_history -> ( ts timestamp, -> key key_ts(ts), -> key key_trx_id(trx_id) -> ) -> as -> SELECT *, null as ts -> FROM INFORMATION_SCHEMA.INNODB_TRX -> LIMIT 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE EVENT IF NOT EXISTS percona.collect_innodb_trx -> ON SCHEDULE EVERY .5 SECOND -> DO -> INSERT INTO percona.innodb_trx_history -> SELECT *, NOW() -> FROM INFORMATION_SCHEMA.INNODB_TRX; ERROR 1542 (HY000): INTERVAL is either not positive or too big
[25 Jan 2015 7:13]
MySQL Verification Team
// 5.6 if (interval_tmp.neg || expression == 0 || expression > EVEX_MAX_INTERVAL_VALUE) { my_error(ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG, MYF(0)); DBUG_RETURN(EVEX_BAD_PARAMS); } There is no sub-second interval but still error should have been more clearer than "INTERVAL is either not positive or too big" interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
[4 Feb 2015 19:37]
Justin Swanhart
You can use fractions of HOUR, why can't you use fractions of SECOND. There does not have to be a subsecond interval for subsecond scheduling.
[19 Oct 2017 9:57]
Simon Mudd
I use the event scheduler for master event injection. (for tracking replication delay). currently using 1 second events. I know some people use external injections at a higher frequency e.g. 100ms and I think it would be good to be able to do something similar. 8.0.3 does not have this functionality either. This would be a nice feature for some of us.