4

I am trying to connect to MySQL from JDBC via localhost. But the connection fails. In the exception, I see that JDBC is trying to connect to 127.0.0.1

    String connectionString = "";
    try {
        loadProperties();
        Class.forName("com.mysql.jdbc.Driver");
        // Setup the connection with the DB
        connectionString = "jdbc:mysql://" + properties.getProperty("host") + "/" + properties.getProperty
                ("database") + "?user=" + properties.getProperty("user") + "&password=" + properties
                .getProperty
                        ("password");
        connect = DriverManager
                .getConnection(connectionString);
        logger.debug("Connected to " + properties.getProperty("host"));
    } catch (Exception e) {
        logger.error("Database Connection failed with connection string - " + connectionString,e);
    }

From the log:

Database Connection failed with connection string - jdbc:mysql://localhost/testdb?user=testuser&password=testpass

java.sql.SQLException: Access denied for user 'testuser'@'127.0.0.1' (using password: YES)

Why is it replacing localhost with 127.0.0.1? I have configured login only for localhost.

Raj
  • 22,346
  • 14
  • 99
  • 142

2 Answers2

7

I stumbled across this question when encountering the same issue.

To answer the question "Why is it replacing localhost with 127.0.0.1?":

From the MySQL docs, using localhost in your connection URL implies that you want to connect to a socket. Using 127.0.0.1 implies that you want to connect through TCP/IP.

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. ... To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1

According to this answer, it appears that by default JDBC only supports TCP/IP connections, at least for some Java versions. Original source: http://lists.mysql.com/java/8749:

Java itself doesn't support unix domain sockets

So, I'd guess that since JDBC only connects through TCP/IP, it converts localhost to 127.0.0.1 internally.

To solve the problem in my case:

  • I granted permission in MySQL for user@127.0.0.1
  • I changed localhost to 127.0.0.1 in my connection URL.
Community
  • 1
  • 1
kentr
  • 969
  • 1
  • 11
  • 21
1

The IP address 127.0.0.1 is a special purpose address reserved for use on each computer. 127.0.0.1 is conventionally a computer's loopback address. Network software and utilities can use 127.0.0.1 to access a local computer's TCP/IP network resources. Messages sent to loopback IP addresses like 127.0.0.1 do not reach outside to the local area network (LAN) but instead are automatically re-routed by the computer's own network adapter back to the receiving end of the TCP/IP stack. In simple words, localhost can also be referred as 127.0.0.1. There is a problem with MySql access privileges. This link would help you resolve it

Keerthivasan
  • 12,760
  • 2
  • 32
  • 53
  • I know this. But my MySQL server is open only to localhost not to any other IP address including 127.0.0.1 – Raj Oct 20 '13 at 16:45
  • Do you have mapping in your hosts file config? – Keerthivasan Oct 20 '13 at 16:46
  • Yes. I have `127.0.0.1 localhost` in my `/etc/hosts` – Raj Oct 20 '13 at 16:47
  • Yes. I have been using this server for 3 years now. This is my new application. Hence created a new DB/User/Password for this application. I am able to connect to mysql from command prompt using the same credentials and via localhost/127.0.0.1. It isnt working from JDBC only. – Raj Oct 20 '13 at 16:54
  • Enabling `%` (all host access) resolved the issue. but the question still remains open. JDBC should not try to connect to 127.0.0.1 when we specify localhost. – Raj Oct 20 '13 at 16:58
  • Can you tell me the Java version? – Keerthivasan Oct 20 '13 at 18:38
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/39621/discussion-between-emaillenin-and-keerthi-ramanathan) – Raj Oct 21 '13 at 07:37
  • 1
    This is a problem. In production environments, its common to only allow localhost. This is an unexpected behavior -- is there a way to get java to trust that I know what I'm doing and actually use the host I provided, (localhost)? – BikerJared Mar 06 '14 at 19:06
  • It is actually mysql driver which converts localhost to 127.0.0.1. There is nothing we can do about it. we need to ensure that the loop back address 127.0.0.1 points to localhost – Keerthivasan Mar 07 '14 at 04:05