Bug #70949 [Opn]: SQL Result Sets With NULL Can Cause Fatal Memory Errors

Edit report at https://bugs.php.net/bug.php?id=70949&edit=1 ID: 70949 Updated by: laruence@php.net Reported by: s7g2vp2 at yahoo dot co dot uk Summary: SQL Result Sets With NULL Can Cause Fatal Memory Errors Status: Open Type: Bug Package: MySQLi related Operating System: Windows Server 2012 PHP Version: 7.0.0RC7 -Assigned To: +Assigned To: laruence Block user comment: N Private report: N New Comment: can you provide a test script and a sh to fill database ? Previous Comments: ------------------------------------------------------------------------ [2015-11-22 17:29:21] s7g2vp2 at yahoo dot co dot uk I have spent some more time investigating this and my original diagnoses is wrong as now, I cannot fix the problem using the method I originally stated. If I check the Apache error log I see that PHP has crashed with a fatal memory error: PHP Fatal error: Allowed memory size of 805306368 bytes exhausted (tried to allocate 1028538193632 bytes) The crash occurs in a loop that concatenates a php variable using data fetched from the result set. The SQL Statement is: SELECT sr.id, sr.groupcode, (SELECT group_concat(sr2.groupcode) FROM SHIPPINGRATES sr2 WHERE sr2.parentid = sr.id), sr.companycode, sr.code, sr.shippingmethodcode, sr.shippingzonecode, sm.name, sz.name, sr.productcode, pr.name, sr.info, sr.rate, sr.active FROM SHIPPINGRATES sr LEFT JOIN SHIPPINGMETHODS sm ON sm.code = sr.shippingmethodcode LEFT JOIN SHIPPINGZONES sz ON sz.code = sr.shippingzonecode LEFT JOIN PRODUCTS pr ON pr.code = sr.productcode WHERE sr.parentid = 0 ORDER BY sr.companycode, sr.code LIMIT 100 OFFSET 0 The 3rd column of the result set (group_concat) returns NULL for rows 1, 2, 3 and 7. It is during the processing of row 7 in the loop that causes the crash. The function includes a line that sets the cursor to read-only: $stmt->attr_set(MYSQLI_STMT_ATTR_CURSOR_TYPE, MYSQLI_CURSOR_TYPE_READ_ONLY); If I remove that line the crash does not occur. If I use IFNULL in the SQL statement to return an empty value instead of a NULL, the crash does not occur. If I just return NULL for the 3rd column the crash does not occur. so... The problem appears to be related to NULL, group_concat and read-only cursors. My guess is this is some type of memory corruption. I have tried disabling opcache but that has not made any difference. I can re-create this in a standalone script but it also requires a database. I can provide a script and MySQL dump but I don't know how I should attach these. Regards, Kev. ps. I am running 64 bit Apache 2.4.16 from Apache Lounge and the 64 bit version of PHP7 RC7 via the Apache module. ------------------------------------------------------------------------ [2015-11-21 00:24:11] rasmus@php.net Are you getting a MySQL error? A common cause of this is that you are sending something than a positive integer as the offset. For example, if you are doing something like: $offset = ceil($page * 10); $offset is now a float because ceil() always returns a float and the driver will pass that as something like "10.0" which will make offset fail. Make sure you cast any offsets you substitute into your query to an int first. eg. $offset = (int)ceil($page * 10); ------------------------------------------------------------------------ [2015-11-20 14:39:32] laruence@php.net Thank you for this bug report. To properly diagnose the problem, we need a short but complete example script to be able to reproduce this bug ourselves. A proper reproducing script starts with <?php and ends with ?>, is max. 10-20 lines long and does not require any external resources such as databases, etc. If the script requires a database to demonstrate the issue, please make sure it creates all necessary tables, stored procedures etc. Please avoid embedding huge scripts into the report. ------------------------------------------------------------------------ [2015-11-20 11:10:42] s7g2vp2 at yahoo dot co dot uk Description: ------------ While testing our app using PHP7RC7 we noticed some of the SQL statements failed to return a result set. After some investigation it appears that the syntax LIMIT x OFFSET y causes a problem. The alternative syntax LIMIT y,x does appear to work. We are using MySQLi driver and prepared statements. The LIMIT & OFFSET values are part of the statement and not bound. All statements work in previous versions of PHP and also work in MySQL Workbench. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=70949&edit=1