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

ERROR 1114 (HY000) table full when performing GROUP BY

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.17, 10.0.27, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.1.27
    • None
    • GNU/Linux
    • 10.2.6-3, 10.3.1-1

    Description

      GROUP BY queries from MyISAM tables consistently fails with 'TABLE FULL' for some lengths of fields being grouped but succeeds for longer lengths. Does not appear to be hitting operating system limits.

      For example, with a source MyISAM table 'test.input' of 10m records defined as field a=int(11) auto-increment primary key (values 1-10,000,000) and field b defined as varchar(128) not null and randomly filled, then the following fail but any other length (1-127) runs OK:

        create table test.test as select a, RIGHT(b,62) as b, count(1) as x from test.input group by 1, 2;
        create table test.test as select a, RIGHT(b,63) as b, count(1) as x from test.input group by 1, 2;
        create table test.test as select a, RIGHT(b,80) as b, count(1) as x from test.input group by 1, 2;
        create table test.test as select a, RIGHT(b,81) as b, count(1) as x from test.input group by 1, 2;
      

      Replacing the length with @len causes the crash to occur with values of @len that are 1 lower than the lengths above: i.e. @len=61, 62, 79 and 80.

      While the query is running, a pair of files (.MAI, .MAD) are being created in /tmp; the query crashes at the point that the .MAI file is about the exceed 512Mb. For other lengths the .MAI file exceeds 512Mb without crashing. /tmp has over 404Gb free space available.

      None of the conditions documented at http://dev.mysql.com/doc/refman/5.5/en/table-size-limit.html apply in this case - plenty of disk space, query runs OK with longer values being grouped, not using InnoDB, file size is suitable for pointer size, etc.

      The solution listed elsewhere for specifying MAX_ROWS is for when explicitly creating an Aria table; MariaDB is implicitly creating the table so cannot specify MAX_ROWS. In any cases, 10m rows is not excessive.

      Table input:

                 Name: input
               Engine: MyISAM
              Version: 10
           Row_format: Dynamic
                 Rows: 10000000
       Avg_row_length: 140
          Data_length: 1400000000
      Max_data_length: 281474976710655
         Index_length: 102784000
            Data_free: 0
       Auto_increment: 10000001
          Create_time: 2017-01-18 11:53:34
          Update_time: 2017-01-18 11:54:14
           Check_time: NULL
            Collation: latin1_swedish_ci
             Checksum: NULL
       Create_options:
              Comment:
      

      Table input created and populated as:

      drop table if exists test.input;
      create table test.input (
        a integer not null auto_increment primary key,
        b varchar(128) not null);
       
      insert into test.input (b) values(REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16));
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input;
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input; -- 2^13 8388608
       
      insert into test.input (b) select REPEAT(LPAD(CAST(RAND()*100000000 AS INTEGER), 8, '0'),16) from test.input where a<=1611392; -- 10m records
      

      Selected settings:

        aria_block_size 8192
        aria_log_file_size 1073741824
        aria_max_sort_file_size 9223372036853727232
        aria_pagecache_buffer_size 134217728
        aria_sort_buffer_size 268434432
        aria_used_for_temp_tables ON
        character_set_client utf8
        character_set_connection utf8
        character_set_database latin1
        character_set_results utf8
        collation_connection latin1_swedish_ci
        collation_server latin1_swedish_ci
        default_storage_engine MyISAM
        default_tmp_storage_engine (blank)
        innodb_data_file_path ibdata1:12M:autoextend (NB: Not using Innodb)
        key_buffer_size 4294967296
        max_heap_table_size 16777216
        myisam_block_size 1024
        myisam_sort_buffer_size 134217728
        sort_buffer_size 2097152
        sql_big_selects ON
        version 10.0.27-MariaDB (server A), 10.1.17-MariaDB (server B)
        version_compile_machine x86_64
        version_compile_os Linux
      

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            Saysell Philip
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.