Bug #22831 make the global mode take effect immediately
Submitted: 29 Sep 2006 16:23 Modified: 4 Oct 2008 23:12
Reporter: Maciej Pilichowski Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.0.24a OS:Linux (opensuse 10.0)
Assigned to: CPU Architecture:Any

[29 Sep 2006 16:23] Maciej Pilichowski
Run mysql:

SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

both empty

set global sql_mode='only_full_group_by';
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

Set, empty. Now try something like this:

select A from T group by B;

No error! Now quit and run mysql again, both modes are set and the error is shown.

How to repeat:
See above.

Suggested fix:
My guess: setting global modes should override all session modes immediately.
[29 Sep 2006 16:27] Mark Leith
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is defined behaviour - global variables only take effect on reconnection if changed whilst sessions are logged in. This is why we have the SET SESSION variable = value syntax, so that the variables can take effect immediately.

Best regards

[29 Sep 2006 16:47] Maciej Pilichowski
You a right, I checked the doc but the behaviour is so subtly described I didn't catch without your comment.

So I rephrase -- please, make the global mode take effect immediately at least for the current session (from which the command was applied). The reason -- it is counter-intuitive to being forced to quit and then connect again. I know, I can also set session mode, but then I have to set two modes -- no saving here.

In other words I opt for
set global_mode in new way := set_global_mode + set_session_mode in old way

Would it be possible?
[2 Oct 2006 13:35] Mark Leith

OK this does indeed make a valid feature request, thank you for following up. 

Best regards

[4 Oct 2008 23:12] Konstantin Osipov
Thank you for your interest in MySQL. I don't see this change happen in the near future. The current behavior is documented and is relied upon by existing applications.