I am working remotely for a firm based in India and I am connecting to the company network via VPN for daily work.
I am currently implementing a project in grails 2.2.4 which is on my local system / computer. The grails project in GGTS runs fine when connected to the local db on my machine.
But, when I am trying to connect to a database which is hosted in a server on my Company's network from my project's development environment, I am getting a lot of connection errors, all of it from MySQL.
I have configured my DataSource.groovy as below:
dataSource {
pooled = true
driverClassName="com.mysql.jdbc.Driver"
dialect = org.hibernate.dialect.MySQL5InnoDBDialect
username = <username>
password = <password>
}
hibernate {
cache.use_second_level_cache = true
cache.use_query_cache=true
cache.provider_class='net.sf.ehcache.hibernate.EhCacheProvider'
cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
}
environments {
development {
dataSource {
dbCreate = "update"
url = "jdbc:mysql://<remoteIP-on company network>:3306/<DB_Name>"
properties{
initialSize="50"
maxActive="100"
minIdle="50"
maxIdle="75"
maxWait="30000"
timeBetweenEvictionRunsMillis="5000"
minEvictableIdleTimeMillis="60000"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="true"
poolPreparedStatements="true"
maxOpenPreparedStatements="100"
validationQuery="SELECT 1 FROM DUAL"
connectionInitSqls="SELECT 1 FROM DUAL"
logAbandoned="true"
}
}
}
test {
dataSource {
dbCreate = "update"
url = "jdbc:mysql://<remoteIP-on company network>:3306/<DB_Name>"
driverClassName="com.mysql.jdbc.Driver"
username = <username>
password = <password>
}
}
production {
dataSource {
dbCreate = "update"
jndiName = "java:comp/env/<JNDIName>"
properties {
maxActive = 50
maxIdle = 25
minIdle = 5
initialSize = 5
minEvictableIdleTimeMillis = 60000
timeBetweenEvictionRunsMillis = 60000
maxWait = 10000
}
}
}
}
My BuildConfig.groovy has the following dependencies set:
dependencies {
runtime 'mysql:mysql-connector-java:5.1.40'
}
and I have the mysql-connector-java-5.1.40-bin.jar file added to the build path and the /lib/ folder .
On the remote DB, by connecting to it through HeidiSQL client on my machine, I have run the following query based on my research on the same topic of discussion.
GRANT ALL PRIVILEGES ON <DB_Name>.* TO <username>@'%' IDENTIFIED BY <password>;
FLUSH PRIVILEGES;
When I ping to the remote server from the command prompt, that too works fine.
ping <Remote Server IP>
Pinging <Remote Server IP> with 32 bytes of data:
Reply from <Remote Server IP>: bytes=32 time=261ms TTL=61
Reply from <Remote Server IP>: bytes=32 time=261ms TTL=61
Reply from <Remote Server IP>: bytes=32 time=257ms TTL=61
Reply from <Remote Server IP>: bytes=32 time=257ms TTL=61
Ping statistics for <Remote Server IP>:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 257ms, Maximum = 261ms, Average = 259ms
Now, when I run the application using GGTS Run As -> Grails Command (run-app) I get the following error:
| Error 2016-12-13 18:36:05,400 [localhost-startStop-1] ERROR context.GrailsContextLoader - Error initializing the application: Error creating bean with name 'transactionManagerPostProcessor': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Cannot resolve reference to bean 'lobHandlerDetector' while setting bean property 'lobHandler'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'lobHandlerDetector': Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (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.)
Message: Error creating bean with name 'transactionManagerPostProcessor': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Cannot resolve reference to bean 'lobHandlerDetector' while setting bean property 'lobHandler'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'lobHandlerDetector': Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (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
->> 334 | innerRun in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 166 | run in java.util.concurrent.FutureTask
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run in java.util.concurrent.ThreadPoolExecutor$Worker
^ 724 | run . . . in java.lang.Thread
Caused by BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory': Cannot resolve reference to bean 'lobHandlerDetector' while setting bean property 'lobHandler'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'lobHandlerDetector': Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (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.)
->> 334 | innerRun in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 166 | run in java.util.concurrent.FutureTask
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run in java.util.concurrent.ThreadPoolExecutor$Worker
^ 724 | run . . . in java.lang.Thread
Caused by BeanCreationException: Error creating bean with name 'sessionFactory': Cannot resolve reference to bean 'lobHandlerDetector' while setting bean property 'lobHandler'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'lobHandlerDetector': Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (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.)
->> 334 | innerRun in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 166 | run in java.util.concurrent.FutureTask
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run in java.util.concurrent.ThreadPoolExecutor$Worker
^ 724 | run . . . in java.lang.Thread
Caused by BeanCreationException: Error creating bean with name 'lobHandlerDetector': Invocation of init method failed; nested exception is org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (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.)
->> 334 | innerRun in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 166 | run in java.util.concurrent.FutureTask
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run in java.util.concurrent.ThreadPoolExecutor$Worker
^ 724 | run . . . in java.lang.Thread
Caused by MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (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.)
->> 334 | innerRun in java.util.concurrent.FutureTask$Sync
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 166 | run in java.util.concurrent.FutureTask
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run in java.util.concurrent.ThreadPoolExecutor$Worker
^ 724 | run . . . in java.lang.Thread
Caused by SQLNestedException: Cannot create PoolableConnectionFactory (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.)
->> 1549 | createPoolableConnectionFactory in org.apache.commons.dbcp.BasicDataSource
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 1388 | createDataSource in ''
| 1044 | getConnection in ''
| 334 | innerRun in java.util.concurrent.FutureTask$Sync
| 166 | run . . . in java.util.concurrent.FutureTask
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run . . . in java.util.concurrent.ThreadPoolExecutor$Worker
^ 724 | run in java.lang.Thread
Caused by CommunicationsException: 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.
->> 409 | handleNewInstance in com.mysql.jdbc.Util
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 1118 | createCommunicationsException in com.mysql.jdbc.SQLError
| 343 | <init> . in com.mysql.jdbc.MysqlIO
| 2308 | connectOneTryOnly in com.mysql.jdbc.ConnectionImpl
| 2122 | createNewIO in ''
| 774 | <init> in ''
| 49 | <init> . in com.mysql.jdbc.JDBC4Connection
| 409 | handleNewInstance in com.mysql.jdbc.Util
| 375 | getInstance in com.mysql.jdbc.ConnectionImpl
| 289 | connect in com.mysql.jdbc.NonRegisteringDriver
| 38 | createConnection in org.apache.commons.dbcp.DriverConnectionFactory
| 582 | makeObject in org.apache.commons.dbcp.PoolableConnectionFactory
| 1556 | validateConnectionFactory in org.apache.commons.dbcp.BasicDataSource
| 1545 | createPoolableConnectionFactory in ''
| 1388 | createDataSource in ''
| 1044 | getConnection in ''
| 334 | innerRun in java.util.concurrent.FutureTask$Sync
| 166 | run in java.util.concurrent.FutureTask
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run in java.util.concurrent.ThreadPoolExecutor$Worker
^ 724 | run . . . in java.lang.Thread
Caused by ConnectException: Connection refused: connect
->> 79 | socketConnect in java.net.DualStackPlainSocketImpl
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 339 | doConnect in java.net.AbstractPlainSocketImpl
| 200 | connectToAddress in ''
| 182 | connect in ''
| 172 | connect . in java.net.PlainSocketImpl
| 392 | connect in java.net.SocksSocketImpl
| 579 | connect . in java.net.Socket
| 528 | connect in ''
| 425 | <init> . in ''
| 241 | <init> in ''
| 256 | connect . in com.mysql.jdbc.StandardSocketFactory
| 292 | <init> in com.mysql.jdbc.MysqlIO
| 2308 | connectOneTryOnly in com.mysql.jdbc.ConnectionImpl
| 2122 | createNewIO in ''
| 774 | <init> . in ''
| 49 | <init> in com.mysql.jdbc.JDBC4Connection
| 409 | handleNewInstance in com.mysql.jdbc.Util
| 375 | getInstance in com.mysql.jdbc.ConnectionImpl
| 289 | connect . in com.mysql.jdbc.NonRegisteringDriver
| 38 | createConnection in org.apache.commons.dbcp.DriverConnectionFactory
| 582 | makeObject in org.apache.commons.dbcp.PoolableConnectionFactory
| 1556 | validateConnectionFactory in org.apache.commons.dbcp.BasicDataSource
| 1545 | createPoolableConnectionFactory in ''
| 1388 | createDataSource in ''
| 1044 | getConnection in ''
| 334 | innerRun in java.util.concurrent.FutureTask$Sync
| 166 | run . . . in java.util.concurrent.FutureTask
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run . . . in java.util.concurrent.ThreadPoolExecutor$Worker
^ 724 | run in java.lang.Thread
I have tried all the available solutions from stack-overflow and other websites. Till now I've been following the solutions available on
and a few other links which I am not able to post here due to less reputation on stack-overflow.
I have done exactly the same that has been asked to be done on all of the solutions available in the above mentioned links. Still I am having the same error. I am not sure what I am missing out on. Not even sure if this is caused due to the VPN connection or something. Please help me.
The issue got resolved finally.
I checked the MySQL conf file my.cnf in the server location ./etc/mysql/ . In the file my.cnf, under [mysqld] section, bind-address is set to 127.0.0.1. Instead of skip-networking being set to True, the default is now to listen only on localhost for all mysql ubuntu installations in order to avoid other servers connecting to mySQL.
Hence I commented out the line, setting bind-address=127.0.0.1 in the file my.cnf in ./etc/mysql/ folder and restarted the mysql and it worked like a charm!