Persistant connections not working with mysqli driver in codeIgniter

Question

I have this db-configuration on my local development environment

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = ''; //Actual username is put inside these quotes
$db['default']['password'] = '';
$db['default']['database'] = ''; //Actual name of database is put inside quotes
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = APPPATH .'cache';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

When I transfer to this to a production server it doesn't work so I've tried a lot of things, but one thing that seemed to work was to change the dbdriver to mysqli instead of mysql. But I also hade to put db_debug to FALSE (so it "worked" wouldn't be the correct statement)

I have read about this a lot, but I haven't found an answer to this anywhere. (I'm not satisfied with: "Change to debug = false and it would work")

I wanted to see what the actual problem was so I changed the local server to mysqli driver as well and then I got the error:

A Database Error Occurred

Unable to connect to your database server using the provided settings.

Filename: C:\Program Files\wamp\www\mellomgarden2\system\database\DB_driver.php

Line Number: 124

After some digging I see that db_connect() and db_pconnect() are working in the exact same manner:

1. If you look in the system/database/drivers/mysqli/mysqli_driver.php - it seems like connect() and pconnect() are working exactly the same way because pconnect() is just calling connect() function.

so $db['default']['pconnect'] = TRUE; is totally useless when using mysqli driver.

function db_connect()
{
    if ($this->port != '')
    {
        return @mysqli_connect($this->hostname, $this->username, $this->password, $this->database, $this->port);
    }
    else
    {
        return @mysqli_connect($this->hostname, $this->username, $this->password, $this->database);
    }

}
// --------------------------------------------------------------------

/**
 * Persistent database connection
 *
 * @access  private called by the base class
 * @return  resource
 */
function db_pconnect()
{
    return $this->db_connect();
}

Taking a closer look at db_connect() and db_pconnect() above - erorrs are suppresed. I removed the @ for the return value and then got this:

Severity: Warning

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

Filename: mysqli/mysqli_driver.php

Line Number: 76

which is a FAR more explainatory error

so my thought is that db_pconnect for mysqli driver should look something like this:

function db_pconnect()
    {       
                 $this->hostname = 'p:' . ltrim($this->hostname, 'p:');                                  
                 return $this->db_connect();
    }

Is this a total miss from CodeIgniter development team or am I missing something?

Answer 1

Referring to your other thread my assumption that you specified to use persistent connections seems to be true, but taking a look at the source code of CodeIgniter we can come to a different conclusion: The MySQL driver actually notices this option begin set. Whereas the MySQLi driver, as you correctly analyzed, does not. This has been also remarked in a bug report and already been fixed.

As you can see the whole MySQLi driver class was rewritten in the development branch.

Therefore you have used persistent connections until now, but (unwillingly) stopped using them when switching to MySQLi, because this fix has not been released... you could try to use the development branch, if this is an option. (Or only replace this single file ... should work too.)

Your other option is, like I stated in my other answer, that you briefly estimate the used connections by your application and resize the max_connections limit according to your real needs.