Mysql – Query execution was interrupted, max_statement_time exceeded

MySQLmysql-5.7

I'm getting following error via Microsoft IIS 8' Error Log:

Query execution was interrupted, max_statement_time exceeded

MySQL is 5.7.11-log running on Windows 2012 and per MySQL :: MySQL Server Version Reference :: 1.5 Option/Variable Changes for mysqld 5.7 max_statement_time was introduced in 5.7.4 and removed in 5.7.8, but error still there…

This feature with some changes was ported from Twitter MySQL patches Statement Timeout · twitter/mysql Wiki.

WITHOUT switching to MySQL fork maintained and used at Twitter, what are my other options?

Please advise.

* UPDATE *

MySQL Bugs: #81071: Query execution was interrupted, max_statement_time exceeded

Best Answer

I was experiencing the same issue. Possible to set the global max_execution_time, but not the session max_execution_time:

SET global max_execution_time = 60000;  
SELECT @@global.max_execution_time -- 60000

SET session max_execution_time=300000;
SELECT @@session.max_execution_time -- 15000

I used a hint as workaround:

select /*+ MAX_EXECUTION_TIME(300000) */ * from table
where ...

Source: https://stackoverflow.com/questions/415905/how-to-set-a-maximum-execution-time-for-a-mysql-query

Related Solutions

MySQL: Lock wait timeout exceeded

This is definitely a head-scratcher, but here's one possibility, especially if you're running MySQL 5.1 or earlier. MySQL Bug #37346 addresses an issue where there are locks at two different layers that are (at least partially) unaware of each other's existence... an uncommitted transaction is holding locks inside the storage engine (invisible to MySQL's core), while the ALTER TABLE is holding a different kind of lock, outside the storage engine (invisible to InnoDB).

In the bug report, when the ALTER TABLE is almost done, it bumps into the inner locks and ultimately times out with the message you see.

This is referenced as "fixed" in the release notes for 5.5.3 but I didn't find it mentioned in 5.1.x, which suggests to me that it might still exist there. It seems like a long shot but it did seem worth mentioning.

Until I found the above, my initial thoughts had to do with the table that is referenced by fk_foo_1... I was going to speculate that InnoDB might be trying something unnecessary related to validation of the foreign key constraint (unnecessary, since the constraint is about to disappear)... or that an uncommitted update or delete on the referenced table might be setting locks on the table you're trying to alter... an update or delete "over there," needing to be validated or trying to cascade "over here," while the ALTER TABLE is underway, and not waiting as it should for the lock held by the ALTER TABLE.

MySQL – Determining Called Queries

You could enable binary logging. If you have not done so already.

To find out if binary logging is enabled, run this:

SHOW MASTER STATUS;

If you get back, Empty set, it is not enabled.

If you have not, please add this to my.cnf under the [mysqld] section

[mysqld]
log-bin = mysql-bin

Then, restart mysql.

From that point forward, you could look for all changes within timeframes.

For example, suppose you run SHOW BINARY LOGS; and get something like this:

mysql> show binary logs;
+----------------+------------+
| Log_name       | File_size  |
+----------------+------------+
| bin-log.000645 |    5933323 |
| bin-log.000646 | 1073745654 |
| bin-log.000647 | 1073759862 |
| bin-log.000648 | 1073757229 |
| bin-log.000649 | 1073776952 |
| bin-log.000650 | 1073791824 |
| bin-log.000651 | 1073743420 |
| bin-log.000652 | 1073785139 |
| bin-log.000653 |  338795678 |
+----------------+------------+
9 rows in set (0.00 sec)

You could then use the mysqlbinlog utility to dump a text representation of all the SQL commands executed within specified timeframes along with the timestamps.

The options to use are

What to do next

Go find the folder where the binary logs are kept. From the command line run this

ls -l bin-log.*
total 7737092
-rw-rw---- 1 mysql mysql    5933323 Aug 17 17:26 bin-log.000645
-rw-rw---- 1 mysql mysql 1073745654 Aug 17 19:04 bin-log.000646
-rw-rw---- 1 mysql mysql 1073759862 Aug 17 20:45 bin-log.000647
-rw-rw---- 1 mysql mysql 1073757229 Aug 17 22:18 bin-log.000648
-rw-rw---- 1 mysql mysql 1073776952 Aug 18 00:05 bin-log.000649
-rw-rw---- 1 mysql mysql 1073791824 Aug 18 05:18 bin-log.000650
-rw-rw---- 1 mysql mysql 1073743420 Aug 18 08:59 bin-log.000651
-rw-rw---- 1 mysql mysql 1073785139 Aug 18 11:31 bin-log.000652
-rw-rw---- 1 mysql mysql  338795678 Aug 18 12:30 bin-log.000653
-rw-rw---- 1 mysql mysql        351 Aug 18 11:31 bin-log.index

The datetime stamp for each file represents the last time the binlog received SQL commands.

You would request the commands by the timeframe and by the binlog names ordered by timestamp

To see all SQL commands executed from midnight to 8:00AM today, you run this

SQLCMDS=/tmp/SQLCommands.txt
DT1="2014-08-18 00:00:00"
DT2="2014-08-18 08:00:00"
BINLOGS="bin-log.000649 bin-log.000650 bin-log.000651"
mysqlbinlog --start-datetime="${DT1}" --stop-datetime="${DT2}" ${BINLOGS} > ${SQLCMDS}

The output file, /tmp/SQLCommands.txt, will contain every INSERT, UPDATE, DELETE, and DDL command executed in the timeframe you requested. Just make sure you supply the necessary binlogs. If you include all binlogs, the mysqlbinlog dump will still work but would take longer because it would parse through binary logs it does not need (i.e., you don't need to parse bin-log.000645 - bin-log.000648, bin-log.000652, bin-log.000653).

Give it a Try !!!

CAVEAT

If you want to see everything, including the SELECTs, you go with Craig Efrein's answer.

UPDATE 2014-08-21 10:45 EDT

@CraigEfrein made a excellent point in his comment

Activating bin-logs also have an impact on performance and disk space.

To reduce disk I/O based on binary logging, you should config binlogs to be stored on a separate disk. See my old post How do I determine how much data is being written per day through insert, update and delete operations? (under the subheading SSD LIFESPAN)