When using SSL to connect to a MariaDB instance on Amazon RDS, I'm able to setup a persistent connection, and login almost every time.
But I will occasionally get:
[Warning] Access denied for user 'xxx'@'xxx' (using password: YES)
I believe this specific "access denied" error is because the database account has
ssl_type set to "ANY", which forces the connection to be via SSL (or TLS).
COUNT_SSL_ERRORS value does keep going up for the host in question.
My server typically maintains ~25 persistent connections.
When restarting the MariaDB database, I will often get a few of these "access denied" errors (anywhere between 0 and 10). This is annoying, especially as it can happen several minutes after the restart, but it's kind of understandable.
But it also happens randomly, once every few days, and this is the problem I'd like to address.
I had assumed it was doing a software update or backup at that time, but these errors were nearly always outside of the Backup and Maintenance windows.
There was also no database restarts in the logs either, just incase the server had recently crashed.
My original PHP code for connecting was roughly:
<?php $link = mysqli_init(); mysqli_ssl_set($link, NULL, NULL, $ca_file, NULL, NULL); mysqli_real_connect($link, 'p:' . $host, $user, $pass, $name); mysqli_set_charset($link, 'utf8mb4'); ?>
I've modified this code since, so if
mysqli_real_connect() returns false, I'll check if
mysqli_connect_errno() returns 1045 (access denied) or 2002 (connection error), and if so, wait 0.5 seconds, and try connecting again - this hasn't helped.
As a side thing, and it might be un-related, but before I used persistent connections, I would sometimes get a "Temporary failure in name resolution", which I assumed was due to a normal DNS lookup failure (as Amazon RDS uses DNS entries with a 5 second TTL); then on second connection attempt, it would nearly always get the "Access denied" error, which I found interesting. And like the issue explained above, this was also happening without a restart, change in IP address, etc.
I am tempted to try using
mysqli_close() after the first failed attempt, but the PHP documentation clearly says it "will not close persistent connections", so I doubt that will help.
I have considered it being a network issue between the EC2 instance and RDS, but it's odd that it happens when logging in (i.e. creating or starting to re-use a connection), where I've not had any issues after logging in, for example issues with queries failing.