テスト用の Debian GNU/Linux etch サーバの mysql-server-5.0 にテスト用のデータを入れておいたらこんなエラーがメールで届いた。
From root@hogehoge.localdomain Sat Sep 29 19:43:16 2007
Envelope-to: root@hogehoge.localdomain
Delivery-date: Sat, 29 Sep 2007 19:43:16 +0900
To: root@hogehoge.localdomain
Subject: WARNING: mysqlcheck has found corrupt tables
From: root <root@hogehoge.localdomain>
Date: Sat, 29 Sep 2007 19:43:15 +0900
/usr/bin/mysqlcheck: Got error: 1045: M-%M-fM-!M-<M-%M-6M-!M-< 'debian-sys-maint'@'localhost' M-$M-rM-5M-qM-HM-]M-$M-7M-$M-^M-$M-9.uUsing password: YES) when trying to connect
Improperly closed tables are also reported if clients are accessing
the tables *now*. A list of current connections is below.
mysqlcheck が壊れたテーブルを見つけちゃいました？
エラーの詳細メッセージっぽい M-%M-fM-!M- の部分は日本語なんじゃ……
$ env | grep LANG
LANG=ja_JP.UTF-8
なんとなくメッセージ部分が EUC-JP っぽい。
From root@hogehoge.localdomain Sat Sep 29 19:43:16 2007
Return-path: <root@hogehoge.localdomain>
Envelope-to: root@hogehoge.localdomain
Delivery-date: Sat, 29 Sep 2007 19:43:16 +0900
Received: from root by hogehoge.localdomain with local (Exim 4.63)
(envelope-from <root@hogehoge.localdomain>)
id 1IbZn1-0000mW-TV
for root@hogehoge.localdomain; Sat, 29 Sep 2007 19:43:16 +0900
To: root@hogehoge.localdomain
Subject: WARNING: mysqlcheck has found corrupt tables
Message-Id: <E1IbZn1-0000mW-TV@hogehoge.localdomain>
From: root <root@hogehoge.localdomain>
Date: Sat, 29 Sep 2007 19:43:15 +0900
Status: RO
/usr/bin/mysqlcheck: Got error: 1045: ユーザー 'debian-sys-maint'@'localhost' を拒否します.uUsing password: YES) when trying to connect
Improperly closed tables are also reported if clients are accessing
the tables *now*. A list of current connections is below.
テーブル壊れてないじゃん……
debian-sys-maint がどうして拒否されてるのかはわからないけど。
追記 2007-09-30: Debian が設定したパスワードが MySQL 内で保持しているパスワードと違っていた
Debian が作ってくれた debian-sys-maint ユーザのパスワードは FYqeWF7NfhEniPKt になってた。
# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = FYqeWF7NfhEniPKt
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user = debian-sys-maint
password = FYqeWF7NfhEniPKt
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
MySQL でユーザ一覧情報がどこにあるのか探索。
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.32-Debian_7etch1-log Debian etch distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hogehoge |
| mysql |
| test |
+--------------------+
4 rows in set (0.05 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)
mysql> show columns from user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) | NO | PRI | | |
| User | varchar(16) | NO | PRI | | |
| Password | varchar(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | | |
| x509_issuer | blob | NO | | | |
| x509_subject | blob | NO | | | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.00 sec)
ユーザ情報は mysql データベースの user テーブルに入っているみたい。
ユーザのパスワードがどうなっているか調べる。
mysql> select Host, User, Password from user;
+-----------+------------------+------------------+
| Host | User | Password |
+-----------+------------------+------------------+
| localhost | root | 4649abdf8d5e1bd3 |
| hogehoge | root | 4649abdf8d5e1bd3 |
| localhost | debian-sys-maint | 6d36e9b3680a6e1f |
| % | hogehoge | 4649abdf8d5e1bd3 |
+-----------+------------------+------------------+
4 rows in set (0.00 sec)
Debian が設定したパスワードと合っているのだろうか？
で、調べてみると、
OLD_PASSWORD関数の結果(673e5d4e154cf4ea)と
user テーブルの Password の値(6d36e9b3680a6e1f)が一致していない。
# password関数は長いパスワードなので違うものっぽい
mysql> select password('FYqeWF7NfhEniPKt');
+-------------------------------------------+
| password('FYqeWF7NfhEniPKt') |
+-------------------------------------------+
| *F7F31087095F0F4FBB8F944E2DD5C5727927B4D8 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select OLD_PASSWORD('FYqeWF7NfhEniPKt');
+----------------------------------+
| OLD_PASSWORD('FYqeWF7NfhEniPKt') |
+----------------------------------+
| 673e5d4e154cf4ea |
+----------------------------------+
1 row in set (0.00 sec)
mysql> set password for 'debian-sys-maint'@'localhost'=password('FYqeWF7NfhEniPKt');
Query OK, 0 rows affected (0.00 sec)
mysql> select Host, User, Password from user;
+-----------+------------------+-------------------------------------------+
| Host | User | Password |
+-----------+------------------+-------------------------------------------+
| localhost | root | 4649abdf8d5e1bd3 |
| hogehoge | root | 4649abdf8d5e1bd3 |
| localhost | debian-sys-maint | *F7F31087095F0F4FBB8F944E2DD5C5727927B4D8 |
| % | hogehoge | 4649abdf8d5e1bd3 |
+-----------+------------------+-------------------------------------------+
4 rows in set (0.00 sec)
やっぱり、設定するときも password 関数じゃなくて OLD_PASSWORD 関数を使わないといけないので……
set password for で OLD_PASSWORD関数の結果(673e5d4e154cf4ea)をパスワードに設定。
これで user テーブルの Password の値(673e5d4e154cf4ea)と一致する。
mysql> set password for 'debian-sys-maint'@'localhost'=OLD_PASSWORD('FYqeWF7NfhEniPKt');
Query OK, 0 rows affected (0.00 sec)
mysql> select Host, User, Password from user;
+-----------+------------------+------------------+
| Host | User | Password |
+-----------+------------------+------------------+
| localhost | root | 4649abdf8d5e1bd3 |
| hogehoge | root | 4649abdf8d5e1bd3 |
| localhost | debian-sys-maint | 673e5d4e154cf4ea |
| % | hogehoge | 4649abdf8d5e1bd3 |
+-----------+------------------+------------------+
4 rows in set (0.00 sec)
でも、また同じエラーが出た orz
OS起動時だけだから気にしなくていいのかなぁ。
お、これで問題なくエラーが出なくなった。
Yokatta, yokatta.
tags: zlashdot Linux Debian MySQL
Posted by NI-Lab. (@nilab)
