SQLSTATE[HY000] [2002] on multiserver setup with MariaDB SSL enabled

Current Development version likely to have breaking changes
Post Reply
sweatyphilosopher
Posts: 1
Joined: Wed Apr 20, 2022 7:31 am

SQLSTATE[HY000] [2002] on multiserver setup with MariaDB SSL enabled

Post by sweatyphilosopher »

Hi there! It's first post on this forum so excuse me if I do anything wrong.

I've been trying to fix this issue for most of today and decided that its time to go to bed. Please let me know if I'm missing something obvious.

I'm running 2 ZoneMinder servers, with a separate server for the database. Both ZoneMinder servers are running Ubuntu 20 LTS and are on the master branch of ZoneMinder (1.37.14~20220501132939-focal). The database server is running Debian 11 (mariadb --version = 15.1 Distrib 10.5.15-MariaDB).

This setup worked fine until I enabled SSL on the database server. From the ZoneMinder servers, I have no problem connecting using `mariadb -u zmuser -h <host> -p`. When I try to navigate to the ZoneMinder page on either of the servers I am presented with this error:

Code: Select all

Unable to connect to ZM db using dsn mysql:host=<db host>;dbname=zm

SQLSTATE[HY000] [2002] 
There is nothing other than `ZoneMinder will retry connection in <n> seconds.` after that.

I have double, triple, and quadruple checked that ZoneMinder uses the same paths as the MariaDB client.

Every 30 seconds, when a ZoneMinder servers try to connect to the database, I get the following log in`systemctl status mysql` on the database server:
` [Warning] Aborted connection <n> to db: 'unconnected' user: 'unauthenticated' host: '<ip of ZoneMinder server>' (This connection closed normally without authentication)`

Here is my `/etc/zm/conf.d/03-certs.conf` from one of the servers:

Code: Select all

# SSL CA certificate for ZoneMinder database
ZM_DB_SSL_CA_CERT=/etc/mysql/ssl/ca-cert.pem

# SSL client key for ZoneMinder database
ZM_DB_SSL_CLIENT_KEY=/etc/mysql/ssl/client-key.pem

# SSL client cert for ZoneMinder database
ZM_DB_SSL_CLIENT_CERT=/etc/mysql/ssl/client-cert.pem
Here is a part of `/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf`:

Code: Select all

[mysql]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
Here is the output of `mariadb -u root -e "SELECT User, Host, Password FROM mysql.user;"`

Code: Select all

User    Host    Password
mariadb.sys     localhost
root    localhost       invalid
mysql   localhost       invalid
zmuser  %       <password>
zmuser  localhost      <password>
zmuser  192.168.0.0/255.255.255.0       <password>
Like I said, I have no problem connecting using `mariadb` in the terminal, so this makes me think that this is an issue with ZoneMinder.

Thanks in advance for any help!

Edit: just wanted to add that I couldn’t see anything related to this in any logs or in `systemctl status zoneminder` on either of the servers. I might’ve missed something so I can provide any logs if required. I have also tried a few things with the keys and certs on the clients like putting them in /var/lib/mysql, changing ownership to mysql and to www-data, changing the permissions to 777, 744, 700, 400, and 444.
User avatar
bkjaya1952
Posts: 282
Joined: Sat Aug 25, 2018 3:24 pm
Location: Sri Lanka

Re: SQLSTATE[HY000] [2002] on multiserver setup with MariaDB SSL enabled

Post by bkjaya1952 »

I had the same problem with mysql 8 .
Later I have tried the procedure mentioned in the following link and found successful .
https://bkjaya.wordpress.com/2022/05/24 ... ntu-focal/
Post Reply