mysqli too many connections (HY000/1040) and (08004/1040) is there a difference?

Question

I'm getting the following errors in my script:

mysqli_connect(): (08004/1040): Too many connections 
mysqli_connect(): (HY000/1040): Too many connections

What is the difference and how can I solve this problem?

" how can i solve this problem" - find out why they multiply faster than they die? — John Dvorak, Jan 18 '14 at 10:06
You can edit value of flag config pconnect in this topic. http://stackoverflow.com/a/42992918/7760664 — Sinh Bui Dinh, Mar 24 '17 at 06:38

score 14 · Accepted Answer · answered Jan 18 '14 at 10:39

14

"Too many connections" indicates, that your script is opening at least more than one connection to the database. Basically, to one server, only one connection is needed. Getting this error is either a misconfiguration of the server (which I assume isn't the case because max connections = zero isn't an option) or some programming errors in your script.

Check for re-openings of your database connections (mysqli_connect). There should only be one per script (!) and usually you should take care of reusing open connections OR close them properly after script execution (mysqli_close)

answered Jan 18 '14 at 10:39

Axel Amthor

10,980
1
25
44

I read that mysqli auto closes the connections. In my application I am connecting the db on every ajax call and using json to return data. is the connection closed after the JSON response ? – sqlchild May 08 '18 at 06:34
@sqlchild No, as long as you do not close it, it will linger open depending on settings of `wait_timeout` and `interactive_timeout` in your my.conf. See manual, but best practice is to close unused connections and release resources. – Axel Amthor May 09 '18 at 08:50
"your script is opening at least more than one connection" Not really. The script could open just one connection and being called multiple times while the connection is still open. – user34814 Jan 12 '19 at 17:09
1

"as long as you do not close it, it will linger open" According to PHP doc: "Open non-persistent MySQL connections and result sets are automatically destroyed when a PHP script finishes its execution ... explicitly closing open connections and freeing result sets is optional" – user34814 Jan 12 '19 at 17:12
@sqlchild **YES** it is closed as this is how PHP works – Your Common Sense Mar 20 '20 at 09:12

score 6 · Answer 2 · edited Jun 06 '19 at 10:08

6

Steps to resolve that issue:

Check MySQL connection limit in configuration file or my.cnf

Run below command to check:

mysql -e "show variables like '%connection%';"

You will see like this:
```
max_connections = 500
```
Increase it as per you want:
```
max_connections = 50000
```
Restart the MySQL service:
```
$ service MySQL restart
```

Now check your website, I hope the error will not occur!

Thank You!

edited Jun 06 '19 at 10:08

Cà phê đen

1,883
2
21
20

answered Jun 06 '19 at 08:30

Everdata technologies

69
1
2

Alternatively you can SSH into your server and executy `mysql`. Afterwards: `SET GLOBAL max_connections = 50000;`. However, I expect the setting to be lost after a server restart. – Avatar Apr 27 '23 at 09:49

John · Answer 3 · 2020-05-20T02:34:31.933

While I could not tell you the difference between the 2 error numbers above, I can tell you what causes this.

Your MySQL database only allows so many connections at the same time. If you connect to MySQL via PHP, then you generally open a new connection every time a page on your site loads. So if you've got too much traffic to your site this can cause this issue.

I think it is pretty common for people to have one connection to their database per page load, and multiple queries for sure. So really what it comes down to are 3 points:

(Let me just tell you now, persistent connections will not solve your issue.)

If you have access to your server's CLI/SSH, try to increase the limit by modifying your MySQL configuration (don't forget to restart the service for changes to take affect). This will of course consume more system resources on your database server.

If you have a lot of AJAX requests or other internal database connections you should try to get these down to a single script with a single call. Your site may make multiple AJAX calls to various PHP files that pulls MySQL data, which uses a whole database connection for each one. Instead, create a single PHP file to collect all the data you need on a given page, this script can get all the data you need while only using 1 database connection.

I read that mysqli auto closes the connections. In my application I am connecting the db on every ajax call and using json to return data. is the connection closed after the JSON response is sent to the user or does it needs to be closed ? — sqlchild, May 08 '18 at 06:35
I have read the same, but my experience has not agreed with it. I specifically call the mysqli close function at the end of my scripts to be sure. — John, May 20 '20 at 02:36

score 1 · Answer 4 · answered Nov 11 '19 at 20:30

1

As far as the difference between the two, I believe that HY000 is a PDO exception where 08004 is actually coming from MySQL. Error 1040 is the code for "Too Many Connections".

answered Nov 11 '19 at 20:30

fylzero

460
6
18

score 1 · Answer 5 · answered Mar 20 '20 at 06:16

1

You should also check if your disk is full, this can cause the same error:

df -h

will show you the remaining space on each partition, you probably have to check the root partition / (or /var/ in case you have an extra partition for this):

df -h /

answered Mar 20 '20 at 06:16

rubo77

19,527
31
134
226

mysqli too many connections (HY000/1040) and (08004/1040) is there a difference?

5 Answers5