CTAS or ITAS - max_execution_time

MySQL & MariaDB Percona Server for MySQL 8.0
,
1

Hi Guys!
Got a concern regarding execution time limit for specific type of SQL statements. We are migrating our database from Mariadb to Percona for Mysql and we are used to rely on the "set statement max_statement_time for " (Mariadb scope) apart for select statements, the main extra usage is in Create [temporary] table as Select or Insert as Select.
I’ve further checked that when applying the limit on the select clause, as this:

CREATE TEMPORARY TABLE tmp (id int) AS SELECT /*+ MAX_EXECUTION_TIME(xx) */ …

the limit is not working properly. I’ve tested the case when an specific select gets interrupted, but when the same select is part of a CTAS , the interruption is not raised.

Also the same check was done as a CREATE TABLE + INSERT AS SELECT , but got same results. Another check was on a set local variable max_execution_time, but again not success.

This CTAS max_execution_time are not supported at percona mysql? or is there any workaround possible to set this limit in this type of clauses?

Thanks you in advance for any help :slight_smile:

1 Like
2

Percona Server matches MySQL behavior in this case

https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-execution-time

This is considered to be a hint and it only works for SELECT questions and ignored for others. It is not the best usability in my opinion and I’d file a bug/FR with MySQL team so they are aware there is demand for this feature beyond SELECTs

1 Like
3

Thanks a lot Peter for your replay, i’m creating a FR for MySQL team on this matter and in the meanwhile i’ll try out a workaround with pt-kill schedules or similar approach.

Thanks a lot!

Best regards.

1 Like
4

https://bugs.mysql.com/bug.php?id=102903

1 Like