CODEWITHSUNDEEP
mysqlmysql-5.7mysql-8.0
johnd3v
johnd3v

Reputation: 13

Issue with ENUM Column Becoming Nullable After Upgrading from utf8mb3 to utf8mb4

I recently upgraded the character set of my MySQL database from utf8mb3 to utf8mb4. One of my table columns is defined as an ENUM('saved', 'updated', 'deleted') NOT NULL DEFAULT 'saved'.

However, after performing the charset upgrade, I noticed that the column definition changed unexpectedly to ENUM('saved', 'updated', 'deleted') DEFAULT NULL, making it nullable with a default value of NULL.

It should retain the not null and default to saved

Upvotes: 1

Views: 63

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76464

To cope with this issue, you will need to create a table with the new data format, so if anything goes wrong, you can recover your data without having to roll back or to run some backup. First, run

show create table yourtable;

Now, you see your table's definition, copy the create table yourtable command entirely into a text editor, change three things in this new script:

  • have yourtable -> yourtable_temp
  • change the column definition for the field you are to change to the one that's more appropriate to you
  • remove the fields you are not interested in from this definition, just have your id and the field you are changing

Now, run this script, having something like create table yourtable_temp ...

And insert your data:

insert into yourtable_temp(id, your_enum_field)
select id, your_enum_field
from yourtable;

Now alter your original table and make sure it looks like

alter table yourtable
modify column your_enum_field ENUM('saved', 'updated', 'deleted') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'saved';

And now check for your data. If it's good, then you can drop your temporarily created table. If not, then

update yourtable
join yourtable_temp
on yourtable.id = yourtable_temp.id
set yourtable.your_enum_field = yourtable_temp.your_enum_field;

check your data, it should be correct. Now you can drop your temporary table.

Upvotes: -1

Related Questions