Capture query errors for the primary query

Problem

If an error occurs during the execution of a SQL statement immediately followed by another SQL statement (like SQL_CALC_FOUND_ROWS or any post-statement), the error from the initially failed SQL statement could be lost. This is because the ex_mysql_error function captures the last error that occurred on the MySQL connection, and it is only called after the second SQL statement is executed.

$query = 'SELECT NONSENSE';
$this->result = $this->ex_mysql_query( $query, $this->dbh );
$this->ex_mysql_query( 'SELECT FOUND_ROWS()', $this->dbh );
$this->last_error = $this->ex_mysql_error( $this->dbh );
// No error

Solution

Capturing the error right after the SQL statement executes will ensure that any mistake generated during that operation is immediately recorded. This will prevent subsequent SQL statements from overwriting it.

Then, for backward compatibility and to keep the integrity of the rest of the code, we can re-capture the error where it was previously captured. This should ensure that handling code expecting an error at this point still functions as expected.

Discussion

While the discussed solution improves error tracking and handling by immediately capturing errors right after SQL statement execution, it might not be the optimum solution, especially for complex, multi-statement queries.

In an ideal world, we would want to capture errors after each SQL statement. This is due to the atomic nature of SQL operations—each SQL statement is essentially a separate operation and has its own chance of failure for various reasons, ranging from syntax errors to integrity constraint violations to transient connection issues.

Capturing errors after each statement allows for granular error tracking, which can significantly improve debugging and maintenance. It could save a lot of time isolating which particular SQL statement caused the error, especially if you run large batches of SQL statements together.

However, this would require a significant code overhaul and adjustment to the current structure of the query() function. Another consideration is the performance trade-off. More error checking means more interactions with the database, and that could potentially slow down the overall operation, especially in high-throughput environments.

While it's technically more precise to check for errors after each statement, the possible performance impact should be carefully assessed and weighed against the benefits of more granular error tracking. The current solution could be an acceptable trade-off in specific scenarios, especially for more straightforward applications or where performance is paramount.

Automattic / HyperDB