ERROR 1264 (22003): Out of Range Value for Column in MySQL

Error Message#

ERROR 1264 (22003): Out of range value for column 'age' at row 1

The same SQLSTATE 22003 covers every numeric type and the auto-increment ceiling. You will see it worded slightly differently depending on the column:

ERROR 1264 (22003): Out of range value for column 'price' at row 1
ERROR 1264 (22003): Out of range value for column 'id' at row 1
Warning 1264: Out of range value for column 'qty' at row 1

That last line matters. In non-strict sql_mode, MySQL turns 1264 into a warning and quietly clamps the value to the column limit, so a number you thought you saved is now something else. Under STRICT_TRANS_TABLES, on by default since MySQL 5.7, it fails instead. Keep strict mode on. A loud error beats silent data corruption every time.

What Triggers This Error#

The value does not fit the column's numeric range. Five situations produce it, and the fix is different for each:

• Value exceeds the integer type: a TINYINT got something over 127, or an INT got something over 2,147,483,647.
• Negative value into an UNSIGNED column: there is no room below zero, so -1 overflows.
• DECIMAL(p,s) precision overflow: the integer part has more digits than p − s allows.
• Arithmetic or aggregate overflow: a SUM(), a multiplication, or a running total grew past the result type.
• Auto-increment hit the ceiling: an INT primary key reached ~2.1 billion and cannot advance.

Bulk loads hit all five, just one row at a time. Find the offending row before you reach for a config change.

Fix by Scenario#

Value exceeds the integer type#

MySQL integer types are narrower than people expect. TINYINT stops at 127 signed (255 unsigned), SMALLINT at 32,767, MEDIUMINT at about 8.3 million, INT at about 2.1 billion. A counter that looked safe at launch outgrows SMALLINT faster than you would guess.

SELECT COLUMN_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'people' AND COLUMN_NAME = 'age';

If the data is real, widen the type:

ALTER TABLE people MODIFY age INT NOT NULL;

If the data is wrong, a parsing bug or a misplaced decimal, fix it at the source. Widening the column just hides the bug.

Negative value into an UNSIGNED column#

UNSIGNED columns reject anything below zero. This usually shows up on a balance or quantity column that someone declared UNSIGNED because "it's always positive," right up until the first refund.

UPDATE accounts SET balance = balance - 100 WHERE id = 7;
-- ERROR 1264 (22003): Out of range value for column 'balance' at row 1

If negatives are legitimate, drop UNSIGNED:

ALTER TABLE accounts MODIFY balance BIGINT NOT NULL DEFAULT 0;

DECIMAL(p, s) precision overflow#

DECIMAL(5,2) holds three digits before the decimal point, because p − s = 3. So 1000.00 overflows even though it reads as a small number. This catches people constantly.

INSERT INTO invoices (total) VALUES (1000.00);
-- ERROR 1264 (22003): Out of range value for column 'total' at row 1

Widen the precision to match real invoice sizes:

ALTER TABLE invoices MODIFY total DECIMAL(12,2) NOT NULL;

Arithmetic or aggregate overflow#

Sometimes the column is fine and the expression over it is not:

SELECT SUM(amount) FROM ledger;        -- result outgrows the inferred type
UPDATE counters SET n = n * 1000000;   -- the product exceeds INT

Cast the operand to a wider type before the math runs:

SELECT SUM(CAST(amount AS DECIMAL(20,2))) FROM ledger;
ALTER TABLE counters MODIFY n BIGINT NOT NULL;

Auto-increment reached the ceiling#

The 2.1-billion wall. An INT primary key runs out of values, and because auto-increment never reuses the gaps left by deleted rows, a high-churn table gets there with far fewer than 2.1 billion live rows.

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'events';

Migrate the key to BIGINT. On a large table this rewrites every row, so run it in a maintenance window or with an online schema-change tool rather than a bare ALTER in the middle of the day.

ALTER TABLE events MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

Bulk load or ETL row out of range#

In strict mode, one bad value aborts the whole LOAD DATA or multi-row INSERT. Do not turn strict mode off to make the load pass. Find the row instead:

SELECT * FROM staging_orders WHERE qty > 32767 OR qty < -32768;

Stage into a wide table, validate, then insert the good rows into production. Loosening sql_mode does not fix the load. It just pushes the corruption into clamped values you will not notice until a report looks wrong.

Prevention#

• Size integer types to the real domain, and keep STRICT_TRANS_TABLES on (the default as of MySQL 8.4) so overflows fail loudly.
• Use BIGINT for any auto-increment key that could plausibly pass a few hundred million rows over its life. The four extra bytes per row are nothing next to a midnight INT → BIGINT migration on a table that has already stopped accepting writes.
• Pick DECIMAL(p,s) precision from the largest legal amount, not the typical one. Money columns are where this bites.
• Reserve UNSIGNED for values that stay non-negative for the column's whole life. Refunds and corrections rarely qualify.
• Validate numeric ranges in the application and in schema review, before the write reaches MySQL.