MySQL Bugs: #66713: slow_query_log=ON in my.cnf doesn't cause the variable to be set at runtime

Bug #66713	slow_query_log=ON in my.cnf doesn't cause the variable to be set at runtime
Submitted:	5 Sep 2012 20:32	Modified:	6 Sep 2012 11:13
Reporter:	Dustin Mitchell	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The setting of slow_query_log in my.cnf doesn't seem to affect the value at startup.  It can be turned on interactively *after* startup with no problems.

How to repeat:
[root@addons5.stage.db.phx1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10204
Server version: 5.1.61-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql> Bye
[root@addons5.stage.db.phx1 ~]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@addons5.stage.db.phx1 ~]# grep slow_query /etc/my.cnf 
slow_query_log = ON
slow_query_log_file        = /var/log/mysql/mysql-slow.log
[root@addons5.stage.db.phx1 ~]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
[root@addons5.stage.db.phx1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.61-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

Thank you for the bug report. Could you please try: slow_query_log = 1 instead of
slow_query_log = ON in the my.cnf file. Thanks.

That works.  Parsing error?

See bug #55290. Looks like this is fixed/implemented in 5.6

This behavior is mentioned in the 5.1 Manual:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

Note
Some of the following variable descriptions refer to “enabling” or “disabling” a variable. These variables can be enabled with the SET statement by setting them to ON or 1, or disabled by setting them to OFF or 0. However, to set such a variable on the command line or in an option file, you must set it to 1 or 0; setting it to ON or OFF will not work. For example, on the command line, --delay_key_write=1 works but --delay_key_write=ON does not.

The manual is misleading. " setting it to ON or OFF will not work. For example, on the command line, --delay_key_write=1 works but --delay_key_write=ON does not."

It works to set these as ON but MySQL takes that as text, and converts it to the number 0. So there's a silent concatenation there, and to say it "will not work" is incorrect.

Also, it's bizarre that MySQL shows you "ON" when you actually shouldn't use that value. I know it's fixed in 5.6, but it doesn't seem like it should be that difficult to backport, and I think it's critically important.

Valeriy, you say that according to http://bugs.mysql.com/bug.php?id=55290 this is fixed in MySQL 5.6, but that bug does not show it's actually fixed, it just shows that it's in triage. Can you elaborate on which 5.6 version it's fixed in?