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:
Best Answer
You problem is incredibly straightforward.
In Linux Systems, the file extensions for a MyISAM table
tablename
must betablename.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 nameUsersInGroups
andusersingroups
.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?)