Library throws randomly [Error: Failed to execute prepared statement]

[matthiasfeist]

Hi DuckDB team!

I have a weird and hard to reproduce error. I'm running a relatively simple query in a loop (the query is easily executed over 10000 times in a short time).
From time to time, the duckDB node API randomly throws an error. I can't find a pattern because sometimes it works for the whole script, sometimes it's every 100 loop-iteration that the error is thrown.

The error in itself is also not very helpful. When I console.log the Error I get:

[Error: Failed to execute prepared statement]

No stacktrace or anything. Only this.

The query I am running in this loop is also very straight forward:

    const reader = await connection.runAndReadAll(
      `SELECT i.id, adminCategoryId, mapping_coop, mapping_coop_level, consumptionMixRule, cm.useCountries, cm.useManufacturingCountry 
      FROM ingredients i LEFT JOIN consumptionMix cm ON i.consumptionMixRule = cm.id
      WHERE hideInHub = false AND adminId = $adminId LIMIT 1`,
      { adminId },
    );
    const result = reader.getRowObjects()[0];

I am using: "@duckdb/node-api": "^1.2.2-alpha.18"

I'm sorry for this shitty bugreport but I can't really re-produce it consistently and I'm not sure how to get more relevant information about the error out of this library.

[jraymakers]

Hm, interesting. That error message indicates that duckdb_execute_prepared returned an error status, but a subsequent call to duckdb_result_error to get more details about the error returned nullptr.

My best guess is that there's some sort of race that results in this problem, but I can't figure out how that would happen.

A few things to try that might either avoid the problem or tell us more:

• Inline the parameter rather using string interpolation rather than binding a SQL parameters.
• Create the prepared statement outside of the loop, and only execute it (with different parameters) in the loop.

[matthiasfeist]

I tried both those things and it still happens pretty randomly.

I also tried to generate a synthetic test using synthetic testdata from TPC-H and then I can't reproduce the bug. So I am suspecting it has something to do with the way I run my queries and the data I have in the DB...

[jraymakers]

If you inline the parameter and also don't pass any parameters (i.e. just pass the single SQL string), then it shouldn't create a prepared statement, so it shouldn't ever produce the error about failing to execute a prepared statement. Do you get a different error message in that case?

[jraymakers]

Although this occurred on alpha.18, and so doesn't have quite the same cause as #223 (which only occurs on alpha.19), it's possible it's related and the fix for that issue will address this. See #224.

[jraymakers]

Please give 1.3.0-alpha.21 a try and see if this repros for you.

[QuantBull]

The code below encountered the same issue on 1.3.0-alpha.21.

async insertBars(bars: Bar[]): Promise<void> {
    if (bars.length === 0) return;
    const stmt = await this.connection.prepare(
      `INSERT OR REPLACE INTO bars VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
    );
    try {
      for (const bar of bars) {
        stmt.bind([
          bar.symbol,
          bar.period,
          bar.datetime,
          bar.open,
          bar.high,
          bar.low,
          bar.close,
          bar.volume,
          bar.turnover
        ]);
        await stmt.run();
        stmt.clearBindings();
      }
    } finally {
      stmt.destroySync();
    }
  }

[matthiasfeist]

It solved my bug in the new 21 version :) thanks a lot!

[jraymakers]

@QuantBull I'm going to resolve this issue because it seems the original issue is addressed. Could you file a separate issue with more details about the problem you're seeing?