Question regarding Ignition Gateway and MySQL connection

Hi,

I'm having issues with the connection between Ignition Gateway and MySQL server 8.0. I'm using Ignition Perspective and I've got the Gateway running on one VM whereas the MySQL server runs on another VM.

The Error is
" 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.) "

I tried the following:

  1. Pinged the IP address of the server from the Gateway VM and vice versa. It was successful, no issues with that.
  2. Ensured that MySQL server is running. It wasn't starting up so I added a line "bind_address = 127.0.0.1" in the config file and it worked. No issues with that since then.
  3. Ensured that firewall is disabled and port 3306 is enabled
  4. Upgraded MySQL JDBC driver, on my Gateway, from 8.0.32 to 8.2.0
    5.Reviewed the login details just to tick that possibility off. No issues with that either.

I haven't had any luck yet with whatever I've tried so far. Any suggestions please ...

Thank You..

This means bind to localhost, i.e. only accept connections from the localhost, and you have the Gateway running on a different server.

Try setting that to 0.0.0.0 instead.

Yes, I tried doing that too. But no luck yet. In fact the MySQL service also stopped when I did that.

Did you actually use this incorrect syntax? It should be:

bind-address = 0.0.0.0

No I used the hyphenated one "bind-address=0.0.0.0"

What if instead of 127.0.0.1 and 0.0.0.0 if you use the static IP of the MySQL server instead? Will MySQL start then?

Edit: Also, try connecting using a management tool like MySQL Workshop, Heidi SQL, or DBeaver and see if you can connect with it once you get MySQL running.

I tried that as well when nothing worked but the problem persisted.

Which problem? The problem that MySQL wouldn't start or that you couldn't connect to it? I feel like you're fighting 2 issues here, with the primary one being that you need to get MySQL running with a non-loopback binding address first.

Yes Michael, I've managed to get MySQL to work by binding it to 0.0.0.0. I started "mysqld" from Windows CLI as I noticed that it wasn't running earlier. MySQL server is up and running now. I looked at the Ignition Gateway but it still reads the same error
" 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.) " .

I tried some of the solutions posted on Ignition forums, but no luck yet. Any suggestions on what else could be done. Thank You..

There has to be more in the trace. There should be a 'caused by' section that may provide more clues.

I don't understand why you are using 127.0.0.1 at all. That is localhost, and inside a VM, means just inside that VM.

When communicating with MySQL or MariaDB across a network, you must be listening on a networked IP address, and have your DB's authentication allow connections from other IP addresses. By default, MySQL is only going to allow root connections from localhost (same VM).

Some hypervisor technologies also isolate VMs from each other by default, permitting only guest-host communications. Check your hypervisor docs to see if that is applicable. Orchestration technologies like docker compose handle those settings for you--others won't.

If you've really got it working at 0.0.0.0 you can confirm this with the CLI or a tool like CurrPorts: CurrPorts: Monitoring TCP/IP network connections on Windows

As @pturmel mentioned, make sure your credentials that you're using aren't using localhost for the host portion. By default it's root@localhost, but if your user is ignition, then your username configuration inside MySQL will need to be ignition@* and have the proper permissions granted. You should be able to check all of this running the MySQL Workbench on the same server MySQL is installed on.

If everything there is good, the next step would be to test the connection from the Ignition server with either MySQL Workbench or another database tool like the following using your credentials that have a host set to * (none of the users with localhost as the host will work remotely).

Here's additional SQL Tools you can try:

https://dbeaver.io