Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.2
-
None
-
Windows 10 Pro 1607 x64, 128GB RAM, i7 6800k MariaDB 10.2.2 Beta
Description
When using a Recursive CTE to generate a sample data set I'm getting a table is full error. It seems to be related to the underlying temporary table created by the CTE. Changing tmp_table_size and max_heap_table_size did not have any effect. I've attached an example and the error log.
Attachments
- Example SQL.sql
- 0.5 kB
- Test Results.sql
- 2 kB
Activity
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.
If I add those parameters to the config file it works. I was trying to set them after startup.
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.
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
|
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
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