How to Troubleshoot MySQL Database Connectivity Issues

Prerequisites

In order to connect to MySQL on DigitalOcean Managed Databases, you will need two things:

  • A MySQL client installed on your local computer. You can install either MySQL version 8 or greater, which lets you use the mysql command in a terminal, or MySQL Secure Shell, which lets you use the mysqlsh command in a terminal.

  • The database’s connection parameters. For both of the above tools you will use the Flags format, which supplies the variables as individual flags that are easier to read and customize.

To get the database’s connection parameters from your control panel, visit the Databases page, open the database’s More menu, then select Connection details and click Flags.

Databases Overview screen showing connection string

Before troubleshooting connection problems, check the DigitalOcean status page for ongoing issues in your database’s region.

Anatomy of the Connection String

The connection string passes different parameters to MySQL via Flags. Here’s a high-level breakdown of what information those flags correspond to:

  • Hostname and Port: The hostname is specified with the -h flag, and tells your client computer how to reach the cluster. Port is specified with the -P flag (notice it’s upper case). If you do not specify a port, the client will attempt to use the default MySQL port, 3306.

  • Username and Password: The username and password are specified with the -u flag for user and the -p flag for password. By default your database has only one accessible user, doadmin. If you leave the -p flag blank, you will be prompted for the user’s password.

  • Database: The database is specified with the -D flag (notice it’s upper case), this tells the MySQL client which database you want to access.

  • Other Flags: You can find a more comprehensive list of the flags you can pass to the MySQL command on the official documentation for MySQL at mysql Client Options.

Errors

Below are some common MySQL database connectivity errors and error codes along with likely causes and solutions. Learn more about specific error codes in the Server Error Message Reference.

Access Denied

Access denied errors can look different depending on the root cause.

For example, this error means that you’re using the wrong password:

ERROR 1045 (28000): Access denied for user 'sammy'@'203.0.113.0' (using password: YES)

This access denied error means the connection string didn’t contain the -p flag but a password is required:

ERROR 1045 (28000): Access denied for user 'sammy'@'203.0.113.0'' (using password: NO)

This access denied error means the user does not have privileges on the database specified in the connection string:

ERROR 1044 (42000): Access denied for user 'sammy'@'%' to database 'defaultdb'

To troubleshoot the issue, ensure you have the correct username, password, and database name in your connection string. You should also ensure the user has privileges on the database you want to connect to. Learn more about privileges in Privileges Provided by MySQL.

The following error message means you are attempting to connect to MySQL using an application or a version 5.x client that does not support the caching_sha2_password.

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

As a workaround, you can connect using MySQL 8.x and issue the following statement, which will change the password type the server will accept for the current user. Replace use_your_user with your MySQL username, and replace use_your_password with the user’s current password. After successfully executing the statement, you should be able to connect to MySQL with a 5.x client or an application that does not support caching_sha2_password:

  
    
ALTER USER use_your_user IDENTIFIED WITH mysql_native_password BY 'use_your_password';

  

This error means the client you’re using to connect does not support TLS/SSL:

ERROR 2026 (HY000): SSL connection error: unknown error number

It’s most common on MySQl/MariaDB version 5.7.x, which is the default version in the Ubuntu repository. To troubleshoot this issue, ensure you’re using the correct client version (8.x) to connect to the MySQL node. See How To Install the Latest MySQL on Ubuntu 18.04 for more information.

Unknown Host

ERROR 2005 (HY000): Unknown MySQL server host 'myqsl--do-user-example-0.db.ondigitalocean.com' (0)

This error means that you are using an incorrect hostname, or your local computer cannot resolve the hostname. To troubleshoot this error, ensure you have the correct hostname for the database server, and that your local computer has a connection to the Internet. You may also need to look into your local DNS configuration.

Lost Connection

ERROR 2013 (HY000) at line xx: Lost connection to MySQL server during query

This error means either network connectivity issues or the query being issued is taking too long to complete. If you get this error message often, check your network connection to ensure its stability. If the query is taking longer than 30 seconds to complete (the default value for the net_read_timeout variable), consider revising the query so it’s not sending as much data, or splitting the query into multiple queries.

Unknown Database

ERROR 1049 (42000): Unknown database 'default'

This error means that the database specified in the connection string does not exist. To troubleshoot, check your spelling, and ensure the database exists.

Host is Blocked

ERROR 1129 (HY000): Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

This error means that the MySQL server received too many interrupted connection requests from your location. The default value of the max_connect_errors system variable is 100, so if you get a lot of disconnects in a short period of time, this could happen. To troubleshoot this issue, make sure your queries are not timing out and that your network connection is stable.