Impossible to load database (Session file Error) and too many connexions with mysqli driver with CI 3.0

[eluser]casa10[/eluser]

On CI 2.1.4 i haven't any problems to access to my web application. I'm testing with CI3.0 dev and i have some problems.

- impossible to connect to my databases : error Filename: libraries/Session/Session.php Line Number: 674
i use session when i identify an user and session config use database with the ci_sessions table.

My config :
$db['default'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'dev',
'password' => 'dev',
'database' => 'fuge',
'dbdriver' => 'mysql',
'dbprefix' => '',
'pconnect' => TRUE ,
'db_debug' => TRUE,
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'autoinit' => TRUE,
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);

$db['replique']['hostname'] = 'localhost';
$db['replique']['username'] = 'dev';
$db['replique']['password'] = 'dev';
$db['replique']['database'] = 'fugereplique';
$db['replique']['dbdriver'] = 'mysql';
$db['replique']['dbprefix'] = '';
$db['replique']['pconnect'] = TRUE;
$db['replique']['db_debug'] = TRUE;
$db['replique']['cache_on'] = FALSE;
$db['replique']['cachedir'] = '';
$db['replique']['char_set'] = 'utf8';
$db['replique']['dbcollat'] = 'utf8_general_ci';
$db['replique']['swap_pre'] = '';
$db['replique']['autoinit'] = TRUE;
$db['replique']['stricton'] = FALSE;
$db['replique']['save_queries'] = TRUE ;

- when i'm using mysqli driver instead mysql driver, i have too many connexions errors too (more than 150 connexions by user !!).
I was used mysql driver and my Mysql server shows on CI 2.1.4 between 2 and 4 connexions process by user. (option pconnect is on true) With Ci3.0 and mysql driver, i had between 5 and 7 connexions process by users. (Option pconnect is onTRUE.)

Could you explain me why i had this problems with CI3.0 whereas CI 2.1.4 i had not this problems ?
Thanks very much.

[eluser]ivantcholakov[/eluser]
1. What is the version of MySQL server?
2. Change
‘pconnect’ => TRUE ,
to
‘pconnect’ => FALSE ,
Close all the connections, even restart the machine. Then does this problem appear again?

Edit: Also you may test what would happen with ‘mysqli’ driver.
Edit 2: Try to see whether your case matches to this one: http://stackoverflow.com/questions/18422...ing-reused

[eluser]ivantcholakov[/eluser]
For
‘pconnect’ => TRUE ,

I think I know what causes the problem. Compare the sources of the file system/core/CodeIgniter.php. For CI 2.1.4 at the end of this file there is the following fragment:

Code:

/*
* ------------------------------------------------------
*  Close the DB connection if one exists
* ------------------------------------------------------
*/
if (class_exists('CI_DB') AND isset($CI->db))
{
  $CI->db->close();
}

For CI 3.0-dev this fragment is missing. This is it.

For persistent connections it is important the connection to be closed. The native MySQL connection in this case does not actually close, it gets "released" and available to be used again.
When $CI->db->close(); is not executed, the current persistent connection does not get "released" and on the next page request a new one is to be created. Usually an idle persistent connection lives 8 hours before it is automatically closed. So during this time you hit the maximum connection limit that usually is 150.

-----------------------------------------
Edit: The explanation may not be correct, it may be more complicated. Anyway, avoid persistent connection as a workaround.
-----------------------------------------

A workaround: Switch for now to ‘pconnect’ => FALSE ,
By the way: When in the future the newest Session library appears to 'develop' repository, check whether ‘pconnect’ => TRUE , is applicable at all.

About the permanent solution: The text will continue on GitHub.

[eluser]casa10[/eluser]
Hello, thanks for your help.
I will try to see on CI3.0 file where $CI->db->close() is missing.
For pconnect, i think to set it at FALSE is a bad thing for my application because when it is at FALSE, with only one user i can have 98 simultaneous connexions( with CI3.0 dev).
I had develop again my models with another way to use multiple databases and with persistent connexion (with mysql driver not mysqli because mysqli doesn't support persistance) i have only 2 or 3 simultaneous connexions instead of 98 without persistance connexion.
The wait_timeout can be set on a server and shut down to 3600 seconds for example.
For static sites, pconnect at false can be a solution but for an application with statistics and a lot of data to manage is different.

i will give you my results after new tests and development.
Thanks.

[eluser]ivantcholakov[/eluser]
I have just did a test. My explanation is wrong, the removed code is not the cause.

For now I only see the option of avoiding persistent connections.

[eluser]Narf[/eluser]
The whole point of persistent connections is to NOT close them.

[quote author="casa10" date="1405264999"] (with mysql driver not mysqli because mysqli doesn't support persistance)[/quote]

This is not true.

[eluser]casa10[/eluser]
i review my code for my module in my application when CI_3.0 will be released.
To avoid my problem, i discrease my level security allowing the same mysql user (login/password) to access to my mutliple databases, that is to say same database user can access to the different databases which i use.
Also, i had no problems with too many connexions when i use mysqli driver or mysql driver without persistent connexion. With persistent connexion, no problem too.
I can use mysql driver or mysqli without any problems with persistent connexion or no.

Thanks for your help.

[eluser]Narf[/eluser]
[quote author="casa10" date="1405326069"]i review my code for my module in my application when CI_3.0 will be released.
To avoid my problem, i discrease my level security allowing the same mysql user (login/password) to access to my mutliple databases, that is to say same database user can access to the different databases which i use.
Also, i had no problems with too many connexions when i use mysqli driver or mysql driver without persistent connexion. With persistent connexion, no problem too.
I can use mysql driver or mysqli without any problems with persistent connexion or no.

Thanks for your help.[/quote]

That is a clearer explanation, revealing why you're having that problem.

Persistent connections work like this: when you're trying to do a 'pconnect', MySQL will look for an existing connection that was initiated by the same username and password, and if there's a match - that connection will be reused.
As I previously mentioned, the whole point behind using persistent connections it that you don't close them and because you're initiating many connections with different usernames and/or passwords, you're not reusing the already established ones, or that happens very rarely. As a result, your max connections limit is quickly reached.

The solution is simple - don't use persistent connections, they aren't magically better. It just so happens that most applications would use a single mysql user and so would always reuse the same connection.