Edit report at https://bugs.php.net/bug.php?id=80653&edit=1
ID: 80653
User updated by: gman dot n at xrbr dot com
Reported by: gman dot n at xrbr dot com
Summary: MySQL BIGINT UNSIGNED value in prepared statement
treated incorrectly
Status: Not a bug
Type: Bug
Package: MySQLi related
Operating System: Ubuntu 20.04.1 LTS
PHP Version: 7.4.14
Assigned To: dharman
Block user comment: N
Private report: N
New Comment:
There is a new comment in the MySQL bug tracker ( https://bugs.mysql.com/bug.php?id=102338 )
regarding this issue:
[29 Jan 12:51] Georgi Kodinov
Posted by developer:
Can I please get the exact sequence of libmysql C API calls resulting from the above PHP snippet to
reproduce the bug?
Previous Comments:
------------------------------------------------------------------------
[2021-01-22 13:45:17] cmb@php.net
Should be not a bug, then. :)
------------------------------------------------------------------------
[2021-01-22 11:41:51] dharman@php.net
Reported upstream with MySQL.
------------------------------------------------------------------------
[2021-01-22 10:58:12] kieran at supportpal dot com
Suggest to close. MySQL have verified a regression in 8.0.22+
------------------------------------------------------------------------
[2021-01-21 22:54:35] gman dot n at xrbr dot com
Done: https://bugs.mysql.com/bug.php?id=102338
------------------------------------------------------------------------
[2021-01-21 21:43:13] dharman@php.net
Thanks, I can see that there is a problem. I have been looking into the issue for the past hour and
I still haven't narrowed it down, but I have some more findings I would like to keep a record
of here. It looks like the only affected version is MySQL 8.0.22. I am 90% sure that the bug is on
MySQL side...
What I have found out so far is:
- It affects all PHP versions
- Engine is irrelevant
- As you have noticed the type (both column and binding) of the second column impacts the result
- Only certain numbers are affected. There is a pattern, but it's unclear to me what that
pattern is. The bigger the numbers get the more irregular the pattern becomes. e.g. numbers between
13326067295508630 and 13326067295508660 (00 fail, 11-success):
11 13326067295508630
00 13326067295508631
11 13326067295508632
11 13326067295508633
11 13326067295508634
00 13326067295508635
11 13326067295508636
11 13326067295508637
11 13326067295508638
00 13326067295508639
11 13326067295508640
11 13326067295508641
11 13326067295508642
00 13326067295508643
11 13326067295508644
11 13326067295508645
11 13326067295508646
00 13326067295508647
11 13326067295508648
11 13326067295508649
11 13326067295508650
00 13326067295508651
11 13326067295508652
11 13326067295508653
11 13326067295508654
00 13326067295508655
11 13326067295508656
11 13326067295508657
11 13326067295508658
00 13326067295508659
-----------------------------
- The type used in binding makes huge difference, regardless of what it is or which parameter it is.
For example, this works fine:
$type3 = 30;
$statement1->bind_param('ss', $hash3, $type3);
$statement1->execute();
echo $statement1->get_result()->num_rows;
$type4 = 30;
$statement1->bind_param('ss', $hash3, $type4);
$statement1->execute();
echo $statement1->get_result()->num_rows;
but this does not
$type3 = 30;
$statement1->bind_param('si', $hash3, $type3);
$statement1->execute();
echo $statement1->get_result()->num_rows;
$type4 = 30;
$statement1->bind_param('ss', $hash3, $type4);
$statement1->execute();
echo $statement1->get_result()->num_rows;
This leads me to believe that the problem is on MySQL side with the way the optimizer handles type
casting in prepared statements. MySQL 8.0.22 has changed the way that prepared statements are parsed
and optimized. This looks like an unintentional bug. The only explanation I have for this erratic
behaviour is that there is a bug in the new way that MySQL handles PS and type casting. I see no way
that mysqli or mysqlnd could produce such bug.
Therefore, I would kindly ask you to report it to Oracle (https://bugs.mysql.com/) as soon as
possible.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=80653
--
Edit this bug report at https://bugs.php.net/bug.php?id=80653&edit=1