Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ERROR 1264: Out of range value for column on INSERT

ErrorERROR 1264 (22003): Out of range value for column 'i1' at row 1

Short answer

The value you insert is outside what the column type can store. In strict mode MySQL rejects the row with error 1264; otherwise it clips the value and only warns. Compare the value with the type's range (INT tops out at 2147483647), then widen the column with MODIFY or correct the data.

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find the column type and the value that fails, then compare with the range of that type.

    SHOW CREATE TABLE my_table\G
    SELECT @@sql_mode;
  2. 2Match what you see, then jump to its fix

    1. 1The failing value has many digits: a phone number, a timestamp in milliseconds, an external id.The number is larger than the integer type allowsFix
    2. 2The failing value is negative, for example -1 from a balance, a delta or a failed calculation.A negative number goes into an UNSIGNED columnFix
    3. 3One server rejects the row with 1264 while another stores 127 or 2147483647 and only warns.Strict mode decides whether you see an error or silent clippingFix

What does the error look like?

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

Cause 1 of 3

The number is larger than the integer type allows

1Check

Signed ranges: TINYINT 127, SMALLINT 32767, MEDIUMINT 8388607, INT 2147483647, BIGINT 2^63-1. Unsigned doubles the top (INT UNSIGNED 4294967295). A 10-digit phone number does not fit in INT.

SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'my_table';

2Fix

Widen the column. MODIFY does not carry old attributes forward, so repeat NOT NULL, DEFAULT and the like. For values that are identifiers rather than quantities, such as phone numbers, a VARCHAR keeps leading zeros and avoids the limit.

ALTER TABLE my_table MODIFY visits BIGINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE my_table MODIFY phone VARCHAR(20) NOT NULL;

3Verify

Insert the value that failed.

INSERT INTO my_table (phone) VALUES ('03001234567');

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

A negative number goes into an UNSIGNED column

1Check

The minimum of every UNSIGNED integer type is 0.

SHOW COLUMNS FROM my_table LIKE 'balance';

2Fix

Make the column signed if negative values are real, or fix the code that produces the negative.

ALTER TABLE my_table MODIFY balance INT NOT NULL DEFAULT 0;

3Verify

A negative value now inserts.

INSERT INTO my_table (balance) VALUES (-5);

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

Strict mode decides whether you see an error or silent clipping

1Check

With strict SQL mode, out-of-range values are rejected. Without a restrictive mode, MySQL clips the value to the nearest end of the range and issues a warning. The manual's example stores 256 as 127 in a signed TINYINT.

SELECT @@sql_mode;
SHOW WARNINGS;

2Fix

Keep strict mode on so bad data is refused, and fix the data or the column. If rows were already clipped, find them by the boundary value and correct them.

SELECT id FROM my_table WHERE visits = 2147483647;

3Verify

No more rows hold the boundary value after the data is corrected.

SELECT COUNT(*) FROM my_table WHERE visits = 2147483647;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Use BIGINT UNSIGNED for counters and ids that can grow, and VARCHAR for phone numbers and codes.
  • Keep strict SQL mode on in every environment so test and production behave the same.
  • Validate numeric ranges in the application before the INSERT.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.