Hi, Simon! On Dec 27, Simon J Mudd wrote:
Slightly off topic but changes in sql_mode has the potential to cause problems with major version changes and incompatible behaviour. So combining all sorts of new settings into this variable does not seem like a good idea to me. It’s already rather complex.
Yes, precisely. I thought today what should be the criteria for choosing between a new sql mode or a separate variable, like @@concat_null_is_null. And I realized that one such criteria could be - does it need to be stored *per routine*? Saved with every storage procedure, function, trigger and event definition?
Note: don’t just think of how the server behaves with this change but also think of replication. Downstream slaves may not be running the same (major or minor) version of MariaDB (or MySQL) and sql_mode is fed into the binlog stream so will be interpreted by the SQL thread(s) of the downstream slaves too.
I’d be much more comfortable using a new global variable which defines this behaviour, though that _may_ require the setting being propagated via the binlog stream if setting the value globally on the downstream slave is not good enough.
I think that a global variable does not solve the replication issue, when slaves are of a different version. When slaves don't implement this configurable option (whether sql_mode or a separate variable), the only way for replication to work is not to change this option on the master. It means that for a new sql_mode the default backward compatible behavior should be "new sql mode is not set" and for the global variable the default value should provide the compatible behavior. Although a global variable does provide a benefit that only a SUPER user can change it (and thus break the replication). Regards, Sergei Chief Architect MariaDB and security@mariadb.org