Menu

As simple as possible, as complex as necessary

The simplicity of migrating from MySQL to MariaDB

29 July 2014
MariaDB

Here at SimplicityWeb we tend to be in no rush to fix things that ain't broke, especially when it comes to databases. We've been using MySQL 5.0 for years and it's proved an excellent back-end store for our apps. A few legacy systems linger on Microsoft SQL Server, but the ease of replication alone is enough to have made the open source product our RDMS of choice for some time.

While not broken, version 5.0 is almost a decade old however, and has been EOL-ed by its current owner Oracle. It's therefore been on my to-do list to move to a newer, actively supported release.

Decoupling from Oracle

Having noticed a slow but apparently growing trend towards MariaDB, the "community" fork of MySQL led by its original founder, we decided on this as the replacement instead of the current Oracle offering.

Drop-in with a wizard?

Given the popularity of MySQL and the unpopularity of its acquisition by Oracle I was surprised at the relative lack of blog material on the pitfalls of migration. MariaDB is heavily promoted as a "drop-in" replacement so I concluded that the process must be so straightforward as to be not worth documenting.

Apparently it is even possible to upgrade from MySQL to MariaDB on Windows (our OS) using a graphical tool. GUIs are great for lots of things, but I didn't feel ready to trust such a critical operation to a wizard, especially given our replication setup (Multi-Master Active/Passive).

One-by-one approach

Instead the basic steps we took were:

  1. Install a new, clean instance of MariaDB on the same machine in parallel with MySQL but running on a different port
  2. Get the MariaDB replication going before adding any data
  3. For each application:
    1. Take the site down temporarily to prevent writes
    2. Transfer the individual database from MySQL to MariaDB (using Navicat, a GUI in which I do have faith)
    3. Change the ColdFusion data source to point to the MariaDB server
    4. Bring the site back up

Each app was migrated and checked in a test environment before performing the transfer on our production servers.

Connectors and data sources

Notice the apparent ease with which the ColdFusion data source was changed: we simply modified the address/port of the existing MySQL4/5 driver shipped with ColdFusion 9. MariaDB is after all a MySQL clone.

There were no problems whatsoever with the first half a dozen or so apps thus migrated. Everything just carried on working.

Problem 1: Maxrows

Then a test of one particular app brought up an exception:

Error Executing Database Query. Unknown system variable 'OPTION'

We tracked this down to the use of the maxrows attribute in a number of <cfquery>s. Nowhere in our code was there any mention of an OPTION variable, but it seemed probable that maxrows was causing the MySQL connector in CF9 to add a SET OPTION command invisibly.

At first we assumed this was a MariaDB-specfic issue and so we followed Tony Junkes's helpful instructions for creating CF datasources using the latest MariaDB JDBC connector instead of the default MySQL4/5 driver.

Problem 2: cf_sql_longvarchar

With the new connector in place, the 'OPTION' errors went away, but more appeared triggered by operations using <cfqueryparam> with a cfsqltype attribute of cf_sql_longvarchar. Switching to cf_sql_varchar would have been a solution but we didn't want particular data types to be out of bounds just because of a driver issue.

Solution: upgrade the MySQL connector

Stumbling across a post by Charle Arehart we finally realised that the OPTION issue was not related to MariaDB alone, but to a change in MySQL 5.6 - the version the current MariaDB release is based on.

Charlie doesn't refer to MariaDB in his post, but his advice to upgrade the MySQL connector to the latest version to solve the OPTION issue seemed worth trying to see if it would also deal with the data type issue.

It did.

No further issues have arisen and we are now benefitting from the performance enhancements accrued over 3 releases of MySQL, as well as apparent optimisations specific to MariaDB (about which I now need to learn).

YMMV of course (please comment if that's the case), but from our experience it would seem that migrating from MySQL to MariaDB for ColdFusion apps is indeed a rather straightforward process. Having transferred your data, you simply need to drop the current MySQL JDBC connector into your [cf or instance root]/lib folder (see Charlie's post for version specific instructions), restart CF and then point your existing MySQL datasources at MariaDB.

Posted on . Updated

Comments

  • Formatting comments: See this list of formatting tags you can use in your comments.
  • Want to paste code? Enclose within <pre><code> tags for syntax higlighting and better formatting and if possible use script. If your code includes "self-closing" tags, such as <cfargument>, you must add an explicit closing tag, otherwise it is likely to be mangled by the Disqus parser.
Back to the top