2

Suddenly my project starts saying there's too many connections to the database.

"my_mysql_user already has more than 'max_user_connections' active connections"

This always worked fine and I'm using multidatabase and the settings are

$config['hostname'] = 'localhost';
$config['username'] = 'my_mysql_user';
$config['password'] = 'my_mysql_password';
$config['database'] = ($db == null ? $this->CI->session->company['db'] : $db);
$config['dbdriver'] = 'mysqli';
$config['dbprefix'] = '';
$config['pconnect'] = FALSE;
$config['db_debug'] = FALSE;
$config['char_set'] = 'utf8';
$config['dbcollat'] = 'utf8_general_ci';

And I need to load like 20/30 models..and I always load them in the __construct() of the controller.

Even thought it always worked, I thought I might change it and load the models only in the functions that are needed, and the problem persists.

By executing SHOW PROCESSLIST it returns nothing, there's no query being done.

What can I do to track the problem? It seems the connections are not closed!

Linesofcode
  • 5,327
  • 13
  • 62
  • 116
  • see https://dba.stackexchange.com/questions/47131/how-to-get-rid-of-maximum-user-connections-error and https://stackoverflow.com/questions/4079531/mysqli-error-user-already-has-more-than-max-user-connections-active-connectio hope these links help to figure it out – Vickel Feb 24 '18 at 21:33
  • Here's the only thing that worked in our case: https://stackoverflow.com/questions/53226015/error-too-many-connections-on-codeigniter-website – Pedro Araujo Jorge Nov 09 '18 at 17:26

1 Answers1

0

Turn off persistent connections.

$db['default']['pconnect'] = FALSE;

If you want to prevent them from MySQL, set in my.cnf:

mysql.allow_persistent=Off
Randy Casburn
  • 13,840
  • 1
  • 16
  • 31
  • Hey, thanks. I sent a ticket to my hosting asking for that. I'll see if that solves the problem. – Linesofcode Feb 24 '18 at 21:33
  • @Linesofcode this normally may not be set for a shared server. you would need a VPS or dedicated server for global mysql server changes to be made – Vickel Feb 24 '18 at 21:36
  • @Vickel I printed the phpinfo() and it is indeed the variable set to true `mysqli.allow_persistent=on` and I asked them to change it. What it bothers me is the fact it was suddenly that this start becoming a problem..and why? – Linesofcode Feb 24 '18 at 21:38
  • @Linesofcode maybe they upgraded mysql/apache/php version, they do that all the time and normally never advise (shared server) – Vickel Feb 24 '18 at 21:40
  • What has changed recently? Something has. You must consider the entire infrastructure involved with this from end-to-end. What has changed? – Randy Casburn Feb 24 '18 at 21:44
  • Additionally, the `my.cnf` change is not necessary if you turn off persistent connection in CI. That is only a nice to have anyway. – Randy Casburn Feb 24 '18 at 21:44
  • @RandyCasburn I didn't change a thing in the last month !! The only reason I can see it's my hosting fault !! – Linesofcode Feb 24 '18 at 21:55
  • @RandyCasburn and btw, it works perfectly on my local environment !! – Linesofcode Feb 24 '18 at 22:16
  • @Linesofcode check also your localhost environment (mysql, php, CI version) versus your production environment, if you see them upgrading, you at least can track it – Vickel Feb 24 '18 at 22:27
  • @Vickel everything is OK in terms of versions, both CI and PHP versions match, besides I have like 30 different pages and everything works fine, only one has problem. The `Show processlist` does not return any query being done..fml !! – Linesofcode Feb 24 '18 at 22:34
  • It sure does sound like you've isolated it to your ISP. if all versions are same, absolutely nothing has changed that you know of, all versions of all software(s) match, and it works locally but not in deployed environment, the only thing that could have changed, given all these assertions, is at your ISP. Good luck. – Randy Casburn Feb 24 '18 at 22:46
  • @RandyCasburn well, my hosting rebooted the MySQL, increased the user limitation and everything started working. I need to understand what lead to the error, but its complicated. Thanks anyway. – Linesofcode Feb 25 '18 at 02:29
  • Yeah - looks like maybe you've been lucky up until now. Some investigation is necessary. If you enjoyed my help, please accept my answer. Thanks! – Randy Casburn Feb 25 '18 at 02:33
  • tried it but not sure if it works , i will monitor first – zero8 Feb 18 '19 at 09:36