0

Why am I getting too many connections error:

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

when switching from

$db['default']['dbdriver'] = 'mysql';

TO

$db['default']['dbdriver'] = 'mysqli';

in the db config file of codeIgniter? ?

This indicates that the mysqli driver doesn't close all connections correctly?

UPDATE:

Too many connections appeared when switching db driver didn't seem to be the correct reason (as indicated in the answer)

I've looked at my clients web hosting company and they have set max_user_connections to 40 (no, not impressive - but i basically just want to list of users with values using group_concat). 

$this->db->select('u.id AS user_id, u.first_name, 
u.housenr, u.address, u.phone, u.garage_nr, u.parking_nr, 
u.standing_correction, u.email, u.note, GROUP_CONCAT(wc.consumption) AS consumption, 
GROUP_CONCAT(wc.id) AS consumption_id, GROUP_CONCAT(wc.consumption_date) AS 
consumption_date, GROUP_CONCAT(wc.working_meetings) AS working_meetings, 
GROUP_CONCAT(wc.nrof_garage) AS nrof_garage, GROUP_CONCAT(wc.nrof_parking) AS nrof_parking, GROUP_CONCAT(wc.correction) AS correction, , 
GROUP_CONCAT(wc.correction_refers) AS correction_refers')->from('water_consumption wc');
$this->db->join('user u', 'u.id = wc.user_id');        
$this->db->join('role r', 'u.role_id = r.id');
$this->db->where('r.name', 'member'); //Only users with members-role        
$this->db->group_by('u.id'); 
$this->db->order_by('LENGTH(housenr), housenr, first_name'); //Natural sorting
$this->db->order_by('consumption_date', 'desc');

$q = $this->db->get();
if ($q->num_rows() > 0) {            
    $res = $q->result('Water_consumption');
    //more code...

This is really strange, but it seems that one connection is used for each returned object when using:

$res = $q->result('Water_consumption');

This is my assumption because about 40 objects are created and max_user_connections is set 40.

BUT when using

$res = $q->result();

it doesn't seem to use that much connections and the template is shown (incorrectly though because it calls function for each Water Consumption object).

This works on my local wamp without any issues and returning all objects (about 60) takes about 2-3 seconds.

bestprogrammerintheworld
  • 5,417
  • 7
  • 43
  • 72

2 Answers2

1

Basically MySQL does not care, if the application closes connections correctly or not. It has a system variable wait_timeout and any connection in which nothing happened for the specified amount of seconds will be automatically closed. (Default value seems to be 28800 seconds though, which should be far more than any web application needs. You might want to test lowering the value.)

Maybe you accidentally use persistent connections for MySQLi by specifying the hostname with a preceding p:? You can also trigger Codeigniter to use persistent connections by specifying an option. Maybe you did that without taking the consequences into account?
Using persistent connections can exceed your limits faster. I would recommend the recommendations in the accepted answer of the linked question: Do not use persistent connections by default.

Have you briefly estimated the used connections by your application? (How many php processes or web server processes can occur maximally at a time + cronjobs.) Maybe your max_connections limit has been too low all the time, but this did not manifest in error messages up to now. (For whatever reason...)

Community
  • 1
  • 1
Ulrich Thomas Gabor
  • 6,584
  • 4
  • 27
  • 41
  • I have thoughtfully read your answer(s) and tested different things. I'm using mysqli connection without persistant connections now when having about 40 rows in the water_consumptions table. See me updated question. – bestprogrammerintheworld May 21 '14 at 23:13
  • When I delete some rows in the water_consumptions table it works as expected. – bestprogrammerintheworld May 21 '14 at 23:25
  • Which version of PHP are you using? Have you tested your code with the development branch of CodeIgniter? Maybe there was another error in the database driver... if this error persists, you should open a bugreport at CodeIgniter. Maybe you are right. [The MySQL extension reused connections](http://php.net/function.mysql-connect) when calling `mysql_connect` multiple times. Whereas there is no notice left, that the [MySQLi extension does the same too](http://php.net/manual/en/mysqli.construct.php). `mysqli_connect` seems to open a new connection every time. – Ulrich Thomas Gabor May 22 '14 at 00:12
  • If connect is called multiple times can be easily checked, by adding a debug output to the connect method. Maybe you should try that. – Ulrich Thomas Gabor May 22 '14 at 00:13
  • I have verified that connect is called multiple times when using $this->db->result('object'); but when using $this->db->result(); It's the same result both when using mysql driver or when using mysqli driver. I tried with PDO as well and exactly the same issue. Locally I'm using PHP version 5.4.12 and on productions server it's PHP Version 5.5.7-1+sury.org~precise+1 – bestprogrammerintheworld May 22 '14 at 10:50
  • I've figured it out. I put an answer here. – bestprogrammerintheworld May 22 '14 at 11:27
1

Sometime the most obvious answers are staring at you...

I stated that

$res = $q->result('Water_consumption');

would make a connection each time.

BUT when using

$res = $q->result();

it didn't.

It turns out that each Water_consumption is extended from another class that makes a connection for handling login stuff. And that class is making a connection, so

$res = $q->result('Water_consumption');

that contained two objects would make two connections.

But the same would happen here..

$x = new Water_consumption;
$x = new Water_consumption;

This would also create two connections.

bestprogrammerintheworld
  • 5,417
  • 7
  • 43
  • 72