What are user connections - when are the created and destroyed?

Question

We've been getting an intermittent error on out site:

Warning: mysql_connect() [function.mysql-connect]: User '557574_prod' has exceeded the 'max_user_connections' resource (current value: 10)

Which prevents the site from being accesses & effectively it is down.

(obviously increasing the 'max_user_connections' would fix it but that is not an option through our hosting scheme)

I may have 'fixed' it, but I am not 100% sure I understand the issue & I would like to be clear. I thought the problem must be cause by too many cron jobs using up the connections (we have a couple running every minute), and I cut down the number of cron jobs & that seemed to lessen the amount of errors. I have created a separate user account for the cron jobs, so if that was the problem it should be solved now.

However, our logging system does not record the error when it occurs, so I am still concerned.

So I am asking: what constitutes a 'user_connection' in MySQL?

• is there a separate connection for each DB call?
• if not, when would DB calls require a separate connection?
• why would this error only occur on the live production site? perhaps there is a separate connection for each unique ip or simialr?

I feel I am over-thinking it . . .

Answer 1

Please notice the makeup of mysql.user:

mysql> show create table mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

Notice the column max_user_connections. It puts a moratorium on the number of connections a user may make per hour. Check and make sure the user '557574_prod' isn't connecting to mysql a ton of times within less than an hour.

This is used in conjunction with the option max_user_connections that you can set in /etc/my.cnf

Please change either max_user_connections in /etc/my.cnf or, if the individual user has this column set, that column mysql.user.max_user_connections. If you have no config control over my.cnf and you cannot do SQL against the mysql schema, then you beg and implore your hosting company to raise those limits for you.

I believe you can set the max_user_connections as a session variable, but you may need certain privileges, probably SUPER.

As for establish connections, you need to keep those connections open. Perhaps sending a heartbeat of some kind by doing a SELECT. But guess what ??? The db columns max_questions monitor SELECT queries per hour and max_updates monitors INSERTs, UPDATEs, DELETEs per hour. YOu may need to check these columns in mysql.user. You can quickly check those number with:

SHOW GRANTS;

I am not sure if the the user's per-hour connection limits are per thread or per user. You could answer that quickly by connecting to the DB aggressively using 2 or 3 DB connecitons at the same time and the error message should manifest itself. It is up you how to count the number of connections, perhaps "SHOW STATUS LIKE 'Connections';" It should reach either the max limits of the connection within the one connections or it could be the sum of all connections' connection status value.

Please also check the wait_timeout and interactive_timeout values. The default is 28800 in a standalone server. They may be set already. You cannot set them within a current connection. You must be able to set them as follows: (for new conenctions coming)

SET GLOBAL interactive_timeout = 86400;
SET GLOBAL wait_timeout = 86400;

If you cannot run even these commands, my condolences !!! :(