« Blog Overview

Fedora 20: Replacing MariaDB with MySql (when MariaDB crashes on a query)

With Fedora 20, one of the more drastic changes is the move from MySql to MariaDB. While MariaDB is designed to be a drop-in replacement for MySql, the replacement of our long-time (and favorite) workhorse (MySql) with another package is something which we both looked forward to and dreaded. Don't get us wrong, we're entirely for the evolution of the LAMP stack, but at the same time we like what we know. Read below why (for us at this very time) MariaDB doesn't quite cut it yet and what we had to do in order to get MySql installed on our Fedora 20 system.

So we installed Fedora 20, restored our backups and proceeded to load our databases into MariaDB. So far no problems. We then called up a few random web pages generated by the LAMP stack and everything seemed fine. We were happy. 

But then, in response to a bug report from a customer, we tried to reproduce a problem they had reported against our latest and greatest software project (we’ll fill on you on that once it’s done, in the meantime don’t hold your breath). And low and behold, the following query consistently caused MariaDB to crash:

SELECT COUNT(*) , a.spoo_value AS "1__autojoinUser__name_first" , b.spoo_value AS "2__autojoinUser__name" ,
    c.spoo_value AS "2__autojoinUser__city" , d.spoo_value AS "1__autojoinUser__name_last" ,
    e.spoo_value AS "2__autojoinUser__image" , f.spoo_value AS "3__autojoinUser__image" ,
    g.spoo_value AS "1__autojoinUser__image" , h.spoo_value AS "3__autojoinUser__city" ,
    i.spoo_value AS "1__autojoinUser__coverimage" , j.spoo_value AS "1__autojoinUser__city" ,
    k.spoo_value AS "1__autojoinUser__musicstyle" , l.spoo_value AS "3__autojoinUser__name" ,
    m.spoo_value AS "2__autojoinUser__coverimage" , n.spoo_value AS "3__autojoinUser__coverimage" ,
    o.ssco_value AS "__autojoinUser__userTypeID" , p.ssco_value AS "__autojoinUser__profileAlbumID"
FROM bps_post AS tbl
LEFT JOIN bps_profile_object_option a ON a.spoo_uid = sspo_uid AND a.spoo_option_id = 1
LEFT JOIN bps_profile_object_option b ON b.spoo_uid = sspo_uid AND b.spoo_option_id = 2
LEFT JOIN bps_profile_object_option c ON c.spoo_uid = sspo_uid AND c.spoo_option_id = 3
LEFT JOIN bps_profile_object_option d ON d.spoo_uid = sspo_uid AND d.spoo_option_id = 5
LEFT JOIN bps_profile_object_option e ON e.spoo_uid = sspo_uid AND e.spoo_option_id = 4
LEFT JOIN bps_profile_object_option f ON f.spoo_uid = sspo_uid AND f.spoo_option_id = 11
LEFT JOIN bps_profile_object_option g ON g.spoo_uid = sspo_uid AND g.spoo_option_id = 7
LEFT JOIN bps_profile_object_option h ON h.spoo_uid = sspo_uid AND h.spoo_option_id = 10
LEFT JOIN bps_profile_object_option i ON i.spoo_uid = sspo_uid AND i.spoo_option_id = 18
LEFT JOIN bps_profile_object_option j ON j.spoo_uid = sspo_uid AND j.spoo_option_id = 6
LEFT JOIN bps_profile_object_option k ON k.spoo_uid = sspo_uid AND k.spoo_option_id = 8
LEFT JOIN bps_profile_object_option l ON l.spoo_uid = sspo_uid AND l.spoo_option_id = 9
LEFT JOIN bps_profile_object_option m ON m.spoo_uid = sspo_uid AND m.spoo_option_id = 19
LEFT JOIN bps_profile_object_option n ON n.spoo_uid = sspo_uid AND n.spoo_option_id = 20
LEFT JOIN bps_config o ON o.ssco_domain = CONCAT('user_', sspo_uid) AND o.ssco_name = 'userTypeID'
LEFT JOIN bps_config p ON p.ssco_domain = CONCAT('user_', sspo_uid) AND p.ssco_name = 'profileAlbumID'
WHERE tbl.sspo_uid IN (
    SELECT ssli_oid FROM bps_like WHERE ssli_ot = 'profile' AND ssli_uid = 5 UNION ALL SELECT 5
GROUP BY a.spoo_value, b.spoo_value, c.spoo_value, d.spoo_value, e.spoo_value, f.spoo_value,
g.spoo_value, h.spoo_value, i.spoo_value, j.spoo_value, k.spoo_value, l.spoo_value, m.spoo_value,
n.spoo_value, o.ssco_value, p.ssco_value

Hmm, back on another machine which ran MySql the same query ran flawlessly. Since the query is syntactically correct and since MySql so far has run it thousands of times without any isssues, for now, MariaDB will have to be replaced with MySql. In case you're wondering, yes, we logged a bug in Fedoras bug tracker. Here's what we had to do to install MySql on Fedora 20 and make MySql start at system boot time:

yum remove mariadb mariadb-libs mariadb-server
yum install community-mysql-server community-mysql-libs community-mysql akonadi-mysql qt-mysql
systemctl enable mysqld

So we spent a few minutes importing our databases back into MySql and low and behold, the above query (which crashed MariaDB) once again worked flawlessly in MySql.

Don't get us wrong: we would like to like MariaDB, but we can't sit on our butts waiting for our newly logged bug to be fixed, so for now, MySql it is. We'll be more than happy to use MariaDB in the future, but our first try has tempered our enthousiasm to do so significantly. But then again, as software developers, this wasn't the first time and certainly won't be the last time something like this has happened to us (and we're pretty sure to our esteemed readers as well); it's just part of getting through another day at the office.

« Blog Overview