First-time Ignition Connection to MySQL - Error

Archived Topics
1

Hello All,
I have a brand-spankin’ new install of Ignition on a new install of Ubuntu Linux, and I’m trying to connect to mySQL.
I’m a newbie at ALL of this (except maybe Linux). I can’t get the connection to work. Here are the specifics:

Linux: Ubuntu 12.04.2 LTS (64-bit version) on a DELL PowerEdge SC1420, 4Gb RAM, ~150Gb disc
MySQL: 5.5.31-0ubuntu0.12.04.2 plus MySQLWorkbench
Ignition: 7.5.8 (build 1410 | 64-bit)

I confirmed mySQL was running OK, started Ignition, then setup a DB connection inside Ignition, and it immediately faulted.
Here is the error message I received:

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:194)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:150)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1103)
java.lang.Thread.run(Thread.java:724)
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.GeneratedConstructorAccessor52.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:526)com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
com.mysql.jdbc.MysqlIO.<init>(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.<init>(ConnectionImpl.java:822)
com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)sun.reflect.GeneratedConstructorAccessor47.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:526)
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:194)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:150)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1103)
java.lang.Thread.run(Thread.java:724)
java.net.ConnectException: Connection refused
java.net.PlainSocketImpl.socketConnect(Native Method)
java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
java.net.Socket.connect(Socket.java:579)sun.reflect.GeneratedMethodAccessor24.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:332)com.mysql.jdbc.MysqlIO.<init>(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.<init>(ConnectionImpl.java:822)com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
sun.reflect.GeneratedConstructorAccessor47.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:526)
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:194)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:150)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:1103)
java.lang.Thread.run(Thread.java:724)

Any suggestions would be appreciated. In the meantime, I planned to focus on ensuring mySQL is correctly set up and listening on the right port, etc…

2

The error is about half way down in there:

Something is not setup properly. Firewall, port, database setup....
I would check the MySQL Server logs via Workbench to see if it can offer a reason.

Cheers,
Chris

3

Hi Chris,
Thanks for the response - appreciated! This was a suggestion made in response to a similar forum question earlier, and I started to investigate that line of query then, but “no joy” yet. I’ll let the forum know what I find.
Cheers,
Red

4

Red, when you created a user acct for Ignition to use to connect to the MySQL instance, did you grant it permission on anything aside from @localhost?

5

Hi Dravik,
Thanks for the reply. I set mySQL up using the standard install, so I’m not sure how that established user ID. I’ll go check this out, too…
Red

6

OK -- Got it working. Made a couple of changes, (simultaneously) so I don't know which one "fixed" the problem.
I'm working off an iMac connected on the same network segment as the Linux server hosting both mySQL and Ignition.
I'm connecting remotely using SSH. Getting terminal service is no problem, and once connected, I have root privileges and access to mySQL using
mysql, the CLI:

  1. I installed mySQL Workbench but was unable to connect to the DB. I fooled around with this, was able to get connected but without remote
    management privileges. I tweaked the server instance, added remote management capability, and that worked OK.

  2. I deleted the initial DB connection in Ignition and re-created it identically, except with one change - in place of:

jdbc:mysql://192.168.1.148:3306/test

I substituted:

Presto, everything worked....
Why, I have no idea... kind of feels like "button-mashing" in a video game where you blast the baddie and win the game, without a clear
understanding of which button delivered the K-O!
I'm ok with that! ;o)

7

Red, the difference between your connections is that by default a user can access from ‘localhost’ but CAN’T access from anywhere else.
See dev.mysql.com/doc/refman/4.1/en/ … names.html for some examples of how to setup users that can access from other hosts.