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

(05-24-2017, 01:16 PM)xuhaib Wrote: 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.

(05-24-2017, 01:16 PM)xuhaib Wrote: (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.

(05-24-2017, 01:16 PM)xuhaib Wrote: 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.

(05-24-2017, 01:16 PM)xuhaib Wrote:
  • 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.

(05-24-2017, 01:16 PM)xuhaib Wrote:
  • 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.
Reply


Messages In This Thread
RE: How CodeIgniter close db connections? - by dave friend - 05-25-2017, 10:13 AM



Theme © iAndrew 2016 - Forum software by © MyBB