Monday. 19:30 Time for a break

Double database jeopardy

I’m assuming it’s Apple related as it’s only my machines running the latest OS update that are affected. Maybe it’s not Apple. Another quick look for people suffering the same problems as me has found some entries on the MariaDB website that suggests it could be something they’ve missed. Either way it’s a tad inconvenient. If I can’t run a database on my local machine, I can’t really develop efficiently on my local machine.

This isn’t the first time I’ve had problems with MySQL/MariaDB using the InnoDB storage engine. Something that makes this more of a problem than it otherwise might be is that you can’t just re-install the software and feed in the older data files. Thankfully, as I’ve been around the block several times, I have lots of backups of everything in lots of different places. Yesterday, rather like the last time InnoDB corrupted itself, I wiped my local data and went through a load of back ups data to rebuild my local data set-up. After several hours all was well again, until I needed to restart the database and the original glitch re-glitched.

Time to switch DBs?

Maybe. I’ve considered this on a few occasions: when MySQL was bought by Oracle and each time I’ve had a major local data outage. To be fair to MySQL / MariaDB it’s been the perfect data-partner for me for over 20 years. Fast, reliable, and free, so there’s a part of me that wants to take a day off and wait for the inevitable fix or workaround. But the failure of a re-install and fresh data import, together with not being able to effectively do my job, mean it is time to look at an alternative, and it’s always good to have options.

Postgres

The choice of replacement for MariaDB doesn’t need any deliberation, it’s always going to be Postgres. So that’s the easy bit out of the way. The tricky bit is translating table structures and exporting/importing data between the two DB systems fast enough to avoid having to pause live, production projects.

Today, instead of dredging the nerdsphere for repair kits, I’ve focussed on writing something to translate MariaDB dumps. I’m working through 100 or so tables. Each time an incompatibility is encountered, it’s a case of looking for the best Poastgres column equivalent to the MariaDB column, writing a new translation rule, and going again.

Importing the data part of the dumps might prove to be more challenging, particularly when the dataset is in the multiple GB range. But then I suppose I can always export the data in smaller chunks. Tomorrow’s problem. I think that’s enough for today.