I got this weird issue today when I restored a full .sql backup from one server to another server using dbForge Studio for MySQL (third party software).

  • Both are having same properties like
    table type: MYISAM
    latin1_swedish_ci: latin1_swedish_ci
    latin1: latin1
  • I am not using any temporary tables
  • Is it possible to save same table names in MySQL?

Here is an illustration of the issue:

enter image description here

  • Some from Windows (where case folding is the norm) and some from *nix (where case is distinguished in table names)?
    – Rick James
    Sep 14 '16 at 16:53
  • To exclude an bug in your client-software, check the information_schema: select * from information_schema.tables where lower(table_name) = 'usersingroup' If there are several tables with the same name, but in different case, quoting the table name might be the cause.
    – Lennart
    Sep 14 '16 at 17:03

You problem is incredibly straightforward.

In Linux Systems, the file extensions for a MyISAM table tablename must be

  • tablename.frm
  • tablename.MYD
  • tablename.MYI

The file extensions are case-sensitive

Windows does not check case-sensitivity for .myd and .myi file extensions, so a MyISAM table in a Windows MySQL instance will allow for this. Such is not the case with Linux.

In your case, let's look at the UsersInGroups table. You evidently have two tables with two different filenames and/or extensions:

  • UsersInGroups.frm
  • UsersInGroups.MYD
  • UsersInGroups.MYI
  • usersingroups.frm
  • usersingroups.myd
  • usersingroups.myi

You will have to rename the files in the OS so that there are only .MYD and .MYI extensions. You can then query each table using the case-sensitive name UsersInGroups and usersingroups.

If you simply want to replace one with the other. delete the old files, keep the new files, and make sure you only have .MYD and .MYI extensions. BTW .frm must always be lower case.

If you are using dbForge to make a backup of MyISAM tables from a Windows machine and you restore that backup on a Linux machine, you must always remember to change the file extensions after restoring from backup.

I talked about this 5 years ago (How can I move a database from one server to another?)


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.