Bug: reconnect() keeps the old transaction on a replaced MySQLi socket #10648

PHP Version

8.5

CodeIgniter4 Version

4.7.0 (measured). Still present on develop at df8b96bee (4.7.6-dev) by source read: BaseConnection::reconnect() still only calls close() and initialize() when ping() is false.

CodeIgniter4 Installation Method

Composer (as dependency to an existing project)

Which operating systems have you tested for this bug?

macOS

Which server did you use?

cli

Environment

testing

Database

MySQL, MySQLi driver. No production failure has been observed. This is a measured framework defect.

What happened?

reconnect() during an open transaction replaces the socket and leaves CodeIgniter's transaction state pointing at the transaction that died with the old socket.

Measured on PHP 8.5.6, CodeIgniter 4.7.0, MySQLi:

1. SET SESSION wait_timeout = 1, then sleep 3 seconds.
2. ping() is false.
3. reconnect() runs.
4. transDepth stays 1.
5. transStatus() stays true. ping() uses simpleQuery(), and simpleQuery() does not call handleTransStatus(). A dead-socket ping therefore does not flip the flag. query() does call handleTransStatus().
6. The row inserted before the timeout is already gone (the server rolled that transaction back when it closed the socket).
7. transComplete() returns true.
8. The row count stays 0.

A true return from transComplete() means CodeIgniter committed a transaction that the new socket does not have. The insert is not in the table.

BaseConnection::reconnect():

public function reconnect()
{
    if ($this->ping() === false) {
        $this->close();
        $this->initialize();
    }
}

Neither close() nor initialize() clears transDepth or transStatus.

The same reconnect() body is on the 4.8 branch (e710fc3d9, source read). This was not re-run there.

Steps to Reproduce

$db = db_connect();
$db->query('CREATE TABLE IF NOT EXISTS reconnect_probe (id INT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(32))');
$db->query('DELETE FROM reconnect_probe');

$db->transStart();
$db->table('reconnect_probe')->insert(['note' => 'before-timeout']);

$db->query('SET SESSION wait_timeout = 1');
sleep(3);

// Preconditions, measured: ping is false, depth is 1, status is true.
$db->ping();
$db->reconnect();

$completed = $db->transComplete();
$count     = $db->table('reconnect_probe')->countAllResults();

Observed: $db->transDepth is 1 after reconnect(), transStatus() is true, $completed is true, $count is 0.

Expected Output

A replaced socket must not keep the old transDepth or transStatus. transComplete() after that reconnect() must not return true. The pre-timeout insert stays uncommitted, which is already the server outcome once the old socket is gone.

Anything else?

Searched issues and pull requests, open and closed, on codeigniter4/CodeIgniter4 before opening this. Nothing already covers this defect.

Neighbors that do not:

• Bug: TypeError in pg_ping() #9043 (closed) and fix: Postgre\Connection::reconnect() TypeError in pg_ping() #9279 (merged): PostgreSQL pg_ping() TypeError after close(). Not transaction state.
• MySQLi sane reconnect #462 (merged): MySQLi reconnect closes and reconnects because mysqlnd ignores the reconnect flag. It does not reset transaction depth or status.
• fix transStart() connection init #346 (merged): transStart() before the first query called autocommit() on a boolean. Connection init, not a dead socket inside a transaction.
• Bug: ErrorException: Undefined index: file: SYSTEMPATH\Debug\Toolbar\Collectors\Database.php at line 142 #5539 (closed): debug toolbar trace line. The body mentions transDepth only as part of a dumped session object.
• feat: add BaseConnection::resetTransStatus() #8767 (closed) and open discussion Dev: DB transactions after v4.3 #6909: resetTransStatus() so a later transaction group can run after strict mode. It does not run from reconnect(), and it does not clear transDepth.
• Bug: updateBatch() silently ignores chained where() conditions, creating unsafe broad-update footgun #10234 / fix: prevent updateBatch with existing where conditions #10236: updateBatch() plus an existing Query Builder WHERE. Different defect. Please do not treat this report as a reopen of those.

ping() → simpleQuery() → execute() never calls handleTransStatus(), so a failed ping cannot be what clears the transaction flag. The reset belongs in reconnect() when the socket is replaced.