ლ(ಠ益ಠლ)

Debian: Warnings in MySQL Slow Query Log Upon Initialization

Observed warnings surfacing within the MySQL slow query log during mysql-server initialization.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/usr/sbin/mysqld, Version: 5.5.41-0+wheezy1-log ((Debian)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 150306 11:04:11
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000179  Lock_time: 0.000071 Rows_sent: 1  Rows_examined: 4
SET timestamp=1425639851;
SELECT count(*) FROM mysql.user WHERE user='root' and password='';
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.004310  Lock_time: 0.000083 Rows_sent: 31  Rows_examined: 81
SET timestamp=1425639851;
select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`')
      from information_schema.TABLES where ENGINE='MyISAM';
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.018225  Lock_time: 0.000230 Rows_sent: 0  Rows_examined: 808
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`COLUMNS`;
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000446  Lock_time: 0.000353 Rows_sent: 0  Rows_examined: 0
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`EVENTS`;
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000341  Lock_time: 0.000246 Rows_sent: 0  Rows_examined: 0
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`PARAMETERS`;
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.004574  Lock_time: 0.000199 Rows_sent: 0  Rows_examined: 81
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`PARTITIONS`;
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000455  Lock_time: 0.000170 Rows_sent: 0  Rows_examined: 23
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`PLUGINS`;
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000315  Lock_time: 0.000202 Rows_sent: 0  Rows_examined: 1
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`PROCESSLIST`;
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000508  Lock_time: 0.000401 Rows_sent: 0  Rows_examined: 0
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`ROUTINES`;
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.003001  Lock_time: 0.000200 Rows_sent: 0  Rows_examined: 0
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`TRIGGERS`;
# [email protected]: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.002677  Lock_time: 0.000156 Rows_sent: 0  Rows_examined: 0
SET timestamp=1425639851;
select count(*) into @discard from `information_schema`.`VIEWS`;

This is caused by the directive “log-queries-not-using-indexes” within /etc/mysql/my.cnf

1
2
# grep index /etc/mysql/my.cnf
log-queries-not-using-indexes

Example EXPLAIN:

1
2
3
4
5
6
7
mysql> EXPLAIN select count(*) into @discard from `information_schema`.`VIEWS`;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------+
|  1 | SIMPLE      | VIEWS | ALL  | NULL          | NULL | NULL    | NULL | NULL | Open_frm_only; Scanned all databases |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------+
1 row in set (0.00 sec)

Key fields are NULL, which is sufficient to trigger the notification push to the slow query log.

Comments