0

I have a MySQL table Blog in a database Blog:

mysql> SHOW TABLE STATUS WHERE Name = 'Blog' \G
*************************** 1. row ***************************
           Name: Blog
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 42
 Avg_row_length: 10142
    Data_length: 425984
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 114
    Create_time: 2024-03-24 10:39:25
    Update_time: 2024-03-23 07:10:41
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

When I do select on the table I get:

mysql> select * from blog limit 10 \G
ERROR 29 (HY000): File './blog/blog.MYD' not found (OS errno 2 - No such file or directory)

which, I think, indicates that MySQL thinks the table engine is MyISAM.

But a directory listing of my data directory for the database shows nothing but .idb files:

bash3.2/usr/local/mysql-8.0.28-macos11-arm64$ sudo ls -Al data/Blog/
total 39744
-rw-r-----+ 1 _mysql  _mysql   557056 Mar 23 07:10 blog.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 24  2023 blogpoll.ibd
-rw-r-----+ 1 _mysql  _mysql  9437184 Mar 24 10:00 comment.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Aug  2  2022 novelpoll.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 22  2022 pandg.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 22  2022 pandgsecret.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 22  2022 produceshort.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Mar 14 07:47 recipient.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Mar  7 11:50 tanka1.ibd
-rw-r-----+ 1 _mysql  _mysql  9437184 Mar  6 13:30 viewing.ibd
-rw-r-----+ 1 _mysql  _mysql   114688 Apr 22  2022 writing.ibd

shows no .MYD files, just .idb files, which makes me think the engine ought to be InnoDB.

If I attempt to change the table's engine to InnoDB, I get:

mysql> ALTER TABLE Blog ENGINE = InnoDB;
ERROR 29 (HY000): File './blog/blog.MYD' not found (OS errno 2 - No such file or directory)

which is the same error I get when trying to do the select.

Trying to repair the table results in the same error message:

bash3.2/usr/local/mysql-8.0.28-macos11-arm64/bin$ ./mysqlcheck -u root -p -r Blog Blog
Enter password: 
blog.blog
Error    : File './blog/blog.MYD' not found (OS errno 2 - No such file or directory)
Error    : Can't find file: 'blog' (errno: 2 - No such file or directory)
error    : Corrupt

And attempting to dump the table result in the same error message again:

bash3.2/usr/local/mysql-8.0.28-macos11-arm64/bin$ ./mysqldump -u root -p Blog Blog > ~/blog.dump
Enter password: 
mysqldump: Got error: 29: File './blog/blog.MYD' not found (OS errno 2 - No such file or directory) when doing LOCK TABLES

So, MySQL really wants this table to be a MyISAM table, but there are no MYD files.

So, how do I convert the InnoDB files to MyISAM files to make MySQL happy?

2
  • 1
    Try the next. Stop MySQL. Backup your blog.ibd file. Remove it. Create empty blog.MYD. Start server. Repair blog table. Drop it. Create empty InnoDB blog table with the structure which completely matches the structure in blog.ibd file. Stop server. Replace blog.ibd file with saved one. Start server. Repair blog table.
    – Akina
    Mar 26 at 4:45
  • @Akina Thanks for the advice, but I've solved the problem--I don't exactly know how I solved it, but I'm post it as an answer. Mar 27 at 19:53

1 Answer 1

0

What OS? On Windows, "blog" = "Blog" in the file system. Not so for Linux and kin.

Be consistent in upper/lower case when referring to table names.

1
  • Sorry--I should have mentioned this--Mac OS 14.4.1 (Sonoma). Mar 28 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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