Bug #59826 interaction of init_connect options are not documented
Submitted: 30 Jan 2011 20:50 Modified: 31 Jan 2011 14:18
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Tags: qc
Triage: Needs Triage: D4 (Minor)

[30 Jan 2011 20:50] Peter Laursen
Description:
There are two way to specify init_connect options (for a non-SUPER user)

1) using such setting in options file or as a GLOBAL variable
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_init_connect

2) specify init_connect as in mysql_options() MYSQL_INIT_COMMAND -parameter
http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html
(provided that the client environment supports it - what C/C++ do and PHP does not)

It is not documented how they interact - both 
1) when conflicting options are specified. Which will have effect?
2) non-conflicting options are specified. Will they both be used or only one?

How to repeat:
1)
I tried a simple exercise as regards 1).

I put in configuration: init_connect = 'SET SQL_MODE = no_engine_substitution;'
I put in mysql_options() 'SET SQL_MODE = '';'

.. connected and now

SHOW GRANTS; -- GRANT SELECT ON *.* TO 'nonsup'@'localhost'
SHOW GLOBAL VARIABLES LIKE 'sql_mode'; -- STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SHOW SESSION VARIABLES LIKE 'sql_mode'; -- empty set

So in this particular case it seem that setting in mysql_options have effect and init_connect in configuration is ignored. But not sure if it always is the case wiht conflicting specifications.

2)
In case 2) where two statements that do not conflict (say SET NAMES and SET SQL_MODE) are specified in in one place will they be 'added' or will only mysql_options setting have effect? Is the behavior consistent with all settings7options/variables?

Suggested fix:
Please document how the two different ways to define init_connect interact (or not).  Actually I'd like a prompt reply on this too.
[30 Jan 2011 22:46] Peter Laursen
Maybe you'll find this off-topic, but I cannot help say that I personally find init-connect option 'antique' as with introduction of SESSION variables the client can handle all this (with the sole exception of AUTOCOMMIT = 0) and as mysql_options() (with reconnect flag set) is a more consistent way with recent servers in my opinion.  The 'thinking' is that server should decide over clients but anybody can execute (SET) statement overriding the init-connect for the session. The option does not ensure the server settings over client settings. init-connect option is simply inconsistent with MySQL 5.x, I think.
[30 Jan 2011 23:00] Peter Laursen
My remark "(with the sole exception of AUTOCOMMIT = 0)" was wrong.  

What I meant: Using init_connect is only required to set (GLOBAL) AUTOCOMMIT = 0 at startup. 

Anyway .. now that and as long as both init_connect and (API/client) mysql_options() are there please clarify in detail what happens if both are used.
[31 Jan 2011 8:38] Valeriy Kravchuk
For me it is expected that options you set with mysql_option() redefine values you get from option files (as described at http://dev.mysql.com/doc/refman/5.1/en/option-files.html). 

Do you have any evidence that it is not the case? Why do you consider init_connect option as something so special that it needs separate explicit documenting?
[31 Jan 2011 9:08] Peter Laursen
I do not have any evidence that it is not the case as you describe it. Actually that is exactly what I see myself when setting different SQL_modes the two places. But I think it is not obvious for users that do not know server internals. In particular if they only know/find one of the pages.

This page 
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_init_connect
.. just says "A string to be executed by the server for each client that connects". I think it should have a note that any statement defined here may effectively be overridden by a client's settings in mysql_options() - if that is what happens. Or in other words: mysqloptions() are executed after --init_connect so in case of conflicts settings, settings in mysqloptions() will have effect for the new connection. And 
http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html
.. could have a similar note/reference/link as well.

Now as docs was not clear to me I had to *experiment a little* last night.  This should not be necessary for users to do.
[17 Apr 2011 17:02] Shane Bester
i would certainly not want the global init_connect options to be overridden for a non-super user who connects and gives a mysql_options() value..  

super user should ignore the global init_connect option though.  just my 2c.