Database tables case issue

[erinoms]

Windows is not respecting the uppercase database table names when I pull a site down in Local. When using a Mac, it respects the uppercase names, but on Windows it changes them to lowercase.This is causing a variety of issues. Is there a way to fix this problem?

Note: I have tried to change lower_case_table_name to 0 as documented here, but it did not work.

[kfukawa]

I’ve asked this question a couple of times and never got a response, including replying directly to an email from Jack Sellwood, the Product Manager, who was asking if I had any questions! No reply, not even an acknowledgement, so I’m assuming this isn’t even on their radar.

Yes, this isn’t an issue on a Mac, but my primary work OS is Windows 10.

Unfortunately, if this can’t be resolved, Local is useless to me on Windows.

[flyjack]

Hi there! Mind emailing me again? Email me at jack.sellwood@getflywheel.com.

Our marketing emails weren’t set to properly handle replies, so your rely accidentally went to a “no-reply” address.

Happy to help!

[kfukawa]

Hi Jack,

Thanks for reaching out.

When installing Local Lightning on Windows 10, the default MySQL settings has lower_case_table_names set to 1. I need to be able to change this setting to 0, but I can’t find where to make this setting change.

Do you know where I can make this change?

The problem is, if I can’t change the setting from 1 to 0, I cannot sync my local websites with my live sites (hosted on Linux/Unix machines) as they have lower_case_table_names set to 0. With different settings, tables created locally have different table names than on my live sites - i.e. wp_Custom_Table vs wp_custom_table (or vice versa). After you sync websites, the live server has both wp_custom_table and wp_Custom_Table, yet only reads data from one of them.

Although this isn’t an issue with Local Lightning on a Mac, it is a deal breaker for development on a Windows machine.

I’d find it hard to believe that I’m the only person experiencing this issue.

Please let me know if you know how to fix this issue - I’d hate to have to go back to XAMP or DesktopServer.

Thanks for your help,

Kevin.

[ben.turner]

Hey Everyone –

Thanks for your patience with this as we’re trying to zero in on what’s happening!

I am curious to know @kfukawa @erinoms – did you install MySQL manually outside of Local? Another thing to consider, are you using another offline development tool like WAMP?

One thing I’d like for you to try is to manually add this configuration to the MySQL configuration for the site. To do this:

1. Turn off the site within Local
2. Open the configuration file here: ~/Local Sites/site-name/conf/mysql/my.cnf
3. Add lower_case_table_name = 2 to this configuration file. Here’s a screenshot to help visualize where to put this line:

4. Start the site so that the configuration options are being used
5. Re-pull the site and let us know how it goes!

[ben.turner]

I’ve been taking a closer look at things and I think I have more information to help out with this issue. Thanks for you patience with this, and sorry for the delay – it’s been a tricky thing to zero in on.

It looks like the general issue has to do with the fact that Windows’ file system is case-insensitive, while the remote, UNIX-y file system is case sensitive. Because Windows is case-insensitive, this can cause problems with MySQL when changing environments.

The MySQL documentation goes into detail about these settings and recommends adopting a convention of all lowercase table names for maximum portability:

To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

– https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

In terms of how to proceed so that you are able to pull the site down using Local, I would recommend updating the remote site’s table prefix to only have lowercase characters.

This is definitely something that you want to be careful of when doing, so definitely take a backup, and consider putting the remote site into maintenance mode while updating those table names.

In the above Mysql.com link, they outline the general workflow of exporting the db, running a search and replace on the dumped file, and then importing the database with the new table names.

In the case here with WordPress, you should be able to do something similar using wp-cli commands, so something like this:

wp db export db.sql
# update the sql file to have new table names
wp db reset
wp db import db.sql

Remember to update the wp-config.php file with the new table prefix so that the new table structure is being used for the WordPress site! Also, if your site has any custom queries or custom tables, make sure those are updated to take into account the change in table names!