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

ERROR 1114 (HY000) table full when performing GROUP BY

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
    • Storage Engine - MyISAM
    • 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.