MySQL connection fault

Ignition
1

Hi everybody,
I´m trying to connect to a mysql database and it´s not possible because an error like this i get.
Anybody has an idea?

“org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unable to load authentication plugin ‘caching_sha2_password’.)
Caused by: java.sql.SQLException: Unable to load authentication plugin ‘caching_sha2_password’.”

My connection url is like this :
jdbc:mysql://localhost:3306/normon_report
My ignition gateway is a 7.9.9 version.

Thank you very much.

Eduardo.

2

As per this post on StackOverflow, starting with MySQL 8.0.4 they have changed the default authentication plugin for MySQL server from mysql_native_password to caching_sha2_password.

You can either force it back to using the older password plugin or try using the newest MySQL connector/J.

1 Like
3

Thank you,
I have tried to do this as the link above and now have another error.

“https://support.inductiveautomation.com/index.php?/Knowledgebase/Article/View/125/0/connection-errors-when-configuring-mysql-80-database-in-ignition”

My new error is:
“Cannot create PoolableConnectionFactory (The server time zone value ‘Hora estándar romance’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)”

:rage:

4

Sorry i mean below
“https://support.inductiveautomation.com/index.php?/Knowledgebase/Article/View/125/0/connection-errors-when-configuring-mysql-80-database-in-ignition”

5

You need to add a serverTimezone property to the connection properties for that MySQL connection - eg:
serverTimezone=UTC or serverTimezone=America/Los Angeles. There's a change going into 7.9.11 that will automatically add the gateway's timezone to your connection string if it's not already populated, but until that happens you will need to set it manually.

1 Like
6

Thank you very much.

7

Had me stumped this one. Thanks.

8

How do you get this list of the available timezones? I keep getting this:
image

I tried using everything (i.e. Id, DisplayName) from the result from running PowerShell command: get-timezone
image

and also had a look in the windows registry for all available timezones:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

Edit:
It seems like you have to use the format: Country/City, as in your example (couldn’t get your example to work originally)

9

To get the full list supported by your local Java runtime:

from java.util import TimeZone

for tzid in TimeZone.getAvailableIDs():
	print tzid
2 Likes