MySQL on Debian Linux Gateway - Installation and Connection Error

Ignition
1

Slowly working my way through IU training and I am now at the point of building a demonstration system. I am trying to install MySQL and create a DB connection. My Ignition Gateway and MySQL DB are installed on the same headless Debian Linux server. Here are the steps I took:

  1. I installed MySQL 8.0 via the mysql-apt-config_0.8.22-1_all.deb package obtained from Oracle.
  2. I installed the connector via the mysql-connector-java_8.0.28-1debian11_all.deb obtained from Oracle
  3. I recieved the “missing files” error when attempting to create the connection in the Database connections configuration page. To solve this (this may be the incorrect method) I copied the mysql-connector-java-8.0.28.jar file from the /usr/share/java folder on the server to my Windows client so I then point to it when updating the driver file.
  4. I then created a DB connection, which faulted with the following error:
java.sql.SQLException: Cannot create PoolableConnectionFactory (Access denied for user 'sa'@'localhost' (using password: YES))
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:656)
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:534)
	at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:273)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:226)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.lambda$newRetryRunnable$0(DatasourceManagerImpl.java:1064)
	at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:582)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Access denied for user 'sa'@'localhost' (using password: YES)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:829)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:449)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:242)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
	at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:52)
	at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:357)
	at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:103)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:652)
	... 12 more

8.1.12 (b2021111810)
Azul Systems, Inc. 11.0.11

Any ideas on where to start?

2

The error's pretty straightforward. Make sure your credentials are correct, or, better, make a new user account on MySQL just for Ignition to use, instead of your sa account (even for a testing system, it's not really a good practice).

2 Likes
3

I think you are on to something. When I installed MySQL, I didn’t create an acount. So, MySQL is either not accepting the credentials I am giving it for the default user account that was created during installation or there arent any accounts at all.

Let me explore what accounts exist in the existing MySQL installaiton and if necessary I’ll create a new one as you suggest. I’ll report back.

4

Issue is resolved. There were actualy two problems:

  1. When I created the DB connection in Ignition, I was not using the correct password for the default root user. Once I used the correct password, I cleared the authentication error. However (see point #2).
  2. The DB Configuration was pointing to a “test” database in the connection configuration that didn’t exist. I had to manualy create a “test” database in MySQL.

Once I perofrmed the above actions, I was able to create a VALID DB connection in ignition.

As previosuly suggested, I created a second user with permissions and created a new connection using those credentials instead.

Thank you for the support.

1 Like