Error
Error Code:
3105
MySQL Error 3105: Invalid Generated Column Value
Description
This error occurs when you attempt to explicitly provide a value for a generated column during an `INSERT` or `UPDATE` operation. Generated columns automatically derive their values from other columns within the table, and MySQL does not allow users to set these values directly.
Error Message
The value specified for generated column '%s' in table '%s' is not allowed.
Known Causes
4 known causesExplicit Insertion Attempt
Trying to insert a specific value into a generated column using an `INSERT` statement, which is not permitted as MySQL computes its value automatically.
Explicit Update Attempt
Attempting to modify the value of a generated column with an `UPDATE` statement. Generated columns are read-only and cannot be manually changed.
Schema Misunderstanding
Not being aware that a particular column is defined as a generated column and mistakenly treating it as a regular column that accepts direct user input.
Application Data Mismatch
An application or ORM sends data for a column that the database has defined as generated, leading to an attempt to set its value directly.
Solutions
3 solutions available1. Correct the Generated Column Expression medium
Modify the expression defining the generated column to produce a valid value.
1
Identify the generated column and its table from the error message. The error message will typically look like: `ERROR 3105 (HY000): Invalid generated column value for generated column '%s' in table '%s'` where `%s` are placeholders for the column and table names.
2
Examine the `CREATE TABLE` or `ALTER TABLE` statement that defines the generated column. Look for the `GENERATED ALWAYS AS (...) STORED` or `GENERATED ALWAYS AS (...) VIRTUAL` clause.
SHOW CREATE TABLE your_table_name;3
Analyze the expression within the `GENERATED ALWAYS AS (...)` clause. Ensure that the expression adheres to MySQL's rules for generated columns. Common issues include:
4
Common issues and their fixes:
- **Data Type Mismatch:** The expression's result type is incompatible with the column's declared type.
*Fix:* Adjust the expression to cast or convert values to the correct type, or change the column's data type if appropriate.
- **Unsupported Functions/Expressions:** The expression uses functions or constructs not permitted in generated columns (e.g., subqueries, user-defined functions, `RAND()`, `NOW()` for `VIRTUAL` columns if not handled carefully, or functions that depend on external state).
*Fix:* Replace unsupported functions with equivalent, supported ones. For example, instead of `RAND()`, consider pre-generating values or using a deterministic approach.
- **NULL Propagation:** The expression might produce `NULL` when `NOT NULL` is expected, or vice-versa.
*Fix:* Use `COALESCE()` or `IFNULL()` to handle potential `NULL` values in the expression's operands.
- **Circular Dependencies:** A generated column's definition indirectly depends on itself.
*Fix:* Restructure the generated column definitions to break the cycle.5
Once the expression is corrected, apply the changes using an `ALTER TABLE` statement. For example, if you are changing a `STORED` generated column:
ALTER TABLE your_table_name
MODIFY COLUMN generated_column_name data_type
GENERATED ALWAYS AS (your_corrected_expression) STORED;6
If you are changing a `VIRTUAL` generated column:
ALTER TABLE your_table_name
MODIFY COLUMN generated_column_name data_type
GENERATED ALWAYS AS (your_corrected_expression) VIRTUAL;2. Remove and Recreate the Generated Column easy
Temporarily remove the problematic generated column and then re-add it with a corrected definition.
1
Identify the generated column and its table from the error message.
2
Save the current definition of the table, including the generated column, for reference.
SHOW CREATE TABLE your_table_name;3
Drop the generated column from the table.
ALTER TABLE your_table_name
DROP COLUMN generated_column_name;4
Re-create the table or add the generated column back with a corrected expression based on the insights from Solution 1. Ensure the expression is valid and produces expected results.
ALTER TABLE your_table_name
ADD COLUMN generated_column_name data_type
GENERATED ALWAYS AS (your_corrected_expression) [STORED | VIRTUAL];3. Adjust Data in Existing Rows medium
If the error occurs during `INSERT` or `UPDATE` due to existing data not conforming to the generated column's logic, update the source data.
1
Identify the generated column and its table from the error message.
2
Examine the `CREATE TABLE` or `ALTER TABLE` statement to understand the generated column's expression.
SHOW CREATE TABLE your_table_name;3
Determine which rows in the table, or which incoming data during an `INSERT`/`UPDATE`, would cause the generated column's expression to produce an invalid value. This often involves looking at the source columns used in the generated column's definition.
4
For existing rows, write `UPDATE` statements to modify the source columns so that the generated column's expression can produce a valid value. For example, if a generated column calculates `column_a + column_b` and `column_a` is `NULL` when it shouldn't be:
UPDATE your_table_name
SET column_a = COALESCE(column_a, 0) -- Or some other appropriate default value
WHERE generated_column_name IS NOT NULL AND some_condition_causing_error;5
If the error occurs during an `INSERT` or `UPDATE` operation, ensure that the data being inserted or updated into the source columns is valid and will result in a permissible value for the generated column.
INSERT INTO your_table_name (column_a, column_b, ...)
VALUES (valid_value_for_column_a, valid_value_for_column_b, ...);6
After adjusting the source data, retry the operation that previously failed.