Connecting to Managed DB on Digital Ocean: Connection Error

Product Support
1

Your software
My Mautic version is: 2.16.3
My PHP version is: 7.2

Your problem
My problem is:
I am trying to migrate a mautic from running its DB on local mysql to managed DB in digital ocean.

I have gone ahead and imported DB to the Managed DB, updated the local.php in config to reflect new host, port, username and password, however keep getting an error connection, and I notice that it is still using my old password when trying to authenticate.

From my instance I am able to access the managed DB from the command line with correct credentials.

These errors are showing in the log:
2020/09/17 10:28:31 [error] 7153#7153: *436 FastCGI sent in stderr: "PHP message: PDOException: SQLSTATE[HY000] [2002] Connection timed out in /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:43

Stack trace:

#0 /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(43): PDO->__construct(‘mysql:host=priv…’, ‘MYUSER’, ‘OLD_PASSWORD’, Array)

#1 /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOMySql/Driver.php(44): Doctrine\DBAL\Driver\PDOConnection->__construct(‘mysql:host=priv…’,‘MYUSER’, 'OLD_PASSWORD, Array)

#2 /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(360): Doctrine\DBAL\Driver\PDOMySql\Driver->connect(Array, ‘MYUSER’, 'OLD_PASSWORD, Array)

#3 /var/www/mautic/app/AppKernel.php(125): Doctrine\DBAL\Connection->connect()

#4 /var/www/mautic/app/middlewares/CORSMiddleware.php(93): AppKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)

#5 /var/www/mautic/app/middlewares/CatchExceptionMiddleware.php(45): Mautic\Middleware\CORSMiddleware->ha…

PHP message: Database connection error (code #0) - in file /var/www/mautic/app/AppKernel.php - at line 128" while reading response header from upstream, client: MY.IP.ADDRESS, server: MY.HOST, request: “GET / HTTP/1.1”, upstream: “fastcgi://unix:/run/php/php7.2-fpm.sock:”, host: “MY.HOST”

Steps I have tried to fix the problem:

2

small update, the old password was coming from /cache/prod
Went ahead and deleted that, but still unable to connect:

these are the logs
2020/09/17 10:43:09 [error] 7153#7153: *442 FastCGI sent in stderr: "PHP message: PDOException: SQLSTATE[HY000] [2002] Connection timed out in /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:43

Stack trace:

#0 /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(43): PDO->__construct(‘mysql:host=priv…’, ‘MY_USER’, ‘NEW_PASSWWORD’, Array)

#1 /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOMySql/Driver.php(44): Doctrine\DBAL\Driver\PDOConnection->__construct(‘mysql:host=priv…’, '‘MY_USER’, ‘NEW_PASSWWORD’, Array)

#2 /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(360): Doctrine\DBAL\Driver\PDOMySql\Driver->connect(Array, ‘MY_USER’, ‘NEW_PASSWWORD’, Array)

#3 /var/www/mautic/app/AppKernel.php(125): Doctrine\DBAL\Connection->connect()

#4 /var/www/mautic/app/middlewares/CORSMiddleware.php(93): AppKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, true)

#5 /var/www/mautic/app/middlewares/CatchExceptionMiddleware.php(45): Mautic\Middleware…

PHP message: Database connection error (code #0) - in file /var/www/mautic/app/AppKernel.php - at line 128" while reading response header from upstream, client: MY.IP, server: SERVER.NAME, request: “GET / HTTP/1.1”, upstream: “fastcgi://unix:/run/php/php7.2-fpm.sock:”, host: “SERVER.NAME”

3

Hi, did the db location change maybe?
Is the port open?
Im just asking because youexperienced a timeout.
Joey

4

Hey @joeyk

What do. you mean if the DB location changed ? The name is still the same, however I moved it from locally installed on MariaDB to the managed server, which has a different host name.

Port is open, I am able to connect to to the database on remote server from the ubuntu instance I am running.

The difference I can see is that the database is running mysql8.

I have the option of choosing mysqli or postgresql as well. But historically I have gone with MariaDB.

Mike

5

Ok, and the database info is also updated in your local.php, right? I mean the line ‘db_server_version’ ?

6

I never even thought about that!! It was not updated. Currently it is sitting as follows:
db_server_version’ => ‘5.5.5-10.1.44-MariaDB-0ubuntu0.18.04.1’,

This is the info I see if I do status; in mysql

mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper

Connection id: 44227
Current database: mysql
Current user: USR@IP
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ‘’
Using delimiter: ;
Server version: 8.0.20 Source distribution
Protocol version: 10
Connection: SERVER_ADDRESS via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 25060
Uptime: 1 day 21 hours 20 min 33 sec

7

Okay, it’s not like I know what to do, just pointing out possible solutions. :slight_smile:

8

so any idea what I should write in that line ?

db_server_version’ => ‘5.5.5-10.1.44-MariaDB-0ubuntu0.18.04.1’, ?

9

ok so finally got this to work together.

The issues were mysql 8 authentication methods were not playing nicely with the mysql installed on the server.

So what I had to do was run the following command: ALTER user ‘username’.‘IP’ IDENTIFIED with mysql_native_password BY ‘PASSWORD’;

also in the db_server_version I set it to ‘db_server_version’ => ‘8.0.20’

1 Like