Menu
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)
441
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.
157
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".)
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
