Max_user_connections Limit Exceeded

I get this error on my website:

max_user_connections limit exceeded

Sometimes the site loads, sometimes I get this error.

I changed the max_user_connections from 128 to 1024 in my php.ini file to avoid the error.

However, I don't understand why I get that error to start with. What counts as a connection to mysql? Is it everytime you call the mysql_connect() function, or is it every time you send a query to mysql?

Thanks in advance

 

Everytime you call mysql_connect, that is a connection. Are you running mysql_close as needed?

The configuration of max_connections is for overall connections, max_user_connections should be connections by individual users. It may be best to modify both.

Alternatively, you can alter the max connections via php:
<?php
ini_set('max_user_connections', 200);
ini_set('max_connections', 200);
?>

Hope that helps!

 

They are not php settings, they are mysql settings. As such you cannot effect them using ini_set.

Every time you call mysql_connect, that counts as a connection. When the script finishes executing, all connections will be cleaned up automatically. In general, your code should not create more than one connection per request, this connection should be shared among all of your code. When you are finished with the connection, as mentioned you should close it. It's worth mentioning that PHP should clean up these connections for you, when it destroys the resource it used to represent them, but there are so many versions of mysql and database abstraction included in PHP now that you should do what is correct and close them explicitly. There can obviously be a difference between cleanup and explicit close with some drivers/protocols. While PHP will cleanup and free the resource to avoid memory leaks, it may not ( or it may ) send the final packet to the server telling it you are done. As I said, there's so many possibilities, you should do what is correct.

1024 connections is quite a lot, can your mysql instance really handle that many concurrent connections ?? Raising the connection limits in this way is rarely the answer, mysql is a very fast protocol, designed to make and break connections with as little overhead as possible.

You might try adjusting the wait_timeout setting for mysql, it can often be the case that idle connections mount up and take up all the legitimate slots, either because of code that didn't mysql_close the connection or because of errors in the queries/protocol.

 

This problem is usually solved with simply replacing:

mysql_connect to -> mysql_pconnect

That means that php will NOT open new connection every time, it will rater use existing (p = permanent)

This is solution for 99% of situations that you described(Max_user_connections Limit Exceeded)

 

This kind of issue also frequently arises on shared hosts, who will limit mysql resources at the server level in ways that you can't override. So if you're on a shared host, start by taking this as an indication that your site is becoming busy enough to consider taking it to a dedicated server or virtual dedicated server.

On a shared host I know of, users were able to mitigate this issue by creating three separate mysql users for their database and then changing/rotating the username for each new mysql connection in PHP.

 

i had this issue before and was due to shared host. had to upgrade my hosting package

 

I've seen the exact opposite effect in some cases. Persistent connect was used, but the database connections were not closed explicitly. The server had a fair amount of load so the connections were staying open until the timeout limit was hit, and the server quickly ran out of available connections. Be aware that you MUST properly close the database connection if you use pconnect or you'll just create new problems for yourself.

 

Not its by PHP, you need edit the file on the server located in /etc/cnf.conf you can remplace your code by this,

[client]
port                = 3306
socket                = /var/lib/mysql/mysql.sock
 
 
[mysqld]
port                = 3306
socket                = /var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
skip-locking
#skip-innodb
max_connections=1000
max_user_connections=1000
max_connect_errors=20
connect_timeout=10
max_allowed_packet=8M
key_buffer = 512M
max_allowed_packet = 128M
table_cache = 32768
#max_open_files=65535
open_files_limit = 65535
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size  = 8M
myisam_sort_buffer_size = 128M
thread_cache_size = 128
query_cache_size = 256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
log_slow_queries=/var/log/mysql/slow_queries.log
log-error=/var/log/mysql/error.log
long_query_time = 10
log-long-format
expire_logs_days = 1
concurrent_insert = 2
local-infile=0
 
[mysqldump]
quick
max_allowed_packet = 128M
 
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
 
[isamchk]
key_buffer = 512M
sort_buffer_size = 8M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer = 512M
sort_buffer_size = 8M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout

Code (markup):