Donnerstag, 18. Februar 2016

Sliverstipe - XAMPP Windows - Lowercase Database Tables Cause Troubles With Upload To Production Sites On Linux

Using XAMPP on Windows is a very comfortable thing to develop and test web sites locally. My favourite CMS system is Silverstripe which has an awesome framework to map class-models to database tables automatically.

However if you install XAMPPP the default behaviour of MySQL is to create all tables with lower-case names. That causes troubles if you want to export the DB to a production site running under Linux. There all files and also MySQL tables are case sensitive. In the case of Silverstripe it will not find any of its own tables. Fortunately I have found a quite elegant way to solve this problem.

1) Make MySQL table names case sensitive under Windows

Stop your MySQL-server. Open your my.ini and look for the section [mysqld]. Add a line to the end of that section: lower_case_table_names = 2.

It should look like this...

lower_case_table_names = 2
This option changes the behaviour in that way that tables are created with upper and lower case names (file names). But MySQL server will still treat names in SQL statements as cases-in-sensitive. That means the table names test, Test and TEST would address the same table and there can only be one table with the name Test or test or TEST etc. in a DB.

2) How to correct the table names of an existing Silverstripe DB?

There must be a more elegant way than correcting the names by hand. First...

...MAKE A BACKUP OF EVERYTHING (Database and Silverstripe-Installation) !!!

Here are the steps...
  1. If you have upgraded your Silverstripe CMS from older versions. Please read Chapter 3 of this post.
  2. Export the DATA only of the existing DB with Phpmyadmin.
  3. Delete the file _config.php in your mysite directory of the Silverstripe installation.
  4. Copy the install.php file back into your Silverstripe folder. You can get it from a clean Silverstripe install (of the same Silverstripe version!).
  5. Delete all tables in your Silverstripe Database.
  6. Open <your site URL>/install.php and make a clean install. After that you will have all tables with upper and lower-case names.
  7. Empty all tables with Phpmyadmin.
  8. Import the exported data from step 2 back into your DB.
  9. Restore your old _config.php in your mysite folder and delete the install.php file.
After this procedure your database should be restored with mixed case names and your old data. Your old site should run perfectly.

3) Clean-up a Silverstripe DB if Silverstripe was upgraded one or more times from an older version

Silverstripe has a stunning upgrade feature. I have upgraded my site through several versions. Very cool how the database structure will be updated by the framework. But it has a small draw back: Silverstripe does not delete obsolete tables and columns and that will cause problems with the procedure of restoring the data of an old DB into a new one which was created from a clean install. The structures do not match.

Surf to Silverstripe Artefactcleaner. This extension has done the job perfectly in my case. Install it and clean-up your DB before doing the procedure of Chapter 2. In my case it has removed dozens of obsolete tables and a lot of obsolete columns. After that the structure was identical to the one of a new installation (of the same Silverstripe version of course).

I wish you good luck with your Silverstripe DB and remember: a backup is your friend ;)