MySQL Bugs: #75608: Event scheduler does not allow subsecond scheduling and gives incorrect error

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:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.6.24, 5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The event scheduler complains that .5 is either negative or too large.  Sub-second scheduling should be supported by the event scheduler.

How to repeat:
For example:
-- -----------------------------------------------
--  Event for capturing I_S.INNODB_TRX
-- -----------------------------------------------
CREATE DATABASE IF NOT EXISTS percona;

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;

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;

Result:
ERROR 1542 (HY000): INTERVAL is either not positive or too big

Suggested fix:
Allow subsecond scheduling, or at least report that the value may be too small

Hello Justin Swanhart,

Thank you for the report and test case.

Thanks,
Umesh

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

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

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

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.

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.