SNOW-1887475: curl_easy_perform() failed: Out of memory

[android-rewstar]

Hi. I'm having a problem with curl I think. I have the following problem. The query is large and I can't share it here. I've been building the driver locally so I can test changes to the driver if needed. The problem happens on my windows dev machine and on the php:8.2-apache docker container that it runs on so it's not limited to an operating system. Thanks.

1. What version of PDO/PHP driver are you using?
   Snowflake PHP PDO Driver: 8.2.18-3.0.3
   PHP 8.2.27 (same issue on Windows and Linux)

2. What operating system and processor architecture are you using?
   Snowflake C/C++ API: 1.1.0, OS: Windows, OS Version: 10.0-x86_64

3. What version of C/C++ compiler are you using?
   Not sure, VS installer says 16.11.42 for the

4. What did you do?
   I was running a large query. The query works fine but every so often it fails. It seems to work the first time.

this is the PHP, the connection string includes a private key file location. I've tried a few different ways of writing this php and they all result in the same error.
$db = new PDO($connectionstring, $username, "");
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$statement = $db->prepare($sql);
$statement->execute($data);
$results = [];
foreach ($statement as $i => $row) {
$results[] = $row;
}
$statement->closeCursor();

5. What did you expect to see?
   No error and query results

6. Can you set logging to DEBUG and collect the logs?

2025-01-13 08:59:41.129 INFO C client.c 841: Snowflake C/C++ API: 1.1.0, OS: Windows, OS Version: 10.0-x86_64
2025-01-13 08:59:41.129 INFO C client.c 489: Connecting to GLOBAL Snowflake domain
2025-01-13 08:59:41.129 DEBUG C client.c 518: application name: PDO
2025-01-13 08:59:41.129 DEBUG C client.c 519: application version: 8.2.18-3.0.3
2025-01-13 08:59:41.129 DEBUG C client.c 520: authenticator: snowflake_jwt
2025-01-13 08:59:41.129 DEBUG C client.c 521: user: ****
2025-01-13 08:59:41.129 DEBUG C client.c 522: password: ****
2025-01-13 08:59:41.129 DEBUG C client.c 524: priv_key_file: ****
2025-01-13 08:59:41.129 DEBUG C client.c 525: jwt_timeout: 60
2025-01-13 08:59:41.129 DEBUG C client.c 526: jwt_cnxn_wait_time: 10
2025-01-13 08:59:41.129 DEBUG C client.c 532: port: 443
2025-01-13 08:59:41.129 DEBUG C client.c 534: region: (null)
2025-01-13 08:59:41.129 DEBUG C client.c 535: database: ****
2025-01-13 08:59:41.129 DEBUG C client.c 536: schema: ****
2025-01-13 08:59:41.129 DEBUG C client.c 537: warehouse: ****
2025-01-13 08:59:41.129 DEBUG C client.c 538: role: ****
2025-01-13 08:59:41.129 DEBUG C client.c 539: protocol: https
2025-01-13 08:59:41.129 DEBUG C client.c 540: autocommit: true
2025-01-13 08:59:41.129 DEBUG C client.c 541: insecure_mode: false
2025-01-13 08:59:41.129 DEBUG C client.c 542: ocsp_fail_open: false
2025-01-13 08:59:41.129 DEBUG C client.c 543: timezone: (null)
2025-01-13 08:59:41.129 DEBUG C client.c 544: login_timeout: 300
2025-01-13 08:59:41.129 DEBUG C client.c 545: network_timeout: 0
2025-01-13 08:59:41.129 DEBUG C client.c 546: retry_timeout: 300
2025-01-13 08:59:41.129 DEBUG C client.c 547: retry_count: 7
2025-01-13 08:59:41.129 DEBUG C client.c 548: qcc_disable: false
2025-01-13 08:59:41.129 DEBUG C client.c 549: include_retry_reason: true
2025-01-13 08:59:41.133 DEBUG C connection.c 696: URL: ****
2025-01-13 08:59:41.238 ERROR C http_perform.c 403: curl_easy_perform() failed: Out of memory
2025-01-13 08:59:41.238 ERROR C client.c 970: No response
2025-01-13 08:59:41.238 DEBUG PDO snowflake_driver.c 45: file=ext\pdo_snowflake\snowflake_driver.c line=821
2025-01-13 08:59:41.238 ERROR PDO snowflake_driver.c 52: connection error
2025-01-13 08:59:41.238 ERROR PDO snowflake_driver.c 56: error code: 240012
2025-01-13 08:59:41.238 ERROR PDO snowflake_driver.c 71: sqlstate: 08001, msg: curl_easy_perform() failed: Out of memory
2025-01-13 08:59:41.238 ERROR PDO snowflake_driver.c 74: Failed to allocate DBH

[sfc-gh-dszmolka]

hi - thanks for raising this issue. I remember typing a response but somehow now it's gone? Anyhow.

This issue looks to be similar to #318 and as of such, I believe that one could be followed for updates if/when there will be any, for this enhancement effort.
Until then, as a workaround you can consider

• using LIMIT .. OFFSET .. predicates to paginate the big query result and read it in chunks, instead of forcing it to fit inside a single array - if this is applicable for your use case
• alternatively perhaps creating temporary tables (which cease to exist automatically as Snowflake Session ceases to exist) to read 'pages' of the results into them, then iterate over the content of those temp.tables.
  Indeed cumbersome, but perhaps some relief until this capability is available.

Can you please see if this is relevant to your issue ? If so, I would suggest marking this one as closed and follow 318 instead.

[android-rewstar]

Hi. Thanks for looking at this. I can see that it could be related so I'll close this as duplicate and hopefully 318 will fix the issue.

[sfc-gh-dszmolka]

based on

• SNOW-1858311: Large memory usage (support for unbuffered queries) #318 (comment)
• SNOW-1858311: Large memory usage (support for unbuffered queries) #318 (comment)

i'm reopening this issue to investigate why we still OOM even with subset result set.

[sfc-gh-dszmolka]

Hi,

the PHP dev team took a look into this issue. For now, short version is: it looks like an application issue and not with the driver.

Long version is, given from shanedale's example :

Memory usage after snowflake retrieval: 36798616 number of records: 16488
Memory usage after creating an array: 21027928 bytes for semester: M18  numrecs: 16488
Memory usage before snowflake pdo creation: 21026112

it looks like the memory was used for the array which stored the query result returned from the driver.
After creating the array, about 15MB was freed and we think that's where the driver frees the query result. However, the array itself is not freed.

There seems to be indeed a little bit (1KB) of memory leak on the driver side, but it's not significant enough to be the root cause for the issue which is described here, and to crash the whole process with OOM.

Then they did a local test, using SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.ORDERS LIMIT 20000 in a loop, which I believe can be taken as a representative test for retrieving the big query result in 'pages'.

Here's the code used for the test:

    for ($i = 1; $i <= 20; $i++) {
        try {
            echo 'Memory usage before snowflake pdo creation: ' . memory_get_usage() . "\n";
            $pdo = new PDO($dsn, $user, $password);
            $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $query = 'SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.ORDERS LIMIT 20000';
    
            echo 'Memory usage before snowflake retrieval: ' . memory_get_usage() . "\n";
            $stmt = $pdo->prepare($query);
    
            $stmt->execute();
            $records = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo 'Memory usage after snowflake retrieval: ' . memory_get_usage() . " number of records: " . sizeof($records) . "\n";
        } catch (PDOException $e) {
            error_log($e->getMessage());
            echo "Snowflake Connection failed: " . $e->getMessage() . "\n";
        } finally {
            if ($stmt) {
                $stmt = null;
                echo 'Memory usage after stmt cleaning: ' . memory_get_usage() . "\n";
            }
            if ($pdo) {
                $pdo = null;
                echo 'Memory usage after pdo cleaning: ' . memory_get_usage() . "\n";
            }
            if ($records) {
                $records = null;
                echo 'Memory usage after array cleaning: ' . memory_get_usage() . "\n";
            }
        }
    }

and its result:

Memory usage before snowflake pdo creation: 413144
Memory usage before snowflake retrieval: 413712
Memory usage after snowflake retrieval: 22991368 number of records: 20000
Memory usage after stmt cleaning: 22990376
Memory usage after pdo cleaning: 22989840
Memory usage after array cleaning: 415992
Memory usage before snowflake pdo creation: 415992
Memory usage before snowflake retrieval: 416528
Memory usage after snowflake retrieval: 22994184 number of records: 20000
Memory usage after stmt cleaning: 22993192
Memory usage after pdo cleaning: 22992656
Memory usage after array cleaning: 418808
Memory usage before snowflake pdo creation: 418808
Memory usage before snowflake retrieval: 419344
Memory usage after snowflake retrieval: 22997000 number of records: 20000
Memory usage after stmt cleaning: 22996008
Memory usage after pdo cleaning: 22995472
Memory usage after array cleaning: 421624
Memory usage before snowflake pdo creation: 421624
Memory usage before snowflake retrieval: 422160
Memory usage after snowflake retrieval: 22999816 number of records: 20000
Memory usage after stmt cleaning: 22998824
Memory usage after pdo cleaning: 22998288
Memory usage after array cleaning: 424440
Memory usage before snowflake pdo creation: 424440
Memory usage before snowflake retrieval: 424976
Memory usage after snowflake retrieval: 23002632 number of records: 20000
Memory usage after stmt cleaning: 23001640
Memory usage after pdo cleaning: 23001104
Memory usage after array cleaning: 427256
Memory usage before snowflake pdo creation: 427256
Memory usage before snowflake retrieval: 427792
Memory usage after snowflake retrieval: 23005448 number of records: 20000
Memory usage after stmt cleaning: 23004456
Memory usage after pdo cleaning: 23003920
Memory usage after array cleaning: 430072
Memory usage before snowflake pdo creation: 430072
Memory usage before snowflake retrieval: 430608
Memory usage after snowflake retrieval: 23008264 number of records: 20000
Memory usage after stmt cleaning: 23007272
Memory usage after pdo cleaning: 23006736
Memory usage after array cleaning: 432888

we can see that even after the 20th loop, memory usage is almost the same as it was initally (432888 vs 413144) which small increase is attributed to the aforementioned memory leak, which is not the root cause of the issue you're seeing.

Hope this helps somewhat.

[cjsfj]

While I can't provide any debug output at this time...

I did encounter the exact same SQLSTATE[08001]: Client unable to establish connection: 240012 curl_easy_perform() failed: Out of memory errors upon updating to driver version 3.0.3. Version 3.0.2 did not show this same behavior.

The error would occur intermittently on queries returning one result as well as queries returning tens of thousands of results. I think I can reproduce but eventually had to move on to get it "working" again.

Driver was built with several different PHP versions over time (though I realize 8.4. support isn't officially in 3.0.2): 8.3.14, 8.3.15, 8.4.0, 8.4.1, and 8.4.2. All on Debian Bullseye.

Given that driver version 3.0.3 had the following changes in the release notes:

• Upgraded libsnowflakeclient to version 1.1.0.
• Upgraded openssl to version 3.0.15.
• Upgraded curl to version 8.10.1.

It could be any of these that caused the issue. It looks like the previous version of curl in the driver was 8.7.1.

My "fix" was to revert to driver version 3.0.2. The error went away (until I have to go to 3.0.3 or later).

Maybe this helps? Thank you for supporting this driver.

[android-rewstar]

I'll have to try reverting to a previous version as logically I can't see the queries being a problem.
We're moving those queries from synapse to snowflake and even though the queries have slight syntax differences they are the same with the same amount of results returned and the dataverse results have never caused out of memory execeptions before.

[sfc-gh-dszmolka]

this is brand new information folks which wasn't available until now; that it only happens with 3.0.3 and not the previous version. Thank you for pointing it out. We'll look into it.

[android-rewstar]

this is brand new information folks which wasn't available until now; that it only happens with 3.0.3 and not the previous version. Thank you for pointing it out. We'll look into it.

I've changed my version to 3.0.2 and I've not seen the error again.

[sfc-gh-dszmolka]

@cjsfj @android-rewstar and other folks who might be seeing this issue. The dev team is unable to reproduce this issue for now.
If you have some time , could you please help us by providing response for a couple of questions:

• what's the PHP memory limit you folks are using?
• cca. how many times do you run the 'get subset of query response' query before seeing the OOM error?
• could you please provide the php code with the test output with 3.0.2 and 3.0.3, including how the result array is being handled?
  In the existing php code we already have as example, and test output in this comment we have Memory usage after creating an array: 21027928 bytes for semester: M18 numrecs: 16488 in the output but that's not included in the php code so we don't know how that array is handled.

Thank you in advance!