When Im swtiching from mysql to mysqli driver I ge

问题:

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.

回答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...)

回答2:

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.