What wrong with SQL_MODE?

Let me tell you what I think is wrong, oh so wrong, with SQL_MODE. And there has been numerous debates and ideas around this for a long time, and many comments on how this really should work. So I assume that you are now all waiting for my very opinionated opinion.
And the problem is that we (the MySQL Server that is) allows it to be set just about everywhere, it's dynamic. This is do NOT like. not one bit. And let me tell you why.

If I work in a small project, like when I'm playing around with my own websites or my own small projects, I am both the DBA and the developer. So there isn't much argumentation going on, I know what I am about to do. But still, I want the database to determine the consistency of data, and the application to be just that: an application that conforms to the rules I have set up in the database schema.

This is similar to my arguments for FOREIGN KEYs. In a small project, I know my application needs to do this or that to keep the internal consistency correct, but the larger the application gets and the more people involved, so with database work, and some with application work maybe in completely different groups, then we have to have a way to set the rules.

Another reason is that setting rules for data in a declarative fashion is so much more foolproof than doing it in the application. If we assume that you have done all the FK stuff in program code, and you want to know what they are. And you didn't write the application, what do you do? If you are using proper FK relationships, CHECK and UNIQUE constraints, just run, say MySQL Workbench, on your schema, and you know what data looks like.

If I make a mistake in teh application in this sense, I may stand to loose data here, as links between objects are "lost". No, even in my small websites (my main site is PapaBlues use InnoDB and foreign keys all over. It's so nice to know that InnoDB will keep me on track here, even if I happen to forget something in some application. Fact is, it is extremely useful and I would have lost data, even in THIS rather simple application (30ish tables).

So, back to SQL_MODE, what's wrong with it? Problem is you can set it anywhere. The same goes for SET FOREIGN_KEY_CHECKS. In a production environment, I want to be able not only to set these, but to enforce them, so some developer, not by spite, but by not knowing better, sets SQL_MODE to something less strict than I want it to be, and hence will corrupt my data.

So then, what about any existing MySQL applications, am I not forgetting about them here? Nope, I'm not. What I think should work here is an extra parameter that allows me to force FK CHECKS and SQL_MODE, if I so wish. If I don't set these, FK CHECKS and SQL_MODE would work just as before. The forcing should be set independently for SET FK CHECKS and SQL_MODE, and have three different values:

• Not enforced - Just like today.
• Enforced with error - Override enforcement will cause an error.
• Enforced with no error - Overriding the enforcement will be silently ignored.
  

I know that not everyone agrees with me here. Not everyone is so strict with design of the database as I am, but I have worked at places where data is money. And loosing data isn't only caused by data disappearing from the disk. A lost connection between two data items is just as bad, and can be just as difficult to track.

Another thing is that setting SQL_MODE in some cases will be tied to an object created with a particular SQL_MODE. I can only image the kind of issues this can cause. The solution: The ability to enforce even existing objects to use a particular SQL_MODE. The way enforcement should, in my work, work then, would be in one of three ways:

• The server will ignore all this, and work like today.
• The server will refuse to start if there are objects that are not aligned with the server defined SQL_MODE.
• The server will start, but log all offending object states to the error log, and will disable those particular objects.

And in the last case above, what will happen to data consistency checks in triggers? The answer: They will NOT run, which means that you should put this in proper PRIMARY KEY / UNIQUE KEY / FOREIGN KEY definitions.

And then, when we have that, can we have another thing? A transaction safe data dictionary? Please!

Yours truly opinionated
/Karlsson
I will have a look at testing a forced SQL_MODE eventually