I'm inserting rows into a mariadb aria table using a loop.
The table in question is very simple.
CREATE TABLE `table` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`col1` BIGINT(20) UNSIGNED NOT NULL,
`col2` CHAR(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`col1`,`col2`) USING BTREE
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin PAGE_CHECKSUM=1 ROW_FORMAT=FIXED;
The insert query
INSERT IGNORE INTO `table` ( `col1`, `col2` ) VALUES ( %s, %s )
I've inserted more rows in other aria tables with more convoluted queries without any problems but now the process died at around row 1014064.
1017 (HY000): Can't find file: './schema/table.MAI' (errno: 2 "No such file or directory")
This is the first time I encounter this error. I'm the only one using the database and all rows are inserted sequentially. Checking the table.
CHECK TABLE `schema`.`table` EXTENDED
'schema.table', 'check', 'warning', 'Table is marked as crashed'
'schema.table', 'check', 'error', 'Found key at page 59908096 that points to record outside datafile'
'schema.table', 'check', 'error', 'Corrupt'
Not much help using google or looking at the bin log. I've tried repairing the table
REPAIR TABLE `schema`.`table` EXTENDED 'schema.table', 'repair', 'status', 'OK'
and
REPAIR TABLE `schema`.`table` USE_FRM
'schema.table', 'repair', 'warning', 'Number of rows changed from 0 to 1014064'
'schema.table', 'repair', 'status', 'OK'
In both cases running CHECK TABLE afterwards
'schema.table', 'check', 'error', 'Record at: 122722652 Can't find key for index: 3'
'schema.table', 'check', 'error', 'Corrupt'
I can still insert into the table. And select from the table. So I'm not sure what's going on.
I'm about to drop and recreate the table and reinsert my data. However this will take some time so I was hoping for some insight into why this error may have occurred in the first place hopefully preventing it on the next run.
UPDATE 1
- Recreated the table, restarted the server, crashed at row 1014114.
- Checked and repaired the table and ran the query again on the corrupted table and it runs until it's finished. But the table can't be repaired and is reported as corrupt.
- Recreated the table again. Increased
table_open_cache
to 4000 and
aria_pagecache_buffer_size
to 2048MB anddelay_key_write
to FALSE (which shouldn't matter in this case?). It crashes at row 1014064 again.
The data length and table size is around 1GB when it crashes. I'm suspecting some form of cache/space limit. However there is plenty of space for the database and tmp dir on the drive.
UPDATE 2
Inserted ~1gb worth of rows without having aria throwing an error. The CHECK EXTENDED still gives "Can't find key for index: 3" The REPAIRS do not help…..
I tested setting bulk_insert_buffer_size
to 0, key_cache_block_size
to 4096 (MyISAM only?), flush
and flush_time
to 1 with no luck.
Attempting to repair this mess running aria_chk I have noticed that there are several corruptions and the error only catches the last?
aria_chk: error: Record at: 31035831 Can't find key for index: 3
aria_chk: error: Record at: 73116176 Can't find key for index: 3
aria_chk: error: Record at: 135193892 Can't find key for index: 3
aria_chk: error: Record at: 178073365 Can't find key for index: 3
aria_chk: error: Record at: 349220954 Can't find key for index: 3
aria_chk: error: Record at: 388331812 Can't find key for index: 3
aria_chk: error: Record at: 427438634 Can't find key for index: 3
aria_chk: error: Record at: 466547474 Can't find key for index: 3
aria_chk: error: Record at: 505658332 Can't find key for index: 3
aria_chk: error: Record at: 544765154 Can't find key for index: 3
aria_chk: error: Record at: 583876012 Can't find key for index: 3
aria_chk: error: Record at: 622982834 Can't find key for index: 3
aria_chk: error: Record at: 662091674 Can't find key for index: 3
aria_chk: error: Record at: 701202532 Can't find key for index: 3
aria_chk: error: Record at: 740309354 Can't find key for index: 3
aria_chk: error: Record at: 779420212 Can't find key for index: 3
aria_chk: error: Record at: 818527034 Can't find key for index: 3
aria_chk: error: Record at: 857637892 Can't find key for index: 3
aria_chk: error: Record at: 896746732 Can't find key for index: 3
aria_chk: error: Record at: 935853554 Can't find key for index: 3
To my horror I have now found that another Aria table has the same problem but because it didn't throw any errors I didn't notice it before.
UPDATE 3
Tried setting transactional=1
. Still failed at 1014064 but this time I got
'1030 (HY000): Got error 175 "File too short; Expected more data in file" from storage engine Aria'
Still can't repair the table. A different error message was given though
'schema.table', 'repair', 'error', '22 for record at pos 116128'
UPDATE 4
The only relating search I found for https://dba.stackexchange.com/questions/161288/1030-hy000-got-error-175-file-too-short-expected-more-data-in-file-from-st was
https://bugs.mysql.com/bug.php?id=79527
The workaround solution in that thread didn't work (I made sure that all my allocated sizes were <= 1gb)
key-buffer-size = 1G
myisam-sort-buffer-size = 1G
sort-buffer-size = 1G
Best Answer
For any poor soul who'll make acquaintance with this problem. I couldn't fix it. My last resort was to switch to xtradb. I recreated my datadir, reset my perms and recreated my tables. Seems to be working. It didn't seem to be a hardware problem at least.