Error
Error Code:
3904
MySQL Error 3904: JSON CAST Range Issue
Description
This error indicates that a JSON value being processed by a functional index cannot be cast to its target data type because the value falls outside the valid range for that type. This typically occurs when the JSON data contains numerical or date values that exceed the limits of the data type specified in the functional index definition.
Error Message
Out of range JSON value for CAST for functional index '%s'.
Known Causes
4 known causesMismatched Numeric Range
A numeric JSON value (e.g., an integer or float) is too large or too small to fit into the target numeric data type (e.g., INT, SMALLINT) defined in the functional index.
Invalid Date/Time Value
A JSON string representing a date or time value is outside the valid range for the target DATE, DATETIME, or TIMESTAMP type used in the functional index.
Incorrect Target Data Type
The data type specified in the functional index's CAST operation is too restrictive or inappropriate for the range of values expected in the JSON column.
Evolving JSON Data
The range of values stored in the JSON column has changed over time, and new data now exceeds the limits of the data type used in the functional index.
Solutions
3 solutions available1. Adjust Data Type in Functional Index medium
Modify the data type used in the functional index to accommodate the range of JSON values.
1
Identify the problematic functional index. The error message provides its name.
2
Examine the `CREATE FUNCTIONAL INDEX` statement. Pay close attention to the data type specified for the indexed column within the JSON.
Example: CREATE FUNCTIONAL INDEX idx_user_age ON users (CAST(json_column->'$.age' AS UNSIGNED));3
Determine the actual range of values being stored in the JSON field. If the values exceed the capacity of the current data type (e.g., `UNSIGNED` for a value larger than 4,294,967,295), choose a more appropriate type like `BIGINT UNSIGNED` or `DECIMAL`.
4
Drop the existing functional index.
DROP FUNCTIONAL INDEX idx_user_age ON users;5
Recreate the functional index with the corrected data type.
CREATE FUNCTIONAL INDEX idx_user_age ON users (CAST(json_column->'$.age' AS UNSIGNED BIGINT));2. Modify JSON Data for Index Compatibility medium
Cleanse or transform the JSON data to fit within the expected range of the functional index's data type.
1
Identify the functional index causing the error and the specific JSON path it's indexing.
2
Query the table to find rows where the JSON value at the specified path is out of range for the index's casted data type.
SELECT * FROM your_table WHERE CAST(json_column->'$.your_field' AS UNSIGNED) IS NULL AND json_column->'$.your_field' IS NOT NULL;3
For each identified row, decide on a strategy to correct the JSON data. This might involve capping values, converting them to a different format, or marking them as invalid.
4
Update the JSON data in the table. Use `JSON_SET` or `JSON_REPLACE` for targeted updates.
UPDATE your_table SET json_column = JSON_SET(json_column, '$.your_field', new_valid_value) WHERE id = row_id;5
Alternatively, if the data is fundamentally incompatible, consider removing it or setting it to a valid `NULL` representation for the cast.
UPDATE your_table SET json_column = JSON_SET(json_column, '$.your_field', NULL) WHERE id = row_id;6
Rebuild or ensure the functional index is up-to-date after data modification.
3. Use a More Lenient Data Type for Casting easy
Change the CAST operation in the functional index definition to a data type that can handle a wider range of values.
1
Locate the `CREATE FUNCTIONAL INDEX` statement for the index mentioned in the error.
2
Examine the `CAST` operation within the index definition.
Example: CREATE FUNCTIONAL INDEX idx_json_value ON my_table (CAST(json_data->'$.numeric_field' AS INT));3
Consider if `INT` is sufficient. If values might exceed the range of `INT` (typically -2,147,483,648 to 2,147,483,647), change it to a larger type like `BIGINT`.
Example: CREATE FUNCTIONAL INDEX idx_json_value ON my_table (CAST(json_data->'$.numeric_field' AS BIGINT));4
If the values are non-negative and can be very large, use `UNSIGNED BIGINT`.
Example: CREATE FUNCTIONAL INDEX idx_json_value ON my_table (CAST(json_data->'$.numeric_field' AS UNSIGNED BIGINT));5
If the values are potentially fractional, use `DECIMAL` with appropriate precision and scale.
Example: CREATE FUNCTIONAL INDEX idx_json_value ON my_table (CAST(json_data->'$.numeric_field' AS DECIMAL(18, 4)));6
Drop the existing functional index.
DROP FUNCTIONAL INDEX idx_json_value ON my_table;7
Recreate the functional index with the updated `CAST` data type.
CREATE FUNCTIONAL INDEX idx_json_value ON my_table (CAST(json_data->'$.numeric_field' AS BIGINT));