Databases play a critical role in the proper working of any web applications. They store, organize and manage a large amount of user data.

Any problem with database connectivity can cause website errors. And the error show up as “sqlstate hy000 error 2002.”

At Bobcares, we specialize in fixing database errors for our customers as part of our Server Management Services.

Today, we’ll see various reasons for sqlstate hy000 error 2002 and how our Database Engineers fix it.

 

Examining sqlstate 2002 error

Before we dive into the solution, let’s first check the sqlstate 2002 error in detail.

In general, when we configure any PHP application, we specify the details of the database in the configuration file. The same is applicable in case of popular content management systems like WordPress, Joomla, Magento, etc. And, when we update or change any setting in the website, it gets saved on the database. For this to work, there should not be any problem with database connection with MySQL host server.

The database connection can fail due to reasons like wrong database details in the configuration file, port restrictions on the server, missing files, lack of resources on the server and so on.

In simple terms, MySQL error 2002 denotes a problem of connection between the website code and the database.

There are many variants of the sqlstate hy000 2002 error. The details are readily available in the second part of the error message.

From our experience in managing servers, our Dedicated Engineers see variants of sqlstate hy000 error 2002 as

Connection failed: SQLSTATE[HY000] [2002] Connection refused

Connection failed: SQLSTATE[HY000] [2002] No such file or directory

SQLSTATE[HY000] [2002] Resource temporarily unavailable
exit status 3

Again, a similar error on a Magento website appear as :

 

What causes sqlstate hy000 error 2002?

Now, let’s have a look on the possible causes for sqlstate hy000 error 2002.

 

1.MySQL server not running

In many of the cases where websites report sqlstate 2002 error, the underlying reason would be stopped MySQL server. And, this can happen due to broken MySQL libraries, resource crunch on the server, etc. When MySQL is not running on the server, the website will not be able to connect to the database and its reports “Connection refused error.

 

2. Incorrect database settings

Similarly, a major reason for sqlstate hy000 2002 error is the incorrect database settings in the configuration file. For the proper working of the program, the file should contain correct database server name, database user details and password.

If the MySQL service is listening on 127.0.0.1, the same settings should be given in the database connection string. Moreover, the MySQL server should accept connections on this IP address too.

 

3. Insufficient server resources

Last and not the least, insufficient server resources also can be a reason for MySQL errors. When the server has too many MySQL queries, it requires too much server memory. Additionally, when websites use resource intensive queries, it add to the server load and lack of memory on the server. It ends up in errors like “SQLSTATE[HY000] [2002] Resource temporarily unavailable.”

 

How we fix sqlstate hy000 error 2002

Let’s take a look on how our Dedicated Engineers helped one of our customers to fix the sqlstate hy000 error 2002.

The customer was using a PHP tracking script on his website. There was a high volume of MySQL activity (about 4 inserts a second), from this tracking script, and this was causing the MySQL Connection time out error. And, on the website it resulted in error:

Exception 'PDOException' with message 'SQLSTATE[HY000] [2002] Connection timed out

It was a sign of PHP script timing out while connecting to MySQL host.

 

1. Ensure MySQL running status

Here, we first checked the status of MySQL server. It was running fine on the server as per the snippet below.

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2019-03-09 15:25:43 xx; 2 weeks 5 days ago

Therefore, we isolated the problem with MySQL server status.

 

2. Correcting Configuration file

As the second step, we checked and confirmed that the script was using correct Database details including Hostname, User and Password. Moreover, the website was showing data from the database too. Thus, the connection was happening. The error was happening only at certain intervals.

Additionally, we checked that MySQL database user was able to connect from the command line.

 

3. Fixing server resources

Since we already ruled out configuration errors, the next thing to check was the server resources. Here, our Dedicated Engineers checked the memory usage on the server. We found that MySQL memory usage was high in the server (around 89% of available memory).

PID  USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                             
30571 mysql     20   0 58.947g 0.055t   9832 S 135.1 89.2  16872:22 /usr/sbin/mysqld  

The memory usage statistics on the server looked as below.

We could see that there was a scope for MySQL tweaks in the server. Therefore, we analyzed the MySQL server performance by watching the MySQL queries and its impact on the server. Then our expert Database Engineers tweaked certain MySQL parameters such as join_buffer_size, table_open_cache and innodb_buffer_pool_instances.

After the tweaking, we restarted MySQL services at off-peak hours to minimize the impact on live websites. The performance on the MySQL server improved drastically and it fixed sqlstate hy000 error 2002 as well.

[Looking for tweaking MySQL server for improved performance? Our MySQL experts are available 24×7.]

 

Conclusion

In short, sqlstate hy000 error 2002 can happen due to stopped MySQL server, insufficient server resources, etc. Today we saw how our Dedicated Engineers troubleshooted and fixed MySQL errors for one of our customers.