10

We have one Database instance in Amazon RDS with one DB. (By default its lower_case_table_names=0).

Now I have created another DB in same instance. For new site its giving table not found error because of case sensitive table name.

So I changed lower_case_table_names=1 (as 2 is not allowed) in RDS for that instance. New site started working but old site giving error tables not found.

Any solution? I would like to set lower_case_table_names=1 so issue will not appear for new DB's.

Thanks in advance!

Abhi
  • 1,963
  • 7
  • 27
  • 33

3 Answers3

14

I got the same issue, by default when you create a new MySQL instance using AWS RDS, it will use the default-parameter-group, the problem is you can't change any parameter in this default parameter group. To change the parameter value you have to create a new parameter group. Here how you do it:

  1. On left panel go to Parameter Groups > Create Parameter Group > give name and description > Create > search for the parameter you want, in this case "lower_case_table_names" > Edit parameter, change it to 1> save.
  2. On left panel go to Instances > in the content panel select Instance Action > Modify > Database Options > Database Parameter Group > Select the parameter group you want > Continue > reboot the instance > wait for few minutes then everything all set

enter image description here

enter image description here

Hope it helps :)

Hung Vu
  • 5,624
  • 2
  • 30
  • 27
  • 1
    100% perfect, @hung-vu. Thanks! Mine was saying `(pending-reboot)`, just like you said it would, in the configuration section under `Parameter group`. after reading [this](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html), I rebooted as stated. – tylerlindell Jun 05 '20 at 23:37
  • For those using MySQL 8.0 instances, you'll not be able to change it. It's failing with error: `The parameter value for lower_case_table_names can't be changed for MySQL 8.0 DB instances.` – Shubham Dhingra Mar 12 '23 at 17:24
2

after your configuration by setting lower_case_table_names=1, try to export your database and import it again.

Gouda Elalfy
  • 6,888
  • 1
  • 26
  • 38
  • Db is huge. Client don't want to take site off, :-( – Abhi Dec 11 '15 at 14:17
  • As obvious as it may seem, on AWS RDS, you have to create a `"parameter group"`, then modify your instance to use it, and finally restart your instance. Note that dropping old case-named tables BEFORE could be an idea, since you may be unable to access them after this change. Just don't do that with production data, of course :) – Balmipour Jun 09 '16 at 11:32
1

If you set lower_case_table_names=1 parameter, the DB will convert all newly creating table names to lower case from that point onwards. It will also convert table names in all DB queries to lower case before the lookup.

However, if you already have a set of tables in the DB before enabling the parameter, they will not be changed. Since the queries are converted to lower case, your application will tell you "table not found" because the actual table is in upper case and the lookup happens in lower case.

I could resolve this issue by following these steps.

  1. Export all DBs to a file before enabling the parameter (reference)
  2. (Disable the parameter and restart the server if already enabled)
  3. Delete all DBs from the DB server
  4. Enable the parameter
  5. Restart the server to apply the change
  6. Import the exported DBs

This will convert all new table names to lower case when importing. Since the lookups also happening in lower case, there won't be any issue.

Thilina
  • 114
  • 1
  • 5