Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.17, 10.0.27, 5.5, 10.0, 10.1, 10.2
-
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
|