Description:
Over the last decade, MySQL has evolved. Replication, with GTIDs, mixed mode replication, and more have all improved MySQL overall.
However something seems utterly bizarre to me.
Why is it, when using STATEMENT based replication, and an UNSAFE query is submitted to MySQL, that it merely logs it?
Certainly, not all statements flagged as UNSAFE are necessarily unsafe. If you know your schema, understand what is happening, then there are cases where an UNSAFE query is just fine to run. You won't trash the slave. You won't damage the data integrity of the slave.
However, the way MySQL works right now is:
- user writes a query on the console
- user hits enter
- user is now informed "that query was unsafe!"
- user is now utterly screwed if the query was unsafe, because the slave needs to now be repaired or bootstrapped from scratch
Basically, MySQL simply tells you "Sorry buddy, you've just screwed yourself!".
And in fact, a diligent user may want to prevent such a scenario yet make a mistake. And, that user seems to have no easy way to know if UNSAFE will be triggered until AFTER they've now trashed their slave.
I've looked extensively, and I can find no easy way to force UNSAFE messages to prevent the query from being run. If I've missed something, my apologizes, but there is a LOT of noise via Google/etc about people suppressing UNSAFE messages.
And, I did examine the docs, yet... no fruit. So, if there IS a way to do this, then please transition this into a doc enhancement.
Note, that I realise that as a default this is clearly not the way to go. Too much of a change in expected behaviour. However, as a config option?
It seems weird that it is not available.
Note, that I am fully aware that MIXED exists. I don't want MIXED. We want to keep binlogs small. We don't like the gotchas with MIXED.
And realistically, this is about a STATEMENT based replication enhancement/fix.
TL;DR -- why does MySQL not provide a way to prevent UNSAFE queries from trashing your slaves. Why does MySQL only alert you, after you have potentially destroyed the slave, that it is now time for you to be sad.
How to repeat:
Turn on STATEMENT based replication, and write an UNSAFE query. MySQL will happily inform you, after the fact, that you should pack up your things and look for a new job, because you have trashed your slave.
Suggested fix:
Possible solutions:
- some way to selectively force certain WARNINGS to become ERRORS via my.cnf
- an overall config option, to do what is suggested above
Optimal would be some global variable, so one could examine their query, see why the error was thrown, make a determination, and then do:
SET I_KNOW_THIS_IS_DANGEROUS_I_SAY_ITS_OK=1
This could be used prior to running their now vetted query. That way, a user could validate that all is OK, and continue regardless.
