How to handle unexpected MySQL DB engine changes?

Question

I came across this problem two or three times. One of my customer restored the backup but it modified the engine settings unexpectedly. The result, all foreign-key constraints were lost and the application started showing weird results.

The second incident happened on our live production server. MySQL stopped supporting InnoDB and displayed error, "Unsupported Engine Type".

I searched Google and found many similar queries. I want to know whether MySQL will be fixing this bug or not. Is it the right-time to shift to PostgreSQL? Why still I am sticking to MySQL is because of its wide user base. I don't know much about PostgreSQL.

Can anyone suggest ways to handle this MySQL issue or the alternative?

Answer 1

This sounds like a clear indication that the restore from backup wasn't properly tested, which obviously needs to be done.

That said, it's not the first time I've heard of this happening, sometimes with a lot worse results than that. Unfortunately, there is AFAIK not really a way to prevent MySQL from "downgrading" your table engine if it can't fulfill the request. So you just need to be careful and check the results of your restores. Which, really, you should always be doing.

This is the way MySQL operates in a lot of cases (silently changing the table manager, ignoring foreign keys rather than throwing an "unsupported feature" error when you use the wrong table manager etc), and it's not considered a bug. So if you do consider this a bug that you don't want to live with then yes, it might be worth looking at switching database.

Now, for a tip on the actual problem: I've seen a number of sites that simply deploy a check in their monitoring (nagios for example) that verifies that all tables are InnoDB, and if they're not it'll throw a critical alert in the general monitoring system. Something like that would likely have found this issue for you well before you got bad data into the system. It's far from perfect, but it gives you a chance of catching the problem earlier.

Answer 2

MySQL can be configured using a bunch of different SQL Modes which govern how silent and permissive it is with incorrect queries.

The default configuration in most distros is, in my experience, far too permissive.

You might want to look at changing these configuration options before you write off MySQL as not being a solid enough DBMS.

Set it to a stricter mode and you might start to find errors when restoring your backup dumps which give you clues as to what is wrong.

Answer 3

This smells of operational incompetence.

You should manage your server builds so that it will only be restored on to a well-defined reproducable server build which is not going to have a misconfigured or inappropriate version of mysql. This is the only way to manage applications in general.

I'd have a word with your operations team, or your operations manager.

You should definitely NOT switch to a different database because your ops team get things wrong.