Bug #77959 max_statement_time test is unstable
Submitted: 6 Aug 2015 10:45 Modified: 6 Aug 2015 11:25
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Tests Severity:S3 (Non-critical)
Version:5.7.8, 5.7.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: max_statement_time, mtr, testsuite

[6 Aug 2015 10:45] Laurynas Biveinis
Description:
main.max_statement_time fails intermittently with

main.max_statement_time                  w3 [ fail ]
        Test ended at 2015-08-05 16:00:24

CURRENT_TEST: main.max_statement_time
--- /mnt/workspace/mysql-5.7-param/BUILD_TYPE/debug/Host/centos6-64/mysql-test/r/max_statement_time.result	2015-08-04 09:25:11.000000000 +0300
+++ /mnt/workspace/mysql-5.7-param/BUILD_TYPE/debug/Host/centos6-64/build/mysql-test/var/3/log/max_statement_time.reject	2015-08-05 19:00:24.128295502 +0300
@@ -232,6 +232,7 @@
 SELECT /*+ MAX_EXECUTION_TIME(3600000) */ * FROM t2;
 f1
 0
+0
 DELETE FROM t2;
 SET @@global.event_scheduler= @global_event_scheduler_status;
 SET @@global.max_execution_time= 0;

mysqltest: Result content mismatch

The relevant testcase bit is

# Case 7.3: Events.
#           Execution time limit is not applicable to SELECTs of events.
SET @global_event_scheduler_status= @@global.event_scheduler;
SET @@global.event_scheduler= ON;
SET @@global.max_execution_time= 1;
CREATE TABLE t2 (f1 int);

# Both of the following selects are interrupted because of timeout.
SELECT SLEEP(2) into @a;
SELECT @a;

delimiter |;

# Specifying MAX_EXECUTION_TIME for SELECTs of events is ignored.
CREATE EVENT event1 ON SCHEDULE AT CURRENT_TIMESTAMP
DO BEGIN
  SELECT SLEEP(2) into @a;
  SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(2) into @b;
  INSERT INTO t2 VALUES(@a);
  INSERT INTO t2 VALUES(@b);
END|
DROP EVENT event1|
TRUNCATE TABLE t2;

CREATE EVENT event1 ON SCHEDULE AT CURRENT_TIMESTAMP
DO BEGIN
  SELECT SLEEP(2) into @a;
  INSERT INTO t2 VALUES(@a);
END
|

delimiter ;|

--echo # Wait until at least one instance of event is executed.
let $wait_condition= SELECT /*+ MAX_EXECUTION_TIME(3600000) */ COUNT(*) FROM t2;
--source include/wait_condition.inc

#Since MAX_EXECUTION_TIME is not applicable for events & SELECTs of event,
#selects of event1 are not interruped so table contains 0 value.
SELECT /*+ MAX_EXECUTION_TIME(3600000) */ * FROM t2;

How to repeat:
Not sure. Run the testcase often enough? Seen on debug builds so far. Not encountered in 5.7.7 or earlier.

Suggested fix:
The above testcase snippet has one suspicious bit, introduced in 5.8.8:

delimiter |;
...
DROP EVENT event1|
TRUNCATE TABLE t2;

CREATE EVENT event1 ON SCHEDULE AT CURRENT_TIMESTAMP
...
delimiter ;|

Shouldn't the TRUNCATE TABLE statement be terminated with | ?
[6 Aug 2015 11:25] Umesh Shastry
Hello Laurynas,

Thank you for the report.
Observed that max_statement_time failed intermittently but with different reasons.

Thanks,
Umesh
[6 Aug 2015 11:25] Umesh Shastry
test results

Attachment: 77959.results (application/octet-stream, text), 8.36 KiB.