[Resolved] Connecting to MySQL

Archived Topics
1

Hi all,

I have MySQL database set up on my current machine with IP: 192.168.0.108
My Ignition gateway is set up on a server somewhere in our building with IP: 192.168.0.169

The database schema I’m trying to reach is called “demo”

I’m able to access the Ignition gateway, and I’ve set up a connection to the database using connection URL:

jdbc:mysql://192.168.0.108:3306/demo

In MySQL, I’ve set up a user: ‘user’. I read somewhere that default user privileges allow connections from localhost only, and that I’d have to allow the user to connect from a foreign IP. To do this, I ran the following query:

grant all privileges on db.* to 'user'@'%'

My SQL connection in ignition is faulted, and I’m not sure how to fix it. Here’s the log:

[quote]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.)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:199)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:155)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer$1.run(DatasourceManagerImpl.java:1121)
com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:573)
java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
java.util.concurrent.FutureTask.runAndReset(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)
com.mysql.jdbc.exceptions.jdbc4.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.
sun.reflect.GeneratedConstructorAccessor86.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
java.lang.reflect.Constructor.newInstance(Unknown Source)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
com.mysql.jdbc.MysqlIO.(MysqlIO.java:355)
com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2461)
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2498)
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2283)
com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:822)
com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
sun.reflect.GeneratedConstructorAccessor83.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
java.lang.reflect.Constructor.newInstance(Unknown Source)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:199)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:155)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer$1.run(DatasourceManagerImpl.java:1121)
com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:573)
java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
java.util.concurrent.FutureTask.runAndReset(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)
java.net.ConnectException: Connection timed out: connect
java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
java.net.AbstractPlainSocketImpl.connect(Unknown Source)
java.net.PlainSocketImpl.connect(Unknown Source)
java.net.SocksSocketImpl.connect(Unknown Source)
java.net.Socket.connect(Unknown Source)
sun.reflect.GeneratedMethodAccessor109.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:332)
com.mysql.jdbc.MysqlIO.(MysqlIO.java:305)
com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2461)
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2498)
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2283)
com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:822)
com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
sun.reflect.GeneratedConstructorAccessor83.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
java.lang.reflect.Constructor.newInstance(Unknown Source)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:199)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:155)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer$1.run(DatasourceManagerImpl.java:1121)
com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:573)
java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
java.util.concurrent.FutureTask.runAndReset(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
java.lang.Thread.run(Unknown Source)[/quote]

Any ideas?

Thanks!

2

Trouble connecting to the database.

You could check out and try the solutions that other people have had to this problem.
See here:
google.com/search?q=Cannot+ … server.%29

google.com/search?q=Cannot+ … 8&oe=utf-8

3

Thanks.

I never got “user” to be able to access the database.

I ended up telling Ignition to access the database as root. Then, under Users and Privileges in MySQL Workbench, I changed the “Limit to Hosts Matching:” field in one of the “root” user accounts to “%”. Then, I ran the following query:

grant all privileges on db.* to 'root'@'%'

After that, the connection was made.

1 Like