Why you can not connect to your fresh installed MySQL database.


I read in Russian the talk which I presented at MySQL Conference & Expo this year. Really it was not exactly same, but some parts existed in both. And there was one of my friends who heard the talk in Russian at the conference. He didn't attend English version, but when it finished he came and asked: "Did you say the most important?" "The most important what?" "Why people can not connect to MySQL"


So here is most important part of my talk :)


And if go through our bugs database you will see enormous quantity of bug reports with similar synopsis: "I can not connect to my fresh installed MySQL". Why does it happen?


Major reason is localhost has special meaning. This problem can look different, but has same route.


Favorite problem of Windows users, especcially ones who love GUI Tools is default host. Default host in MySQL is %, but not localhost, neither 127.0.0.1. So if you CREATE USER `abc` IDENTIFIED BY 'xyz'; you really create user `abc`@`%`. Af first glance it looks like you are safe for "Access denied" errors, but MySQL sorts its privilege tables in such a way what more exact address is first and less exact is last and if you have default anonymous user (and you have unless you had run script mysql_secure_installation or manually edited privilege tables) this user would be chosen if you connect as abc from localhost


Favorite problem of UNIX users, especcially ones who use Windows as developer's machine and UNIX on production, is mix of localhost and 127.0.0.1. Assuming you have user test@'127.0.0.1' identified by 'password'; and trying to connect from the same machine MySQL is installed on. Again you will get "Access denied" error, because localhost on UNIX has special meaning and used by default even if you specify --port option. Workaround here is force mysql to use protocol TCP/IP with option --protocol=TCP/IP or --host=127.0.0.1


And last but not least popular reason for "Access denied" errors is mixing name-based and IP-based hostnames for the same username, then specifying different privileges for these users. Which privilege MySQL chooses depends from sort order of mysql.\* tables, but in such cases this can be unpredictable and can create bad headaches, especcially in case of upgrade. Better just don't mix them.

Comments:

This is really is a piece of disgusting behavior from MySql. I remember spending half a day as a beginner to figure out what the problem was. I get so angry even today and it's a amazing the lack of respect MySql has for its users.

Whatever the technical reasons might be, whatever the security concerns might be, MySql must do something to eliminate this shortcoming. Do a default resolution of localhost and if there be any security concern, just give the user a popup warning. Man, these developers can be so obnoxious sometimes. Aloof and disrespectful, looking down on the others simply out of arrogance or spite.

Posted by florin on May 07, 2009 at 05:50 PM MSD #

Yes, this is very common problem. But is not easy to change it as it existed since very early days and many applications rely on this behavior. At least to know about would be good.

Popup can be good idea though. But still there is a security concern: what if such privileges were created by DBA not because of lack of knowledge, but with good reason? Probably more detailed warning when grant, not when connect. But here is another problem: not everybody issue SHOW WARNINGS just after she saw positive "Warning count" after a statement.

Posted by Svetlana Smirnova on May 08, 2009 at 03:30 AM MSD #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today