I'm having trouble getting WordPress to talk to a MySQL database once I moved it behind a linuxserver/swag container to enable https.
I'm using Docker version 20.10.2, build 2291f61 and docker-compose version 1.27.4, build 40524192; these are running on a VPS running Debian 10.
Here is the docker-compose.yml file I created. In short, I have three containers:
-wordpress: a WordPress container
-mysql: the database for wordpress, and
-certman: a linuxserver/swag (Secure Web Application Gateway) container that manages SSL/TLS certificates.
version: "3.8"
services:
mysql:
image: mysql:latest
container_name: mysql
environment:
- PUID=1000
- PGID=1001
- MYSQL_ROOT_PASSWORD=[password 1]
- TZ=America/New_York
- MYSQL_DATABASE=wordpress
- MYSQL_USER=wpuser
- MYSQL_PASSWORD=[password 2]
ports:
- 3306:3306
- 33060:33060
volumes:
- /home/[my username]/appdata/mysql:/var/lib/mysql
restart: unless-stopped
swag:
image: linuxserver/swag:latest
container_name: certman
cap_add:
- NET_ADMIN
environment:
- PUID=1000
- PGID=1001
- TZ=America/New_York
- URL=[my domain name]
- SUBDOMAINS=www,
- VALIDATION=http
- EMAIL=[my email]
- CERTPROVIDER=zerossl #used up weekly quota from let's encrypt troubleshooting already
volumes:
- /home/noreply/appdata/swag:/config
ports:
- 443:443
- 80:80
depends_on:
- mysql
restart: unless-stopped
wordpress:
depends_on:
- mysql
image: wordpress:latest
ports:
- 8080:80
restart: unless-stopped
volumes:
- /home/noreply/appdata/swag/www/wordpress:/var/www/html
environment:
- WORDPRESS_DB_HOST=mysql
- WORDPRESS_DB_NAME=wordpress
- WORDPRESS_DB_USER=wpuser
- WORDPRESS_DB_PASSWORD=[password 2 from above]
container_name: 'wordpress'
After running this, I go to ~/appdata/swag/nginx/site-confs/default and change the root location so nginx uses WordPress:
...
# main server block
server {
listen 443 ssl http2 default_server;
listen [::]:443 ssl http2 default_server;
root /config/www/wordpress; #the only change is adding /wordpress here
...
After restarting the certman container and waiting a moment, https://[my domain name] only shows the "Error establishing a database connection" page from WordPress.
From the logs, I think certman is working fine (I can inspect a certificate in my browser and the logs end with "Server Ready").
If I run docker logs mysql, this output is obtained (I don't think there's anything remarkable here?):
2021-01-26 00:01:49-05:00 [Note] [Entrypoint]: Creating database wordpress
2021-01-26 00:01:49-05:00 [Note] [Entrypoint]: Creating user wpuser
2021-01-26 00:01:49-05:00 [Note] [Entrypoint]: Giving user wpuser access to schema wordpress
2021-01-26 00:01:49-05:00 [Note] [Entrypoint]: Stopping temporary server
2021-01-26T05:01:49.440142Z 13 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.23).
2021-01-26T05:01:50.967557Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23) MySQL Community Server - GPL.
2021-01-26 00:01:51-05:00 [Note] [Entrypoint]: Temporary server stopped
2021-01-26 00:01:51-05:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
2021-01-26T05:01:51.757509Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 1
2021-01-26T05:01:51.770145Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-26T05:01:52.015633Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-26T05:01:52.134045Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-01-26T05:01:52.225683Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-26T05:01:52.225949Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-26T05:01:52.231383Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2021-01-26T05:01:52.254800Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
However, docker logs wordpress returns:
WordPress not found in /var/www/html - copying now...
Complete! WordPress has been successfully copied to /var/www/html
[26-Jan-2021 05:01:37 UTC] PHP Warning: mysqli::__construct(): (HY000/2002): Connection refused in Standard input code on line 22
MySQL Connection Error: (2002) Connection refused
MySQL Connection Error: (2002) Connection refused
MySQL Connection Error: (2002) Connection refused
MySQL Connection Error: (2002) Connection refused
MySQL Connection Error: (2002) Connection refused
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 192.168.32.4. Set the 'ServerName' directive globally to suppress this message
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 192.168.32.4. Set the 'ServerName' directive globally to suppress this message
[Tue Jan 26 05:01:52.428348 2021] [mpm_prefork:notice] [pid 1] AH00163: Apache/2.4.38 (Debian) PHP/7.4.14 configured -- resuming normal operations
[Tue Jan 26 05:01:52.428437 2021] [core:notice] [pid 1] AH00094: Command line: 'apache2 -D FOREGROUND'
I read here that the 2002 error means that the specified user can't edit the database but, since the access parameters set as environment variables for both are identical (database name, user, and password), I don't know what to do with this. I also tried using a bash shell inside the wordpress container to ping the database hostname ("mysql") and that worked fine (after installing inetutils-ping), so I don't think it's a networking problem. The mysql container can ping wordpress just fine, as well.
Things I've tried to fix the issue that made no changes I could see:
-Restarting the wordpress container just in case WordPress was starting up too fast for mysql (in place of the wait for function or something) and vice versa
-Deleting everyting (docker-compose down then pruning the images and deleting the volume folders in ~/appdata) and repeating setup, but this doesn't work either (it worked briefly the very first time I did it but I have been unable to replicate that at all several times without any changes to the .yml; all I accomplished was using up my let's encrypt quota for the week trying to replicate the success).
-Using non-latest images of MySQL and WordPress (inspired by this)
-Connecting wordpress to mysql with the root password instead of the wpuser (inspired by this, which is the same page I linked underneath the logs).
-Using Links: mysql under the wordpress service in the docker-compose.yml (inspired by this)
Also of note, if I remove the linuxserver/swag content from the docker-compose.yml and set ports:80:80 for the wordpress container, I get the WordPress setup screen over http. Inspecting the wordpress container logs of this working instance shows that it briefly had that 2002 error but I think it autoresolved:
[26-Jan-2021 05:48:24 UTC] PHP Warning: mysqli::__construct(): (HY000/2002): Connection refused in Standard input code on line 22
MySQL Connection Error: (2002) Connection refused
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 192.168.112.3. Set the 'ServerName' directive globally to suppress this message
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 192.168.112.3. Set the 'ServerName' directive globally to suppress this message
If I set 80:80 under wordpress (bypassing certman) and leave the certman container (with 443:443), http://[the IP address] works (I think my nginx configuration in ~/appdata/swag/nginx/site-confs/default reroutes http://[my domain name] traffic to https by default, so it makes sense that http://[my domain name] just reroutes to https and does not work).
Since setting 80:80 under wordpress works, I know that wordpress can in fact communicate with mysql. But moving it behind certman/nginx causes the database connection to break or something like that and I haven't been able to figure it out.
Does anyone know what might cause this and how to fix it or how to troubleshoot it further? Many thanks to everyone in advance.
