php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #73491 Memory leak in simple loop through database results
Submitted: 2016-11-10 21:48 UTC Modified: 2016-11-14 12:47 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: michael at logaholic dot com Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.6.28 OS: Ubuntu 16.04
Private report: No CVE-ID: None
 [2016-11-10 21:48 UTC] michael at logaholic dot com
Description:
------------
While reading results from the database, memory usage increases on each iteration until the script runs out of memory.

I have tested this on two machines with the same result:

Dev server: PHP 5.6.24 on Ubuntu 16.04, with MariaDB 10.1.16 as provided by Xampp.

Production server: PHP 5.6.27 on CentOS with Mysql.

Calling mysqli_fetch_array() seems to cause the problem (any loop without it does not cause memory usage to keep growing)

The same script with older versions of PHP worked fine.

This is my configure line:
 './configure' '--prefix=/opt/lampp' '--with-apxs2=/opt/lampp/bin/apxs' '--with-config-file-path=/opt/lampp/etc' '--with-mysql=mysqlnd' '--enable-inline-optimization' '--disable-debug' '--enable-bcmath' '--enable-calendar' '--enable-ctype' '--enable-ftp' '--enable-gd-native-ttf' '--enable-magic-quotes' '--enable-shmop' '--disable-sigchild' '--enable-sysvsem' '--enable-sysvshm' '--enable-wddx' '--with-gdbm=/opt/lampp' '--with-jpeg-dir=/opt/lampp' '--with-png-dir=/opt/lampp' '--with-freetype-dir=/opt/lampp' '--with-zlib=yes' '--with-zlib-dir=/opt/lampp' '--with-openssl=/opt/lampp' '--with-xsl=/opt/lampp' '--with-ldap=/opt/lampp' '--with-gd' '--with-imap=/bitnami/xamppunixinstallerstackDev-linux-x64/src/imap-2007e' '--with-imap-ssl' '--with-gettext=/opt/lampp' '--with-mssql=shared,/opt/lampp' '--with-pdo-dblib=shared,/opt/lampp' '--with-sybase-ct=/opt/lampp' '--with-mysql-sock=/opt/lampp/var/mysql/mysql.sock' '--with-oci8=shared,instantclient,/opt/lampp/lib/instantclient' '--with-mcrypt=/opt/lampp' '--with-mhash=/opt/lampp' '--enable-sockets' '--enable-mbstring=all' '--with-curl=/opt/lampp' '--enable-mbregex' '--enable-zend-multibyte' '--enable-exif' '--with-bz2=/opt/lampp' '--with-sqlite=shared,/opt/lampp' '--with-sqlite3=/opt/lampp' '--with-libxml-dir=/opt/lampp' '--enable-soap' '--with-xmlrpc' '--enable-pcntl' '--with-mysqli=mysqlnd' '--with-pgsql=shared,/opt/lampp/' '--with-iconv=/opt/lampp' '--with-pdo-mysql=mysqlnd' '--with-pdo-pgsql=/opt/lampp/postgresql' '--with-pdo_sqlite=/opt/lampp' '--with-icu-dir=/opt/lampp' '--enable-fileinfo' '--enable-phar' '--enable-zip' '--enable-intl' 'CC=gcc' 'CFLAGS=-I/opt/lampp/include/c-client '-I/opt/lampp/include/libpng' '-I/opt/lampp/include/freetype2' '-O3' '-fPIC' '-L/opt/lampp/lib' '-I/opt/lampp/include' '-I/opt/lampp/include/ncurses'' 'LDFLAGS=-Wl,--rpath '-Wl,/opt/lampp/lib' '-L/opt/lampp/lib' '-I/opt/lampp/include' '-L/opt/lampp/lib' '-L/opt/lampp'' 'CPPFLAGS=-I/opt/lampp/include/c-client '-I/opt/lampp/include/libpng' '-I/opt/lampp/include/freetype2' '-O3' '-fPIC' '-L/opt/lampp/lib' '-I/opt/lampp/include' '-I/opt/lampp/include/ncurses'' 'CXX=g++' 'CXXFLAGS=-I/opt/lampp/include/c-client '-I/opt/lampp/include/libpng' '-I/opt/lampp/include/freetype2' '-I/opt/lampp/include/ncurses' '-O3' '-L/opt/lampp/lib' '-I/opt/lampp/include'' 



Test script:
---------------
$sql = "select field from mytable limit 5000";
$data =  mysqli_query($db->_connectionID, $sql);
$n=0;
while ($row = mysqli_fetch_array($data)) {
	$n++;
	echo "$n )  ";
	echo number_format((memory_get_usage() / 1024)/1024, 2);
	echo " mb<br>";
}


//Note I am using 3 echo statements as I read somewhere that concatenating can cause memory problems




Expected result:
----------------
I expect the memory usage to stay pretty constant while the script runs. 

Actual result:
--------------
The actual output of the test script looks like this:

1 ) 7.22 mb
2 ) 7.22 mb
3 ) 7.22 mb
4 ) 7.23 mb 
...
...
4998 ) 14.08 mb
4999 ) 14.09 mb
5000 ) 14.09 mb 

Memory usage just keeps growing steadily with each line.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-11-11 19:48 UTC] mpoletto at gmail dot com
We had the same problem. We tested with the script reported and the memory leak occurs too. Our version was 5.6.27 on CentOS 7.
 [2016-11-13 17:10 UTC] bwoebi@php.net
-Status: Open +Status: Not a bug
 [2016-11-13 17:10 UTC] bwoebi@php.net
This is using buffered queries, which store the whole resultset including the decoded data (decoded data is stored as soon as it has been decoded the first time). [This allows for mysqli_result::data_seek() for example.]

This isn't leaking memory, the memory is tied to the mysqli_result object. Once it's freed, all associated memory is properly released too.

If your code is consuming too much memory you might want to look into unbuffered queries: https://secure.php.net/mysqlinfo.concepts.buffering
 [2016-11-14 12:47 UTC] michael at logaholic dot com
Wow, thank you very much for pointing that out. So for me it wasn't the PHP version that caused the change in memory behaviour, but switching to the mysqlnd driver was the culprit.

As the docs say, only with mysqlnd memory is added in PHP. Good to know.

Thanks again.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 13:01:31 2024 UTC