[Shawn]

I use flash messages to display simple error messages to the user in the event of database error. The full error stack trace gets written to the log. I have found a repeatable issue in which the flash message (and non-flash as well) disappears if the db error is thrown inside of a db transaction.

In my form controller I catch any exception thrown within my model ($this->donation->update() is a library method, which calls a donations_model method)
I write the error to the log (always works) and set a flash message
Then I redirect to another (ledger) controller, which will display the message

Code:

            try {
                $this->donation->update($transaction_id, $program_id, $friend_id, $gross, $fee, $agency_transaction,
                                        $date, $receipt_number, $donation_revenue, $donation_asset);
            } catch (Exception $e) {
                //logging works with or without db transaction
                log_message('error', $e->getMessage());
                //This flash message only remains if db transaction is commented out
                $_SESSION['message'] = 'There was an error updating the donation record';
                $this->session->mark_as_flash('message');
                //The session message can always be echoed here with or without a db transaction
                echo $_SESSION['message'];
            }
            redirect('ledger');

The ledger controller checks for any flash messages. If none, then it sets the message in the view to ''

Code:

//The message is undefined if the error in the model is thrown within a transaction.
        // But It prints successfully if the transaction is commented out in the model
        echo $_SESSION['message'];
        if (isset($_SESSION['message'])) {
            $view_content['message'] = $_SESSION['message'];
        }
        else {
            $view_content['message'] = '';
        }

Here is the method in the donations_model with a deliberate SQL error (look for 'Hey man!') and a thrown exception
If the transaction is commented out then the flash message will successfully remain in the session and can be displayed on the ledger page

Code:

    public function update($transaction_id, $friend_id, $gross, $fee,
                            $net, $agency_transaction, $receipt_number) {
        // Session message remains set if the transaction is commented out
        //$this->db->trans_start();
        $this->db->where('transaction_id', $transaction_id);
        $data = array(
                'friend_id'        => 'Hey man!', //$friend_id,
                'gross'            => $gross,
                'fee'            => $fee,
                'net'            => $net,
                'agency_transaction' => $agency_transaction,
                'receipt_number'    => $receipt_number
        );
        if (!$this->db->update('donations', $data)) {
            $error = $this->db->error();
            throw new Exception('Donations_model->update: ' . $error['code'] . ' ' . $error['message']);
        }
        //$this->db->trans_complete();
        return TRUE;
    }

If you need more details for a bug report I can provide a link to my project on github.

[skunkbad]

It certainly could be a bug. I really don't like using flash data, because even a 404 can "use up" the data. If it were me, I'd append a query string var to the redirect and lose the flash data usage.

[Narf]

"flash" is irrelevant here - this will happen with any type of session data when you use the database driver.

The problem is, when you share the database connection (and that may happen even if you try to avoid it, it's just how PHP works), a transaction started for your own queries will obviously include the session library's queries - you can't avoid that.

And the library can't avoid it either because there are two strategies to take - discard current state of the transaction or commit it unconditionally. Both ways would cause a problem for somebody.

[Narf]

FYI, interferences like these are why persistent connections are disabled for usage with sessions, but transactions happen at runtime, so you can't detect them until it's already late.

[skunkbad]

"flash" is irrelevant here - this will happen with any type of session data when you use the database driver.

The problem is, when you share the database connection (and that may happen even if you try to avoid it, it's just how PHP works), a transaction started for your own queries will obviously include the session library's queries - you can't avoid that.

And the library can't avoid it either because there are two strategies to take - discard current state of the transaction or commit it unconditionally. Both ways would cause a problem for somebody.

Interesting. So the failed transaction is actually rolling back the session insert/update. I wonder if this would still be true if the sessions table was MyISAM instead of InnoDB.

[Narf]

"flash" is irrelevant here - this will happen with any type of session data when you use the database driver.

The problem is, when you share the database connection (and that may happen even if you try to avoid it, it's just how PHP works), a transaction started for your own queries will obviously include the session library's queries - you can't avoid that.

And the library can't avoid it either because there are two strategies to take - discard current state of the transaction or commit it unconditionally. Both ways would cause a problem for somebody.

Interesting. So the failed transaction is actually rolling back the session insert/update. I wonder if this would still be true if the sessions table was MyISAM instead of InnoDB.

Since MyISAM doesn't support transactions, that should work around the issue - indeed. The SQL for table creation in the user guide doesn't specify a database engine ... maybe I should add this as a tip, hadn't thought of it before.

[skunkbad]

"flash" is irrelevant here - this will happen with any type of session data when you use the database driver.

The problem is, when you share the database connection (and that may happen even if you try to avoid it, it's just how PHP works), a transaction started for your own queries will obviously include the session library's queries - you can't avoid that.

And the library can't avoid it either because there are two strategies to take - discard current state of the transaction or commit it unconditionally. Both ways would cause a problem for somebody.

Interesting. So the failed transaction is actually rolling back the session insert/update. I wonder if this would still be true if the sessions table was MyISAM instead of InnoDB.

Since MyISAM doesn't support transactions, that should work around the issue - indeed. The SQL for table creation in the user guide doesn't specify a database engine ... maybe I should add this as a tip, hadn't thought of it before.

In my own testing within the last week, I noticed that creating a table without specifying the database engine left the choice up to MySQL. In one case it created it with InnoDB, and in another case it created it with MyISAM. A tip regarding the database engine would be nice, and perhaps some pros and cons of using one or the other when specifically related to sessions.

[Shawn]

Thanks to Narf for the info on sessions and rollback!

It makes sense that all database changes, which has to include setting session data, will be rolled back the moment you leave the calling controller.

What I have done, instead, is use manual transactions. This allows me to rollback explicitly during error conditions, prior to setting a flash message.

Code:

        // Use manual transactions so that you can explicitly rollback prior to setting
        // a flash message in the session
        //$this->db->trans_start();
        $this->db->trans_begin();
        $this->db->where('transaction_id', $transaction_id);
        $data = array(
                'friend_id'        => 'Hey man!', //$friend_id,
                'gross'            => $gross,
                'fee'            => $fee,
                'net'            => $net,
                'agency_transaction' => $agency_transaction,
                'receipt_number'    => $receipt_number
        );
        if (!$this->db->update('donations', $data)) {
            $error = $this->db->error();
            $this->db->trans_rollback();
            throw new Exception('Donations_model->update: ' . $error['code'] . ' ' . $error['message']);
        }
        $this->db->trans_commit();
        //$this->db->trans_complete();
        return TRUE;

[Narf]

"flash" is irrelevant here - this will happen with any type of session data when you use the database driver.

The problem is, when you share the database connection (and that may happen even if you try to avoid it, it's just how PHP works), a transaction started for your own queries will obviously include the session library's queries - you can't avoid that.

And the library can't avoid it either because there are two strategies to take - discard current state of the transaction or commit it unconditionally. Both ways would cause a problem for somebody.

Interesting. So the failed transaction is actually rolling back the session insert/update. I wonder if this would still be true if the sessions table was MyISAM instead of InnoDB.

Since MyISAM doesn't support transactions, that should work around the issue - indeed. The SQL for table creation in the user guide doesn't specify a database engine ... maybe I should add this as a tip, hadn't thought of it before.

In my own testing within the last week, I noticed that creating a table without specifying the database engine left the choice up to MySQL. In one case it created it with InnoDB, and in another case it created it with MyISAM. A tip regarding the database engine would be nice, and perhaps some pros and cons of using one or the other when specifically related to sessions.

Yea, they changed the default to InnoDB in version 5.5.