Story of 2 Bugs

I was working on a 2nd article on the CONNECT engine, but instead found myself Yak Shaving. For those not familiar with the term, it refers to the situation where in order to reach your goal, you need to solve a prior issue, but in order to solve this issue, you need to take care of a prior one and so on … until you find yourself “shaving a yak” to reach your initial goal. The URL I embedded above will take you to the origin of the term back in MIT, but here’s an animated GIF from Malcom in the Middle that illustrates it. Following the article’s advice, I won’t shave that yak and instead present to you the 2 bugs I found and since I’m feeling generous, a 3rd one we came across while working on another project.

ALTER TABLE To Switch Engines

The original article was planned to be about CONNECT Pivot tables. This is a powerful feature of the CONNECT tables that I will write about some other time. I thought, why not start with the data I used on my MariaDB 10 CONNECT Engine article. What I did, is convert the CSV table into an InnoDB one and this is what I got as the final result:

CREATE TABLE `test_alter` (
  `ts` date NOT NULL `date_format`='YYYY-MM-DD' `flag`=1,
  `merchant` varchar(128) NOT NULL `flag`=2,
  `amount` decimal(10,2) NOT NULL `flag`=3,
  `category` varchar(65) NOT NULL `flag`=5
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `table_type`=csv `file_name`='/var/lib/mysql/Export_v2.csv' `header`=0 `quoted`=1;

Notice that although the ENGINE option clearly specifies InnoDB, the rest of the options are clearly related to CONNECT’s CSV table type. While this is not a big deal per se, since the resulting table is a 100% valid InnoDB table, this output breaks some functionality like logical backups. You won’t be able to use the result of the CREATE TABLE command to re-create the table. This is filed as bug MDEV-5867. You can check Sergei Golubchik’s comment for a workaround and why this was an intended behavior for ALTER TABLE.

Pivot Table With DATE Columns May Crash MariaDB

Once I figured out that I was facing a bug with ALTER TABLE (and filing the bug, something you should always do when working with an Open Source software), I created and loaded the table manually and went on with my little project. Only to find myself crashing the server over and over again. The details and the reproducer are attached to bug MDEV-5869. It took me a while to isolate the problem and make sure that I could reliably reproduce it in a clean environment. Olivier Bertand, the CONNECT engine creator, identified the bug and seems to be on his way to fix it and it.

GTID and Circular Replication

This one is completely unrelated to my original project. But I thought it’d be a good companion to the previous 2 bugs. It is a common practice to set up MySQL / MariaDB servers to replicate in a circle, especially between 2 servers (aka master-to-master replication). So if one server goes becomes unresponsive or goes down, you’ll have other servers to which you’ll be able to switch the application traffic and still have some redundancy. When the failed server comes back again, circular (or master-to-master) replication is re-established, the failed server catches up through the replication stream, and everything returns to normal. There’s plenty of bibliography on the subject if you’re not familiar with these topologies. For example, the MySQL Administrator’s Bible explains this and other topologies in some detail. Enter MariaDB 10 multi source replication and the usual replication topologies for High Availability become much more interesting. For example, if you have a multi master topology that replicates as in this figure.

If one of the servers goes offline or one of the links is severed, the replication chain will be interrupted. What you could do with MariaDB 10 is to have a 2nd replication stream that will move the transactions in the other direction. Similar to this representation:

In this example, server B is now a slave from A and C. If any link or server fails, the remaining servers will still be updated through the remaining replication links. However, there is a bug by which the same GTID, if it is received through 2 different replication streams according to bug MDEV-5804, this will work as expected in the next point release: MariaDB v10.0.10. In conclusion, the current release of MariaDB 10 is a release candidate, but given the speed with which the engineering team responded to these 3 bugs, I’m confident we’ll soon have a good quality GA release. I’ll keep testing these advanced features and sharing with you my findings either through this blog or the OurSQL Podcast.