title: MySQL "max_execution_time" defaults to 30000ms when using the ODBC connector in Classic ASP, and cannot be changed
body: <p>This is an issue that has been bugging me for several months now, and one which I have yet to find a solution for.</p>
  
  <p>The default <code>max_execution_time</code> 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.</p>
  
  <p>I have a large table (400,000+ rows) and I'm using the <code>UPDATE</code> 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 <code>[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.15]Query execution was interrupted, maximum statement execution time exceeded</code> errors being logged.</p>
  
  <p>I keep coming across the same answers when searching for a solution:</p>
  
  <ul>
  <li><p>Set <code>max_execution_time = x</code> in <code>my.ini</code></p></li>
  <li><p>Or execute <code>SET [GLOBAL]/[SESSION] max_execution_time = x;</code> before
  running a SQL command.</p></li>
  </ul>
  
  <p>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.</p>
  
  <p>The following code will always output <code>max_execution_time: 30000</code></p>
  
  <pre class="lang-all-vbs prettyprint-override"><code>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") &amp; ": " &amp; dbRS("Value"))
  
  dbRS.close() : Set dbRS = nothing
  dbConn.close() : Set dbConn = nothing
  </code></pre>
  
  <p>Another solution that I've come across is to use:</p>
  
  <p><code>SELECT /*+ MAX_EXECUTION_TIME(x) */ FROM TABLE...</code></p>
  
  <p>But this only works for read-only SELECT statements, and not UPDATE commands.</p>
  
  <p>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 <code>max_execution_time</code> remains at 30000ms when using the ODBC connector to access MySQL within Classic ASP.</p>
  
  <p>I did find <a href="https://bugs.mysql.com/bug.php?id=81071" rel="nofollow noreferrer">the following bug report and discussion</a> 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.</p>
  
  <p>I'm just wondering if anybody else has come across this issue and was able to find a way of increasing the <code>max_execution_time</code> value?</p>
  
  <hr>
  
  <p><strong>The solution thanks to @Shadow</strong></p>
  
  <pre><code>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") &amp; ": " &amp; dbRS("Value"))
  
  dbRS.close() : Set dbRS = nothing
  dbConn.close() : Set dbConn = nothing
  </code></pre>
  
  <p>Output: <code>max_execution_time: 120000</code></p>