SQLSTATE[HY000] [2002] on multiserver setup with MariaDB SSL enabled
Posted: Sun May 01, 2022 11:23 pm
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:
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:
Here is a part of `/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf`:
Here is the output of `mariadb -u root -e "SELECT User, Host, Password FROM mysql.user;"`
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.
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]
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
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
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>
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.