Question 414860 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you for the response. I have fixed this issue, the issue is that the script that i wrote is changing the columns to utf8mb4 but not retaining their old values for enum and default values


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search