MySQL: Troubleshooting

MySQL common issues and solutions

👋 Welcome to the Stackhero documentation!

Stackhero offers a ready-to-use MySQL cloud solution that provides a host of benefits, including:

  • Unlimited connections and transfers.
  • phpMyAdmin web UI included.
  • Effortless updates with just a click.
  • Optimal performance and robust security powered by a private and dedicated VM.

Save time and simplify your life: it only takes 5 minutes to try Stackhero's MySQL cloud hosting solution!

phpMyAdmin timeout

If you encounter timeouts while using phpMyAdmin, it might be due to handling a substantial amount of data. As phpMyAdmin operates on PHP, it can struggle with lengthy requests or large data exports. For managing more complex queries, you might consider using the MySQL command-line interface or a non-web-based client.

Resolving MySQL error: "Authentication plugin 'caching_sha2_password' cannot be loaded"

MySQL 8 defaults to using the caching_sha2_password authentication system. When working with older client libraries, you might encounter the error: "Authentication plugin 'caching_sha2_password' cannot be loaded". To resolve this, you can modify the default authentication method to use the MySQL native password.

In your Stackhero dashboard, navigate to the MySQL configuration section and select "MySQL native password" as the authentication plugin. This setting affects the root user and any newly created users.

For existing users (excluding the root user), you can execute the following commands using the MySQL command-line interface or phpMyAdmin:

USE mysql;
ALTER USER '<userToUpdate>'@'%' IDENTIFIED WITH mysql_native_password BY '<userPassword>';
FLUSH PRIVILEGES;

Resolving MySQL error: "SSL connection error: unknown error number"

If you encounter the error "ERROR 2026 (HY000): SSL connection error: unknown error number", it often indicates that your client does not support modern TLS versions. Stackhero's MySQL instances are configured to support only up-to-date TLS protocols and ciphers for enhanced security.

This error might occur if your client tries to use TLS v1.1, which is outdated and unsupported by modern MySQL and MariaDB versions. To address this issue, you may need to upgrade your MySQL client to a version that supports the latest TLS protocols.

Addressing root user deletion or permission changes

If you accidentally modify permissions, change the password, or delete the "root" user, you can restart your MySQL instance. Doing so will automatically recreate the root user with the correct password and permissions.

Reclaiming disk space after data deletion

When data is deleted from a MySQL table, the disk space it occupied is not immediately reclaimed. To recover this space, you can execute the following command:

OPTIMIZE TABLE <table>;

This process creates a temporary table, transfers the data to it, and then replaces the original table. During the optimisation process, the table will be locked and temporarily unavailable. The duration of this lock and the overall time required depend on the size of the table. Since this operation is resource-intensive, it is advisable to schedule it during periods of low server activity to minimise any impact on performance and availability.

Other articles for MySQL