How to Fix MySQL Error 1264: Out of Range Value for Column

MySQL raises error 1264 (ER_WARN_DATA_OUT_OF_RANGE, SQLSTATE 22003) when a value being written to a column exceeds the valid range for that column's data type. The error message typically reads:

ERROR 1264 (22003): Out of range value for column 'column_name' at row N

In strict SQL mode this is a hard error that aborts the statement. In non-strict mode it downgrades to a warning and MySQL silently clamps the value to the nearest boundary of the column's range.

What This Error Means

SQLSTATE 22003 belongs to the SQL standard class 22 ("Data Exception"). MySQL maps this class to situations where a value is numerically valid on its own but cannot be represented in the target column's type — the value is too large, too small, or has too many digits of precision.

Every MySQL numeric type has a fixed storage range. For signed integer types, values must fall within the two's-complement range for the column's width: TINYINT accepts −128 to 127, SMALLINT −32768 to 32767, INT −2147483648 to 2147483647, and BIGINT −9223372036854775808 to 9223372036854775807. Unsigned variants shift the range to start at 0 and double the positive maximum. DECIMAL(p, s) and FLOAT/DOUBLE columns impose their own precision and magnitude limits.

The behavior when this limit is breached depends on the active sql_mode. With STRICT_TRANS_TABLES or STRICT_ALL_TABLES enabled (the default since MySQL 5.7.5), the statement fails immediately with error 1264 and no rows are written. Without a strict mode, MySQL truncates the value to the column maximum, writes it, and emits a warning — which is silent data corruption from the application's perspective.

Common Causes

1. Inserting a value that exceeds the column's maximum. A common example is storing a user-supplied integer in a TINYINT UNSIGNED (max 255) or SMALLINT column without validating the input range first.

2. Schema was changed after the application was written. A column was originally BIGINT but was altered to INT or SMALLINT to save storage, and existing application code still writes values that fit in the larger type.

3. Auto-increment column overflow. A table with an INT primary key (AUTO_INCREMENT) has reached 2147483647 (for signed) or 4294967295 (for unsigned). The next insert attempt produces error 1264.

4. Arithmetic overflow inside an expression. A computed column value or a value produced by an expression in a SET clause overflows the column type, even though the individual input values were in range.

5. Incorrect column type choice for the domain. Storing a Unix timestamp (which can exceed 2147483647 after January 2038) in an INT column, or storing large monetary amounts in a FLOAT column where the precision is insufficient.

6. ORM or framework generates incorrect casts. An ORM maps an application-layer Long or int64 field to a SMALLINT column because of a misconfigured mapping, and the values assigned at runtime are too large.

How to Fix ER_WARN_DATA_OUT_OF_RANGE

1. Widen the column type to accommodate the actual data range.

   -- Widen a SMALLINT column to INT
   ALTER TABLE orders MODIFY COLUMN status_code INT NOT NULL;
   
   -- Widen an INT primary key to BIGINT before auto-increment overflow
   ALTER TABLE events MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
   

   This is the correct fix when the data is legitimate and the schema was simply under-designed.

2. Validate and clamp values in the application before writing.

   If the column type is intentional (e.g., a TINYINT flag), ensure the application never passes a value outside 0–255. Add validation at the service or DAO layer rather than relying on MySQL to clamp values silently.

3. Use DECIMAL instead of FLOAT/DOUBLE for precise numeric storage.

   -- Replace imprecise FLOAT with exact DECIMAL
   ALTER TABLE products MODIFY COLUMN price DECIMAL(10, 2) NOT NULL;
   

   Floating-point types can also trigger 1264 when very large magnitudes overflow their exponent range.

4. Correct auto-increment overflow before it occurs.

   Query the current maximum and compare it with the column type's limit:

   SELECT MAX(id) FROM your_table;
   SHOW CREATE TABLE your_table\G
   

   If MAX(id) is approaching the type limit, run the ALTER TABLE ... MODIFY COLUMN id BIGINT ... statement during a maintenance window.

5. Do not disable strict SQL mode as a workaround.

   Removing STRICT_TRANS_TABLES from sql_mode makes the error silent and allows corrupted, clamped data into the table. The underlying range mismatch still exists — you have just hidden the symptom. Fix the schema or the application instead.

6. Use INSERT ... ON DUPLICATE KEY UPDATE or application-side guards for edge cases.

   If the occasional out-of-range value is expected and should be clamped to the column max as a business rule, apply the clamp explicitly in SQL rather than letting MySQL decide silently:

   INSERT INTO metrics (device_id, reading)
   VALUES (42, LEAST(32767, :raw_value));
   

Additional Information

• Error 1264 as a hard error (rather than a warning) became the default behavior in MySQL 5.7.5, when STRICT_TRANS_TABLES was added to the default sql_mode. Applications migrated from MySQL 5.6 or earlier may encounter this error for the first time after upgrading.
• The related SQLSTATE 22001 covers string truncation ("Data too long for column"), which shares the same SQL standard class. Both indicate that the application is writing values the column was not typed to hold.
• Drivers and ORMs: JDBC drivers surface this as java.sql.SQLDataException with SQLState 22003. Python's mysql-connector-python raises mysql.connector.errors.DataError. Hibernate/JPA will throw javax.persistence.PersistenceException wrapping a DataError. In all cases the MySQL error code 1264 is available from the underlying exception's errno or getErrorCode().
• When sql_mode does not include a strict mode, MySQL still increments the warning count. Applications using connection pools that never call SHOW WARNINGS will never see these warnings, making the silent truncation particularly dangerous.
• BIGINT UNSIGNED (max ~1.8 × 10¹⁹) is almost always sufficient for auto-increment primary keys; plan the migration before the INT limit is reached rather than after.

Frequently Asked Questions

Why does this appear as a warning in one environment and an error in another?

The difference is the sql_mode setting. Environments running MySQL 5.7+ with the default sql_mode include STRICT_TRANS_TABLES, which converts the out-of-range condition into a hard error. Older installations or those with a permissive sql_mode produce a warning instead and silently clamp the value. Run SELECT @@sql_mode; on each server to compare.

My INT auto-increment column hit the limit. How do I fix it without downtime?

Use ALTER TABLE ... MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT. On MySQL 5.7+ with InnoDB, a MODIFY COLUMN that only changes the integer width is an in-place online DDL operation (no full table copy required for most cases). Verify with EXPLAIN ALTER TABLE ... ALGORITHM=INPLACE. For very large tables, use pt-online-schema-change or gh-ost to avoid locking.

Can I catch error 1264 specifically in application code?

Yes. All MySQL client libraries expose the numeric error code alongside the SQLSTATE. In Python: except mysql.connector.errors.DataError as e: if e.errno == 1264: .... In Java: catch (SQLDataException e) { if (e.getErrorCode() == 1264) { ... } }. This lets you distinguish a range overflow from other data errors.

Does INSERT IGNORE suppress error 1264?

Yes — INSERT IGNORE demotes the error to a warning and MySQL writes the clamped value instead. This is almost never the right solution because it silently corrupts data. Reserve INSERT IGNORE for duplicate-key scenarios, not for range violations.