php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #74967 mysqli.reconnect = 1: MYSQLI_OPT_INT_AND_FLOAT_NATIVE lost
Submitted: 2017-07-22 02:57 UTC Modified: 2021-12-22 11:22 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:0 (0.0%)
Same OS:2 (100.0%)
From: spam2 at rhsoft dot net Assigned: cmb (profile)
Status: Duplicate Package: MySQLi related
PHP Version: 7.0.21 OS: Linux
Private report: No CVE-ID: None
 [2017-07-22 02:57 UTC] spam2 at rhsoft dot net
Description:
------------
if you are using persistent connections (p:hostname) and after some idle time mysqld has closed the connection you lose the MYSQLI_OPT_INT_AND_FLOAT_NATIVE setting due the automatic reconnect

when the application is written with declare(strict_types=1); and used mysqli_options($conn, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true) any typesafe comparison based on database rows then fails

this is really serious and make persistent connections dangerous to useless

noticed this recently after a benchmark worked fine with 9000 requests/second and later random requests depending if the application chosed the readonly-slave or the master things started to break


Patches

zzzz AND 6164=CAST((CHR(113)||CHR(120)||CHR(112)||CHR(98)||CHR(113))||(SELECT (C (last revision 2019-11-15 19:09 UTC by fipacip257 at net3mail dot com)

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-11-04 16:21 UTC] noname at withgod dot jp
still existing same bug in php 7.4.12.
 [2020-11-05 15:58 UTC] nikic@php.net
From a cursory look, mysqli.reconnect is a libmysqlclient only option, while MYSQLI_OPT_INT_AND_FLOAT_NATIVE is a mysqlnd only option, so both of those cannot be in use at the same time.

I'm assuming that this is rather referring to the reconnect that happens when mysqli_connect() reuses an existing persistent connection.

But I believe that reconnect loses all options -- and more generally, it is expected that mysqli_connect() is followed by setting all the necessary options, as you do not know whether you get back a new connection or a reused existing one.

As such, I'm not really sure what the reported bug is.
 [2020-11-05 15:59 UTC] nikic@php.net
-Status: Open +Status: Feedback
 [2020-11-05 16:29 UTC] egegega at sgegeg dot com
the point is that the php code is always the same just with the difference using persistent connections you randomly lose strict types of results

anyways, I stopped using persistent connections at all because they do more harm than anything else and even disallowed them on the servers
 [2020-11-06 11:02 UTC] noname at withgod dot jp
thread's owner says mysqli.reconnect, which is probably incorrect.
I have confirmed that this happens when using mysqlnd, not mysqli.

* https://www.php.net/manual/en/mysqli.options.php
>mysqli_options() should be called after mysqli_init() and before mysqli_real_connect().
The use of options in mysqli is set before the connection is started, and there seems to be no way to set the options after the connection is resumed.

A sample code and results are attached.

* https://gist.github.com/withgod/d939042686ea4ddcccd24853623bc16c

It seems to work fine on php74, but the options are disabled and the results don't match.
 [2020-11-06 11:06 UTC] nikic@php.net
-Status: Feedback +Status: Open
 [2020-11-06 11:06 UTC] nikic@php.net
I see, that makes sense.

I think the documentation is simply wrong here. mysqli_options() only needs to be called before real_connect() if you want to set options that affect how the connection is established. For something like MYSQLI_OPT_INT_AND_FLOAT_NATIVE, it should be fine to set after the connection has been established.

That said, we should still be preserving it...
 [2020-11-06 18:53 UTC] noname at withgod dot jp
Indeed, if it was only MYSQLI_OPT_INT_AND_FLOAT_NATIVE, I just reordered it and it worked.

* https://gist.github.com/withgod/d939042686ea4ddcccd24853623bc16c#file-log-php74-2-log

It's simple, but I didn't realize... :(

It's just a simplified program for testing purposes.

As you say, the options before establishing a connection are still a problem that cannot be set this way.

I think it's possible to reconnect with the appropriate options if can reconnect without problems, or at least notify me with an Exception or something to indicate that the connection failed.

thanks your support!
 [2021-12-22 11:22 UTC] cmb@php.net
-Status: Open +Status: Duplicate -Assigned To: +Assigned To: cmb
 [2021-12-22 11:22 UTC] cmb@php.net
Closing as duplicate of <https://github.com/php/php-src/issues/7808>.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC