ERROR 1264 (22003): Out of range value for column

What it means

Every numeric column has a range. In strict mode (the default on MySQL 8 and MariaDB), storing a number outside it fails with error 1264, naming the column and the row of the statement. Nothing is stored.

A DECIMAL(p, s) holds p digits, s of them after the point: DECIMAL(5,2) goes up to 999.99. A value with more decimals is rounded first, so 999.999 becomes 1000.00 and is then out of range.

The display width in old definitions, such as int(11), doesn’t change the range.

Common causes

1. Phone numbers, card numbers or other long codes in an INT: 4155550123 is bigger than INT allows. Leading zeros would be lost anyway.
2. A counter or id that outgrew INT, at 2,147,483,647 (or 4,294,967,295 unsigned).
3. A negative value in an UNSIGNED column, often from a calculation such as a stock count going below zero.
4. A DECIMAL too narrow for prices, totals or exchange rates with more digits than planned.
5. A TINYINT used for a quantity, age or percentage that can exceed 127.

How to fix it

See the column’s type

SHOW COLUMNS FROM stock LIKE 'qty';

Widen the column

ALTER TABLE stock MODIFY qty int NOT NULL DEFAULT 0;
ALTER TABLE orders MODIFY total decimal(12,2) NOT NULL;
ALTER TABLE events MODIFY id bigint NOT NULL AUTO_INCREMENT;

Restate everything else about the column (NOT NULL, default, AUTO_INCREMENT), because MODIFY replaces the whole definition. Changing a column’s type rebuilds the table, and an id referenced by foreign keys must be changed in the referencing columns too; see changing a column type.

Store identifiers as text

Phone numbers, postcodes and account numbers aren’t quantities: you never add them up, and they can start with 0 or +. Use varchar:

ALTER TABLE contacts MODIFY phone varchar(20);

Keep unsigned columns from going negative

Check before you subtract (UPDATE stock SET qty = qty - 1 WHERE id = 4 AND qty > 0), or use a signed type with a CHECK (qty >= 0) constraint, which gives a clearer error.

Don’t switch off strict mode

Without strict mode, the server stores the nearest value in range instead (200 in a TINYINT becomes 127) with only a warning, so data is silently wrong.

Out of range in a calculation: error 1690

Arithmetic that overflows before anything is stored gives a different error, naming the expression. The common case is subtracting from an unsigned column:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`seo_err_mysql`.`stock`.`big` - 1)'

CAST(col AS SIGNED) - 1 does the arithmetic as signed.

Reproduce it

On MySQL 8.4.11, with stock (qty tinyint, n int, price decimal(5,2), big int unsigned):

INSERT INTO stock (qty) VALUES (200);
INSERT INTO stock (n) VALUES (3000000000);
INSERT INTO stock (price) VALUES (1000);
INSERT INTO stock (price) VALUES (999.999);
INSERT INTO stock (big) VALUES (-1);
UPDATE stock SET n = n + 1 WHERE n = 2147483647;

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

UPDATE stock SET big = big - 1 WHERE big = 0 gave the 1690 above. A phone number in an int column failed the same way, whether sent as 4155550123 or '07700900123'. With sql_mode = '', 200 went into qty as 127 with Warning 1264.

An INT AUTO_INCREMENT id that reached its limit gave a different error on each server. After ids 2147483646 and 2147483647, the next insert on MySQL said:

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'hits.PRIMARY'

MariaDB 11.4.13 said ERROR 167 (22003): Out of range value for column 'id' at row 1. Otherwise it gave the same 1264 errors, and the same 1690 without the outer brackets.

In Inlet

Inlet’s structure editor changes a column’s type in one ALTER TABLE, shows the DDL before it runs, and notes that MySQL may copy the table while it runs. When an insert or update fails, Inlet shows the error and links to this page.