SQLSTATE[22003] / units column overflow during Whisper transcription

[SoleroTG]

Which version of integration_openai are you using?

4.5.1

Which version of Nextcloud are you using?

v33.0.6

Which browser are you using? In case you are using the phone App, specify the Android or iOS version and device please.

Firefox Ubuntu Snap 152.0.1

Describe the Bug

During audio transcription using a local Whisper backend (such as LocalAI), the background job (cron.php) crashes and discards the transcription result.

In nextcloud.log, the following two error logs are visible:

1. Could not create quota usage for user: test and quota type: 2. Error: An exception occurred while executing a query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'units' at row 1
2. Can not summarize call recording as no TextToTextSummary task provider is available (This secondary warning is raised because the transcription fails/crashes before completion, and/or llm_provider_enabled is set to 0).

Root Cause Analysis

1. Numeric Overflow via Whisper Hallucination / Silence
   In lib/Service/OpenAiAPIService.php, the audio duration is calculated by fetching the end timestamp of the last segment in the response:

if (isset($response['segments'])) {
    $audioDuration = intval(round(floatval(array_pop($response['segments'])['end'])));
}

When local backends encounter silence or audio artifacts, they can loop and produce extremely high timestamps or return inf / nan.

• intval(INF) on a 64-bit PHP environment evaluates to PHP_INT_MAX (9223372036854775807).
• The database column units in the oc_openai_quota_usage table is defined as INT (signed 32-bit integer, max value 2147483647) via Version010100Date20231008173034.php.
• Inserting PHP_INT_MAX into this column triggers a SQLSTATE[22003] numeric value out of range database error.

2. Broken Exception Handling
   The insertion is wrapped in a try-catch block:

try {
    $this->createQuotaUsage($userId ?? '', Application::QUOTA_TYPE_TRANSCRIPTION, $audioDuration);
} catch (DBException $e) {
    ...
}

This block fails to catch the database exception because:

• Casing mismatch: DBException is imported as use OCP\Db\Exception as DBException; with a lowercase b, while the Nextcloud Core class namespace is OCP\DB\Exception.
• Class mismatch: When the QueryBuilder fails during executeStatement(), it throws a Doctrine-level exception (\Doctrine\DBAL\Exception\DriverException), which does not inherit from \OCP\DB\Exception.
• The exception goes uncaught, causing the background job to crash, and the finished transcript is lost.

Note: This unsafe catch (DBException $e) pattern is present in 6 locations inside OpenAiAPIService.php (lines 506, 575, 608, 941, 983, 1065).

Expected Behavior

1. Sanitize $audioDuration: The calculated audio duration should be sanitized to ensure it is finite and fits within a signed 32-bit integer.
2. Robust Exception Catching: Database logging failures must never crash the main application/transcription flow. The service should catch generic \Throwable instead of DBException.

To Reproduce

1. Configure Nextcloud with the integration_openai app using a local Whisper backend (like LocalAI).
2. Trigger the transcription of an audio file containing background noise/silence which causes the Whisper backend to hallucinate high segment end timestamps.
3. Run php cron.php or wait for the background job to process the transcription.
4. The cron job will crash with the SQLSTATE[22003] error, and the transcript will be lost.

Suggested Fixes

1. Limit $audioDuration bounds in OpenAiAPIService.php:

if (!is_finite($audioDuration) || $audioDuration < 0 || $audioDuration > 2147483647) {
    $audioDuration = 0; 
}

2. Catch \Throwable in all 6 quota logging locations inside OpenAiAPIService.php:

try {
    $this->createQuotaUsage(...);
} catch (\Throwable $e) {
    $this->logger->warning('Could not create quota usage: ' . $e->getMessage());
}

[lukasdotcom]

Hi @SoleroTG,

The first fix may make sense, but wouldn't fix your issue. I wouldn't set the audioDuration to 0 as it would suppress an error the admin should see in some way. Also invalid numbers such as infinity would not create PHP_INT_MAX (It would be 0).

The Second fix won't change anything as that exception was caught as seen with the log line.

[SoleroTG]

Hi @lukasdotcom ,

Thanks for the super quick response and for taking a look at this!

You're absolutely right on both points:

1. intval(INF) evaluates to 0 in PHP (and same for floatval("inf")), so literal infinity isn't what's causing the DB to choke here.
2. The warning log in nextcloud.log shows that the exception was indeed caught by the catch (DBException $e) block.

However, the reason the background job still crashes and rolls back the whole transcription is a bit sneaky:

1. Large Finite Numbers from Hallucination Loops

When a Whisper backend gets stuck in a hallucination loop, it doesn't return INF. Instead, it keeps appending segments and returns extremely large finite float timestamps (for example, 9999999999.0 seconds).

• On 64-bit systems, PHP converts intval(round(9999999999.0)) to 9999999999.
• But the units column in the database is a signed 32-bit INT (which maxes out at 2147483647).
• Because 9999999999 exceeds the 32-bit signed range, the database rejects the insert with the SQLSTATE[22003] Out of range error.

2. Tainted Transactions Abort the Whole Job

Since Nextcloud runs background jobs inside a database transaction:

• On PostgreSQL, any failed query immediately aborts the active transaction. Even though our PHP code catches the exception and tries to carry on, any subsequent DB write (like saving the finished transcription text) fails with SQLSTATE[25P02]: In failed sql transaction. This subsequent error is not caught, causing the job to crash and roll back everything.
• On MySQL/MariaDB, a failed query can also prevent the transaction from committing successfully at the end of the job execution.

So, catching the exception in PHP is great, but once the database query itself fails, the active transaction is already tainted. The only way to keep the transaction alive and actually save the transcript is to prevent the insert from failing by sanitizing the duration value beforehand (like in the first fix).

What do you think?

Cheers,
Solero

[lukasdotcom]

Hi @SoleroTG,

I've created a pr that clamps the numbers (#395), but the large finite numbers are likely to come from a broken backend and I'd report it to them as whisper should not hallucinate timestamps.

[SoleroTG]

Hi @lukasdotcom ,

Thank you so much for creating the PR (#395) so quickly! Clamping the numbers will be a lifesaver to prevent the database transactions from rolling back during transcription.

And thank you for the hint about the backend. I did some research and indeed, this is a known issue with Whisper engines (like whisper.cpp used in LocalAI) where silence/hallucinations trigger endless timestamp loops (e.g., in whisper.cpp issues #1881 and #1511). I'll look into configuring VAD or adjusting the temperature parameters on my LocalAI setup to prevent the backend from getting stuck in the first place.

I really appreciate your help and the lightning-fast fix!

Cheers,
Solero

[kyteinsky]

@SoleroTG
would you mind logging the whole $response object? It would be interesting to see if both $response['duration'] and segment's end return incorrect values.
a simple patch for this is given below:

diff --git a/lib/Service/OpenAiAPIService.php b/lib/Service/OpenAiAPIService.php
index 7519d26..a772f9b 100644
--- a/lib/Service/OpenAiAPIService.php
+++ b/lib/Service/OpenAiAPIService.php
@@ -829,6 +829,7 @@ class OpenAiAPIService {
                        throw new Exception($this->l10n->t('Unknown audio trancription error'), Http::STATUS_INTERNAL_SERVER_ERROR);
                }
 
+               $this->logger->warning('transcription response: ' . json_encode($response));
                // Extract audio duration from response and store it as quota usage:
                if (isset($response['segments'])) {
                        $audioDuration = intval(round(floatval(array_pop($response['segments'])['end'])));

how to apply it: https://docs.nextcloud.com/server/stable/admin_manual/issues/applying_patch.html#applying-a-patch

[SoleroTG]

@kyteinsky ,

I do not mind, but over the time of one our I wasn't able to restore the stetup as I experimented with non local providers before.
Somehow nextcloud caches old entries and is very resistant when switching back to the local provider.

Sorry

[SoleroTG]

I am puzzled. After more debugging I found out that nextcloud appears to ignore the frontend setting of doing the transcription and sets it to '0' after every API endpoint change. I switched it manually with occ config:app:set integration_openai stt_provider_enabled --value="1" and after that I got a working locally done transcription.