HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Migrating database from Windows to Linux


#1

Hi all,
I’m trying to migrate my HelpSpot installation from a Windows server to a Linux server. What I did was:

  1. setup Helpspot on Linux
  2. tested Helpspot install, everything looked good
  3. full backup of windows server database using MySQL admin to get a big SQL script
  4. imported new database to Linux MySQL instance
  5. restart server and test on Linux

What I found is that my real data wasn’t showing up on the Linux server…just the same old test data. After a bit of poking around I found that the db backup I made from Windows has all the table names in lower case. The table names setup in Linux use mixed case. Since the table names didn’t match up, the import just created new tables and left the old data alone.

Does anyone know an easier way to migrate this data to Linux than manually tweaking all the database table names in the backup file?

As a follow up, anyone know if this is a side effect of some MySQL setting that turns off case sensitivity? Or does the Linux and Windows install script actually do something different?

-Al


#2

I think I found the answer to my own question.
There is a setting in the my.cnf that you can put under [mysqld]
lower_case_table_names=1

If lower_case_table_name is set to 0 MySQL will use case dependent table names if the os supports it.
If it is set to 1 it will lowercase table creating sql and also table accessing sql
If it is set to 2 it will lowercase accesses by not creations (or maybe the other way around. Check the docs if you care)

The default for windows OS installs of MySQL is 1, but on linux it defaults to 0. If you want to move a HelpSpot db from Win to Linux, just set the linux lower_case_table_names setting to 1 and restart the db before you install helpspot.

While it’s probably too late to change anything in HelpSpot now, I’ll chalk it up to another reminder of the dangers of cross platform development :slight_smile:

-Al


#3

Yes, Al. I was going to recommend you change that setting if it didn’t mess up anything else for you.

Yeah, cross platform development is… fun.