In one of my previous projects I encountered a strange issue. The SQL queries that worked in our development code always failed on the server. The logs indicated that the queried tables did not exist.
This did not make any sense to us. The code ran perfectly on local MySQL database installed on our developer machines. But the minute we did it on the server we got table does not exist error. After a lot of digging we discovered that there was no problem in the code. Our table name in the ddl queries was in lower case but in our code it was in title case. But why did it work on Windows then. Some searching on the net explained the actual cause of the issue.
From the MySql docs:
In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix
Our development machines were using Windows operating systems. In Windows you cannot have two files in same directory with same names but different cases. So the case difference did not cause any problems on Windows. MySql was able to find the table correctly on the file system and all things worked fine. But our test server was a linux environment. The table in the SQL query was not same as the table in the database. Result - requested table not found.
Our solution was to create constants in the code to hold our table names in small case and to ensure that all references in our queries used these values only.
An alternative action to doing drop/rename is also available in MySql docs.
How table and database names are stored on disk and used in MySQL is affected by the value of lower_case_table_names. Changing this value on linux can make MySQL use Windows style treatment for the tables.
This did not make any sense to us. The code ran perfectly on local MySQL database installed on our developer machines. But the minute we did it on the server we got table does not exist error. After a lot of digging we discovered that there was no problem in the code. Our table name in the ddl queries was in lower case but in our code it was in title case. But why did it work on Windows then. Some searching on the net explained the actual cause of the issue.
From the MySql docs:
In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix
Our development machines were using Windows operating systems. In Windows you cannot have two files in same directory with same names but different cases. So the case difference did not cause any problems on Windows. MySql was able to find the table correctly on the file system and all things worked fine. But our test server was a linux environment. The table in the SQL query was not same as the table in the database. Result - requested table not found.
Our solution was to create constants in the code to hold our table names in small case and to ensure that all references in our queries used these values only.
An alternative action to doing drop/rename is also available in MySql docs.
How table and database names are stored on disk and used in MySQL is affected by the value of lower_case_table_names. Changing this value on linux can make MySQL use Windows style treatment for the tables.
No comments:
Post a Comment