[debian-mysql] Bug#695089: mysql dns resolve doesn't work well with /etc/hosts

[YunQiang Su]

Package: mysql-server

eth0: static: 172.16.200.1/24
  netmask 255.255.255.0
  gateway 192.168.1.254
eth1: dhcp: 192.168.176.137/24
Hostname: debian

apt-get install dnsmasq
  modify /etc/dnsmasq.conf  uncomment local=/localnet/
  modify /etc/resolv.conf: nameserver 172.16.200.1

/etc/hosts
   127.0.0.1 debian -> 172.16.200.1 debian
ping debian
ping debian.localnet
dig debian.localnet
dig debian
ALL WORKS

apt-get install mysql-server #set mysql root password to 123
sed -i -e 's/127.0.0.1/0.0.0.0/' /etc/mysql/my.cnf
service mysql restart
mysql -uroot -p123
  CREATE DATABASE keystone;
  GRANT ALL PRIVILEGES ON keystone.* TO 'keystone'@'localhost' IDENTIFIED
BY 'password';
  GRANT ALL PRIVILEGES ON keystone.* TO 'keystone'@'172.16.200.%'
IDENTIFIED BY 'password';
  FLUSH PRIVILEGES;
  user mysql;
  select * from user where User='keystone';
+--------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host         | User     | Password                                  |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv |
Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv |
Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer
| x509_subject | max_questions | max_updates | max_connections |
max_user_connections | plugin | authentication_string |
+--------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost    | keystone | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
N           | N           | N           | N           | N           |
N         | N           | N             | N            | N         |
N          | N               | N          | N          | N            |
N          | N                     | N                | N            |
N               | N                | N                | N              |
N                   | N                  | N                | N          |
N            | N                      |          |            |
|              |             0 |           0 |               0
|                    0 |        | NULL                  |
| 172.16.200.% | keystone | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
N           | N           | N           | N           | N           |
N         | N           | N             | N            | N         |
N          | N               | N          | N          | N            |
N          | N                     | N                | N            |
N               | N                | N                | N              |
N                   | N                  | N                | N          |
N            | N                      |          |            |
|              |             0 |           0 |               0
|                    0 |        | NULL                  |
+--------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
2 rows in set (0.00 sec)



mysql -ukeystone -ppassword
mysql>
mysql -ukeystone -ppassword -hdebian
ERROR 1045 (28000): Access denied for user 'keystone'@'debian' (using
password: YES)
mysql -ukeystone -ppassword -h172.16.200.1
ERROR 1045 (28000): Access denied for user 'keystone'@'debian' (using
password: YES)
mysql -ukeystone -ppassword -hdebian.localnet
ERROR 1045 (28000): Access denied for user 'keystone'@'debian' (using
password: YES)

$ dig debian
; <<>> DiG 9.8.1-P1 <<>> debian
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 10416
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;debian.                IN    A

;; ANSWER SECTION:
debian.            0    IN    A    172.16.200.1

;; Query time: 4 msec
;; SERVER: 172.16.200.1#53(172.16.200.1)
;; WHEN: Mon Dec  3 23:58:44 2012
;; MSG SIZE  rcvd: 40

$ host debian
debian has address 172.16.200.1
Host debian not found: 5(REFUSED)
Host debian not found: 5(REFUSED)

$nsloopup


wordaround:
1. add 'skip-name-resolve' to my.cf
2. add '172.16.200.1 debian.localnet' to hosts and must be put before
'172.16.200.1 debian'

-- 
YunQiang Su
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.alioth.debian.org/pipermail/pkg-mysql-maint/attachments/20121204/4ae3f5c1/attachment.html>