CODEWITHSUNDEEP
phpdatabasecodeigniterdatabase-connection
ArthurGuy
ArthurGuy

Reputation: 1577

CodeIgniter database connections not being closed

I have built a social community website in CodeIgniter which is now getting a fair bit of traffic, the hosting company have started complaining and saying that the database is receiving null connections as well as connections which aren't being closed.

I am not entirely sure what a null query is or how one would end up being issued, any ideas?

I have added in additional code to force close connections when the code reaches an end but apparently this isn't working.

Can anyone offer any suggestions as to where to look or start debugging an issue like this?

Thanks

I have the following at the bottom of my core MY_Controller

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

Upvotes: 24

Views: 42379

Answers (6)

Wallysson Nunes
Wallysson Nunes

Reputation: 750

I think that you're gettting problemns with the initial configurations from Codeigniter connection database.

At this page, you can see each value from config array:
https://www.codeigniter.com/user_guide/database/configuration.html

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "database_name";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";
$db['default']['swap_pre'] = "";
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

This it's an basic array config database, try to set the variable of pconnect to FALSE, when it is turned on, the system doesn't close any connection and it stay opened to news queries at any time.

If you set it to false like i said, your system will continue working perfectly, but the codeigniter will close and open the connection when he need's to use the database.

Here, you can find and post inside codeigniter forum with a guy that's have a problem with pconnect variables, may help you! http://codeigniter.com/forums/viewthread/177573/#842016

Upvotes: 28

blagi
blagi

Reputation: 26

I solved same problem on one CI site by changing driver 'mysql' to 'mysqli' in: Database.php:

$db['default']['dbdriver'] = "mysqli";

Upvotes: 0

Matt Barry
Matt Barry

Reputation: 57

I have had similar problems with CodeIgniter in the past and they were caused by persistent connections in MySQL which is enabled by default in CodeIgniter. I used the SHOW PROCESSLIST query in MySQL to view open connections, their current running query, the time (in seconds) the connection has been open for, etc. If the connection is idle the Command field returned would contain Sleep and the Info field (the query) would be NULL which is probably what your host is referring to. That's a good place to start with an issue like this.

Another thing that I want to note is a nuance of PHP, mysql_close which is being called here from your controller's __destruct method will not close a persistent MySQL connection. PHP will close non-persistent connections at the end of the script's execution so it is usually not necessary to call it.

I realize that you had said persistent connections were not enabled, this was how I went about debugging my problem which sounds very similar to your problem.

Upvotes: 1

landons
landons

Reputation: 9547

I had that same issue a while back, and the solution was multi-part (no single part of the problem surfaced until all parts became problematic together). PConnect should be off unless you know how to use it (as others have said).

Another thing to consider is whether your web server is threaded (such as Apache's worker mode--most common web servers are). If you're getting a lot of traffic, and your threads aren't closing quickly, you might be hitting some concurrent web server / database server connection limits, or exhausting memory/processor resources. That could cause some database connections to hang.

So, I'd check for other signs of the problem, and not assume the database is the source. It could very well be a symptom. Do you have any information about the null connections (like which database user spawned them)? That could help you trace it back...

Edit: One thing I forgot--sometimes PHP errors can screw up CI's destruct operations (which automatically close the connections, from my understanding), so you might check your error logs too.

Upvotes: 0

simnom
simnom

Reputation: 2620

Codeigniter should automatically close the database connection but you can implicitly call it with $this->db->close();

See http://codeigniter.com/user_guide/database/connecting.html

Upvotes: 6

Marco Monteiro
Marco Monteiro

Reputation: 39

have you tried to do $this->db->close(); after a query is made?

Upvotes: 2

Related Questions