U4-2187 - Remove limitiation for lower case file system for MySQL on Linux

Created by Chris Tomlinson 03 May 2013, 15:22:53 Updated by Sebastiaan Janssen 23 Jul 2013, 13:27:10

We're running Umbraco 6.0.3 on our new website (http://www.wildphotos.org.uk) but we've had to use a custom build because the standard installer prevents the use of MySQL on Linux.

We can see no reason for this ban to remain so I've attached a patch to change the part of the installer that deals with this.

As per the MySQL docs (http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_lower_case_file_system), setting the sysvar_lower_case_file_system variable is not allowed so I’ve removed the check for it and the instruction to modify it.

We can't see any reason that the value of this variable should make a difference to Umbraco (although the lower_case_table_names clearly does matter).

It would be great to get this change incorporated into the main Umbraco build because the need for a custom build is making the deployment of new versions into a large task that we don't currently have time to tackle.

Thanks, Chris

1 Attachments

Download umbraco6.0.3MySQLLinuxFix.patch

Comments

Sebastiaan Janssen 06 May 2013, 12:36:04

Hi Chris,

We understand the problem (https://groups.google.com/forum/?fromgroups=#!searchin/umbraco-dev/mysql%7Csort:relevance/umbraco-dev/GWmCApX0v1k/p470l0a7mAIJ ) and unfortunately have no good solution yet. It's more than just an installation problem though, as you can see from the discussion, and there's no "easy" fix.

Just as an FYI, the number of people using MySQL with v6 is less than 2% and the number of people using it on Linux is much lower even. We currently don't have the resources to fix and maintain this for such a small amount of people. We'd be happy to discuss a community contributed fix though, please add to the discussion in the Google Group if you would like to participate in that.


Chris Tomlinson 07 May 2013, 17:00:18

Hi Sebastiaan,

I understand that out-of-the-box support for a default MySQL database server requires extra work but the small change in the patch I supplied at least allows users to configure their Linux database server and avoid all the cost of creating a new Windows architecture for MySQL databases.

As long as lower_case_table_names is set to 1 everything works fine for us apart from the one installation issue fixed by this patch.

Thanks for the info about MySQL usage. The relatively low usage does help to explain how we've ended up in the current situation but you do note on #U4-2176 that MySQL in case-insensitive mode is supported - all this patch does is enable that support for Linux users who have changed the lower_case_table_names setting to 1, as described in the setup wizard database instructions.


Chris Tomlinson 08 May 2013, 08:26:05

By the way, this behaviour didn't happen in the original release of 6.x - we installed that just fine and it was only when upgrading to 6.0.3 to fix some other show-stopping bugs that we came across this installer problem. I'm not sure whether it's an issue in 6.0.1 or 6.0.2.


Sebastiaan Janssen 08 May 2013, 08:35:50

Chris, I'm not sure what the issue is. It is my understanding that both lower_case_file_system AND lower_case_table_names must be true in order for Umbraco to work on MySQL for Linux. This has to do with the fact that MySQL stores the tables on the file system with the same name and cannot query the database with case-insensitive queries otherwise. If you can point me to some documentation as to why I'm wrong, I'd be happy to have a look.


Chris Tomlinson 08 May 2013, 12:22:42

In the MySQL documentation (http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_lower_case_table_names), they explain that "If [lower_case_table_names is] set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive."

The section above discusses lower_case_file_system and it makes no mention of an impact on query behaviour (nor do I see any effect on our website).


Sebastiaan Janssen 08 May 2013, 15:25:06

@Chris Ah, okay, I didn't realize you have it running on a live site like that, cool. I'll try to see if I can test get this change into 6.0.6 then.


Sebastiaan Janssen 27 May 2013, 15:00:12

Hey Chris, I tried to test this but MySQL refuses to start if lower_case_file_system is on so I'm going to have to put this one on hold for a while until I'm able to build a proper test environment, sorry.


Nathan Doak 08 Jul 2013, 22:21:25

Hi Sebastiaan,

This issue is a problem for me as well. However, instead of straight linux/mono/apache support, we use a combination of Windows IIS and mysql in Amazon's RDS. I know there are several workarounds we could put in place, mssql RDS instance or an EC2 instance with a properly configured mysql server, but neither work well in our existing backup, restore, and monitoring scripts and we would like to limit costs and platform fragmentation.


Sebastiaan Janssen 23 Jul 2013, 13:27:01

Merged in your change Chris, it should work but I'm sure we'll get complaints if it doesn't.


Priority: Normal

Type: Feature (request)

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 6.0.1, 6.0.2, 6.0.3, 6.0.4, 6.0.6

Due in version: 6.1.3

Sprint:

Story Points:

Cycle: