Saotn.org

Umbraco doesn’t like users with MySQL databases

Umbraco doesn’t like users with MySQL databases

Umbraco 6.0.2 fails when using MySQL

Umbraco has changed their database layer/logic, breaking MySQL support. Use SqlCE instead as your Umbraco database back-end

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 
    (
    ...
    

    Or umbraco.datalayerSqlHelpersMySqlSqlVersion4_Upgrade.sql:

    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.

    Reported issues with Umbraco 6 and MySQL

    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.

    MySQL server case-insensitive configuration settings

    Mister Niels Hartvig references the MySQL settings:
    lower_case_table_names=1
    lower_case_file_system=1

    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:

    1. Users
    2. users
    3. uSeRs

    after changing the lower_case settings, they’ll be treated the as the same, which will only cause problems.

     

    Conclusion

    Umbraco users with MySQL databases are left out in the bitter cold. Nice go Umbraco.

    Update
    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.

    Update
    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.

    Hi Jan,

    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:

    https://github.com/umbraco/Umbraco-CMS/commit/a8cc8fa68823a6e7e8b949cab3eb4caf840b06df

    Feel free to follow me on Twitter. I’ll be posting on my blog as I continue to make progress:
    http://www.stephenpauladams.com

     

     


    Sysadmins of the North
    About The Author
    My name is Jan Reilink. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing his daily thing at Vevida Services in the Netherlands. With over 10 years of experience, my specialties include Windows Server (2003, 2008 and 2012), Windows 7, IIS (6.0, 7.5 and 8.0), Linux (CentOS, Debian), PHP, websites, optimization and security. More about me @ www.reilink.nl.

     

    6 Comments:

    • http://umbraco.com Niels Hartvig

      Hi Jan!

      While I’d wish that MySQL just worked, our research shows that MySQL usage on Umbraco is in fact less than 10%. In addition it’s possible to use MySQL as long as you turn off case-sensitivity = there’s an easy workaround.

      Step by step we’ll update our queries in the codebase so the case sensitivity eventually will be solved, but we don’t have endless resources, so I’d prefer that we prioritise fixes that improve the experience for the 90%+.

      There’s nothing that prevents eager MySQL users who don’t want to turn off case sensitivity to provide patches for the core. After all, Umbraco is open source and if you have those specific demands, maybe you could consider contributing which is the currency of open source.

      Best,
      Niels…

    • http://www.saotn.org Jan Reilink

      Hi Niels,

      Thank you for your comment!

      I know it’s a lot of work to convert the casing. Not only table names, but also column names needs converting (on the database side, or in the source code). On the other hand, looking at the source code I believe there is little or no naming convention in place for Umbraco, as to how to write table- and column names for example. The example “umbracoNode < -> umbraconode” points that out. Or the naming convention is not forced/mandatory, despite http://our.umbraco.org/wiki/reference/umbraco-best-practices. That should be step 1 IMHO, and not just for the database back-end or just MySQL.

      As twittered, I’d be more than happy to test new (Beta) versions of Umbraco. But I’m simply not a .NET programmer. I’ve just set up Umbraco 6.0.2 with a SqlCE back-end (on umbraco.testingcode.org, will be there temporarily), which installs smoothly. And fortunately it’s possible to convert a MySQL database to SqlCE, either by using tools or by converting MySQL to MSSQL and then to SqlCE. Maybe I’ll devote a blog post to converting MySQL to SqlCE in the near future.

    • Daniel Burge

      We’re working on a large installation that’s running in the Amazon cloud. Very high traffic both on the public site, but also with the content editors. We’re considering MySQL as one option for performance, as Amazon offers their

      Amazon Relational Database Service (RDS), which is MySQL, as a means for vertical scaling of the database. Might be something we could contribute if the clients wants to move forward with that transition.

    • BigBlue

      Had similar problem when my hosting company moved the mySQL DB from a windows to Linux server had to change all tables names to Uppercase:

      First run this in the SQL window of phpmyadmin

      select concat(‘rename table ‘, table_name, ‘ to ‘ , upper(table_name) , ‘;’) from information_schema.tables where table_schema = ‘your_schema_name';

      this produces a script that will change all tables names to uppercase copy the script and paste it into the SQL window and run.

      As always backup the database and use at your own risk….

    • StephenPAdams

      Hi Jan,

      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:

      https://github.com/umbraco/Umbraco-CMS/commit/a8cc8fa68823a6e7e8b949cab3eb4caf840b06df

      Feel free to follow me on Twitter. I’ll be posting on my blog as I continue to make progress:
      http://www.stephenpauladams.com

      • http://www.saotn.org/ Jan R

        Hi Stephen,
        Thank you for your comment, great work and keep it up!

    About Sysadmins of the North

    Hi and welcome to Sysadmins of the North!
    Sysadmins of the North is just another technical blog. Just like so many others out there. Most posts are written in English, some in Dutch. On Saotn.org you can find all kinds of computer, server, web, sysadmin, database and security related stuff.

    About me: My name is Jan Reilink. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing his daily thing at Vevida Services in the Netherlands. Living in the north of the Netherlands, so hence the name Sysadmins of the North :-)
    Drop me a comment somewhere or send an email to say hi, or discuss about security, website or WordPress, performance, Windows or IIS topics.

    Support Saotn.org

    If you feel that Sysadmins of the North has helped solve your problem, saved you time, or you just simply like Saotn.org, then please consider making a donation with Paypal. Thanks! :)

    Search & find

    Custom Search
    IT Books & WP Themes

    Windows PowerShell Cookbook: The Complete Guide to Scripting Microsoft\'s Command Shell
    DNS and BIND - 5th Edition
    DNSSEC Mastery: Securing the Domain Name System with BIND
    Windows Server 2012 Unleashed
    Enfold - Responsive Multi-Purpose WordPress Theme
    Striking MultiFlex & Ecommerce Responsive WordPress Theme

     

    The Sysadmins of the North network

    Just for the fun of it, Sysadmins of the North is hosted on mulitple servers:

    1. one (shared) Windows Server 2012, IIS 8.0 webserver running PHP 5.5
    2. one (shared) MySQL database server, running MariaDB 5.5
    3. one Varnish Cache HTTP reverse proxy with Nginx Droplet, for offloaded static content, running Debian 7.0 @ DigitalOcean
    Easy share

    Share this post easy on:
    RSS feed
    If you like Saotn.org:
    donate to Sysadmins of the North
    Twitter Feed

    What's happening, right now, around Saotn.org?


    Bad Authentication data
    Copyright © 2007-2014 Saotn.org . Design by OrangeIdea