CODEWITHSUNDEEP
phpmysqlsqldatabase
RNK
RNK

Reputation: 5792

mysql table names case sensitive

I have one website running on development server, over there I can execute query like,

SELECT * FROM Agents;
OR
SELECT * FROM agents;

I mean, It runs in both conditions. But, In my production server, it's giving me error. What should I do to overcome this problem. Because I have used uppercase and lowercase in my php scripts, EVERYWHERE.

ERROR:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]:
Base table or view not found: 1146 Table 'database_name.Agents' doesn't exist'

Upvotes: 2

Views: 3467

Answers (2)

Lazaro Fernandes Lima Suleiman
Lazaro Fernandes Lima Suleiman

Reputation: 1932

You need to edit /etc/my.cnf and set lower_case_table_names server variable.

  • Open MySQL configuration file: /etc/my.cnf
  • Look up for: [mysqld] section
  • Add: lower_case_table_names = 2
  • Save and restart MySQL service: /etc/init.d/mysql restart

If lower_case_table_names set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases.

Upvotes: 1

PressingOnAlways
PressingOnAlways

Reputation: 12376

Is your development server on Windows or Mac? Mysql on linux has case sensitive table names by default while Windows and Mac don't. For best compatibility between all systems, I recommend keeping your casing always consistent.

Unfortunately, I would advise you to fix it in ALL your scripts. If you can disable case sensitivity on your production server, that would be great, but if you ever migrate to a different system or work on a system where you cannot control the production settings, it is best coding practices to assume it will be case sensitive.

Upvotes: 0

Related Questions