Welcome Guest, Not a member yet? Register   Sign In
How CodeIgniter close db connections?

(This post was last modified: 05-28-2017, 08:51 AM by xuhaib.)

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:
Quote:1. To choose a specific group from your config file you can do this:
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:

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.

Messages In This Thread
RE: How CodeIgniter close db connections? - by xuhaib - 05-27-2017, 09:46 AM

Theme © iAndrew 2016 - Forum software by © MyBB