📋

Description

MySQL Error 3505, 'Too long enumeration/set value for column %s.', occurs when you attempt to insert or update a value into an `ENUM` or `SET` column that does not match any of the predefined allowed values for that column. This error indicates a data integrity violation where the provided data fails to conform to the column's schema definition.

💬

Error Message

Too long enumeration/set value for column %s.

🔍

Known Causes

3 known causes

⚠️

Value Not in Defined List

The string value being inserted or updated into an `ENUM` or `SET` column does not match any of the explicitly predefined allowed members for that column.

⚠️

Incorrect Data Type or Format

The input provided for the `ENUM` or `SET` column is not a string, or its format is incompatible with how MySQL expects to process `ENUM`/`SET` values.

⚠️

Case Sensitivity Mismatch

The case of the input string value does not match the case of the defined `ENUM` or `SET` members, especially when the column's collation is case-sensitive.

🛠️

Solutions

3 solutions available

1. Truncate or Modify ENUM/SET Values easy

Shorten existing ENUM/SET values or remove less critical ones.

1

Identify the problematic column and its current ENUM/SET definition. You can do this by querying the information schema.

SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name' AND COLUMN_NAME = 'your_column_name';

2

Examine the list of values in the ENUM or SET definition. Determine which values are either too long or no longer necessary. You can also consider shortening lengthy values.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name ENUM('short_val1', 'short_val2', 'another_short_val');

3

Alternatively, if you need to add a new value but the current list is too long, you may need to remove less frequently used or redundant values to make space.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name ENUM('val1', 'val2', 'val3', 'val4', 'val5'); -- Assuming this was the original definition and you need to add a new one

2. Change Data Type to VARCHAR medium

Replace the ENUM/SET column with a VARCHAR to accommodate longer strings.

1

Back up your table before making structural changes.

CREATE TABLE your_table_name_backup LIKE your_table_name;

2

Modify the column to a VARCHAR type with an appropriate length. Consider the maximum possible length of your intended values.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255); -- Adjust 255 to your needs

3

If you have existing data that was restricted by the ENUM/SET, you might need to carefully review and update it to ensure consistency and correctness after the type change. This might involve a separate data migration script.

-- Example: If you had a known set of values, you could use a CASE statement to map them before the change, or re-validate after.

3. Normalize Data into a Separate Table advanced

Move the ENUM/SET values to a separate lookup table and use foreign keys.

1

Create a new table to store the distinct values that were previously in the ENUM/SET column. This table will have an auto-incrementing primary key.

CREATE TABLE your_lookup_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value_name VARCHAR(255) NOT NULL UNIQUE
);

2

Populate the new lookup table with the desired values.

INSERT INTO your_lookup_table (value_name) VALUES ('value1'), ('value2'), ('value3');

3

Add a new integer column to your original table that will serve as a foreign key referencing the new lookup table.

ALTER TABLE your_table_name ADD COLUMN your_column_id INT;
ALTER TABLE your_table_name ADD CONSTRAINT fk_your_column
  FOREIGN KEY (your_column_id) REFERENCES your_lookup_table(id);

4

Update the new `your_column_id` in the original table by joining with the lookup table to map existing values. Then, drop the old ENUM/SET column.

UPDATE your_table_name t
JOIN your_lookup_table l ON t.your_column_name = l.value_name
SET t.your_column_id = l.id;

ALTER TABLE your_table_name DROP COLUMN your_column_name;

5

Rename the new ID column to something more descriptive if desired, e.g., `your_column`.

ALTER TABLE your_table_name CHANGE COLUMN your_column_id your_column INT;
ALTER TABLE your_table_name ADD CONSTRAINT fk_your_column
  FOREIGN KEY (your_column) REFERENCES your_lookup_table(id);

🔗

Related Errors

5 related errors

MySQL Error 3505: Value Exceeds ENUM/SET Limit