Error
Error Code:
1690
MariaDB Error 1690: Data Value Out of Range
Description
This error indicates that a data value provided for an operation, such as an INSERT or UPDATE, falls outside the permissible range for its intended data type. It typically occurs when attempting to store a number too large or too small for an integer column, or an invalid date/time value.
Error Message
%s value is out of range in '%s'
Known Causes
4 known causesNumeric Overflow/Underflow
Attempting to store a number larger than the maximum or smaller than the minimum value allowed by the column's integer or floating-point data type.
Invalid Date/Time Value
Providing a date or time value that does not conform to a valid format or falls outside the supported range for a DATE, DATETIME, or TIMESTAMP column.
Incorrect ENUM or SET Value
Inserting a string into an ENUM or SET column that is not among the predefined allowed values for that column.
Arithmetic Operation Result
A calculation or conversion performed during an SQL statement results in a value that exceeds the target column's data type limits.
Solutions
3 solutions available1. Correct Data Type for the Column medium
Ensure the column's data type can accommodate the value being inserted or updated.
1
Identify the table and column causing the error. The error message often provides clues like '%s value is out of range in '%s'' where the second '%s' is the column name.
2
Determine the current data type of the problematic column using `DESCRIBE` or `SHOW COLUMNS`.
DESCRIBE your_table_name;3
Compare the data type with the value that is failing. For example, if you're trying to insert a value larger than `TINYINT`'s maximum (255), you'll get this error.
4
If the data type is too small, alter the table to use a larger or more appropriate data type. For example, to change a `TINYINT` to an `INT`:
ALTER TABLE your_table_name MODIFY your_column_name INT;5
Consider the range of values for common numeric types: `TINYINT` (-128 to 127 or 0 to 255), `SMALLINT` (-32768 to 32767), `MEDIUMINT` (-8388608 to 8388607), `INT` (-2147483648 to 2147483647), `BIGINT` (very large range). For dates, ensure the year is within the supported range (usually 1000-9999).
2. Validate Input Data Before Insertion/Update medium
Implement checks in your application logic to prevent out-of-range values from reaching the database.
1
In your application code (e.g., Python, PHP, Java), before executing an SQL `INSERT` or `UPDATE` statement, add validation logic for the specific fields that could cause this error.
2
For numeric fields, check if the value falls within the expected range for the database column. For example, if the column is `TINYINT UNSIGNED` (0-255):
if (value < 0 || value > 255) { throw new Error('Value out of range for TINYINT UNSIGNED'); }3
For date/time fields, ensure the year, month, and day are valid and within the database's supported range.
4
If validation fails, either correct the data or inform the user instead of attempting the database operation.
3. Review and Correct Values in Existing Data medium
Identify and rectify existing rows with out-of-range data that might be causing issues during queries.
1
If the error occurs during a `SELECT` or other query that processes existing data, you might have rows with values that are technically out of range for a particular data type (though MariaDB can sometimes be more lenient with `INSERT` than `SELECT` in certain scenarios).
2
Run queries to find potential problematic values. For example, to find values exceeding the `TINYINT UNSIGNED` limit:
SELECT your_column_name FROM your_table_name WHERE your_column_name > 255;3
If you find such rows, use `UPDATE` statements to correct the values. If the original value was a mistake, you might set it to a default or a reasonable alternative.
UPDATE your_table_name SET your_column_name = 255 WHERE your_column_name > 255;4
If the data truly needs to be larger, consider the first solution to alter the column's data type.