Workaround to Resolve ER_WARN_DATA_OUT_OF_RANGE (1264) for batch_id in Metrics Pipeline

{question}

How to resolve the error Type: ER_WARN_DATA_OUT_OF_RANGE (1264) — Out-of-range value for column batch_id in the metrics pipeline?

{question}

{answer}

Note: This issue has been resolved in a later version of Toolbox. Click here to view the release notes.
We recommend upgrading to the latest version of Toolbox to avoid encountering this issue.

You can resolve this error with the following workaround to prevent data loss:

1. Add a new column with a larger data type:

ALTER TABLE pipeline_errors ADD COLUMN batch_id_2 BIGINT;

2. Copy data from the existing column to the new column:

UPDATE pipeline_errors SET batch_id_2 = batch_id;

3. Verify that the data inbatch_id,  batch_id_2 matches.

4. Drop the original column:

ALTER TABLE pipeline_errors DROP COLUMN batch_id;

5. Rename the new column to retain the original name:

ALTER TABLE pipeline_errors CHANGE batch_id_2 batch_id BIGINT;

6. Restart the pipelines.

Note: These operations may take considerable time depending on the data volume.

{answer}