Umbraco 6.0.2 fails when using MySQL, because Umbraco has changed their database layer/logic, breaking MySQL support, . This issue was first spotted in version 6.0.0RC, and reported in the Umbraco development group on Google Groups. This post explains the exact problem:
However I fear that the problems with Mysql are just beginning with the transition to PetaPoco. The main problems have to do with case-sensitivity on mysql.
Consider the following facts:
The ISqlHelper in Umbraco for Mysql automatically converts all identifiers to uppercase eg: “SELECT * FROM umbracoNode” becomes “SELECT * FROM UMBRACONODE” PetaPoco when creating tables and queries respect case. Thus all tables are created with the correct casing “umbracoNode” vs. “UMBRACONODE” previously using ISqlHelper. On Windows mysql is case-insensitive by default and will even convert every identifier to lowercase. Unless a specific option is set to preserve case. (lower_case_table_names = 2) On Unix based systems Mysql is case-sensitive and will not convert any identifiers at all. Thus queries without the correct casing will fail, this is probably why the iSqlHelper had to resort to case conversion in the first place.
This means that Umbraco will work perfectly well on a windows mysql installation but not on a mysql installation running on a unix server. I have confirmed that this is the case with 6.0RC.[…]
PetaPoco created the table “umbracoUser” but MySqlHelper expects “UMBRACOUSER” to exist, and that causes Umbraco to crash.
I believe it will be necessary to remove the uppercasing on the Mysql ISqlHelper and rewrite all sql statements to proper casing in order to make 6RC run on a case sensitive mysql installation. This is a lot of work but I think it’s better to bite the bullet and get it over with once and for all.
I also believe that Umbraco needs to support mysql databases on unix servers as these are very popular on low-cost .Net hosting plans.
The problem lies in SQL statements like (umbraco.datalayerSqlHelpersMySqlSqlTotal.sql):
CREATE TABLE umbracoRelation ( ... CREATE TABLE cmsDocument ( ... CREATE TABLE umbracoLog ( ...
ALTER TABLE umbraconode MODIFY COLUMN id INTEGER NOT NULL; INSERT INTO umbracoNode (id, trashed, parentID, nodeUser, level, path, sortOrder, uniqueID, text, nodeObjectType, createDate)
One and the same table, written in different case.
Others also reported this to Umbraco:
U4-1632 MySQL Database Setup – Table Names
There appears to be an issue when performing an initial installation when the DB is sitting on a Linux installation of MySQL.
[…] This is due to inconsistancies in naming conventions when referencing the tables. A default Linux installation of MySQL is case sensitive and the installation script sometimes refers to table names using uppercase.
One comment by Niels Hartvig to the issue is:
We don’t have any plans to prioritise this in any foreseeable future as there’s a valid workaround and MySQL is only used by a small minority of Umbraco users.
But hé, “only” 16% uses MySQL as their database backend, according to Umbraco’s Twitter poll.
to fix their own wrongdoings in the past. This is not the first bug Umbraco introduces in new releases… Mister Niels Hartvig clearly hasn’t considered the consequences of setting MySQL lower_case_table_names=1 and lower_case_file_system=1 on already running MySQL database servers. Suppose an user has two or three MySQL tables:
after changing the lower_case settings, they’ll be treated the as the same, which will only cause problems.
Umbraco users with MySQL databases are left out in the bitter cold. Nice go Umbraco.
Of course you can use SqlCE or MS SQL Server as your database back-end for Umbraco. An SqlCe driver is also available for classic ASP.
Turns out not all my blog comments were migrated to Disqus successfully. On 2014/04/30 StephenPAdams placed a comment I don’t want to withheld from you.
I’ve ran into the same issue as yourself. But I went ahead and forked Umbraco 7.1.2 and have been slowly fixing some areas of the repositories where the casing was different from what was actually on the file system. In addition, I removed the requirement for lower case table names to be enabled. Earlier this morning my pull request was integrated into 6.2 and will eventually be merged up to 7.1.2. See revision:
Feel free to follow me on Twitter. I’ll be posting on my blog as I continue to make progress:
My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 10 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization.
10 Practical Tips To Optimize WordPress hosting
MySQL InnoDB performance improvement: InnoDB buffer pool instances – Updated!
MySQL DoS in the Procedure Analyse Function – CVE-2015-4870
Optimize WordPress MySQL tables through Cron, behind the scenes
Vevida Optimizer WordPress plugin
PHP, MySQL and IPv6: still slow