Magento Checkout Failing at memory_limit: A Full Table Scan in Stripe 4.6

Some performance problems make a store slow. This one made checkout fail. After an upgrade of the Stripe payments module for Magento to version 4.6, between 28 and 44 percent of order placements on one of the Adobe Commerce stores we support died at PHP’s 2 GB memory limit. Customers pressed the pay button and got an error page.

The cause turned out to be one line in the Stripe module that made Magento load every invoice in the database, on every affected checkout. Stripe has since fixed it, in version 4.6.5. This post covers how we found it, because the method works for any Magento memory limit error, and shares the small diagnostic patch that made the difference.

Why Memory Limit Errors Are Hard to Trace

A PHP memory exhaustion error names the place where the last allocation happened, not the place that used the memory. In a Magento request that is usually somewhere unrelated, deep in the framework. On this store it was worse: the Redis session save handler often masked the error entirely.

The stack traces did agree on one thing. The memory was being consumed inside Zend_Db_Statement_Pdo::fetchAll(), the method that reads a whole database result set into a PHP array. So a single query was returning far more rows than it should. The question was which one.

A Diagnostic Patch for fetchAll()

We added a temporary patch to the magento/zend-db library. Before fetchAll() starts buffering rows, it records the SQL of the query in a static property and clears it again once the rows are in. A shutdown function, registered once per request, checks whether the request ended with a fatal error while a query was still buffering. If so, it writes the SQL and the peak memory to the PHP error log.

The patch changes no behaviour. It costs one string assignment per query and only writes to the log when a request has already died. It is available for download at the end of this post, and we would apply it again for any Magento memory limit error that points at the database layer.

What the Logs Showed

The slow query log and the new error log pointed at the same query:

SELECT `main_table`.* FROM `sales_invoice` AS `main_table`

No WHERE clause. Every invoice the store had ever created: 358,288 rows and 119 MB on the wire, which PHP then turned into objects until it hit the 2 GB limit. The query ran 1,074 times in seven days. In a six-hour window, every one of the 16 failed checkouts contained exactly one of these scans.

The Root Cause in the Stripe Module

For redirect-based payment methods, the Stripe module reverses an open invoice that Magento registers during payment. That code runs while the payment is still being processed, before the order has been saved, so the order has no ID yet.

Magento protects against exactly that situation. When the invoice collection is filtered by an order without an ID, setOrderFilter() adds no WHERE clause. Instead it marks the collection as already loaded and empty, so no query ever runs.

The Stripe module then cleared the cached invoice collection, with a comment saying the line was there for its test suite:

// Clear cached invoices for the test suite
$order->setInvoiceCollection($order->getInvoiceCollection()->clear());

clear() resets the loaded flag. The collection still had no filter, and it was written back onto the order. The next time anything in the request read the order’s invoices, Magento ran the query for real, without a filter, and loaded every invoice in the database.

The Fix

The fix was one line: apply the order filter again after clearing the collection.

$order->getInvoiceCollection()->clear()->setOrderFilter($order);

On an unsaved order, setOrderFilter() marks the collection as loaded and empty again, so no query runs. Once the order has an ID, it adds a proper order_id filter.

Shortly afterwards, Stripe released version 4.6.5 of stripe/module-payments. Its changelog lists a fix for a PHP memory exhaustion error during checkouts with redirect-based payment methods, and the vendor code now re-applies the order filter itself. Our patch no longer applied, so we removed it. If you run any 4.6 version before 4.6.5, upgrade rather than patch.

What We Took From It

• Memory errors need their own instrumentation. The error message points at the wrong place, and the session handler can hide it completely. Log the query, not just the stack trace.
• Check the slow query log for queries without a WHERE clause. A full scan of sales_order, sales_invoice or quote inside a customer request is almost always a bug.
• Payment module upgrades are checkout changes. Test redirect-based payment methods specifically, on a database with production-sized order and invoice tables, not an empty staging copy.
• Remove your patch when the vendor fixes the bug. Ours stopped applying with the upstream release, which is the best possible signal.

This is one entry in our collection of Magento performance patches. If checkout errors or memory limits are showing up in your logs, it is the kind of problem our Magento support team handles.

Download the Diagnostic Patch

The patch targets magento/zend-db 1.16.3 and is meant to be applied temporarily, while you investigate. It is provided as-is: read it, test it on staging, and remove it when you have your answer.

magento-log-sql-on-oom-in-fetchall.patch on GitHub · raw file

All our patches, with a short guide to applying them, are in the paxento/magento-patches repository on GitHub.

The full diff:

--- a/library/Zend/Db/Statement/Pdo.php
+++ b/library/Zend/Db/Statement/Pdo.php
@@ -268,6 +268,18 @@
     {
         return new IteratorIterator($this->_stmt);
     }
+
+    /**
+     * SQL of the fetchAll() that is currently buffering rows, so a fatal shutdown can name it.
+     *
+     * @var string|null
+     */
+    private static $_bufferingSql = null;
+
+    /**
+     * @var bool
+     */
+    private static $_fatalReporterRegistered = false;
 
     /**
      * Returns an array containing all of the result set rows.
@@ -281,23 +293,60 @@
     {
         if ($style === null) {
             $style = $this->_fetchMode;
+        }
+        if (!self::$_fatalReporterRegistered) {
+            self::$_fatalReporterRegistered = true;
+            register_shutdown_function([__CLASS__, 'reportFatalWhileBuffering']);
         }
+        self::$_bufferingSql = $this->_stmt->queryString;
         try {
             if ($style == PDO::FETCH_COLUMN) {
                 if ($col === null) {
                     $col = 0;
                 }
-                return $this->_stmt->fetchAll($style, $col);
+                $result = $this->_stmt->fetchAll($style, $col);
             } else {
-                return $this->_stmt->fetchAll($style);
+                $result = $this->_stmt->fetchAll($style);
             }
         } catch (PDOException $e) {
+            self::$_bufferingSql = null;
             #require_once 'Zend/Db/Statement/Exception.php';
             throw new Zend_Db_Statement_Exception($e->getMessage(), $e->getCode(), $e);
         }
+        self::$_bufferingSql = null;
+        return $result;
     }
 
     /**
+     * Names the query that was buffering rows when the request died, for memory-limit diagnostics.
+     *
+     * A memory exhaustion inside fetchAll() is reported by PHP against whichever code the last
+     * allocation happened in, and the session save handler may mask it entirely, so the query
+     * itself never reaches the log without this.
+     *
+     * @return void
+     */
+    public static function reportFatalWhileBuffering()
+    {
+        if (self::$_bufferingSql === null) {
+            return;
+        }
+
+        $error = error_get_last();
+        if ($error === null || $error['type'] !== E_ERROR) {
+            return;
+        }
+
+        ini_set('memory_limit', '-1');
+        error_log(sprintf(
+            'Fatal while buffering a result set (peak %d MB): %s -- SQL: %s',
+            memory_get_peak_usage(true) / 1048576,
+            $error['message'],
+            self::$_bufferingSql
+        ));
+    }
+
+    /**
      * Returns a single column from the next row of a result set.
      *
      * @param int $col OPTIONAL Position of the column to fetch.