0

I am having serious headache with MySQL database. Here is my problem.

I am developing a website in Php with SQL Server as database, everything working fine. database 1

Than I have to change database to MySQL, I've done that and create some stored procedures and function, everything working fine here too. database 2

Now I've have to shift MySQL database to another MySQL server, database 3. Problem occurs here because now my old stored procedure and query which I've created are not working. My DB3 structure is same as DB2.

If I fire this query in DB2 select * from Online_Patient; no error.

Same query in DB3 throws an error:

Error Code: 1146. Table 'Online_Patient' doesn't exist

But when I change my query to select * from online_patient; it gives me correct result.

So is there any way to solve this error or do I have to change all query and stored procedures, there are dynamic query in stored procedure so just can't convert upper case to lower case.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Archish
  • 850
  • 8
  • 32

1 Answers1

2

Locate your mysql configuration file. It should be named my.cnf. You can run mysqld --help --verbose in the beginning of the output the are the default locations where you can look for the file. My output is:

Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\xampp\mysql\my.ini C:\xampp\mysql\my.cnf

Find the lower_case_table_names directive and set it to 1:

lower_case_table_names = 1

From MySQL documentation: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lower_case_table_names

If 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. For additional information, see Section 10.2.2, “Identifier Case Sensitivity”.

Then restart mysql server.

Pavel Petrov
  • 847
  • 10
  • 19
  • also this article might help: http://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html – Pavel Petrov May 13 '16 at 07:49
  • my DB is on server so how can i change my.cnf ? serve will not let me change my.cnf file don't you think? if it possible please share. – Archish May 13 '16 at 08:00
  • If you have access to the server and rights to change the conf file it won't be problem. If you don't have access you should either rename all your tables or change your queries. I don't see other solution. – Pavel Petrov May 13 '16 at 08:04