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

Note

Please note, the full error text is Query compilation timed out and cannot be executed... Set 'max_compilation_time_s' to equal or higher to try recompiling or 'interpreter_mode' session variable to 'mbc' or 'compile_lite' to only interpret the query and try running the query again.

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.

    SET GLOBAL max_compilation_time_s = 601;
    Query OK, 0 rows affected (0.02 sec)
    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.

    SET SESSION interpreter_mode = mbc;
    Query OK, 0 rows affected (0.02 sec)
    SELECT @@interpreter_mode;
    +--------------------+
    | @@interpreter_mode |
    +--------------------+
    |                mbc |
    +--------------------+
    1 row in set (0.00 sec)
    SET SESSION interpreter_mode = compile_lite;
    Query OK, 0 rows affected (0.02 sec)
    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.

Last modified: August 16, 2023

Was this article helpful?