1

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

  1. Recreated the table, restarted the server, crashed at row 1014114.
  2. 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.
  3. Recreated the table again. Increased table_open_cache to 4000 and aria_pagecache_buffer_size to 2048MB and delay_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'

https://dba.stackexchange.com/questions/161288/1030-hy000-got-error-175-file-too-short-expected-more-data-in-file-from-st

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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.