maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

Sergei,
On SQLServer these kind of option (ANSI_NULLS, CONCAT_NULL_YIELDS_NULL and so on ) can be set at session level or inside stored procedure (but not in function).
In the last case, the scope of the setting is limited to the stored procedure.

Jérôme.

-----Message d'origine-----
De : Sergei Golubchik [mailto:serg@xxxxxxxxxxx] 
Envoyé : mardi 27 décembre 2016 17:01
À : Simon J Mudd
Cc : jerome brauge; maria-developers@xxxxxxxxxxxxxxxxxxx
Objet : Re: [External] [Maria-developers] check_sql_mode

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@xxxxxxxxxxx