MySQL Bugs: #97748: Connection errors appear in error.log

Bug #97748	Connection errors appear in error.log
Submitted:	22 Nov 2019 12:23	Modified:	26 Nov 2019 12:55
Reporter:	Yuri Tkachenko	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connectors: mysqlnd ( PHP )	Severity:	S3 (Non-critical)
Version:	5.7.28 MySQL Community Server	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
When use persist connection on PHP use call mysqli_real_connect with parameter host 'p:localhost' causes error connection when I call the connection second time.
In older versions version 5.7.24 and below was all ok.

When we connect to the database for the first time we see a message in the general log:
454 Connect   dbuser@localhost on dbscheme using Socket
When we reuse connection (connect second time) we see a message in the general log:
454 Connect   Access denied for user 'dbuser'@'localhost' (using password: NO)
455 Connect   dbuser@localhost on dbscheme using Socket
and in the error log we see the same message:
454 [Note] Access denied for user 'dbuser'@'localhost' (using password: NO)

I have second server with 8.0.18 MySQL Community Server and there are no such errors on this server.

links:
https://www.php.net/manual/en/mysqli.persistconns.php
https://github.com/ADOdb/ADOdb/blob/v5.20.14/drivers/adodb-mysqli.inc.php (line 113, 116 use this connection method)

How to repeat:
Use this php script

<?php

function connect()
{
    $connectionID = mysqli_init();
    $ok = mysqli_real_connect($connectionID,
        'p:localhost',
        'dbuser',
        'dbpass',
        'dbscheme',
        3306,
        "",
        0);
    mysqli_select_db($connectionID, 'dbsheme');
}

connect();
connect();

?>

Hi Yuri,

I'm failing to see how is this MySQL bug? 

If you look at the error:
454 Connect   Access denied for user 'dbuser'@'localhost' (using password: NO)

php is trying to connect without password. That's error on the side of the PHP so I assume that's where the bug should be reported.

Hello Bigdan, thank you for your reply.
Ok, I’ll create a ticket in the PHP bug tracker, I hope we find the truth.

Hi,

please do. 

I'm not up2date with latest PHP but IIRC doing

..
connect();
connect();
..

is not ok. The way php used to handle persistent mysql connections was that when you close, it was not closing it so next time you create a connection it uses the one that already exist. There is ton of reasons why using persistent connections between mysql and php is not a good idea but that's for some other discussion. As you can see in the PHP documentation:

[quote]
There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does. In either case, you can use register_shutdown_function() to register a simple cleanup function to unlock your tables or roll back your transactions. Better yet, avoid the problem entirely by not using persistent connections in scripts which use table locks or transactions (you can still use them elsewhere). 
[/quote]

MySQLi extension tries to solve these issues by adding cleanup code but this cleanup code does not execute between your two connect() calls!!!

for e.g. if I make this page:

<?php
$mysqli = new mysqli('p:localhost', 'dbuser', 'QqQqWwWw123!!', 'bugtest');
if ($mysqli->connect_errno) {
    echo "Errno: " . $mysqli->connect_errno . "\n";
    echo "Error: " . $mysqli->connect_error . "\n";
    exit;
}

if (!$result = $mysqli->query('select * from t1')) {
    echo "Errno: " . $mysqli->errno . "\n";
    echo "Error: " . $mysqli->error . "\n";
    exit;
}

while ($row = $result->fetch_assoc()) {
    echo $row['x'] . ' ' . $row['y'] . "\n";
}

$result->free();
$mysqli->close();
?>

and I open this page after the page exits I still see this connection on the mysql server. If I refresh the page I still see the single connection and the data is pulled trough this connection. So, the persistent connection thingy works.

So IMO, your PHP code is not properly written but check with PHP ppl I might be in error

all best
Bogdan