How CodeIgniter close db connections?

I'm wondering how CodeIgniter close db connections. In the doc, it says "CodeIgniter intelligently takes care of closing your database connections". But how does CodeIgniter do this? Seems I didn't find the db driver's "close" method get called in CodeIgniter 3.0.3 source code.

I've also posted in StackOverflow: http://stackoverflow.com/questions/43922...onnections

Thanks!

There is a db->close() method. In a PHP environment that is not using persistent connections there is no real need to call it. For MySQL in particular, all open non-persistent MySQL connections and result sets are automatically destroyed when a PHP script finishes its execution. Which in CodeIgniter happens right after the controller method finishes. So you could call $this->db->close(); in some model or in a controller. But all you would accomplish is freeing up the connection resources a few microseconds earlier than if you let script execution finish.

If you have a high-demand situation and are therefore using persistent connections that is a different story. http://php.net/manual/en/features.persis...ctions.php

There is a db->close() method. In a PHP environment that is not using persistent connections there is no real need to call it. For MySQL in particular, all open non-persistent MySQL connections and result sets are automatically destroyed when a PHP script finishes its execution. Which in CodeIgniter happens right after the controller method finishes. So you could call $this->db->close(); in some model or in a controller. But all you would accomplish is freeing up the connection resources a few microseconds earlier than if you let script execution finish.

If you have a high-demand situation and are therefore using persistent connections that is a different story. http://php.net/manual/en/features.persis...ctions.php

My application structure: 

• CI 3.1.4 with HMVC - All controllers/models/views are in modules
  
• "pconnect" is set to FALSE
  
• "mysql.allow_persistent = Off" is added to php.ini
  
• Module A is called for loading a page (an extensive form)
  
• Module A then calls Modules B to M for loading various (reusable) sections of the page (the form)
  
• Modules B to M may initiate connections to databases if needed (queries are not overly complex, though have a few cross-database joins)
  

Working perfectly on localhost. Gives following error on live server (shared hosting).

Message:  mysqli::real_connect(): (42000/1203): User <user> already has more than 'max_user_connections' active connections
Filename:mysqli/mysqli_driver.php
Line Number: 201

Now, will CI close database connections after finishing execution of Module A script or after finishing individual execution of Modules B to M scripts (which are actually creating database connections). Logically (and "intelligently"), it should be the latter.

The connections in cross-database joins within CI are not clear. How are they opened, managed, and closed? Can those connections cause "max_user_connections" error in MySQL on live server where allowed limit is 10-15?

Any help is appreciated. Thanks.

CI establishes the database connection when the database is loaded either through inclusion in autoload.php or by calling

PHP Code:

$this->load->database(); 


If that line is called more than once nothing will change. Except under special circumstances the database class is only loaded once establishing one connection. That connection will continue to be used throughout the execution of the current instance of CI. It does not matter how many models are loaded or how many database operations are performed the same connection will be used. As mentioned earlier, all open connections and result sets are automatically destroyed (closed) when a PHP script finishes executing.

Additional connections can be created, but that requires providing optional parameters to load->database(). See the documentation for details. This use is the "special circumstances" I mentioned above. I assume you are using this feature to make cross-database queries.

Your 'max_user_connections' troubles might be related to making calls to load->database() with the same parameters. In other words, if you are doing something like this within a model to use more than one db.

PHP Code:

$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE); 


If you make the same calls again in a different model (or module?) that might be the source of your problem.

When the model is done with its job you should probably call

PHP Code:

$DB1->close();
$DB2->close(); 


to assure the connections are closed.

Maybe making the connections ($DB1, $DB2) properties of the controller so they can be utilized in any model or library loaded would be helpful. I have no knowledge on how using HMVC affects the usefulness of this idea.

Thank you dave friend for the reply. I'll look at your proposed solution (since it's a large application and I'll have to really dig deep to implement this correctly). Meanwhile, can you please give some feedback on the following.

• There are three database groups.
  
• Database library is not autoloaded. (Although either case is resulting in same problem).
  
• Only one DB object created through load->database('group', TRUE) call per model.
  
• $DB1, $DB2 structure not implemented.
  
• Cross-database joins are made through this example code
  

PHP Code:

$this->db->select()
         ->from()
         ->join(db2Name.tblName) 


• Multiple models are loaded for loading the page in question.
  
• Each model is, thus, making a load->database('group') call, in some cases with the same parameters as other model(s).
  

Based on this structure, and your suggestion,

Your 'max_user_connections' troubles might be related to making calls to load->database() with the same parameters.

do you think, 
(a) the multiple load->database() calls with same parameters even for single $DB (group) object can cause the 'max_user_connections' problem?
(b) the method used for cross-database joins can cause the 'max_user_connections' problem? (CI's handling of connections made through this method, in presence of groups, is also unclear).

Logically, I'd think that since multiple databases are supported in CI through groups, and each group can only be accessed through load->database('group'):

• After autoloading 'database' library, load->database('group') calls should only be accessing particular database group and should not be initiating additional connections.
  
• If CI believes it necessary to establish new connection at each load->database('group') call, then each connection thus established in a model should be closed automatically at the end of execution of that model. 
  

What do you think? Thank you for your time.

do you think, 
(a) the multiple load->database() calls with same parameters even for single $DB (group) object can cause the 'max_user_connections' problem?

I assume that you literally mean

PHP Code:

$this->load->database('group'); 


and not

PHP Code:

$this->load->database('group', TRUE); 


If you do mean it the second way then you probably already understand what I'm about to say and I apologize. I don't mean to talk down to you.

If the load uses the second argument e.g. $this->load->database('group', TRUE); then yes, I think it  can cause the 'max_user_connections' problem.
When the second argument is TRUE the loader returns a CI_DB instance (a.k.a. "connection", a.k.a. "resource") whether the 'group' has been loaded previously or not. Once opened the connection will remain open until the framework finishes executing. In other words, until the controller returns and the CI shutdown routines finish.

(b) the method used for cross-database joins can cause the 'max_user_connections' problem? (CI's handling of connections made through this method, in presence of groups, is also unclear).

No, I don't think that is related. Making queries requires an open connection but does not create a connection.

Logically, I'd think that since multiple databases are supported in CI through groups, and each group can only be accessed through load->database('group'):

Once a call to $this->load->database('group') is made (no second argument) any additional calls (with no second argument) are ignored. The loader returns FALSE and nothing changes. In other words, the CI superobject $db is the same resource that was established earlier and is accessed via $this->db.  The first argument - the group name - has no effect on this behavior. Only by passing TRUE to the second argument will an additional connection be created.

• After autoloading 'database' library, load->database('group') calls should only be accessing particular database group and should not be initiating additional connections.
  

That is true. But after the autoload any calls to $this->load->database('group'); will do nothing without setting the second argument to TRUE. If you do that, you have to capture the return from the loader in order to use that resource.

• If CI believes it necessary to establish new connection at each load->database('group') call, then each connection thus established in a model should be closed automatically at the end of execution of that model. 
  

If in a model in order to connect to and use a second database you do this.

PHP Code:

public function get_something()
{
   $model_db = $this->load->database('othergroup', TRUE); 
   return $model_db->query("your query")->result();
} 


It is logical to assume that since $model_db is going out of scope when the method returns that the resource will be destroyed. But it is not!
This is because of the way the MySQL handles resources in PHP. To restate what I said before in a slightly different way: Once created MySQL resources will remain valid until the framework finishes executing.

However, you would be hard pressed to reuse the resource since you have lost the reference to it.

In order to free the resources opened in the get_something() method you have to do this

PHP Code:

public function get_something()
{
   $model_db = $this->load->database('othergroup', TRUE); 
   $result = $model_db->query("your query")->result();
   //close the connection and free the MySQL resource
   $model_db->close();
   return $result;
} 


I have tested this assertion while monitoring resources via phpMyAdmin. Trust me, it's true. The moral of the story is that you have to explicitly close a database in order to free the resources.

There a lots of different ways you could handle multiple database loads. Loading and closing the database in a method as shown above may be easiest and safest but could result in a lot of redundant code. Each method would have to do the same. If you create a class property in the model that is set in __construct() you will probably need a method to cleanup and to run that after you're done with the model.

PHP Code:

class Othermodel extends CI_Model
{
    public $model_db;

    public function __construct()
    {
        parent::__construct();
        $this->model_db = $this->load->database('other_group', true);
    }

    public function get_something()
    {
         return $this->model_db->query("your query")->result();
    }

   public function close()
   {
        $this->model_db->close();   
   }

} 


You would probably want to make sure that $this->model_db is set before trying to use it. So more work would be needed to fill out the above class. But you get the idea.

Using that model in a controller might go like this

PHP Code:

$this->load->model('othermodel');
$var = $this->othermodel->get_something();
//if you done with it - close it
$this->othermodel->close(); 


Hope this helps.

Thanks again dave for a detailed reply. Both your replies are informative and understood. I agree with your explanation. But still not so confident about CI's logic behind this behavior. 

CI docs state that:

1. To choose a specific group from your config file you can do this:
$this->load->database('group_name');
2. If you need to connect to more than one database simultaneously you can do so as follows:
$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE);
Note: By setting the second parameter to TRUE (boolean) the function will return the database object.
3. You don’t need to create separate database configurations if you only need to use a different database on the same connection. You can switch to a different database when you need to, like this:
$this->db->db_select('database2name');

Now, consider this simplest case for multiple databases:

• 3 databases, 6 models (Model 1 & 2 need to access DB1, Model 3 & 4 - DB2, Model 5 &6 - DB3)
  
• Model loading sequence - 1, 3, 5, 2, 4, 6
  
• Thus, DB access sequence - DB1, DB2, DB3, DB1, DB2, DB3
  
• (Either I am terribly off mark here or there actually is nothing wrong with structuring an application this way).
  

Implementing CI's methods above, results:

• Method 1: Only model 1 creates successful connection and no connection made in model 3, which of course gives DB error and halts subsequent execution. This behavior is like you stated that subsequent calls to $this->load->database('group') will be ignored. Clearly, this method is not for use with multiple databases (it's also not described for this use in docs) but it still defies logic to have a function (defined under "Manually Connecting to a Database" in docs) that can be effectively called exactly once per CI instance even with different values for its first parameter. (This is an extremely restrictive feature, which should clearly be mentioned in the docs).
  
• Method 2: Connections are created successfully in all models but obviously, like you stated, causes "max_user_connections" error.
  
• Method 3: Randomly fails to select correct database. For example, Model 1 and 3 would be loaded and processed successfully but in Model 5 (DB3), it would give the error that "DB2.table does not exist", meaning it fails to select DB3 in Model 5. While using this method, only 'default' group is defined in config/database and have tried this method in two ways:
  • autoloading 'database' library and setting $this->db->db_select() in __construct of each model (with 'pconnect' set to TRUE and FALSE). 
    
  • not autoloading 'database' library and calling $this->load->database() in __construct of each model followed by $this->db->db_select() (with 'pconnect' set to TRUE and FALSE).
    

My understanding was that

1. $this->load->database('group', TRUE); would first look for any existing connection with same parameters and
   • if such a resource exists then return it as object, or 
     
   • else create a new connection and return this new resource as object
     
2. "$this->load->database('group')" would first look for any existing connection with same parameters and (since it's not creating simultaneous connections),
   • if such a resource exists then just update $active_group, or 
     
   • else create a new connection and then update $active_group
     
3. While the above two methods can deal with databases with multiple config settings, "$this->db->db_select()" would merely be updating $database for database superobject in CI instance on the same DB connection. (This function is not working as intended even after multiple attempts with different possible settings).
   

I feel that a framework claiming to handle multiple databases would be more efficient in its related resource management and less restrictive in related development process. I don't mean any disrespect to CI and all the people working hard behind it. But the statement that "...CI intelligently takes care of closing your database connections..." is/was/has been misleading for me. I still fail to find anything "intelligent" about: 

• Simply opening a connection at CI instance initialization and closing it at instance shutdown.
  
• Keep creating additional connections at every "$this-load->database('group', TRUE);" call without being able to close them "because of the way MySQL handles resources in PHP".
  
• Not providing capability to reuse already created DB resources (which can't even be destroyed for MySQL limitation).
  
• Not using $active_group more flexibly and efficiently.
  

I'm aware of and agree with the possible solutions you mentioned in your reply. However, as I mentioned earlier, it's a large application and it's going to be an extensive task to implement any of those solutions effectively across the application (not to mention loads of possible redundancy). 

On that note, (and similarly pretty scarce reference material available anywhere), can you please give some suggestions on correctly implementing db_select() if you have had any experience with it. I feel that using db_select() will be the most efficient solution at this point if all databases are moved to same configuration. 

Thank you again for your time and insight.