Description:
According to the manual (http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html):
log_queries_not_using_indexes boolean GLOBAL
log_queries_not_using_indexes system variable is dynamic and can be changed with SET GLOBAL. The value can be really changed, but it does not change server behaviour.
Look:
openxs@suse:~/dbs/5.0> bin/mysqld_safe --log-slow-queries &
[1] 7418
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/d
bs/5.0/var
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | ON |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
+---------------------------------+-------+
8 rows in set (0.01 sec)
mysql> show create table tab1\G
*************************** 1. row ***************************
Table: tab1
Create Table: CREATE TABLE `tab1` (
`c1` int(11) default NULL,
`c2` char(20) default NULL,
`c3` char(20) default NULL,
KEY `k1` (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from tab1;
+------+--------+--------+
| c1 | c2 | c3 |
+------+--------+--------+
| 1 | hello | world |
| 2 | hello2 | world2 |
| 3 | hello3 | world3 |
+------+--------+--------+
3 rows in set (0.00 sec)
mysql> explain select * from tab1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra:
1 row in set (0.00 sec)
mysql> exit
Bye
openxs@suse:~/dbs/5.0> tail var/suse-slow.log
/home/openxs/dbs/5.0/libexec/mysqld, Version: 5.0.44-debug-log (Source distribut
ion). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
openxs@suse:~/dbs/5.0> bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/5.0/var/suse.pid
070531 12:55:10 mysqld ended
[1]+ Done bin/mysqld_safe --log-slow-queries
openxs@suse:~/dbs/5.0> bin/mysqld_safe --log-slow-queries --log-queries-not-using-indexes &
[1] 7458
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/d
bs/5.0/var
openxs@suse:~/dbs/5.0> bin/mysql -uroot test ies
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from tab1;
+------+--------+--------+
| c1 | c2 | c3 |
+------+--------+--------+
| 1 | hello | world |
| 2 | hello2 | world2 |
| 3 | hello3 | world3 |
+------+--------+--------+
3 rows in set (0.00 sec)
mysql> exit
Bye
openxs@suse:~/dbs/5.0> tail var/suse-slow.log
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
/home/openxs/dbs/5.0/libexec/mysqld, Version: 5.0.44-debug-log (Source distribut
ion). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 070531 12:55:20
# User@Host: root[root] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 3 Rows_examined: 3
use test;
select * from tab1;
So, this option owrks only if it is set at server's startup.
How to repeat:
1. Start server with slow log enabled but log_queries_not_using_indexes disabled.
2. Execute:
CREATE TABLE `tab1` (
`c1` int(11) default NULL,
`c2` char(20) default NULL,
`c3` char(20) default NULL,
KEY `k1` (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO tab1 values(1, "hello", "world");
INSERT INTO tab1 values(2, "hello2", "world2");
INSERT INTO tab1 values(3, "hello3", "world3");
show variables like 'log%';
set global log_queries_not_using_indexes=1;
show variables like 'log%';
select * from tab1;
3. Check slow query log.
Suggested fix:
Take this variable into account when the value is changed with SET GLOBAL (or make it not dynamic and document properly).
