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 tombc
orcompile_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 or PROFILE for more details.