Java (Grails) cannot connect to MySQL remotely nor locally, but MySQL command line can

Question

I have setup many MySQL databases on Windows and Linux, and connected many Java applications to them without issue.

This particular one is driving me insane, I have checked all the usual suspects, and come up blank.

The problem:

I installed MySQL 5.6 on Ubuntu 14. I can connect to it fine from local machine or remote PC via MySQL command line, but I cannot connect my Grails application to the database, neither running in Tomcat on the local server where MySQL is installed, or running in dev mode on my local PC. The Grails app connects fine to my local MySQL running on my PC, but not the one running on Ubuntu.

Ubuntu MySQL configuration:

1. MySQL 5.6 is installed on Ubuntu 14 via apt-get install mysql-server-5.6
2. CREATE DATABASE mydb;
3. create user 'myuser'@'%' identified by 'mypass';
4. grant all privileges on * . * to 'myuser'@'%';
5. The bind address is commented out in my.cnf, so it binds to all ips.
6. service mysql restart.
7. iptables are not running.
8. ISP firewall not configured for this server, everything wide open.

Testing connection:

1. from Local server: "mysql -u myuser -p mydb" - works and can create tables etc.
2. as above from remote PC running windows via: "mysql -u myuser -p -h x.x.x.x mydb" where x.x.x.x is the IP of my server. Works fine. Can create tables etc.

The above implies that MySQL is running fine, that there are no firewalls or networking issues.

the Grails "DataSource.groovy" configuration

dataSource {
    pooled = true
    driverClassName = "com.mysql.jdbc.Driver"
    dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
} 
environments {
    development {
        dataSource {
            dbCreate = "create-drop"
            url = "jdbc:mysql://x.x.x.x:3306/mydb"
            username = "myuser"
            password = "mypass"
        }
    }
}

NOTE:

• Also tried: url = "jdbc:mysql://x.x.x.x/mydb"
• The JDBC jar is: mysql-connector-java-5.1.34.jar
• The Java used is: Windows jkd1.7.0_60

The error

Message: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    Line | Method
->>  377 | handleNewInstance             in com.mysql.jdbc.Util
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|   1036 | createCommunicationsException in com.mysql.jdbc.SQLError
|    338 | <init> . . . . . . . . . . .  in com.mysql.jdbc.MysqlIO
|   2232 | coreConnect                   in com.mysql.jdbc.ConnectionImpl

Logs: There is nothing related in the file /var/log/upstart/mysql.log nor var/log/mysql/error.log on the server.

The million dollar question is: Why would MySQL be able to connect, but JDBC not? What is the difference between the way they work (both from local and remote machines)?

This post has the most information about this issue, but I have not found any solution.

Also tried this:

properties {
    maxActive = -1
    minEvictableIdleTimeMillis=1800000
    timeBetweenEvictionRunsMillis=1800000
    numTestsPerEvictionRun=3
    testOnBorrow=true
    testWhileIdle=true
    testOnReturn=true
    validationQuery="SELECT 1"
}

Running the Grails war on Tomcat on the same server as the DB

I have more or less the same error, but different Java, different OS, different connection method.

Tomcat7 /var/lib/tomcat7/conf/context.xml

<Resource name="myresource" auth="Container" type="javax.sql.DataSource"
    maxActive="50" maxIdle="5" maxWait="10000"
    username="myuser" password="mypass" driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/mydb"/>

Grails configuration

environments {
    production {
        dataSource {
            dbCreate = "update"
            jndiName = "java:comp/env/myresource"
            pooled = true
            driverClassName = "com.mysql.jdbc.Driver"
            dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
            properties {
                maxActive = -1
                minEvictableIdleTimeMillis=1800000
                timeBetweenEvictionRunsMillis=1800000
                numTestsPerEvictionRun=3
                testOnBorrow=true
                testWhileIdle=true
                testOnReturn=true
                validationQuery="SELECT 1"
            }
        }
    }
}

The error in /var/log/tomcat7/catalina.out

2015-01-16 23:18:41,425 [localhost-startStop-1] ERROR pool.ConnectionPool  - Unable to create initial connections of pool.
java.sql.SQLException: Driver:com.mysql.jdbc.Driver@52fc036d returned null for URL:jdbc:h2:mem:grailsDB;MVCC=TRUE;LOCK_TIMEOUT=10000
        at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:296)
        at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)

Interestingly, the above mentions h2:mem db, but I think this is only a red-herring, its perhaps trying that after trying to connect to the MySQL db. There is no mention of h2 db anywhere in the DataSource.groovy file.

NOTE:

Java on the server (same as MySQL server) is OpenJDK, not Oracle's:

# apt-get install openjdk-7-jre-headless
# java -version
java version "1.7.0_65"
OpenJDK Runtime Environment (IcedTea 2.5.3) (7u71-2.5.3-0ubuntu0.14.04.1)
OpenJDK 64-Bit Server VM (build 24.65-b04, mixed mode)

JDBC (don't know how to verify the version)

# apt-get install libmysql-java
# cp /usr/share/java/mysql.jar /usr/share/tomcat7/lib

I have tried the following bind options in my.cnf (separately, with restart)

bind-address           = 127.0.0.1
bind-address           = x.x.x.x (i.e. my servers external fixed ip)
bind-address           = 0.0.0.0
# bind-address           = 127.0.0.1

all give the same error. On a dev server, I generally leave it with the bind-address commented out, so it listens on all interfaces.

I also checked that:

TOMCAT7_SECURITY=no

in /etc/init.d/tomcat7

Answer 1

I've bumped into a similar issue.

Just to clarify line in the reported error log:

URL:jdbc:h2:mem:grailsDB;MVCC=TRUE;LOCK_TIMEOUT=10000

This DB connection URL seems to be DataSource's default one which is used when you don't specify any valid URL in your custom DataSource. This means, when you see this URL in the log, you haven't specified any valid URL for your DB.