2

Recently I realized the case sensitivity issues in MySQL. I wanted to convert my upper case table names to lower case using this solution but I forgot to do Step 1, and completed the remaining steps. Now I couldn't access my tables that are in upper case. Renaming the tables using

mysql>  rename table BOOK to book;
ERROR 1017 (HY000): Can't find file: './las_data_3_dt2014/book.frm' (errno: 2)

returns error.

I'm unable to use mysqldump on my database.

mysqldump: Got error: 1146: Table 'las_data_3_dt2014.adjustment_type' doesn't exist when using LOCK TABLES 

I want to know if it's okay to rename the upper case .FRM files to corresponding lower case files, so that I can run the rename commands and access the data again.

  • 1
    No, it is not ok. These files are private to the mysqld server. – Basile Starynkevitch May 12 '13 at 13:52
  • @BasileStarynkevitch What is then the recommended way to get out this issue? – Lenin Raj Rajasekaran May 12 '13 at 13:52
  • 1
    Use MySQL requests only. And do a mysqldump. – Basile Starynkevitch May 12 '13 at 13:53
  • @BasileStarynkevitch Unable to mysqldump too. Edited question. – Lenin Raj Rajasekaran May 12 '13 at 13:56
4

change lower_case_table_names back again

Then you should be able to access your tables.

But yes, as long as they as MyISAM tables you CAN rename them. Best to do it while mysql is shutdown. Need to change the MYD and MYI files too.

2

If the table is MyISAM, make sure the case is as follows

  • mv BOOK.FRM BOOK.frm
  • mv BOOK.myd BOOK.MYD
  • mv BOOK.myi BOOK.MYI

If the table is InnoDB, that could a little because the data dictionary within ibdata1 would know what the file's name was originally. Notwithstanding, you could rename the file the same way.

You should also make sure the file has the correct ownership.

chown mysq:mysql BOOK.frm

Give it a Try !!!

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.