Bug #79163 [Ver]: SQL SELECT EXISTS fills up memory
Edit report at https://bugs.php.net/bug.php?id=79163&edit=1
ID: 79163
Updated by: nikic@php.net
Reported by: kinimodmeyer at gmail dot com
Summary: SQL SELECT EXISTS fills up memory
Status: Verified
Type: Bug
Package: MySQLi related
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
The problem is that mysqlnd uses interned strings for column names, so they will never get freed.
Previous Comments:
------------------------------------------------------------------------
[2020-12-13 21:43:30] dharman@php.net
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.
------------------------------------------------------------------------
[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
Thread (4 messages)