php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79163 SQL SELECT EXISTS fills up memory
Submitted: 2020-01-24 09:06 UTC Modified: 2020-12-16 13:49 UTC
From: kinimodmeyer at gmail dot com Assigned:
Status: Verified Package: MySQLi related
PHP Version: Irrelevant OS:
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please — but make sure to vote on the bug!
Your email address:
MUST BE VALID
Solve the problem:
32 - 16 = ?
Subscribe to this entry?

 
 [2020-01-24 09:06 UTC] 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

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-01-24 09:47 UTC] cmb@php.net
I cannot reproduce with PHP 7.4.3-dev using mysqlnd.
 [2020-12-13 21:43 UTC] dharman@php.net
-Status: Open +Status: Verified
 [2020-12-13 21:43 UTC] 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-12-16 13:49 UTC] nikic@php.net
The problem is that mysqlnd uses interned strings for column names, so they will never get freed.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Tue Sep 21 03:03:36 2021 UTC