The Error
ERROR 1264 (22003): Out of range value for column 'age' at row 1
This fires when a value doesn't fit inside the column's data type range. MySQL strict mode — on by default since 5.7 — refuses to silently truncate it and throws this error instead of swallowing bad data.
The classic trigger: your age column is TINYINT (max 127), and someone sends in 300. Another common one: an INT counter column gets a value calculated by multiplying two large numbers, pushing it past 2,147,483,647.
Step 1 — Confirm the Column's Data Type
Check what type the offending column actually is:
DESCRIBE users;
-- or more detail:
SHOW COLUMNS FROM users LIKE 'age';
You'll see output like:
+-------+---------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------+---------+------+-----+---------+
| age | tinyint | YES | | NULL |
+-------+---------+------+-----+---------+
Now you know the ceiling. Here are the signed integer ranges:
TINYINT : -128 to 127
SMALLINT : -32,768 to 32,767
MEDIUMINT : -8,388,608 to 8,388,607
INT : -2,147,483,648 to 2,147,483,647
BIGINT : -9.2 × 10^18 to 9.2 × 10^18
UNSIGNED variants double the positive ceiling — TINYINT UNSIGNED goes 0–255, SMALLINT UNSIGNED goes 0–65,535.
Step 2 — Pick Your Fix
Three approaches. The right one depends on whether the column type is too narrow, the incoming data is bad, or both.
Fix A — Widen the Column Type
When legitimate values just don't fit, alter the column:
-- age can't exceed 127 as TINYINT — bump to SMALLINT UNSIGNED
ALTER TABLE users MODIFY COLUMN age SMALLINT UNSIGNED;
-- view counter hitting INT limits at 2.1B — switch to BIGINT
ALTER TABLE events MODIFY COLUMN view_count BIGINT UNSIGNED NOT NULL DEFAULT 0;
Run the failing INSERT again after the ALTER. It should pass now.
Fix B — Validate and Correct the Input
Wrong data coming in? Fix the source. Add a MySQL CHECK constraint to catch it at the database level:
-- MySQL 8.0.16+ supports CHECK constraints
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age BETWEEN 0 AND 120);
-- Inserting 300 now gives a clean constraint error
INSERT INTO users (name, age) VALUES ('Alice', 300);
-- ERROR 3819 (HY000): Check constraint 'chk_age' is violated.
Or validate in your app before the INSERT ever runs:
# Python example
if not (0 <= age <= 120):
raise ValueError(f"Age {age} is out of valid range")
Fix C — Switch to UNSIGNED When Negatives Are Impossible
Columns that store IDs, counts, or sizes will never hold a negative number. Make that explicit — and double your headroom for free:
ALTER TABLE orders MODIFY COLUMN quantity SMALLINT UNSIGNED NOT NULL DEFAULT 0;
-- SMALLINT UNSIGNED: 0 to 65,535 (vs. signed ceiling of 32,767)
What About Strict Mode?
Older MySQL 5.6 servers — or any instance with strict mode turned off — would silently clamp the value to the type's max or min instead of erroring. Check your current mode:
SELECT @@sql_mode;
Seeing STRICT_TRANS_TABLES or STRICT_ALL_TABLES in the output means strict mode is active. That's correct behavior. The error is a feature — MySQL refusing to quietly corrupt your data.
Don't disable strict mode as a workaround. You'll just be storing wrong data silently, which is far worse than a visible error.
Curious what MySQL would actually clamp to? Test it temporarily:
SET sql_mode = '';
INSERT INTO users (name, age) VALUES ('test', 300);
-- Inserts 127 (TINYINT max) — no error, but the data is wrong
SELECT age FROM users WHERE name = 'test';
-- Returns 127
Re-enable strict mode immediately after:
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Verify the Fix
After the ALTER, confirm the type actually changed:
SHOW COLUMNS FROM users LIKE 'age';
-- Should show SMALLINT UNSIGNED
Then run the INSERT that was failing before:
INSERT INTO users (name, age) VALUES ('Bob', 300);
Query OK, 1 row affected (0.01 sec)
SELECT age FROM users WHERE name = 'Bob';
-- Returns 300 ✓
Tips to Avoid This Again
- Size columns for the actual domain, not just today's data. An
agecolumn doesn't need BIGINT, but TINYINT is cutting it close — SMALLINT UNSIGNED is the right call. - Use BIGINT for IDs and auto-increment counters on high-traffic tables. INT's 2.1 billion ceiling disappears faster than you'd expect.
- Add CHECK constraints (MySQL 8.0.16+) for business logic limits. They enforce rules at the DB level regardless of which app writes to it, and the error messages are clear.
- Test boundary values during development. Insert the type's exact min and max before you ship — edge cases are cheaper to catch in dev than in production.
- Keep strict mode on. Silent data truncation is the kind of bug that takes weeks to diagnose. The upfront error is doing you a favor.