Lightsale instances cannot connect to Lightsail database

Question

I have a web app running on Lightsail instances stacked with LAMP (PHP 7/MariaDB 10 service). When I want to connect the instances to a Lightsail database instead of localhost, it works sporadically. The front end reads "cannot connect to MySQL database" and in the database server log I read : `[MY-010055] [Server] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known" where the IP is the private IP address of my instance.`

My instances and database are in the same region. Even though it is not necessary I have tried enabling VPC peering and opening the db to the public.

**I can connect to the database using the command line tool**: mysql -u uuuu -p -h hhhhh (ie: it is not a problem with entering the wrong credentials)

**The web app seems to work with the dedicated Lightsail db server when I am the only one using it**  and until I open it to more traffic, then I get the connection error.

I have increased the number of max_connections from 65 to 500 using the aws CLI (localhost is 150). My current traffic is 2000 visitors per day.

```
aws lightsail update-relational-database-parameters --relational-database-name Database-1 --parameters "parameterName=max_connections,parameterValue=150,applyMethod=immediate"
```

Localhost is Mariadb 10, dedicated database server is MySQL 8

SSL is handled by Cloudflare. There's no SSL on my instance.

I don't want to keep using localhost dbs, I want to centralize my data in a dedicated database.

Does anyone know how to handle this problem?

Answer

Hi Jeff,
     
In the event that this is still a pending issue, I suspect your error may be related to the MySQL server system variable "skip_name_resolve". With "skip_name_resolve" disabled, any temporary DNS issues can lead to sporadic connection errors like you are seeing.
     
There also appears to be additional security benefits to enabling this server variable. The following links address both why this should resolve your issue and the security benefits of it (Note: I did not write this content and I don't guarantee that it is AWS or Lightsail specific).
     
https://serverfault.com/questions/393862/mysql-warning-ip-address-could-not-be-resolved  
https://www.percona.com/blog/2008/05/31/dns-achilles-heel-mysql-installation/  
https://nixcp.com/skip-name-resolve/
     
I see you're already familiar with modifying Lightsail database parameters, but here is the documentation for doing so if needed.  
https://lightsail.aws.amazon.com/ls/docs/en_us/articles/amazon-lightsail-updating-database-parameters
     
Regards,  
Eric B

Lightsale instances cannot connect to Lightsail database

Relevant content