Exception SQLSTATE[HY000] [2002], SSL encryped database connections

[Lutz 29]

I'm testing DigitalOcean's database cluster service, MySQL server version is 8.0.17.

The tables were migrated from a MySQL 5.7 database, the database user is altered to support mysql_native_password (instead of caching_sha2_password).

In config.php, $config->dbOptions is set to an array with
\PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca-certificate.crt'.

I get the following error message when I try to access the website:

Error: Exception: SQLSTATE[HY000] [2002] Connection timed out (in /path/to/wire/core/ProcessWire.php line 460).

Does somebody use ProcessWire with such a configuration (MySQL 8, SSL) or has any idea how the problem could be solved?

[flydev 👊🏻 1,579]

Quote

Connection timed out

I think that your connection settings are wrong. You should triple check it. 

I don't know how the DigitalOcean cluster work, but I must assume that the host is different that localhost or 127.0.0.1. Also, are you sure you configured the right port number (if different in a cluster config) ?

The error message must indicate that something is misconfigured. In the case it was your credentials that was wrong, you will must get a Connection Refused error not a timeout. (not sure how to formulate this last sentence 😅)

 

[Lutz 29]

@flydev 👊🏻 Thank you very much. Of course, host an port number are different, but the settings are triple checked.

[elabx 664]

I've seen there are some issues with MySQL 8, be aware of that: 

https://github.com/processwire/processwire-issues/issues/973

[dragan 1,612]

19 hours ago, Lutz said:

I'm testing DigitalOcean's database cluster service

Did you try to install the same mySQL version elsewhere (e.g. locally) and see how it goes? You could at least narrow down the issue (mySQL, or some DO-specific settings). Did you try access the DB directly?

[Lutz 29]

Thanks @dragan and @elabx. I fixed the timeout error while testing with a non-PW script, will begin testing with ProcessWire soon.

[Lutz 29]

Tested with ProcessWire 3.0.149, no errors so far. However, it's terrible slow, especially admin, worst when using ListerPro. For example, there's a page with 5 children, the lister shows 5 results, execution time > 5,000 ms. If you open the config of that lister, execution time is > 8,900 ms. Go to Pages (pages tree), execution time > 2,000 ms (for just a few test pages).

Cluster insights shows load average 1-minute peaks above 3 (1 vCPU cluster), for just one user, a few clicks. 5-minute peaks are around 0.9.

CPU usage is ok./low (~ 7.5 %), memory usage ~ 80 % (1 GB RAM). Of course, this combination (PW + DO database cluster 1 vCPU/1 GB RAM) cannot be used at the moment. I try to continue researching.