MySQL Bugs: #75636: can't enable enable sql_auto_is_null automatically on each service' start

Bug #75636	can't enable enable sql_auto_is_null automatically on each service' start
Submitted:	26 Jan 2015 20:28	Modified:	28 Jan 2015 15:17
Reporter:	sebastian gomez	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S1 (Critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
upgrading from 5.0 to 5.5

I want to enable sql_auto_is_null but that option is not a command line nor an option file, so i can't put a parameter so the windows service nor an option on the my.ini 

so, what are my options? 
- modify the mail webpage of my app so every time a user logs in an "SET GLOBAL sql_auto_is_null=1" gets executed 
- create a trigger on the "ACCESS" table of my app (almost the same as the previous option) 

is there any other option that doesn't make me suffer a HUGE overhead? (I mean, a thousand users log in would be a thousand "SET GLOBAL sql_auto_is_null=1"... and only the first one would make sense) 

(Thread in the forums: http://forums.mysql.com/read.php?10,627352)

How to repeat:
N/A

Suggested fix:
getting enabled the option by defult, like it used to be
OR
be able to write the option in the my.ini

Hi Sebastian,

If you are looking for alternatives/workarounds, have you considered using an init file?  This is executed only once at startup (rather than once per connection), and could set the global variable:

http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_init-file

thanks for the answer
yes, that should work, but I've tried:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --init-file="C:\ProgramData\MySQL\MySQL Server 5.6\my2.txt" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

and

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --init-file="C:\ProgramData\MySQL\MySQL Server 5.6\my2.txt" MySQL56

and neither works (doesn't let the service go up)

Hi Sebastian,

Now you're running into the peculiarities of mysqld --install, I think.  I would encourage you to define your configuration file to include the init-file definition:

D:\mysql-5.6.24-win32>type D:\test.ini
[mysqld]
port=3311
init-file=D:\init.sql

D:\mysql-5.6.24-win32>type D:\init.sql
SET @@global.sql_auto_is_null = 1;

Then you only pass the --defaults-file option with --install, and it is much happier:

D:\mysql-5.6.24-win32>bin\mysqld.exe --install MySQLTest2 --defaults-file=D:\tes
t.ini
Service successfully installed.

Just to confirm, this configuration file has the desired effect:

D:\mysql-5.6.24-win32>start bin\mysqld --defaults-file=D:\test.ini --console

D:\mysql-5.6.24-win32>bin\mysql -uroot -P3311 -e"SELECT @@session.sql_auto_is_nu
ll, @@global.sql_auto_is_null;"
+----------------------------+---------------------------+
| @@session.sql_auto_is_null | @@global.sql_auto_is_null |
+----------------------------+---------------------------+
|                          1 |                         1 |
+----------------------------+---------------------------+

Let me know if that doesn't help.

finally!

thanks A LOT.

could you PLEASE add this to the official documentation? I bet i'm not the only one with this kind of problem.

thanks