Application Launcher

Date Published: February 6, 2025

Introduction

This article provides guidance on resolving the SQLSTATE[22003]: Numeric Value Out of Range Error: 1264 which occurs when a value being inserted or updated exceeds the allowable range for the column's data type.

Symptoms

When a particular operation attempts to INSERT or UPDATE data in a table, the operation fails, and the database system returns an error message similar to:

SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'column_name' at row y: INSERT INTO 'table_name'

Cause

The error is caused by one of the following conditions:

A numeric value exceeds the maximum or minimum size that the column can hold.
The value might be too large or too small for the assigned column data type like TINYINT, SMALLINT, or INT.

Prerequisites

Ensure you have access to the database where the operation is being performed.
Identify the column and row that trigger the error.

Solution

Check Data Types and Ranges:

Determine the data type and value range of the column that is causing the issue. You can discover the data type and its range using the following query:

EXPLAIN <table_name>;

Check Next Auto Increment Value If Applicable:

If the column in question is an AUTO_INCREMENT column, check to make sure the next AUTO_INCREMENT value falls within the allowable range for the column data type. Retrieve the current AUTO_INCREMENT value with:

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "<table_name>";

If you find that the AUTO_INCREMENT value is at or near its maximum value, consider the following options:

Alter Table Structure If Necessary:

If the range of the current data type is not sufficient, you may need to adjust the column structure to use a larger data type. Change the column to accommodate larger values by executing:

ALTER TABLE <table_name> MODIFY <column_name> BIGINT;

Truncate the Table:

In cases where it's appropriate and you can afford to lose the data, truncating the table will reset the AUTO_INCREMENT value. Warning: This will delete all the data in the table. Ensure you have a backup before proceeding.

TRUNCATE TABLE <table_name>;

Example Resolution

Encountered error:

SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for co
lumn 'fid' at row 1: INSERT INTO "flood"

Resolution Steps:

Checked data type and auto-increment status with EXPLAIN flood;, revealing 'fid' as an INT with auto_increment.

mysql> EXPLAIN flood;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| fid        | int(11)      | NO   | PRI | NULL    | auto_increment |
| event      | varchar(64)  | NO   | MUL |         |                |
| identifier | varchar(128) | NO   |     |         |                |
| timestamp  | int(11)      | NO   |     | 0       |                |
| expiration | int(11)      | NO   | MUL | 0       |                |
+------------+--------------+------+-----+---------+----------------+

Confirmed auto_increment nearing its limit:mysql

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = "flood";

Result: 2147483647 (max for INT).

Reset auto_increment by truncating the 'flood' table:

TRUNCATE TABLE flood;

Verified reset:

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = "flood";

Result: 1.

Truncation resolved the issue, allowing new rows to be inserted without error.

Additional Considerations

Modifying a table structure or data type should be done with caution. Always back up your database before making structural changes. If you continue to experience problems, please reach out to Acquia support for further assistance.