Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11674

Recursive CTE Raises Table is Full Error

Details

    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

        1. Example SQL.sql
          0.5 kB
        2. JDBC Error Log.log
          4 kB
        3. Test Results.sql
          2 kB

        Activity

          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

          elenst Elena Stepanova added a comment - 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
          absolutesantaja Shawn Weeks added a comment -

          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.

          absolutesantaja Shawn Weeks added a comment - 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.
          absolutesantaja Shawn Weeks added a comment -

          If I add those parameters to the config file it works. I was trying to set them after startup.

          absolutesantaja Shawn Weeks added a comment - If I add those parameters to the config file it works. I was trying to set them after startup.
          absolutesantaja Shawn Weeks added a comment -

          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.

          absolutesantaja Shawn Weeks added a comment - 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
          

          elenst Elena Stepanova added a comment - 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 Igor Babaev added a comment -

          This is a real serious bug in the CTE code.

          igor Igor Babaev added a comment - This is a real serious bug in the CTE code.
          igor Igor Babaev added a comment -

          The fix for this bug was pushed into the 10.2 tree.

          igor Igor Babaev added a comment - The fix for this bug was pushed into the 10.2 tree.
          absolutesantaja Shawn Weeks added a comment -

          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

          absolutesantaja Shawn Weeks added a comment - 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

          People

            igor Igor Babaev
            absolutesantaja Shawn Weeks
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.