🚨 MySQL Error Guide: 'Out of range value for column' — Fix ERROR 1264

Overview

ERROR 1264 is raised when a numeric value exceeds the range the target column can hold, under strict SQL mode:

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

With strict mode active (the default), the write is rejected. Without it, MySQL would clamp the value to the column’s maximum and warn — so 1264 frequently appears after enabling strict mode, revealing values that had been silently capped for a long time. The classic case is an INT counter that has grown past its ceiling.

Symptoms

• INSERT/UPDATE fails on a numeric column with “Out of range value.”
• A counter or ID that worked for years suddenly rejects writes near a round number (2,147,483,647 for signed INT, 4,294,967,295 for UNSIGNED INT).
• Storing a negative number into an UNSIGNED column fails.
• DECIMAL values fail when the total or fractional digits exceed the declared precision/scale.
• The error appeared right after enabling strict mode or migrating.

Common Root Causes

• Integer overflow — a value beyond the type’s range: TINYINT (±127), SMALLINT (±32,767), MEDIUMINT (±8.3M), INT (±2.1B), or their UNSIGNED variants.
• Negative value into an UNSIGNED column — the minimum is 0, so any negative is out of range.
• DECIMAL precision/scale exceeded — DECIMAL(5,2) can hold at most 999.99; 1000.00 overflows.
• Arithmetic overflow — a computed expression (e.g. a * b) exceeding the column type even if the inputs fit.
• A type chosen too small at design time for a value that grows (view counts, byte totals, cents-based money).
• Strict mode now rejecting what used to be clamped.

Diagnostic Workflow

Inspect the column’s exact type and signedness:

SHOW CREATE TABLE stats\G
SELECT column_name, column_type, data_type
FROM information_schema.columns
WHERE table_name = 'stats' AND column_name = 'view_count';

column_type shows both the type and whether it is unsigned (e.g. int unsigned). Compare the offending value against the type’s limits — signed INT tops out at 2,147,483,647:

SELECT 2147483647 AS signed_int_max, 4294967295 AS unsigned_int_max;

Confirm strict mode is what is enforcing rejection:

SELECT @@SESSION.sql_mode;   -- STRICT_TRANS_TABLES makes 1264 an error, not a clamp

To find a counter approaching its ceiling before it fails in production:

SELECT MAX(view_count) AS current_max, 2147483647 AS int_limit
FROM stats;

Example Root Cause Analysis

A view-tracking service began failing at peak traffic:

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

SHOW CREATE TABLE showed view_count INT (signed). SELECT MAX(view_count) returned 2,147,483,646 — one below the signed INT maximum. The counter had simply grown past what a 32-bit signed integer can hold. Because strict mode was on, the write was correctly rejected instead of silently wrapping or clamping. The fix was ALTER TABLE stats MODIFY view_count BIGINT UNSIGNED, which raises the ceiling to over 18 quintillion, followed by application validation. The team also audited other INT counters with the MAX() query to catch the next one before it hit the wall.

Prevention Best Practices

• Choose numeric types with headroom for growth: BIGINT for counters and IDs that can grow large, UNSIGNED when values are never negative (doubling the positive range).
• Store money as DECIMAL with adequate precision, or as integer cents in a BIGINT, never as an undersized DECIMAL.
• Validate numeric input ranges at the application boundary so users get a clear error, not a raw 1264.
• Keep strict mode on so overflow is rejected rather than silently clamped to a wrong value.
• Periodically audit high-growth counters against their type limits with MAX() and migrate to BIGINT well before the ceiling.
• Watch computed columns and aggregates for arithmetic overflow, not just stored inputs.

Quick Command Reference

SHOW CREATE TABLE stats\G                            -- type + signed/unsigned
SELECT @@SESSION.sql_mode;                           -- strict = reject not clamp
SELECT MAX(view_count), 2147483647 FROM stats;       -- distance to the ceiling
ALTER TABLE stats MODIFY view_count BIGINT UNSIGNED; -- raise the range

Conclusion

ERROR 1264 means a number did not fit its column’s range, most often an INT counter that outgrew 2.1 billion or a negative value forced into an UNSIGNED type. The fix is to migrate to a larger type — usually BIGINT or an UNSIGNED variant — and validate ranges at the application boundary. Keep strict mode enabled so overflow is rejected loudly instead of clamped to a silently wrong value, and audit growing counters before they reach the wall.