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: | |
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
[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.