Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm experiencing an unusual issue with my production mysql server. Currently I only have two production servers so I had all the server host name entries in /etc/hosts files on each server. A few days ago I setup a DNS zone in hopes that I can remove the manual entries in the /etc/hosts file.

Due to the fact I only have two production servers, I setup my DNS so that each server had a real hostname, which could be aliased too so that I can control connections by simply updating dns records. For example, server1 hosts httpd and mysql, so in my DNS zone I have server1 with an A record of 4.4.4.2 and store-ro with an alias to server1. Obviously 4.4.4.2 is not my real ip, demonstrative only.

After some configuration, I am able to connect to the local mysql instance via command line, but my application keeps throwing Unknown MySQL server host 'store-ro' errors. The only way I can fix the issue is if I add the following back to the hosts file:

127.0.0.1 store-ro
# or
4.4.4.2 store-ro

(Not sure if it matters, but my app is using Zend_Db to connect to the database.)

So the app can connect to the server if I enter loopback ip or the actual ip, but won't work if removed from the hosts file, so I thought maybe it was a DNS issue, but it appears that the server can resolve the hostname fine:

$ -> nslookup store-ro

Server:   72.14.179.5
Address:  72.14.179.5#53

Non-authoritative answer:
store-ro.example.com    canonical name = server1.example.com.
Name:   server1.example.com
Address: 4.2.2.2

I do have SELinux enabled, and a cursory glance over relevant httpd connection settings appear to be correct:

$ -> getsebool -a | ack httpd

httpd_can_network_connect_db --> on
httpd_can_network_connect --> on

Plus there are no entries showing up in /var/log/audit/audit.log.

My hostname is set correctly:

$ -> hostname

server1

MySQL user perms:

mysql> select host, user from user;
+-----------+-------------+
| host      | user        |
+-----------+-------------+
| %         | ro_user_1   |
| %         | ro_user_2   |

And /etc/resolve.conf:

search example.com
nameserver 72.14.179.5
nameserver 72.14.188.5

And /etc/hosts:

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

4.4.4.2 server1 server1.example.com # This shouldn't be necessary with DNS setup?

The only thing I can think of is if the alias to the cname may be causing the issue, but I doubt it. Any help is appreciated.

--Update--

Forgot to add the actual error: SQLSTATE[HY000] [2005] Unknown MySQL server host 'store-ro'

Per request here is the full row from the mysql table:

mysql> select * from user where user like '%core%'\G
*************************** 1. row ***************************
Host: %
User: ro_ml_core
Password: *2B5FEA45B69826783FD6F8601A9AFB1CFE916D52
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
share|improve this question
    
hey, can you paste complete output of select host, user from user; –  Suku Jan 10 '13 at 2:44
    
@suku: Added the row. –  Mike Purcell Jan 10 '13 at 4:42

3 Answers 3

mysql> select User,Host from user where user like '%core%';
+------------+-----------+
| User       | Host      |
+------------+-----------+
| ro_ml_core | %         |
| ro_ml_core | localhost |
+------------+-----------+
2 rows in set (0.00 sec)

It is necessary to have both (% and localhost) accounts for ro_ml_core to be able to connect from anywhere as ro_ml_core. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when ro_ml_core connects from the localhost. As a result, ro_ml_core would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'ro_ml_core'@'%' account and thus comes earlier in the user table sort order.

About sort order:

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255.255.255.0 are considered equally specific.) The pattern '%' means "any host" and is least specific. The empty string '' also means "any host" but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means "any user" and is least specific).

share|improve this answer
    
Thanks for the information, I posted the answer to how I was finally able to resolve the situation. –  Mike Purcell Jan 10 '13 at 18:49

This is happening because in your resolv.conf you don't have

search foo.com

When mysql is doing a lookup for store-ro, foo.com is not appended to it. Another way of doing this is to use fqdn. Use store-ro.foo.com to connect.

Manpage snippet. resolv.conf(5)


search Search list for host-name lookup. The search list is normally determined from the local domain name; by default, it contains only the local domain name. This may be changed by listing the desired domain search path following the search keyword with spaces or tabs separating the names. Resolver queries having fewer than ndots dots (default is 1) in them will be attempted using each component of the search path in turn until a match is found. For environments with multiple subdomains please read options ndots:n below to avoid man-in-the-middle attacks and unnecessary traffic for the root-dns-servers. Note that this process may be slow and will generate a lot of network traffic if the servers for the listed domains are not local, and that queries will time out if no server is available for one of the domains. The search list is currently limited to six domains with a total of 256 characters.

share|improve this answer
    
Thanks for the response, but I do have search example.com which is the domain to which server1 belongs. –  Mike Purcell Jan 10 '13 at 4:38
up vote 0 down vote accepted

So I was finally able to connect to the mysql server via the application. After implementing Suku's suggestion I restarted the mysql service, but still didn't work. So I figured I would restart the httpd service, and viola the app was able to connect again. It seems that if I remove the entry in /etc/hosts and don't restart the httpd service it has connection problems, but if I remove the entry and restart it works. Even adding back the entry to /etc/hosts didn't work until I restarted httpd.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.