Disappearing Error Message in Mysqli

PHP, whether you love it or hate is, can always surprise you with a new bug (or undocumented behavior). This time it's PHP's Mysqli library and how prepared statements can bite you.

#Programming
Disappearing Error Message in Mysqli

Disappearing Error Message in Mysqli

It is widely known that PHP has problems. Some people even take it to the extreme (I really should write about this one day). But no matter how you hate PHP, you can’t deny the fact it runs half of the website on the internet and that if you know how to program you’re not going to find much friction between your code and PHP (except of course the ridiculously inconsistent function names and arguments ordering).

And not too long ago, at work, I’ve discovered yet another weird quirk, this time it’s something that can seriously bite you in the butt and seems to not be documented. It pertains to mysqli database connection library.

In short, if your statement has returned an error while executing, that error is available via $mysqli->error only as long as the statement object exists. If you unset it or leave the context in which it was created (which means it’ll be collected from the stack), mysqli will report no error happening:



    // Given a table 'test' with not-null column 'id'
    $mysqli = new mysqli();

    query($mysqli);
    echo mysqli_error($mysqli); // Echoes empty string

    function query($mysqli){
        $param = null;
        $statement = mysqli_prepare($mysqli, "INSERT INTO test(id) VALUES(?)");
        $statement->bind_param("s", $param);
        $statement->execute();
        echo mysqli_error($mysqli); // Echoes "Column 'id' cannot be null"
    }

Let’s check the docs:

It’s an annoying issue that can be a huuuuge pain if you’re working in a legacy system and the legacier it is the worse. But it might just be that you’ll never care about it a bit. I tested and reproduced this issue on PHP 7.1.9 and that’s the only one I checked because I don’t really care much about older versions of PHP. Below is a complete example if you’d like to check it yourself.

A complete example



<?php

/*
	Assuming table:

	CREATE TABLE `test` (
		`id` int(11) NOT NULL,
		PRIMARY KEY (`id`)
	);
 */

$mysqli = mysqli_connect("localhost", "root", "", "test");

echo "<pre>";
runQueryWithError();
l("out -> mysqli::query", mysqli_error($mysqli));
runQueryInStatementWithError();
l("out-> statement::query", mysqli_error($mysqli));

/*
Gives:
1. [f() -> mysqli::query] Column 'id' cannot be null
2. [out ->; mysqli::query] Column 'id' cannot be null
3. [f() -> statement::query] Column 'id' cannot be null
4. [f() -> statement::query -> after unset]
5. [out-> statement::query]
 */

function l($title, $message)
{
	static $counter = 0;

	$counter++;
	echo "{$counter}. [{$title}] {$message}<br>";
}

function runQueryWithError()
{
	global $mysqli;

	mysqli_query($mysqli, "INSERT INTO test(id) VALUES(NULL)");
	l("f() -> mysqli::query", mysqli_error($mysqli));
}

function runQueryInStatementWithError()
{
	global $mysqli;

	$param = null;
	$statement = mysqli_prepare($mysqli, "INSERT INTO test(id) VALUES(?)");
	$statement->bind_param("s", $param);
	$statement->execute();
	l("f() -> statement::query", mysqli_error($mysqli));
	unset($statement);
	l("f() -> statement::query -> after unset", mysqli_error($mysqli));
}

Subscribe to newsletter

· New blog posts.
· Game updates.
· Newsletter-exclusive rambling.