Bug #97669 Incorrect key file for table - myisam - mysql 8
Submitted: 17 Nov 2019 16:24 Modified: 19 Mar 6:18
Reporter: mohsen davari Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:8.0.18 OS:CentOS
Assigned to: CPU Architecture:Any

[17 Nov 2019 16:24] mohsen davari
Description:
hi
in mysql 8.0.18 i get too many error like SQLSTATE[HY000]: General error: 126 Incorrect key file for table

i hade 100G space in my tmp  and tmp is empty!
show this in my log

2019-11-17T15:24:16.976706Z 43 [ERROR] [MY-010238] [Server] Got an error from thread_id=43, /export/home/pb2/build/sb_0-36015269-1568970779.69/rpm/BUILD/mysql-8.0.18/mysql-8.0.18/storage/myisam/mi_update.cc:206
2019-11-17T15:24:16.976786Z 43 [ERROR] [MY-011063] [Server] MySQL thread id 43

i set myisam_repair_threads=2 in my.cnf
i think this Cause this error
please help me

How to repeat:
i change all of this parameter 

max_heap_table_size
tmp_table_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
myisam_sort_buffer_size
key_buffer_size
[18 Nov 2019 13:03] Sinisa Milivojevic
HI Mr. davari,

Thank you for your report.

However, this is not a bug. Since you are using MyISAM storage engine, you have to run REPAIR command. Unlike InnoDB SE, MyISAM SE is not an ACID compliant SE.

You can find a syntax for this command in our Reference Manual. This is a short version:

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

Not a bug.
[18 Nov 2019 13:06] mohsen davari
hi
i repair my table about 50 time!
but after a few minutes it say Incorrect key file for table .... again!
and this repeat every minutes!
[18 Nov 2019 13:09] Sinisa Milivojevic
Then, you should definitely check your entire hardware and OS. Also check disk space available on all partitions and quotas. You can try reducing number of repair threads, but I doubt that it is a problem. You could also try repairing by using FRM.

This is all described in our Reference Manual.
[18 Nov 2019 13:24] mohsen davari
i remove myisam_repair_threads=2   from my config and all table is know currect!
i remove it and repair with 1 thread and it is know good
it is a bug!
please check it!
repair with multiple thread is very faster and we need it
please check this what happen when myisam_repair_threads is enable!
[18 Nov 2019 13:33] Sinisa Milivojevic
I will be happy to check this.

Send me your CREATE TABLE statement and some rows. I will then check your test case and if I repeat it, I will verify it..

Do note, however, that MyISAM bugs are not a high priority ones.
[18 Nov 2019 13:53] mohsen davari
sql

Attachment: md_sms_deliver_50002.sql (application/octet-stream, text), 6.91 KiB.

[18 Nov 2019 13:54] mohsen davari
all big table has this error not only this table!
all table more than 5000 rows
[18 Nov 2019 14:01] mohsen davari
i know you want to remove MyISAM but MyISAM  is the best mysql engine and we need this engine
[18 Nov 2019 14:08] Sinisa Milivojevic
Hi,

I was not able to repeat the problem you experience, although I have set number of repair threads to 4 and 8.

Hence, I need a table with more then 5000 rows, plus I need to know how do you manage to corrupt the table so that you have to run REPAIR.
[18 Nov 2019 15:32] mohsen davari
new file - please test on mysql 8 - please first repair your table with multi thread

Attachment: md_sms_deliver_50002(1).zip (application/x-zip-compressed, text), 760.71 KiB.

[18 Nov 2019 15:33] mohsen davari
first repair your table with multi thread
then set one query.update with where
[18 Nov 2019 16:37] Sinisa Milivojevic
Hi,

I literally tried everything with a big table. Number of repair threads set to 2,4,8 and 16.

Updating one or several rows after that.

Selecting, then again repairing , updating ......

Not a single problem with 8.0.18.

Can't repeat.
[18 Nov 2019 19:26] mohsen davari
i can not say anything
if it repeat again i send you my server password for test

this is my sample query with error

UPDATE `md_sms_deliver_50002` SET `send_token`='5dd0197d38341' , `smsid`='27' , `price_back_try`=`price_back_try`+1 WHERE `deliver_state`='5' AND `price_back_try`='0'
[18 Nov 2019 19:34] mohsen davari
error in mysqld.log

Attachment: 1.png (image/png, text), 227.57 KiB.

[19 Nov 2019 13:20] Sinisa Milivojevic
Hi,

What you have sent me is useless to us ....

We need to be able to reproduce the problem, so that we can debug it on our machine in a debugger.

Also, I have already spent more then one hour trying to reproduce the problem with your large table and all combinations of number of threads, UPDATEs etc ...... I think that it is enough ......
[19 Nov 2019 14:05] mohsen davari
now my problem solved with remove myisam_repair_threads=2 from config and repair with 1 thread
if anyone have this problem can use my solution.
i do not have any solution for repeating this error.