How to Fix MySQL Error 1690: BIGINT Value Is Out of Range

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(<expression>)' is raised when an arithmetic operation produces a result that falls outside the valid range of the integer type involved — most commonly when subtracting from an unsigned integer produces a negative value. The error symbol is ER_DATA_OUT_OF_RANGE.

Impact

The query that triggered the overflow is aborted immediately and no rows are modified. Inside a multi-statement transaction, the current statement fails but the transaction remains open; you must decide whether to roll back or continue. The error surfaces as a fatal exception in most database drivers — for example, a MySQLdb.OperationalError in Python, a com.mysql.cj.jdbc.exceptions.MysqlDataTruncation in Java/JDBC, or a raw 1690 error code from any driver that exposes numeric error codes.

Developers most often encounter this during aggregations (SUM, arithmetic updates) on UNSIGNED BIGINT columns when the dataset contains rows that cause the result to go negative, or when doing in-place arithmetic like UPDATE counters SET value = value - 5 WHERE id = 1 on a row where value is already 0 or less than 5.

Common Causes

Unsigned integer underflow. UNSIGNED BIGINT cannot represent negative numbers. Any expression that produces a negative result — including subtraction, negation (-col), or a large negative literal — triggers error 1690. This is by far the most common cause.
SUM() over unsigned columns with mixed sign data. If a BIGINT UNSIGNED column stores values that, when aggregated, would logically go negative (e.g., you store debits as unsigned integers and subtract them during reporting), SUM() can underflow.
Explicit CAST or implicit coercion to a narrower unsigned type. Casting a negative value or a value larger than 18446744073709551615 to UNSIGNED BIGINT fails with this error in strict mode.
Arithmetic in generated columns or default expressions. A generated column expression that can underflow will raise 1690 at insert/update time, not at DDL time, which makes it harder to spot.
SQL mode set to STRICT. MySQL raises 1690 only when strict SQL mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES) is active. In non-strict mode the same operation may silently clamp the value, which is usually worse.

Troubleshooting and Resolution Steps

Reproduce the expression that overflowed. Identify the exact column and operation from the error message, then isolate it:

-- If the error message says: BIGINT UNSIGNED value is out of range in '(`mydb`.`counters`.`value` - 10)'
SELECT value, value - 10 FROM counters WHERE id = 1;
-- Confirm value < 10 causes the underflow

Check whether the column should truly be UNSIGNED. If the column can legitimately hold negative values, change the type:
```
ALTER TABLE counters MODIFY COLUMN value BIGINT NOT NULL DEFAULT 0;
```
Guard updates with a WHERE clause or GREATEST(). Prevent underflow at the application level by ensuring the subtraction cannot go below zero:
```
-- Safe decrement: never go below 0
UPDATE counters
SET value = GREATEST(0, CAST(value AS SIGNED) - 5)
WHERE id = 1;
```
Cast to SIGNED before arithmetic when you need to allow negative intermediate results:
```
SELECT CAST(a AS SIGNED) - CAST(b AS SIGNED) AS diff
FROM my_table;
```

Find rows that will underflow before running a bulk update:

-- Preview rows where the subtraction would underflow
SELECT id, value
FROM counters
WHERE value < 5;
-- Fix or skip these rows before the batch update

Check current SQL mode if you are unsure whether strict mode is active:
```
SELECT @@sql_mode;
-- Look for STRICT_TRANS_TABLES or STRICT_ALL_TABLES
```
If you need to temporarily disable strict overflow checking (not recommended for production), you can remove the strict flags, but address the root cause instead.

For SUM() aggregations over unsigned columns, cast first:

SELECT SUM(CAST(value AS SIGNED)) AS total FROM ledger;

Additional Information

The SQLSTATE code 22003 maps to "numeric value out of range," the same class used by other RDBMS systems for arithmetic overflow.
Related error: ERROR 1264 (22003) ER_WARN_DATA_OUT_OF_RANGE — this is the warning version emitted in non-strict mode when MySQL silently clamps the value to the column's min/max boundary. If you see lots of 1264 warnings in logs, it means the same class of problem is being masked.
MySQL 8.0 made no behavioral changes to unsigned arithmetic compared to 5.7; the rules are identical across both major versions.
ORMs that generate arithmetic SQL (e.g., ActiveRecord's increment!, SQLAlchemy's in-place column math) can produce this error if the underlying column is UNSIGNED and the application does not guard against negative results.
If you own the schema, strongly consider using BIGINT (signed) instead of BIGINT UNSIGNED unless you genuinely need values above 9223372036854775807. The signed range is sufficient for virtually all counters and IDs, and it eliminates this class of error entirely.

Frequently Asked Questions

Why does this error only appear sometimes, not on every MySQL server? The error is gated on strict SQL mode. Servers with STRICT_TRANS_TABLES or STRICT_ALL_TABLES in @@sql_mode raise 1690 as an error. Servers without strict mode emit a warning (1264) and silently clamp the result, which can silently corrupt data. Production servers should always run with strict mode enabled.

The same query works in development but fails in production — why? This nearly always means the two environments have different sql_mode settings. Check SELECT @@sql_mode on both servers and align them. It also commonly occurs when production data has edge-case rows (zero-value counters, for example) that do not exist in development datasets.

Can I fix this without changing the column type? Yes. Wrap the arithmetic in GREATEST(0, CAST(... AS SIGNED) - delta) for decrement operations, or add a WHERE col >= delta guard clause. These approaches prevent underflow at the query level without an ALTER TABLE.

Does this error affect INSERT operations, not just UPDATE? Yes. Any context where MySQL evaluates an arithmetic expression that overflows will raise 1690 — including INSERT ... SELECT, computed column expressions, and UPDATE statements. It is not limited to explicit UPDATE queries.