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

[26 Jan 2015 20:28] sebastian gomez
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
[26 Jan 2015 20:34] Todd Farmer
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
[26 Jan 2015 22:17] sebastian gomez
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)
[27 Jan 2015 15:38] Todd Farmer
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.
[27 Jan 2015 20:46] sebastian gomez
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.
[28 Jan 2015 15:17] sebastian gomez
thanks

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.