Bug #73491 [Nab]: Memory leak in simple loop through database results

Edit report at https://bugs.php.net/bug.php?id=73491&edit=1 ID: 73491 User updated by: michael at logaholic dot com Reported by: michael at logaholic dot com Summary: Memory leak in simple loop through database results Status: Not a bug Type: Bug Package: MySQLi related Operating System: Ubuntu 16.04 PHP Version: 5.6.28 Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2016-11-13 17:10:46] 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-11 19:48:00] 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-10 21:48:53] 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' '--wi! th-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/la! mpp/include/libpng' '-I/opt/lampp/include/freetype2' '-O3' '-f! PIC' '-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. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=73491&edit=1