Reputation: 3740
Modifying enum column succeeds, but value is not valid
When I execute this statement:
ALTER TABLE person MODIFY COLUMN subcommittee
ENUM('Arts','Bus','HC','IA','Pol','ST','Floater','Student') NULL DEFAULT NULL;
the response is
2801 row(s) affected Records: 2801 Duplicates: 0 Warnings: 0
but then this statement:
update person set subcommittee = 'Student' where person_pk = 1381;
always results in this error message:
Error Code: 1265. Data truncated for column 'subcommittee' at row 1
(Person 1381 is in the database, in case you're wondering, or I wouldn't even have gotten to this error message.)
Choosing another enum works:
update person set subcommittee = 'Floater' where person_pk = 1381;
1 row(s) affected Rows matched: 1 Changed: 1 Warnings: 0
I first noticed this when I got the error from my PHP app using PDO, but then reproduced it in MySQLWorkbench, from which the above was taken.
Perhaps deleting the column entirely and redefining it would get around the problem, but this is a live database in which I just want to add one additional enum value (Student).
I have tried retyping the alter statement, copying it to a text editor to examine the characters, and a few other things to rule out weird invisible characters in the value. I also rearranged the values:
ALTER TABLE person MODIFY COLUMN subcommittee
ENUM('Arts','Bus','HC','IA','Pol','ST', 'Student','Floater') NULL DEFAULT NULL;
No joy.
I have seen some bugs related to modifying an enum column when I googled around a bit. Does anyone know if this is a MySQL bug?
(MySQL version 5.5.28.)
Some data a few people asked for:
mysql> describe person;
+------------------------------+-------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+-------------------------------------------------------------+------+-----+---------+----------------+
| person_pk | int(11) | NO | PRI | NULL | auto_increment |
| name_last | varchar(255) | NO | MUL | | |
| name_first | varchar(255) | YES | | NULL | |
| name_index | int(11) | NO | | 0 | |
| name_middle | varchar(255) | YES | | NULL | |
| name_spouse_or_partner_first | varchar(255) | YES | | NULL | |
| name_spouse_or_partner_last | varchar(255) | YES | | NULL | |
| name_legal_first | varchar(255) | YES | | NULL | |
| name_legal_middle | varchar(255) | YES | | NULL | |
| name_legal_last | varchar(255) | YES | | NULL | |
| prefix | varchar(255) | YES | | NULL | |
| suffix | varchar(255) | YES | | NULL | |
| salutation | varchar(255) | YES | | NULL | |
| home_street1 | varchar(255) | YES | | NULL | |
| home_street2 | varchar(255) | YES | | NULL | |
| home_city | varchar(255) | YES | | NULL | |
| home_state | varchar(255) | YES | | NULL | |
| home_zip | varchar(255) | YES | | NULL | |
| home_country | varchar(255) | YES | | NULL | |
| work_org | varchar(255) | YES | | NULL | |
| work_street1 | varchar(255) | YES | | NULL | |
| work_street2 | varchar(255) | YES | | NULL | |
| work_city | varchar(255) | YES | | NULL | |
| work_state | varchar(255) | YES | | NULL | |
| work_zip | varchar(255) | YES | | NULL | |
| work_country | varchar(255) | YES | | NULL | |
| phone_home | varchar(255) | YES | | NULL | |
| phone_work | varchar(255) | YES | | NULL | |
| phone_mobile | varchar(255) | YES | | NULL | |
| phone_fax | varchar(255) | YES | | NULL | |
| phone5 | varchar(255) | YES | | NULL | |
| phone6 | varchar(255) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| email2 | varchar(255) | YES | | NULL | |
| website | varchar(255) | YES | | NULL | |
| preferred_communication | enum('home','work','mobile','email','mail','special') | YES | | NULL | |
| preferred_communication_text | varchar(255) | YES | | NULL | |
| preferred_address | enum('home','work') | YES | | NULL | |
| use_housing | tinyint(1) | YES | | NULL | |
| smoker | tinyint(1) | YES | | NULL | |
| pet_allergies | tinyint(1) | YES | | NULL | |
| pets | tinyint(1) | YES | | NULL | |
| kids | tinyint(1) | YES | | NULL | |
| physical_considerations | text | YES | | NULL | |
| dietary_needs | text | YES | | NULL | |
| appellation | varchar(255) | YES | | NULL | |
| bio | text | YES | | NULL | |
| photo | varchar(255) | YES | | NULL | |
| include_in_directory | tinyint(1) | YES | | NULL | |
| participant_notes | text | YES | | NULL | |
| contact_notes | text | YES | | NULL | |
| available_1A | tinyint(1) | YES | | NULL | |
| available_1P | tinyint(1) | YES | | NULL | |
| available_2A | tinyint(1) | YES | | NULL | |
| available_2P | tinyint(1) | YES | | NULL | |
| available_3A | tinyint(1) | YES | | NULL | |
| available_3P | tinyint(1) | YES | | NULL | |
| available_4A | tinyint(1) | YES | | NULL | |
| available_4P | tinyint(1) | YES | | NULL | |
| available_5A | tinyint(1) | YES | | NULL | |
| available_5P | tinyint(1) | YES | | NULL | |
| interest_x_dis | tinyint(1) | YES | | NULL | |
| interest_arts | tinyint(1) | YES | | NULL | |
| interest_bus | tinyint(1) | YES | | NULL | |
| interest_hc | tinyint(1) | YES | | NULL | |
| interest_ia | tinyint(1) | YES | | NULL | |
| interest_med | tinyint(1) | YES | | NULL | |
| interest_pol | tinyint(1) | YES | | NULL | |
| interest_st | tinyint(1) | YES | | NULL | |
| venue_manager | tinyint(1) | YES | | NULL | |
| committee_arts | tinyint(1) | YES | | NULL | |
| committee_bus | tinyint(1) | YES | | NULL | |
| committee_hc | tinyint(1) | YES | | NULL | |
| committee_ia | tinyint(1) | YES | | NULL | |
| committee_pol | tinyint(1) | YES | | NULL | |
| committee_st | tinyint(1) | YES | | NULL | |
| committee_students | tinyint(1) | YES | | NULL | |
| committee_housing | tinyint(1) | YES | | NULL | |
| committee_moderator | tinyint(1) | YES | | NULL | |
| committee_volunteer | tinyint(1) | YES | | NULL | |
| committee_fundraising | tinyint(1) | YES | | NULL | |
| committee_office | tinyint(1) | YES | | NULL | |
| committee_other | tinyint(1) | YES | | NULL | |
| committee_notes | text | YES | | NULL | |
| houser_fk | int(11) | YES | MUL | NULL | |
| housed_fk | int(11) | YES | MUL | NULL | |
| contact_fk | int(11) | YES | MUL | NULL | |
| hyphen_fk | int(11) | YES | MUL | NULL | |
| introduced_by_fk | int(11) | YES | MUL | NULL | |
| committee_contact_fk | int(11) | YES | MUL | NULL | |
| donor | tinyint(1) | YES | | NULL | |
| houser | tinyint(1) | YES | | NULL | |
| moderator | tinyint(1) | YES | | NULL | |
| producer | tinyint(1) | YES | | NULL | |
| committee_member | tinyint(1) | YES | | NULL | |
| participant | tinyint(1) | YES | | NULL | |
| fan | tinyint(1) | YES | | NULL | |
| student | tinyint(1) | YES | | NULL | |
| volunteer | tinyint(1) | YES | | NULL | |
| staff | tinyint(1) | YES | | NULL | |
| companion | tinyint(1) | YES | | NULL | |
| id_number | int(11) | YES | | NULL | |
| deceased | tinyint(1) | YES | | NULL | |
| load_donation | tinyint(1) | YES | | NULL | |
| load_panel | tinyint(1) | YES | | NULL | |
| load_participant | tinyint(1) | YES | | NULL | |
| conversion_note | text | YES | | NULL | |
| replacedby_fk | int(11) | YES | MUL | NULL | |
| companion_to_fk | int(11) | YES | MUL | NULL | |
| name_for_program | varchar(255) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| subcommittee | enum('Arts','Bus','HC','IA','Pol','ST','Student','Floater') | YES | | NULL | |
| companion_type | enum('spouse/partner','child','friend') | YES | | NULL | |
| days_here | varchar(6) | YES | | NULL | |
| new_or_returning | enum('new','returning') | YES | | NULL | |
| confirmation_sheet | tinyint(1) | YES | | NULL | |
| bio_in | tinyint(1) | YES | | NULL | |
| photo_in | tinyint(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| date_topics_letter_sent | date | YES | | NULL | |
| topics_received | tinyint(1) | YES | | NULL | |
| rating | enum('A','B','C','D','F') | YES | | NULL | |
| flight_info_received | tinyint(1) | YES | | NULL | |
| transportation_email_sent | tinyint(1) | YES | | NULL | |
| bringing_children | tinyint(1) | YES | | NULL | |
| date_address_verfied | date | YES | | NULL | |
+------------------------------+-------------------------------------------------------------+------+-----+---------+----------------+
126 rows in set (0.01 sec)
mysql> select person_pk, subcommittee from person where person_pk = 1381;
+-----------+--------------+
| person_pk | subcommittee |
+-----------+--------------+
| 1381 | Floater |
+-----------+--------------+
1 row in set (0.00 sec)
Upvotes: 5
Views: 6418
Answers (3)
Reputation: 3740
I conclude that this is a bug. MySQL reported no errors when it did the alter, and showed the new value when displaying the details of the column, but it didn't believe the new value was there. RolandoMySQLDBA confirmed that this is a "touchy subject" (his words).
So, what I did was this:
Create a new enum column named 'subcommittee2'.
Update the values of the new column with those of the old (update person set subcommittee = subcommittee2).
Rename 'subcommitte' to 'subcommittee_old'.
Rename 'subcommittee2' to 'subcommittee'.
Running by app confirms that the new value is now accepted.
UPDATE: Now the client asked for another enum, and using the technique just above didn't work. This time creating a new column, transferring the value, and renaming it didn't work. Same error message.
UPDATE2: So I created the new column 'subcommittee2', populated it from the old column, and just left it that way. In my app, I changed to use the new column name. Yucky, but one has to get one's work done...
UPDATE3: I tried to add to the enum values on another field, and it worked, so now I have this theory: The newly-added values must be no longer than existing values. For example, the longest value in this case was 'accepted', and I added values 'hold' and 'wait', which worked fine. (Recall that the original error was "Data truncated".)
Upvotes: 1
Reputation: 44343
This is a very touchy subject with MySQL
I wrote posts about altering ENUM lists in the DBA StackExchange
Jan 24, 2012: Is it possible to change ENUM() lists?Oct 05, 2011: Can I rename the values in a MySQL ENUM column in one query?
It is risky business when doing this with populated data. You should have done this with an empty table and then reload the table.
Upvotes: 1
Reputation: 11142
Judging by this answer, it seems that the previous order of the ENUM must be the same, with the new possibles at the end. So I would say see what the original order was with SHOW CREATE TABLE and then append Student to the end of that list.
Upvotes: 0
Related Questions
- Removing enum values from mysql column
- MySQL Change a column ENUM value
- PHP mysqli - updating value of enum field results in empty string, but same query works when run in PHPMyAdmin
- MYSQL "not equal to" on enum
- MySQL Updating enum field type vlaue
- How can I remove a value from an enum in mysql?
- enum value not updating in mysql table?
- Changing an enum type in mysql
- Changing the Value of a MySQL ENUM Value, Throughout a Table
- enum problem in mysql field