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.
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.
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).
Instead the basic steps we took were:
Each app was migrated and checked in a test environment before performing the transfer on our production servers.
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.
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.
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.
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.
Comments