Error

Error Code: 3504

MySQL Error 3504: ENUM Value Limit Exceeded

📦 MySQL

📋

Description

This error indicates that you are attempting to define an ENUM column with more than the maximum allowed 65,535 distinct enumeration values. MySQL's ENUM data type has a hard limit on the number of members it can store. This typically occurs during `CREATE TABLE` or `ALTER TABLE` operations when the list of ENUM values is excessively long.

💬

Error Message

Too many enumeration values for column %s.

🔍

Known Causes

3 known causes

⚠️

Excessive ENUM Definition

You directly specified a list of more than 65,535 distinct values when creating or altering an ENUM column in your SQL statement.

⚠️

Automated Schema Generation

A script or application is programmatically generating `CREATE TABLE` or `ALTER TABLE` statements that inadvertently define an ENUM column with too many members.

⚠️

Cross-System Migration

Migrating a database schema from another system (e.g., a different RDBMS) that has a higher or no limit on ENUM values to MySQL.

🛠️

Solutions

3 solutions available

1. Re-evaluate ENUM Column Design medium

Analyze and reduce the number of distinct values in the ENUM column.

Identify the column causing the error by checking the error message. The message will typically include the column name.

Review the purpose of the ENUM column. Are all the current values necessary? Can some values be combined or removed?

If the list of values is still extensive but necessary, consider alternative data types like a separate lookup table with a foreign key.

Consider creating a new table like `status_types` with columns `status_id` (INT, PRIMARY KEY) and `status_name` (VARCHAR). Then, replace the ENUM column with `status_id` and create a foreign key constraint.

If you decide to reduce the number of ENUM values, alter the table. Ensure any existing data conforms to the new, smaller list of values.

ALTER TABLE your_table MODIFY COLUMN your_enum_column ENUM('value1', 'value2', 'value3');

2. Increase ENUM Value Limit (MySQL 8.0.13+) easy

Dynamically increase the ENUM limit if your MySQL version supports it and the values are truly necessary.

Check your MySQL server version. This solution is applicable for MySQL 8.0.13 and later.

SELECT VERSION();

If your version supports it, you can increase the ENUM limit by altering the column definition. This will rebuild the table, so plan for downtime or use online DDL if available and appropriate for your setup.

ALTER TABLE your_table MODIFY COLUMN your_enum_column ENUM('value1', 'value2', ..., 'valueN'); -- Add your new values here

Note: While this increases the limit, it's generally a good practice to keep ENUM lists concise. Consider the long-term maintainability.

3. Migrate ENUM to a Separate Lookup Table advanced

Replace the ENUM column with a foreign key referencing a dedicated lookup table.

Create a new table to store the enumeration values. This table will have an auto-incrementing primary key and a column for the value itself.

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

Populate the new lookup table with the existing ENUM values from your problematic column.

INSERT INTO your_lookup_table (value) VALUES ('value1'), ('value2'), ...;

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

ALTER TABLE your_table ADD COLUMN your_lookup_id INT;

Update the new foreign key column with the corresponding IDs from the lookup table based on the old ENUM values. This step requires careful data mapping.

UPDATE your_table t
JOIN your_lookup_table l ON t.your_enum_column = l.value
SET t.your_lookup_id = l.id;

Add a foreign key constraint to enforce referential integrity.

ALTER TABLE your_table ADD CONSTRAINT fk_your_lookup
FOREIGN KEY (your_lookup_id) REFERENCES your_lookup_table(id);

Drop the original ENUM column.

ALTER TABLE your_table DROP COLUMN your_enum_column;

Rename the new column to be more descriptive if needed.

ALTER TABLE your_table CHANGE COLUMN your_lookup_id your_column_name INT;

🔗

Related Errors

5 related errors