Bug #79163 [Opn->Ver]: SQL SELECT EXISTS fills up memory
| From: | dharman@php.net | Date: | Sun, 13 Dec 2020 21:43:30 +0000 |
| Subject: | Bug #79163 [Opn->Ver]: SQL SELECT EXISTS fills up memory | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-231059@lists.php.net to get a copy of this message | ||
Edit report at https://bugs.php.net/bug.php?id=79163&edit=1
ID: 79163
Updated by: dharman@php.net
Reported by: kinimodmeyer at gmail dot com
Summary: SQL SELECT EXISTS fills up memory
-Status: Open
+Status: Verified
Type: Bug
Package: MySQLi related
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
I am able to reproduce also on PHP 8.0. I created simpler reproducible test case available at https://phpize.online/?phpses=20833fbdffb444d3d8b79c6b292ce7cb&sqlses=null&php_version=php7&sql_version=mysql57
This code should report 0Mb difference or almost zero. However, the memory used is increasing with
each iteration. The problem seems to be caused by column names in the result set, but so far the
root cause has eluded me.
-----------------------
<?php
$mem = memory_get_usage(0);
for ($i = 0; $i < 2000; $i++) {
$sql = 'SELECT 42 AS
'.$i.'';
$res = $mysqli->query($sql);
}
unset($i, $sql, $res);
var_dump(memory_get_usage(0)-$mem);
-----------------------
I can reproduce it on Windows CLI, but I do not know why I can't reproduce it using Apache.
Previous Comments:
------------------------------------------------------------------------
[2020-01-24 09:47:04] cmb@php.net
I cannot reproduce with PHP 7.4.3-dev using mysqlnd.
------------------------------------------------------------------------
[2020-01-24 09:06:18] kinimodmeyer at gmail dot com
Description:
------------
SELECT EXISTS in the query fills up the memory. see test-script below.
Test script:
---------------
<?php
$start = microtime(true);
var_dump(memory_get_usage(true));
for ($i = 0;$i<100000;$i++) {
$sql = 'SELECT 1
FROM article
WHERE a_nr =
"'.$mysqli->real_escape_string(bin2hex(random_bytes(10))).'"
LIMIT 1';
$mysqli->query($sql);
}
var_dump(microtime(true) - $start, memory_get_usage(true));
$start = microtime(true);
for ($i = 0;$i<100000;$i++) {
$sql = 'SELECT EXISTS
(
SELECT 1
FROM article
WHERE a_nr =
"'.$mysqli->real_escape_string(bin2hex(random_bytes(10))).'"
LIMIT 1
)';
$mysqli->query($sql);
}
var_dump(microtime(true) - $start, memory_get_usage(true));
Expected result:
----------------
memory1: int(4194304)
memory2: int(4194304)
momory3: int(28311552)
Actual result:
--------------
php memory fills up in the second query
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=79163&edit=1