Connect Error Over Multiple MySQLi Objects

[malcmal]

Description

The following code will setup the circumstances to highlight the issue. It requires that you have the ability to connect to three different MySQL servers via IP. It can be done with only two but the bug is easier to spot when you have three:

<?php

$db_user = "username";
$db_pass = "password";
$db_host1 = "1.2.3.4";
$db_host2 = "2.3.4.5";
$db_host3 = "3.4.5.6";

mysqli_report(MYSQLI_REPORT_OFF);

$server1 = mysqli_init();
$server1->options(MYSQLI_OPT_CONNECT_TIMEOUT, 1);

$server2 = mysqli_init();
$server2->options(MYSQLI_OPT_CONNECT_TIMEOUT, 1);

$server3 = mysqli_init();
$server3->options(MYSQLI_OPT_CONNECT_TIMEOUT, 1);

$server1->real_connect($db_host1, $db_user, $db_pass);
$server2->real_connect($db_host2, $db_user, $db_pass);
$server3->real_connect($db_host3, $db_user, $db_pass);

echo "Server 1 Connect Error Number: {$server1->connect_errno}\n<BR>";
echo "Server 1 Error Number: {$server1->errno}\n<BR>";
echo "\n<BR>";
echo "Server 2 Connect Error Number: {$server2->connect_errno}\n<BR>";
echo "Server 2 Error Number: {$server2->errno}\n<BR>";
echo "\n<BR>";
echo "Server 3 Connect Error Number: {$server3->connect_errno}\n<BR>";
echo "Server 3 Error Number: {$server3->errno}\n<BR>";

?>

The issue is with the error reporting when one of the servers is unreachable. If it's the last one in the list then ALL servers show an error code in a very random fashion. An example output would be:

Server 1 Connect Error Number: 2002 Server 1 Error Number: 0

Server 2 Connect Error Number: 2002 Server 2 Error Number: 0

Server 3 Connect Error Number: 2002 Server 3 Error Number: 2002

This is despite the fact that servers 1 and 2 were successfully connected and able to process queries. It seems to me that the error reporting is somehow contaminating between MySQLi PHP objects.

PHP Version

PHP 8.2.7

Operating System

OpenBSD 7.3

[damianwadley]

It's not clear from the documentation but the connect_errno is a global value - not a per-instance value. The way you can tell is that the procedural version doesn't take a mysqli instance while mysqli_errno does:

https://www.php.net/manual/en/mysqli.connect-errno.php

mysqli_connect_errno(): int

https://www.php.net/manual/en/mysqli.errno.php

mysqli_errno(mysqli $mysql): int

So the correct way to use it with multiple instances is to connect one and then immediately check for a connect_errno before connecting the others.

The property should be static, but (a) I'm not sure if it's worth changing at this point and (b) it would have to wait until PHP 9.

Thus this sounds like a documentation issue. Namely, that it should more explicitly spell out how all instances will report the same value, and the value is for the last connection made anywhere.

[malcmal]

Thank you for such a prompt response. That makes sense what you said even if it strikes me as a very odd way to do it and if you could propose making the property static for PHP 9 it would surely be a good idea. Much appreciated.

[malcmal]

Just to add one other thought - it seems that "errno" / "error" and NOT global values whereas "connect_errno" and "connect_error" ARE globals which adds to the confusion. Personally I think they should all be static so you can re-verify each individual instance at any time during processing. Like you said - the documentation could do with improvement on this one.

[damianwadley]

Personally I think they should all be static

Static means that there is only one value and it's shared between all instances. The errno/error are not shared so they need to remain instance values.

[malcmal]

My apologies. I meant to say that all those values should not be shared between instances in my view but I guess it is as it stands. Thanks again.

[kamil-tekiela]

Just a helpful hint, but this is a very good reason to not turn off the error reporting. With automatic error reporting you get an exception at the time of error. Much less confusion. I will think about what to do with the current situation to reduce the confusion. Thank you for reporting.