MySQL Bugs: #59826: interaction of init_connect options are not documented

Bug #59826	interaction of init_connect options are not documented
Submitted:	30 Jan 2011 20:50	Modified:	5 Aug 2021 9:03
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	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

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.

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.

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.

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?

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.

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.