MySQL SSL & WARN Log Entries [Solved - Maybe???]

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

MySQL SSL & WARN Log Entries [Solved - Maybe???]

Post by PointsWell »

EDITED 2019: The fix is in this post below for the TLDR.

EDITED: rather than post a reply and then choke up the forum with my blather. Not fixed, but now I can ignore it more.

Passing the log to a database
-- END OF EDIT --


If you upgrade the mysql connector to the most up to date version then you get a LOT of log entries like this

Code: Select all

Mon Jul 24 05:13:31 UTC 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
So so many of them that you can't read the log when you are looking for errors.

Allegedly if you add ?useSSL=false to the end of the connection string in BASServer.props it is supposed to suppress these errors, except it doesn't, no matter where in the connection string you put it it either screws the user login OR just ignores it and generates the above warning message.

Has anyone found the way to suppress these?

Alternatively if anyone has a guide as to how to set up SSL for MySQL I'd be very grateful as I have wasted a whole day on this instead of debugging the actual issue that I am having but can't see because of all these *^&**&^@^$&* WARN messages...

Please help, I am down to my last tufts of hair...
Last edited by PointsWell on Wed Jan 30, 2019 1:27 am, edited 4 times in total.
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: MySQL SSL & WARN Log Entries

Post by PointsWell »

I have solved this mostly...

For whatever reason when running with the v5.x J Connector for MySQL the useSSL=false flag is ignored.

So in order to fix this you need to use SSL which requires a client certificate, a client key and a server certificate.

Depending on how your mySQL is setup it will affect how you obtain these, so you need to do a bit of googling.

You then need to add these to your server keystore and truststore (or create yourself a new keystore).

Then you need to explicitly refer to them in your connection string which will then look like:

Code: Select all

DriverURL=jdbc:mysql://localhost/BASDB?user=[DbUser]&password=[DbUserPassword]&useSSL=true&clientCertificateKeyStoreUrl=file:/opt/cert/keystore&clientCertificateKeyStorePassword=[yourKeyStorePassword]&clientCertificateKeyStoreType=JKS&trustCertificateKeyStoreUrl=file:/opt/cert/truststore&trustCertificateKeyStoreType=JKS&trustCertificateKeyStorePassword=[yourTrustStorePassword]

DriverTestURL=jdbc:mysql://localhost/BASDBTEST?user=[DbUser]&password=[DbUserPassword]&useSSL=true&clientCertificateKeyStoreUrl=file:/opt/cert/keystore&clientCertificateKeyStorePassword=[yourKeyStorePassword]&clientCertificateKeyStoreType=JKS&trustCertificateKeyStoreUrl=file:/opt/cert/truststore&trustCertificateKeyStoreType=JKS&trustCertificateKeyStorePassword=[yourTrustStorePassword]

BootstrapURL=jdbc:mysql://localhost/?user=[DbUser]&password=[DbUserPassword]&useSSL=true&clientCertificateKeyStoreUrl=file:/opt/cert/keystore&clientCertificateKeyStorePassword=[yourKeyStorePassword]&clientCertificateKeyStoreType=JKS&trustCertificateKeyStoreUrl=file:/opt/cert/truststore&trustCertificateKeyStoreType=JKS&trustCertificateKeyStorePassword=[yourTrustStorePassword]
You will obviously need to use the correct file path for your keystore and truststore.

Setting this up reduced the number of SSL WARN messages from 1 octillion (give or take a billion) down to about 30. These 30 seem to be related to internal AIM things such as:

Code: Select all

'PublicHolidays' property not found in property file
Wed Jul 26 06:46:33 UTC 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
and

Code: Select all

Tomcat: INFO: Destroying ProtocolHandler ["ajp-nio-8009"]
Process status: Tomcat: Process finished
Wed Jul 26 06:48:43 UTC 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Wed Jul 26 06:48:43 UTC 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Server encountered an exception Error reading business space version from persistence Illegal operation on empty result set.
Which makes me think they are internal database calls
Rennur
Posts: 1191
Joined: Thu Mar 01, 2012 5:13 am
Location: Sydney, Australia

Re: MySQL SSL & WARN Log Entries [Not Solved]

Post by Rennur »

Solution to the annoying SSL messages:
Attachments
Disable SSL messages.
Disable SSL messages.
ssl.png (141.23 KiB) Viewed 26977 times
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: MySQL SSL & WARN Log Entries [Not Solved]

Post by PointsWell »

Rennur wrote:Solution to the annoying SSL messages:
Does that work for you Rennur? I still get a large number of SSL messages.
Rennur
Posts: 1191
Joined: Thu Mar 01, 2012 5:13 am
Location: Sydney, Australia

Re: MySQL SSL & WARN Log Entries [Not Solved]

Post by Rennur »

It worked, but the BASSever.props resets if you open General and click OK.

Did you restart the server?
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: MySQL SSL & WARN Log Entries [Not Solved]

Post by PointsWell »

Rennur wrote:It worked, but the BASSever.props resets if you open General and click OK.

Did you restart the server?
I did. There seems to be less of the messages, but there are still a reasonable number of them and it doesn't appear that the server generates the message while idling as it used to do (yet).
Rennur
Posts: 1191
Joined: Thu Mar 01, 2012 5:13 am
Location: Sydney, Australia

Re: MySQL SSL & WARN Log Entries [Not Solved]

Post by Rennur »

Together with the BASServer.props parameter, try adding a line in Tomcat/conf/context.xml within <Conctext> </Context>

Code: Select all

url="jdbc:mysql://localhost:3308/BASDB?autoReconnect=true&verifyServerCertificate=false&useSSL=false&requireSSL=false"/
Attachments
context.png
context.png (111.89 KiB) Viewed 26959 times
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: MySQL SSL & WARN Log Entries [Not Solved]

Post by PointsWell »

Thanks, added the line to context.xml and it made absolutely no difference at all.

I did think to switch on SSL on my MySQL server, but I am connecting via an SQL proxy which is making it a virtual localhost and it switches off the SSL over localhost.

Ho hum
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: MySQL SSL & WARN Log Entries [Not Solved]

Post by Jaymer »

yeah, this shit is getting old
just happened when i upgraded a client to a more recent 8.1
not onto 8.2 yet

Code: Select all

Thu Nov 29 22:46:53 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Thu Nov 29 22:46:54 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Thu Nov 29 22:47:04 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Thu Nov 29 22:47:04 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
I added the useSSL=false stuff in BASServerProps, but have not done Rennur's last fix... yet.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: MySQL SSL & WARN Log Entries [Solved - Maybe???]

Post by PointsWell »

I think I have managed to suppress the dreaded errors!!!

Update MySQL JDBC jar to 8.0.14


====== EDITED SEE BELOW==========
Last edited by PointsWell on Wed Jan 30, 2019 1:46 am, edited 1 time in total.
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: MySQL SSL & WARN Log Entries [Solved - Maybe???]

Post by PointsWell »

PointsWell wrote: I don't know if the fix is:
the driver update
the driver update and SslMode=none
I guilted myself into testing properly.

The fix is the updated JDBC jar.

I have removed ALL of the extra parameters from the connection string and now it works with zero errors.
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: MySQL SSL & WARN Log Entries [Solved - Maybe???]

Post by PointsWell »

IGNORE - Omitted Steps.


I have rebuilt my install and found that there must have been something else that I had done as this fix has undone itself ...

I should keep notes of what I am changing as I go along.
Last edited by PointsWell on Thu Mar 14, 2019 6:03 am, edited 1 time in total.
customaware
Posts: 2392
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: MySQL SSL & WARN Log Entries [Solved - Maybe???]

Post by customaware »

I hate this and have never been able to resolve it.

Would love to see a clear, succinct, step by step solution that actually works.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
Andrea
Posts: 45
Joined: Mon Nov 02, 2009 3:41 pm
Location: Calgary, Alberta

Re: MySQL SSL & WARN Log Entries [Solved - Maybe???]

Post by Andrea »

+1
Andrea
T0M
Posts: 21
Joined: Mon Jan 28, 2019 9:37 am
Location: Central Europe

Re: MySQL SSL & WARN Log Entries [Solved - Maybe???]

Post by T0M »

+2
8.2 (Build 2570) | MySQL | Win 2016 | UpCloud | == Arabica && || Robusta
Post Reply