ERROR 1146 (42S02): Table 'mysql.slow_log' doesn't exist

mchdba發表於2014-03-18

去檢視最新的slow log,發現沒有最新的記錄,上去檢查slow log是否開啟了。


mysql> show variables like '%slow%';

+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| log_slow_queries    | OFF                                      |
| slow_launch_time    | 2                                        |
| slow_query_log      | OFF                                      |
| slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log |
+---------------------+------------------------------------------+
4 rows in set (0.00 sec)

看到OFF,果然關閉了,立即開啟。


mysql> set global slow_query_log='on';
ERROR 1146 (42S02): Table 'mysql.slow_log' doesn't exist
mysql> mysql> 
mysql> set global slow_query_log=1;
ERROR 1146 (42S02): Table 'mysql.slow_log' doesn't exist
mysql> 
mysql> exit

Bye


報錯了,去mysql庫check下,有無這個表:

mysql> use mysql
Database changed
mysql> desc slow_log;
ERROR 1146 (42S02): Table 'mysql.slow_log' doesn't exist


mysql.slow_log表還是必須的,沒有這個表slow log 也不能輸出到FILE。該表,是當開啟引數log_output設定為table的時候,slow.log會記錄到這個表裡面,但是由於記錄該表會對效能有影響,所以一般都是記錄到FILE裡面,然後再用指令碼來處理。現在報錯那就臨時建立下這個表試試看,不過記得關閉寫入二進位制,因為是雙主嘛:

mysql> set session sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)


mysql> use mysql
Database changed


mysql> CREATE TABLE `slow_log` (
    -> `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> `user_host` mediumtext NOT NULL,
    -> `query_time` time NOT NULL,
    -> `lock_time` time NOT NULL,
    -> `rows_sent` int(11) NOT NULL,
    -> `rows_examined` int(11) NOT NULL,
    -> `db` varchar(512) NOT NULL,
    -> `last_insert_id` int(11) NOT NULL,
    -> `insert_id` int(11) NOT NULL,
    -> `server_id` int(10) unsigned NOT NULL,
    -> `sql_text` mediumtext NOT NULL
    -> ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='just only a slow log';
Query OK, 0 rows affected (0.02 sec)


mysql> 
mysql> 



然後再去開啟slow log 日誌

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


mysql> 


mysql> 
mysql> 
mysql> select sleep(10),1 as a;
+-----------+---+
| sleep(10) | a |
+-----------+---+
|         0 | 1 |
+-----------+---+
1 row in set (10.00 sec)


mysql> 


然後去check下這個慢查詢sql是否寫入slow log了

ll slow_queries_3306.log

-rw-rw---- 1 mysql mysql       0 Feb 10 04:10 slow_queries_3306.log


竟然是空的,為什麼?

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26230597/viewspace-1123960/,如需轉載,請註明出處,否則將追究法律責任。

相關文章