ERROR 2408 (HY000): ER_COMPILATION_TIMEOUT: Query compilation timed out and cannot be executed

Issue

A query compilation timeout occurs when the max_compilation_time_s has been reached. Once the compilation thread is killed, this error is issued to the client. An error is printed to the tracelog: WARN: Compilation of query exceeded set flag time and was terminated: query_text. Internally, the query will be marked with a kill flag. If the same query is executed again, it will automatically and immediately fail successive execution attempts regardless if the query hits the max_compilation_time_s threshold again.

Solutions

• Clear the kill flag by resetting max_compilation_time_s to it's current or higher value on the master aggregator.

  SQL

  Copy

  SET GLOBAL max_compilation_time_s = 601;

  Query OK, 0 rows affected (0.02 sec)

  SQL

  Copy

  SELECT @@max_compilation_time_s;

  +--------------------------+
  | @@max_compilation_time_s |
  +--------------------------+
  |                      601 |
  +--------------------------+
  1 row in set (0.00 sec)

  Note

  If an asynchronous compile limit is being reached, its worthwhile to investigate why and optionally increase the limit to be above the threshold of the queries that are triggering it.

• Set interpreter_mode session variable to mbc or compile_lite so the query will interpret only. Then try running the query again.

  SQL

  Copy

  SET SESSION interpreter_mode = mbc;

  Query OK, 0 rows affected (0.02 sec)

  SQL

  Copy

  SELECT @@interpreter_mode;

  +--------------------+
  | @@interpreter_mode |
  +--------------------+
  |                mbc |
  +--------------------+
  1 row in set (0.00 sec)

  SQL

  Copy

  SET SESSION interpreter_mode = compile_lite;

  Query OK, 0 rows affected (0.02 sec)

  SQL

  Copy

  SELECT @@interpreter_mode;

  +--------------------+
  | @@interpreter_mode |
  +--------------------+
  |       compile_lite |
  +--------------------+
  1 row in set (0.00 sec)

• View the query plan to find out which parts of the query are taking the longest time to execute. Optimize that part of your query statement. See EXPLAIN, PROFILE, or SingleStore Visual Explain for more details.