Elena Stepanova added a comment - 2016-12-28 18:02 Which values for the table size options did you try? 64M seems to be working for me: --tmp_table_size=64M --max_heap_table_size=64M

Shawn Weeks added a comment - 2016-12-30 17:08 See attached test results. Are you not allowed to set those variables after the system is up and running? I'm a little confused by why those options affect it anyway as it should write temp to disk if it's too big for memory. Not doing that limits the use of recursive queries quite a bit especially as they get larger.

Shawn Weeks added a comment - 2016-12-30 17:15 If I add those parameters to the config file it works. I was trying to set them after startup.

Shawn Weeks added a comment - 2016-12-30 17:18 http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html Documentation says those variables should be dynamic though and "If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table." seems to indicate it really shouldn't matter so it still seems like a bug.

Elena Stepanova added a comment - 2017-01-03 12:53 I'll re-direct the last question, why it fails with an error rather than converting to an on-disk table, to the CTE expert igor , who will be able to determine whether it's a bug or specifics of this kind of queries which needs to be documented. Regarding dynamic vs static variables – yes, you can absolutely set these options at runtime too; however, both options have both global and session scope. So, as it usually works in MySQL/MariaDB, session values are set when the connection is established, and when you change global values, your session values remain old: MariaDB [test]> select @@tmp_table_size, @@ global .tmp_table_size, @@max_heap_table_size, @@ global .max_heap_table_size; + ------------------+-------------------------+-----------------------+------------------------------+ | @@tmp_table_size | @@ global .tmp_table_size | @@max_heap_table_size | @@ global .max_heap_table_size | + ------------------+-------------------------+-----------------------+------------------------------+ | 16777216 | 16777216 | 16777216 | 16777216 | + ------------------+-------------------------+-----------------------+------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> set global tmp_table_size=67108864; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> set global max_heap_table_size=67108864; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select @@tmp_table_size, @@ global .tmp_table_size, @@max_heap_table_size, @@ global .max_heap_table_size; + ------------------+-------------------------+-----------------------+------------------------------+ | @@tmp_table_size | @@ global .tmp_table_size | @@max_heap_table_size | @@ global .max_heap_table_size | + ------------------+-------------------------+-----------------------+------------------------------+ | 16777216 | 67108864 | 16777216 | 67108864 | + ------------------+-------------------------+-----------------------+------------------------------+ 1 row in set (0.00 sec) Thus, the new values don't affect your current session. You either need to re-connect so that the new connection could pick up the new values, or change your session variables instead: MariaDB [test]> select @@tmp_table_size, @@ global .tmp_table_size, @@max_heap_table_size, @@ global .max_heap_table_size; + ------------------+-------------------------+-----------------------+------------------------------+ | @@tmp_table_size | @@ global .tmp_table_size | @@max_heap_table_size | @@ global .max_heap_table_size | + ------------------+-------------------------+-----------------------+------------------------------+ | 16777216 | 16777216 | 16777216 | 16777216 | + ------------------+-------------------------+-----------------------+------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> set tmp_table_size=67108864; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> set max_heap_table_size=67108864; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select @@tmp_table_size, @@ global .tmp_table_size, @@max_heap_table_size, @@ global .max_heap_table_size; + ------------------+-------------------------+-----------------------+------------------------------+ | @@tmp_table_size | @@ global .tmp_table_size | @@max_heap_table_size | @@ global .max_heap_table_size | + ------------------+-------------------------+-----------------------+------------------------------+ | 67108864 | 16777216 | 67108864 | 16777216 | + ------------------+-------------------------+-----------------------+------------------------------+ 1 row in set (0.01 sec) MariaDB [test]> drop table if exists test_table; Query OK, 0 rows affected (0.81 sec)   MariaDB [test]> create table test_table (id int , test_data varchar (36)) engine=innodb; Query OK, 0 rows affected (0.49 sec)   MariaDB [test]> insert into test_table(id, test_data) -> select id, test_data -> from ( -> with recursive data_generator(id, test_data) as ( -> select 1 as id, uuid() as test_data -> union all -> select id + 1, uuid() from data_generator where id < 500000 -> ) -> select * from data_generator -> ) as a; Query OK, 500000 rows affected (1 min 6.06 sec) Records: 500000 Duplicates: 0 Warnings: 0

Igor Babaev (Inactive) added a comment - 2017-01-04 07:40 This is a real serious bug in the CTE code.

Igor Babaev (Inactive) added a comment - 2017-01-05 21:59 The fix for this bug was pushed into the 10.2 tree.

Shawn Weeks added a comment - 2017-01-12 00:31 Tested on a local build of 10.2.4 and the issue is now gone. I generated a table of 10 Million Rows without raising an error on the default temp table size. Thanks