First off, as part of this bit of mayhem I found an answer for the recent (and common) question how to verify if the server is properly started with skip-name-resolve, as there's no status variable for that: if you perform a grant on a hostname instead of an IP, it'll spew a warning at you :-)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `ussr`.* TO 'ussr'@'phpstag1' IDENTIFIED BY PASSWORD '*'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------+ | Warning | 1285 | MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work | +---------+------+--------------------------------------------------------------------------------------------------------------+ Useful to know :-) Now, my bit of crap for this morning. Note that I've already worked around it, just wanted to throw it out here in case someone has a clue about possible causes or needs to know they're not alone. The setup is simple: a site on a staging environment that hasn't been used in a few weeks is suddenly unable to connect to the database. The error message makes it clear that it's an authentication issue, not a connection issue. The documented grant matches the host, the host matches the IP in the hosts file. So, I decide to go have a look at what's effectively granted. mysql> show grants for ussr@phpstag1; ERROR 1141 (42000): There is no such grant defined for user 'ussr' on host 'phpstag1' The fuck? mysql> select user, host from user where user = 'ussr' and host = 'phpstag1'; +------+----------+ | user | host | +------+----------+ | ussr | phpstag1 | +------+----------+ 1 row in set (0.00 sec) mysql> select user, host from db where user = 'ussr' and host = 'phpstag1'; +------+----------+ | user | host | +------+----------+ | ussr | phpstag1 | +------+----------+ 1 row in set (0.00 sec) So the entries are there, do not have stray spaces or whatnot, but are not found. Flush hosts, flush privileges, repair extended and even restarting the service are all for nought. Since it claims it doesn't exist, I try reinserting it, which also yields funny messages. mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `ussr`.* TO 'ussr'@'phpstag1' IDENTIFIED BY PASSWORD '*'; ERROR 1133 (42000): Can't find any matching row in the user table In the end, I deleted the relevant rows from the privilege tables, flushed, regranted and then it worked as expected. Now, the point was really that I had to delete that user anyway as we're converting to skip-name-resolve (hence the first bit of this mail), but this seemed very strange indeed... until it hit me. It's a half-bug, probably due to a quirky design decision somewhere along the road: when running in skip-name-resolve, the "show grants" statement does not look up hostnames from the privilege tables, but it does inside the privilege cache. Thus, such grants work - wait, do they? I checked :-) mysql> grant all privileges on ussr.* to czar@phpstag1 identified by 'czar'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------+ | Warning | 1285 | MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work | +---------+------+--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Then, on host phpstag1: 14:34:00|meersjo@phpstag1:~ 0 $ mysql -h172.18.65.23 -uczar -pczar ERROR 1045 (28000): Access denied for user 'czar'@'172.18.65.22' (using password: YES) Good, so that does indeed not work :) So, in summary: when in skip-name-resolve mode, grants will spew a useful warning if they contain hostnames; but "show grants" fails mysteriously. Drop user, interestingly enough, does work as expected. That one kind of surprised me. Not sure if this is a true bug, but it could be handled a bit more gracefully. Anyway, it may save someone's day sometime. Enjoy. Oh, right, this is on 5.0.51a-24+lenny5-log. YMMV. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
