Random MySQL 1045 errors on Docker-setup, while un+pw are correct

[VincentSC]

Matomo gives MySQL 1045 errors all the time.

SQLSTATE[HY000] [1045] Access denied for user 'matomo'@'172.18.0.4' (using password: YES)

Suddenly it connects again (when I try random things like change the password in config.ini.php or redo a grant-statement in mysql), but then it suddenly stops the connection. It stops working at random moments, but always stops working when I reboot the server. Multiple refreshes did not result in one correct connection, so I seem to have to edit the config-file to have a real shot.

The setup is five dockers: Nginx-proxy, letsencrypt-nginx-proxy-companion, MySQL, Wordpress and Matomo. Matomo and Wordpress are accessible via https on their own sub-domain. This setup is to replace a non-virtualised machine, which is working fine. The data and settings have been copied 1-on-1, except the username and password for the database.

As-link is deprecated in Docker, I had to use my own bridge in Docker and reference by name to connect to the database. This works stable and fast, as long as you use the default bridge for connecting and the new bridge for the DNS-handling. You see that when I put the name of the docker "MySQL" or full "MySQL.dockernet" it returns the IP of "MySQL" at the default bridge. I suggest you use this setup too, because of the deprecation.

Connecting as root, MYSQLI-adapter or via ip-address does not help - it gives the same error. When I change the name of the database, it gives errors that are expected: no database there. Removing all in ./tmp did neither help, as I thought the DB-connection might be cached somewhere. The strangest thing was when it did connect when I set mysql_native_password in mysql.user, but again temporary. All MySQL users are available via 172.%.

mysql> select User, Host from mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| matomo           | 172.%     |
| root             | 172.%     |
| wordpress        | 172.%     |

What could have happened is that I changed something important and made everything worse, so I did rebuild the container and replaced the volume with a new one. I did not rebuild the MySQL docker.

As Wordpress, but also mysql-client (as Matomo-user) connect without problems , I think it's how Matomo brings up the database-link. As Docker is far from easy to log problems and Matomo does not log database-stuff (for security reasons), I'm seeking assistance in debugging this. I hope it makes Matomo's database-connector more stable for other users too. Thanks in advance!

[VincentSC]

Could it be that the password is over 50 characters long? When moving the database to docker, I changed the username from piwik to matomo. While I set exactly the same password, it seems to currently only work with username piwik.

[VincentSC]

Update. While adding more Dockers using the database, still only the Matomo service is having this problem. I'd like to have some guidance to pinpoint the problem, so I could craft a PR.

[J0WI]

Can you provide some details about your setup, like ENVs, config files, docker commands etc.?

[VincentSC]

There was no progress from my side.

Using Portainer to handle Dockers. Furthermore nginx-proxy for domains and letsencrypt-nginx-proxy-companion for https. There are two networks, where bridge is used for the dockers that are public and the internal stuff is going through dockernet (as mostly explained in the first post). When creating within Portainer, I only add some configuration for nginx-proxy and letsencrypt-nginx-proxy-companion and thus also no link.

This is the config.ini.php for the database part:

[database]
host = "MySQL.dockernet" ; works in other dockers, including Wordpress
;host = "172.18.0.2" ;hardcoded, for debugging purposes
username = "piwik"
password = "daub-owing-gage-winnie-usgs-papal-cashew-play-oar-ross" ; very long password that looks like this
dbname = "piwik"
tables_prefix = "piwik_"

[J0WI]

So you are trying using the default bridge to connect the containers? It's unclear to me how you were able to resolve the host on the default bridge. This does only work with user-defined bridge networks. Please have a look at https://docs.docker.com/network/network-tutorial-standalone/

[VincentSC]

Yes, a bridge and not a link. When I bash into the docker, I can ping "MySQL.dockernet" (or ip-address). So that works. Currently extended the number of dockers and still Matomo is the only one that is unable to connect.

My request was what to do to debug this, as asked in the first post. For instance, how can I turn on the full logging for database-connections?

[J0WI]

Maybe you can find a solution here: https://matomo.org/faq/troubleshooting/#faq_115