This is an issue that has been bugging me for several months now, and one which I have yet to find a solution for.
The default max_execution_time when using the MySQL ODBC connector (8.0) in a Classic ASP application is set to 30000ms (30 seconds), and I can't figure out how to increase it.
I have a large table (400,000+ rows) and I'm using the UPDATE command to perform various calculations several times a day, usually this takes less than 30 seconds, but as the table continues to grow I'm beginning to see more and more [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.15]Query execution was interrupted, maximum statement execution time exceeded errors being logged.
I keep coming across the same answers when searching for a solution:
Set max_execution_time = x in my.ini
Or execute SET [GLOBAL]/[SESSION] max_execution_time = x; before
running a SQL command.
These solutions work fine, but only when you use a MySQL client (such as MySQL Workbench), but they make no difference when using the ODBC connector within a Classic ASP application.
The following code will always output max_execution_time: 30000
Dim dbConn, dbRS
Set dbConn = Server.CreateObject("ADODB.Connection") : dbConn.Open("DSN=my_system_dsn")
Set dbRS = dbConn.Execute("SHOW VARIABLES WHERE Variable_name = 'max_execution_time';")
Response.Write(dbRS("Variable_name") & ": " & dbRS("Value"))
dbRS.close() : Set dbRS = nothing
dbConn.close() : Set dbConn = nothing
Another solution that I've come across is to use:
SELECT /*+ MAX_EXECUTION_TIME(x) */ FROM TABLE...
But this only works for read-only SELECT statements, and not UPDATE commands.
It doesn't seem to matter what I change, whether it's in the MySQL ODBC Data Source Configuration, changes to the connection string, or the MySQL ini file, the max_execution_time remains at 30000ms when using the ODBC connector to access MySQL within Classic ASP.
I did find the following bug report and discussion on the MySQL website from 2016 where people were reporting the same issue with the ODBC 5.x connector when used with Classic ASP, but nobody was able to offer a solution.
I'm just wondering if anybody else has come across this issue and was able to find a way of increasing the max_execution_time value?
The solution thanks to @Shadow
Dim dbConn, dbRS
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open("DSN=my_system_dsn")
dbConn.CommandTimeout = 120
Set dbRS = dbConn.Execute("SHOW VARIABLES WHERE Variable_name = 'max_execution_time';")
Response.Write(dbRS("Variable_name") & ": " & dbRS("Value"))
dbRS.close() : Set dbRS = nothing
dbConn.close() : Set dbConn = nothing
Output: max_execution_time: 120000
Increase the CommandTimeout parameter of the ADO Connection or Command object before executing the query. This property configures how long the driver waits for the execution of a query.