One important difference between MySQL installation on Windows and Linux

MySQL may be installed both on Windows and Linux. However there is one important change which I would like to know before to install it on Linux :) In this post I will share this finding.

Under the hood MySQL tables data is stored in files on file system. Windows file system is case insensitive and if you try to create table like that:

create table `UserInfo` (
    ...
)

this table will be created with lowercase name anyway (even though you used Camel case in SQL statement. In this example it will be created with "userinfo" name). But at the same time you may read data from this table using all following queries:

select * from UserInfo
select * from userInfo
select * from userinfo

From other side Linux file system is case sensitive which means that by default table will be created there with exactly the same characters registry which was used in SQL statement. So if table was created using SQL statement above then you need to use exactly the same name also in SQL queries:

select * from UserInfo

Other queries will fail with table not found error:

select * from userInfo
select * from userinfo

If you develop cross-platform application which works with MySQL it may cause compatibility issues: code which works in Windows won't work in Linux because of different characters registry in table names used in SQL queries. In order to avoid this error we may configure MySQL on Linux so all tables will be created with lowercase name - i.e. same way how it works on Windows. It can be done by adding the following configuration setting to /etc/my.cnf.d/mysql-server.cnf file (this example is for Red Hat Linux. For other Linux distributives MySQL config file may be located in other folder e.g. in /etc/mysql and may have different name):

[mysqld]
...
lower_case_table_names=1

NOTE: it should be done during MySQL installation before to run database instance service. It is not possible to change this property when MySQL is already installed (in this case you will need to uninstall it and install again). With this approach code will be the same and will work both on Windows and Linux.