Transactions & Orphan MySQL connections

[eluser]jonsolo11[/eluser]
Hi,

I'm running CI (w/PHP 5 & MySQL 5) on an internal development server. When my DB stopped working, I noticed there were ~40 connections from my CI username, all asleep, and some had been open for 10+ hours.

After a little work, I isolated the bug:

Normally in PHP, if I start a transaction and then there's a PHP error, PHP rolls back the transaction and closes the connection before dying.

CI's error handler, however, does not properly close the connection - and furthermore, the transaction is still open, so all the tables are locked.

This code (inside a Model) will throw a PHP Fatal Error and leave a hanging connection:

Code:

$this->db->query("start transaction");
not_a_function();

This code (again in a Model) will die() and clean up after itself properly:

Code:

$this->db->query("start transaction");
die();

This code (in plain PHP, outside of CI) will throw a Fatal Error, but will clean up:

Code:

$conn = mysql_connect("localhost", $user, $password);
mysql_query("start transaction");
not_a_function();

This code will die(), and will clean up also:

Code:

$conn = mysql_connect("localhost", $user, $password);
mysql_query("start transaction");
die();

This is a serious problem: it renders CI transaction-unsafe. It's not too problematic on a debug server, but it could be disastrous on a production server, as it could instantly lock up my database.

[eluser]jonsolo11[/eluser]
After a little more checking, it seems this has nothing to do with transactions.

CI's error handler does not close the active database connection. Try it: add the following line to any file that has a DB connection (i.e., a Model):

Code:

not_a_function();

Run it a few times, then type the following line into a MySQL console:

Code:

show processlist;

You'll see a bunch of orphan connections.


To fix this, I think I'll have to add a db_close() command to "show_php_error()" in libraries/Exceptions.php, but I don't understand CI well enough to do this right.

Please help.

[eluser]Derek Allard[/eluser]
Thanks for reporting jonsolo11. I've closed off your first bug report since is seems invalid now, but I would like to help you get through this issue. Can you post a reduction template of code that recreates your issue. Simply adding in an invalid function isn't doing it for me.

[eluser]jonsolo11[/eluser]
In controllers/test.php:

Code:

class Test extends Controller
{
    function __construct()
    {
        parent::Controller();
        $this->load->model("testmodel");
    }
    
    function initialize()
    {
        not_a_function();
        echo "Foo"; //'Foo' never gets echoed; it dies first
    }
}

In models/testmodel.php:

Code:

class Testmodel extends Model
{
    function __construct()
    {
        parent::Model();
    }
}

When I execute this code by going to 'http://path/to/my/CI/test' it creates an orphan connection.

These, obviously, aren't my real function/class names, but this is pretty much the entire program that I'm executing. I've commented out all my autoloads and hooks; I don't think there's any other code being executed outside of these functions.

And btw, I'm using CI 1.5.4.

Thanks for the very fast reply, Derek :-) I really appreciate the help.

[eluser]Derek Allard[/eluser]
What version of PHP and MySQL. I'm not able to replicate...

[eluser]jonsolo11[/eluser]
PHP 5.1.6
MySQL 5.0.22

[eluser]Derek Allard[/eluser]
If anyone else can offer confirmation/insight I'd appreciate it. I just cannot recreate.

[eluser]blackarma[/eluser]
Different ways to solve your problem :

The register_shutdown_function
So you just have to register a function to close all your DB connexion, handle some logs, etc.

Change the Mysql timeout value (in this case: sleep process will be closed by MySQL after 20 sec)

wait_timeout=20

Think also to handle the ABORT action of the user on his browser, check this php function too :
ignore_user_abort

[eluser]Henrik Pejer[/eluser]
Are you using permanent connections? That's always a bad idea since they never close unless you do it, or they timeout. And the timeout for permanent connections are usually pretty long...

[eluser]jonsolo11[/eluser]
Nope. pconnect is false