Bug #81071 Query execution was interrupted, max_statement_time exceeded
Submitted: 13 Apr 2016 15:36 Modified: 22 Aug 2017 10:06
Reporter: Dmitry Chorine Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.7.11-log OS:Windows (2012)
Assigned to: CPU Architecture:Any

[13 Apr 2016 15:36] Dmitry Chorine
Description:
I'm getting following error via Microsoft IIS 8' Error Log:

Query execution was interrupted, max_statement_time exceeded

per "MySQL :: MySQL Server Version Reference :: 1.5 Option/Variable Changes for mysqld 5.7" (https://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-optvar-chan...), "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. (https://github.com/twitter/mysql/wiki/Statement-Timeout)

---
I asked same question on Database Administrators Stack Exchange as well:

mysql 5.7 - Query execution was interrupted, max_statement_time exceeded - Database Administrators Stack Exchange

http://dba.stackexchange.com/questions/134923/query-execution-was-interrupted-max-statemen...

How to repeat:
if I to run same query via client directly via mysql client, I don't encounter any errors, so this "bug" maybe more related to mysql connector rather then mysql server, i'm not 100% sure

Suggested fix:
after downgrading to mysql 5.6, I no longer get that error... nor really a solution, not even band-aid either, but at least IIS application is not crashing anymore)
[15 Apr 2016 10:00] Chiranjeevi Battula
Hello  Dmitry Chorine,

Thank you for the bug report.
Could you please provide more details like which MySQL connector & version are you using, create tables statement, sample data(please make it as private if you prefer)  to confirm this issue at our end?

Thanks,
Chiranjeevi.
[18 Apr 2016 3:36] Dmitry Chorine
Connector/ODBC 5.3.6
[19 Apr 2016 7:35] Chiranjeevi Battula
Hello  Dmitry Chorine,

Thank you for your feedback.
I could not repeat the issue at our end using with Connector / ODBC 5.3.6. 
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.
[18 Oct 2016 5:55] Charlie Sain
I'm having a similar issue. It seems to be related to the ODBC Connector since it began immediately after we upgraded to 5.3. 

This is a legacy 'Classic' ASP application. The query is a known long-running one in our backend "heavy" script reporting logic. The queries in this logic take 4-5 minutes to execute...this is a known issue with no simple workarounds.

Here is the error:
[MySQL][ODBC 5.3(w) Driver][mysqld-5.7.16-log]Query execution was interrupted

The query seems to execute for exactly 30 seconds and then the error message appears. Since the default time for max_statement_timeout / max_execution_timeout is, I believe, 30 seconds...I suspect that is the issue...but I can't figure out how to override this value in the connection string.

It appears that max_statement_timeout was added and then removed to MySQL server, but support for it at (and how to disable it) by connector version is unclear.

Any help would be greatly appreciated!

CS
[3 Dec 2016 20:00] Johnny C.L.
we are receiving same Error:
[MySQL][ODBC 5.3(w) Driver][mysqld-5.7.16]Query execution was interrupted, maximum statement execution time exceeded (Microsoft OLE DB Provider for ODBC Drivers)

If we test in 5.1 ODBC we did not received any problem
ASP Classic ASP also

ODBC 5.3.6
Mysql 5.7.16
Win64
[8 Mar 2017 15:22] Vinicius Oliveira Vinicius
I have the same problem. I used the ODBC Connector 5.2.7 Driver with windows 7 32 bit with no problem with MS Access. However the company that I work, migrated all computers to windows 10 64 bit and all programs needed to be reinstalled. I installed the ODBC Driver Connector 5.3.07 64bit and for small tables / queries they open in Access without problem, however large tables / queries now get this error.

The server is MySQL 5.7.10 community edition.
[8 Mar 2017 15:49] Vinicius Oliveira Vinicius
I manually added the options 

    max_execution_time = 0

In my my.ini on server (5.7.10 version) in [mysqld] section. I restarted the server and it worked with the ODBC Connector 5.3.7

You think you have a bug applying the default configuration as stated at https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_...
[9 Mar 2017 12:43] Vinicius Oliveira Vinicius
Today I executed a query that is longer than 60 seconds and the bug is back. Yesterday my queries were running in less 60 seconds (Before adding max_execution_time = 0 to my.ini, neither were they running).

I executed this command in my PC (ODBC 5.3.07):
show variables like '%max_execution_time%; 

Results: 
max_execution_time	60000

obs: 60000 = (60 seconds)

I execute a to configure with 5 minutes
SET global max_execution_time = 300000;   
show variables like '%max_execution_time%; 

Results:
max_execution_time	60000;

In other machine with ODBC (5.2.7) I execute:

show variables like '%max_execution_time%; 

Results:
max_execution_time	300000;

In another test:

SET global max_execution_time = 0;

In my Machine (ODBC 5.3.07);

Results: 
max_execution_time	60000

In the other Machine  (ODBC 5.2.7)

Results: 
max_execution_time	0;

I think this 5.3 driver is frozen with max_execution_time setting to 60000 (60 seconds)
[22 Mar 2017 4:00] Brett Jacobson
Is there any progress on this?
I have the same problem. Since upgrading to MySQL 5.7 and ODBC 5.3 I am unable to execute queries over ODBC on large data sets
[24 Mar 2017 18:25] Lonnie King
I am also having the same issue on Windows Server 2008 R2 with MySQL 5.7.17 and ODBC driver 5.3.

I upgraded due to PCO compliance changes. Everything worked fine before the upgrade but since then, my bigger customers are having numerous issues. 

When it fails, I can see this error in Visual Studio 2012:
ERROR [HY000] [MySQL][ODBC 5.3(a) Driver mysqld-5.7.17-log]Query execution was interrupted, maximum statement execution time exceeded

If this is a driver issue, I need some very near term options...including potentially downgrading to an older driver if that is possible.

My customers are very unhappy, as am I so please advise soonest.
[25 Mar 2017 15:08] Lonnie King
Since I have seen no other solutions to what I can now confirm is a bug in the 5.3 ODBC driver, I did the following:
• re-compiled my .NET code from 64x to ANY CPU 
• installed the 5.1 ODBC driver side by side with the 5.3 driver, 
• created the new ODBC DSN to use the 5.1 driver
• updated the web.cfg file to point to the 5.1 driver

Once I was using the 5.1 driver not only was my app working as it used to with no errors or breaks during debugging, but there was a marked increase in speed of presenting the data in the GUI.

Troubleshooting this issue over the last couple of weeks has wasted an incredible amount of time.

I want to thank everyone on this thread, and particularly Vinicius, for documenting your efforts.  I'm not sure if my solution will help you, but I sincerely hope so.

Oracle, please let us know when you get this corrected.
[21 Aug 2017 13:12] MySQL Verification Team
I think this problme caused these conditions.

(1) Caller set SQL_ATTR_QUERY_TIMEOUT
(2) Connector/ODBC version is 5.3.6 or later.
(3) MySQL Server version is 5.7.4 or later.

ODBC has a SQL_ATTR_QUERY_TIMEOUT parameter, but Connector/ODBC 5.3.5 or older just ignored it. source code in ~/driver/options.c

        case SQL_ATTR_QUERY_TIMEOUT:
        case SQL_ATTR_KEYSET_SIZE:
        case SQL_ATTR_CONCURRENCY:
        case SQL_ATTR_NOSCAN:
        default:
            /* ignored */
            break;

But Connector/ODBC 5.3.6 or later, try to set.

        case SQL_ATTR_QUERY_TIMEOUT:
            /* Do something only if the handle is STMT */
            if (HandleType == SQL_HANDLE_STMT)
            {
              return set_query_timeout((STMT*)Handle, (SQLULEN)ValuePtr);
            }
            break;

        case SQL_ATTR_KEYSET_SIZE:
        case SQL_ATTR_CONCURRENCY:
        case SQL_ATTR_NOSCAN:
        default:
            /* ignored */

source code in ~/driver/utility.c

3983 SQLRETURN set_query_timeout(STMT *stmt, SQLULEN new_value)
3984 {
3985   char query[44];
3986   SQLRETURN rc= SQL_SUCCESS;
3987
3988   if (new_value == stmt->stmt_options.query_timeout ||
3989       !is_minimum_version(stmt->dbc->mysql.server_version, "5.7.4"))
3990   {
3991     /* Do nothing if setting same timeout or MySQL server older than 5.7.4 */
3992     return SQL_SUCCESS;
3993   }
3994
3995   if (new_value > 0)
3996   {
3997     unsigned long long msec_value= (unsigned long long)new_value * 1000;
3998     sprintf(query, "set @@max_execution_time=%llu", msec_value);
3999   }
4000   else
4001   {
4002     strcpy(query, "set @@max_execution_time=DEFAULT");
4003     new_value= 0;
4004   }

Thus, If caller set SQL_ATTR_QUERY_TIMEOUT and using Connector/ODBC 5.3.6 or later (with MySQL Server 5.7.4 or later). This problem should occur.

Please check application's default (ODBC Caller) for SQL_ATTR_QUERY_TIMEOUT.
[22 Aug 2017 1:12] MySQL Verification Team
MS Access's default is 60-sec, so please check this article and modify "ODBC Timeout"

http://www.geeksengine.com/article/how-to-change-timeout-value-for-access-sql.html
[22 Aug 2017 10:06] Bogdan Degtyariov
It is not a but, but a setting in MS Access, which has to be changed just as Meiji advised.
[25 Dec 2017 1:18] MySQL Verification Team
Check this document(default timeout = 30)

https://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.commandtimeout(v=vs....

And it's good sample to use it.

https://docs.faircom.com/doc/odbc/48581.htm
[30 Dec 2017 13:47] Vinicius Oliveira Vinicius
But with in same enviroment (Windows/Access) with ODBC Connector 5.2.7 the queries perform normally, including queries that take up to 15 minutes to run

This is a bug of driver...
[5 Nov 2021 16:20] Jonathan Airey
I had similar errors...what I did to resolve the problem was the following.
(Using MySQL Workbench) went to the admin tab and doubled the following Server Variables to be: 
connect_timeout 20
mysqlx_connect_timeout 60
mysqlx_read_timeout 60
net_read_timeout 60

I'm not sure at this point which one(s) resolved the problem but it hasn't come up since.