AWS Database Blog

Troubleshoot Amazon RDS for MySQL and Amazon RDS for MariaDB Errors

In this post, we discuss MySQL or MariaDB errors seen in error logs and application logs related to database, their possible root cause and how to effectively troubleshoot them on Amazon Relational Database Service(Amazon RDS) for MySQL or Amazon Relational Database Service(Amazon RDS) for MariaDB.

Running a database is a critical part of many applications, and if any errors and issues arises with MySQL or MariaDB which results in databases downtime, performance problems, or data inconsistencies then, error messages are quite informative in terms of resolving relative issues, they provide the cause of the errors.

Without proper troubleshooting, identifying and resolving these errors can be a daunting task, especially when working with complex database systems. Administrators and developers often struggle to pinpoint the root cause of the problem and implement effective solutions, which can result in prolonged downtime, data loss, or even complete system failures.

Note: In this post we use MySQL as the default engine, but everything we discuss can be applied to MariaDB as well.

Introduction

MySQL databases support four types of logging methods for monitoring:

  1. Error logs – it is used to record critical errors, warnings, and other important events related to the MySQL server’s operation. These logs are essential for troubleshooting and diagnosing issues within the database server. Error logs typically include information such as startup and shutdown events, server errors, access control issues, and other critical errors that may occur during the server’s operation.
  2. Audit logs – it is a powerful feature that can significantly enhance data security, compliance, and operational visibility within your database environment. By enabling and configuring audit logs, you can gain valuable insights into database activities, monitor user behavior, and maintain a comprehensive audit trail for auditing and forensic purposes.
  3. Slow query logs – it is used to record SQL statements that take a long time to Perform, exceeding a specified threshold. These logs are incredibly valuable for identifying and optimizing slow-running queries, which can significantly impact database performance. Slow query logs typically include information such as the query text, execution time, user, and other relevant details. By analyzing these logs, you can identify and optimize resource-intensive queries, improve application performance, and enhance the overall database efficiency.
  4. General logs – it is also known as general query logs or query logs, are used to record all queries and statements executed on the MySQL server. These logs capture a comprehensive record of database activities, including SQL statements, user connections, and other server-related events. General logs are primarily used for troubleshooting issues, and analyzing database usage patterns. However, enabling general logs can have a performance impact, especially in high-traffic environments, as every query is logged.

Error logs, terminal outputs, and application logs serve as valuable diagnostic tools, providing insights into the root causes of issues encountered. These logs capture detailed error messages, stack traces, and other relevant information that can aid in troubleshooting and identifying the underlying problems. By carefully analyzing the diagnostic messages recorded in these logs, developers and system administrators can gain a deeper understanding of the errors, exceptions, or anomalies that occurred, enabling them to pinpoint the source of the issue and take appropriate corrective actions.

Common errors and troubleshooting steps

The following lists the common errors that customers find on RDS for MySQL server along with their root causes and troubleshooting steps:

  1. ERROR 2003 (HY000): Can't connect to MySQL server on ‘< RDS instance endpoint>’

Cause: This error occurs if the application trying to connect to RDS for MySQL does not have proper network connectivity. You can see this error in application log or while connecting to RDS instance from terminal/command prompt.

Troubleshooting steps:

    • Check if RDS for MySQL or RDS for MariaDB attached security group allows IP of application server from which you are trying to connect.
    • Check if application IP is allowed in Network ACLs attached to subnet in which RDS for MySQL present and there is no explicit deny rule.
    • If the application server is hosted in another Amazon VPC then, please make sure that there is connectivity between both the VPC like you can enable VPC peering. After enabling VPC peering there will be appropriate route table, security group and NACL configuration.
    • If the database instance is hosted in private VPC and application server is running outside of AWS network then, you can use SSH tunneling for connecting to RDS instance for security best practices.
  1. ERROR 2005 (HY000): Unknown MySQL server host ‘<RDS instance endpoint>' OR Error: (2003, "Can't connect to MySQL server on ‘<RDS instance endpoint>’ ([Errno -2] Name or service not known)")

Cause: This error occurs when provided RDS instance endpoint/IP is incorrect.

Troubleshooting steps:

    • Check if provided RDS instance endpoint in your connection string is correct. You can verify the RDS instance endpoint from the RDS console.
    • You can run the “dig” command from the application server terminal or command prompt over RDS instance endpoint and if endpoint name is invalid then, you will see in the output “status: NXDOMAIN”. Example: 
      dig <RDS instance endpoint>

      Output will look like:

    • If the application server caches the IP of the underlying RDS instance and if there is a failover or an underlying host replacement the RDS instance IP address will change. You can clear the cache of application server and try to connect again to RDS instance.
      For MS Windows you can use the following command to clear the cache from command prompt: 

      ipconfig /flushdns

      For Linux server you can use the following command to clear the cache from terminal: 

      sudo systemctl status systemd-resolved
  1. ERROR 1045 (28000): Access denied for user ‘<user name>’@’<client server IP>’ (using password: YES)

Cause: This error occurs due to following reasons:

    • The provided username or password is incorrect.
    • The user is created with option “REQUIRE SSL” and you are trying to connect to RDS for MySQL or RDS for MariaDB server without using SSL based connection.
      The user attempting to login from a server has an IP/CIDR that is not allowed in the “host” column of user table of RDS for MySQL or RDS for MariaDB.

This error can observed in client application log, on command prompt or terminal, and in RDS for MySQL or RDS for MariaDB error logs

Troubleshooting steps:

    • Check if the username and password are correct. If you are trying to login with master user then you can reset the password from RDS console else you can reset the password of your user by logging with master user.
      It is not recommended to use the master user with the application.
    • Login to database and query the mysql.user table which contains all user’s metadata. You can login with another user and run the following query to check if the column ssl_type value is ANY, SPECIFIED, and X509. If this is true, then user can only login with SSL. 
      select * from mysql.user where user=<your username>;
    • You can review the column “host” in mysql.user table to see if your client server IP is allowed.
      You can see output like below when you run query on mysql.user table:

      mysql> select user,host from mysql.user;
+------------------+-------------+
| user             | host        |
+------------------+-------------+
| admin            | %           |
| test             | [IP_Address_Masked]|
| mysql.infoschema | localhost   |
| mysql.session    | localhost   |
| mysql.sys        | localhost   |
| rdsadmin         | localhost   |
+------------------+-------------+
  1. ERROR 2006 (HY000) at line <line number>: MySQL server has gone away

Cause: The following are common causes but not limited:

    • Someone killed your session by explicitly running the stored procedure mysql.rds_kill.
      Troubleshooting steps: If audit logs are enabled then you can check the respective log file if someone killed your session.
    • RDS for MySQL closed the connection from its side due to long inactivity.
      Troubleshooting steps: By default, MySQL server closes the connections after 8 hours of inactivity. Modify wait_timeout and interactive_timeout parameters in parameter group to higher value from existing.
    • If the query submitted to the database server is greater than max_allowed_packet size.

Troubleshooting steps: You can review the problematic query and divide the query in multiple parts. If it is not possible to do so, then you can increase the value for max_allowed_packet parameter.

    • Application is utilizing the same connection for RDS for MySQL or RDS for MariaDB when creating forked child processes.
      Troubleshooting steps: Create a separate connection with RDS for MySQL or RDS for MariaDB for each child process.
  1. ERROR 1040: Too many connections

Cause: You can see this error if the RDS for MySQL reaches to its maximum active connection limit.

Troubleshooting steps:

    • From an active connection to MySQL server run the following query: 
      show variables like "max_connections";

      The output shows the maximum number of active connections RDS for MySQL can handle.Run the following query to verify if limit exhausted:

      show full processlist;

      The output shows the number of active connections. You can kill the connections which are not required and also modify your application code to have connection closing function at the end of the processing logic.

    • In case if you are not able to login into RDS instance then review DatabaseConnections metrics for RDS instance active connections and compare with number of max_connections configured in parameter group attached to RDS instance.You can modify the RDS parameter value associated with RDS for MySQL or RDS for MariaDB instance at higher end for max_connections parameter from existing. Increasing value of max_connections parameter may result in memory contention.
      Note: This is a temporary solution which helps in identifying the issue related to increasing number of connections. If the higher number of connections is expected to persist then you can scale up the database instance to prevent the memory contention.
    • Review the application logic to determine why the application is creating too may connections. You can use close connection logic in application code or consider to use connection pooling.
  1. ERROR 1129 (HY000): Host '<RDS instance endpoint>' is blocked because of multiple connection errors; unblock with 'mysqladmin flush-hosts'

Cause: RDS for MySQL maintains a host cache which consist of client hostnames and IP addresses. MySQL server uses this information to avoid DNS lookup or new connections requests from the same client. In this cache, the server also maintains count of error for unsuccessful TCP/IP connections for each client. You can see this error if the count reaches more than max_connect_errors for a client.
The error in establishing the TCP/IP connection between client and server mainly occurs due to networking issue at client or server side.

Troubleshooting steps:

    • [Recommended] Check the network connection to make sure that there are no TCP/IP connectivity issues between the client and server.
    • You can modify the RDS parameter value associated with RDS for MySQL or RDS for MariaDB instance at higher end for “max_connect_errors”
      parameter from existing.
      However, this is a temporary solution and if count reaches again to current value, then you will again see this error.
    • Use an existing DB connection and flush the host cache by running the following command: 
      FLUSH HOSTS;

      Or, as an alternative, you can connect to MySQL server from client machine and flush the host cache:

      mysqladmin -h <RDS instance endpoint> -P <RDS instance port number> -u <username> -p flush-hosts

Note: The FLUSH HOSTS statement has been deprecated in MySQL 8.0.23 therefore, it is recommended to truncate the performance_schema host_cache table for MySQL version greater than 8.0.23.

Conclusion

In this post we discussed common errors which you can see when working with RDS for MySQL and RDS for MariaDB, common causes (but not limited) and how to troubleshoot them. Share your feedback in comment section.

About Authors

Ankur Bhanawat is a Consultant with the Professional Services team at AWS based out of Pune, India. He’s an AWS certified professional in five areas and specialized in databases and analytics technologies. He has experience in designing, migrating, deploying, and optimizing workloads on the AWS Cloud.

Shyam Sunder Rakhecha is a Lead Consultant with the Professional Services team at AWS based out of Hyderabad, India and specialized in database migrations and modernization. He is helping customers in migration and optimization in AWS cloud. He is curious to explore emerging technology in the database domain. He is fascinated with RDBMS and Big Data. He also loves to organize team building events and regalement in team.