Pipeline Retry Options

Overview

The retry_options clause provides fine-grained control over pipeline batch retries by specifying a JSON configuration. In SingleStore versions 9.0 and later, pipelines use retry_options by default. Exponential backoff is enabled automatically, even if the clause is not explicitly defined. Legacy retry options (e.g., max_retries_per_batch_partition) are supported for versions earlier than 9.0, but are deprecated. If both the parameters are set, retry_options takes precedence.

Syntax

SQL

CREATE PIPELINE <pipeline_name> AS LOAD DATA FS '<data_path>'
retry_options '{
  "exponential": true,
  "retry_interval": <ms>,
  "max_retry_interval": <ms>,
  "max_retries": <integer>
}'
INTO TABLE <table_name>
FIELDS TERMINATED BY '<delimiter>'
LINES TERMINATED BY '<line_terminator>';

Parameters

Key	Type	Description	Default Setting
`exponential`	`boolean`	Enables exponential backoff logic when set to `true`.	`true`
`retry_interval`	`integer`	Initial wait time (in milliseconds) before the first retry attempt.	`batch_interval`
`max_retry_interval`	`integer`	Maximum wait time (in milliseconds) between retries.	`retry_interval*10`
`max_retries`	`integer`	Total number of retries allowed before the pipeline fails.	`pipelines_max_retries_per_batch_partition`

Remarks

The retry_options value must be a valid JSON string.
Parameters in JSON can appear in any order.
If any required parameter is missing or parameter data types are incorrect, the pipeline creation fails with a configuration error.
In SingleStore versions earlier than 9.0, if both retry_options and max_retries_per_batch_partition are not specified, the system uses pipelines_max_retries_per_batch_partition.

Examples

Exponential Backoff Retry

The following example uses an exponential backoff retry strategy to handle transient failures during the loading process. Exponential backoff is a retry strategy where the wait time between retries increases progressively (for example, 1s, 2s, 4s, 8s) to reduce system load and improve recovery stability. This approach allows the system to automatically retry failed attempts with progressively longer wait times between retries, starting with a 3-second interval, up to a maximum of 50 seconds. The retry mechanism is limited to a maximum of 5 attempts, which helps to balance resilience with system efficiency.

SQL

CREATE PIPELINE sample_pipe AS LOAD DATA FS '/path/input.txt'
retry_options '{
  "exponential": true,
  "retry_interval": 3000,
  "max_retry_interval": 50000,
  "max_retries": 5
}'
INTO TABLE t
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Fixed Interval Retry

The following example uses a fixed interval retry strategy where exponential is set to false for the fixed delay. With this configuration, retry attempts occur at a consistent interval of 3 seconds, regardless of previous failures, up to a maximum of 5 retries. This fixed-delay mechanism ensures predictable retry behavior without increasing wait times between attempts.

SQL

CREATE PIPELINE example_pipe AS LOAD DATA FS '/path/input.txt'
retry_options '{
  "exponential": false,
  "retry_interval": 3000,
  "max_retry_interval": 50000,
  "max_retries": 5
}'
INTO TABLE t
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Error Handling

Common Error

SQL

ERROR 1706 (HY000): retry_options {...} could not be set. Refer documentation for correct syntax.

Causes

A required key is missing in the JSON.
The data type is incorrect.
A syntax error in the JSON (e.g., missing quotes or commas).