Upgrading to MySQL 8.0 utf8mb4 issue

[Charlick]

Hello everyone!

We currently have a webstite running on a Centos 7.7 server with MySQL 5.7 and we want to upgrade it to MySQL 8.0. We used the checker beforehand to see if our database can bear it, but we encontered those warnings about utf8mb4 :

Usage of utf8mb3 charset
  Warning: The following objects use the utf8mb3 character set. It is
    recommended to convert them to use utf8mb4 instead, for improved Unicode
    support.

So i get that i have to convert all my databases/tables/columns who are using utf8mb3 to utf8mb4 for improved unicode. But that means going through 250* columns who needs to be modified. For this website a can manage it, but i have the same issue for another server wich is more about 7000 modifications.

So here are my questions : what are the benefits of the use of utf8mb4 instead of utf8mb3? Is it mandatory to have a fonctionnal website? Is there any possibilities to automate it? Will my website explode if i don't convert all thoses tables/columns?

Also, it's a minor thing but i have another question about NO_ZERO_IN_DATE and NO_ZERO_DATE mode with this warning message :

By default zero date/datetime/timestamp values are no longer allowed
    in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
    SQL_MODE by default. These modes should be used with strict mode as they will
    be merged with strict mode in a future release. If you do not include these
    modes in your SQL_MODE setting, you are able to insert
    date/datetime/timestamp values that contain zeros. It is strongly advised to
    replace zero values with valid ones, as they may not work correctly in the
    future.

All my tables are in InnoDB and i tought that issue where already fixed by the use of InnoDB, maybe i'm not aware of what that's impliying for my databases. Can someone give me some explanation about that?

Thank you a lot in advance.

 

[cPanelLauren]

So for the conversion I'm not seeing much on doing it en mass MySQL :: MySQL 8.0 Reference Manual :: 10.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets - StackOverflow has some information on this How to change all the tables in my database to UTF8 character set? for another type of conversion which uses the same methods to change but you'd want to be very careful when making those changes.

As for the zero date mode - are you including those in your SQL_MODE setting? The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes they have some good information on this here but it'd be best to see what your modes are first before moving forward.

 

[Charlick]

Hello,

Thank you for your reply.

Ok, i will investigate that method, i don't see myself convert 10k+ columns for a single server. For what i see it seems more doable than what I found myself. Do you know what might be the outcome if I don't do those convertions? it will a take a large amount of time and i have to report that to my n+1

For the date mode, I checked the my.cnf and i see no modes included. So i guess addind the NO_ZERO_DATE and the NO_ZERO_IN_DATE should solve that issue.

 

[digitaliway]

I am also interested in knowing about this upgrade from 5.7 to 8. We have a server with over 120 wordpress websites and see the exact same warnings listed here. If we perform the upgrade will all the wordpress sites go down if we do not convert not from utf8mb3 to utf8mb4 ? the same question for the warning NO_ZERO_IN_DATE and NO_ZERO_DATE, will all sites go down after upgrade? our SQL MODE is set to this currently: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

[cPanelLauren]

WordPress has some stuff on this already that is worth reading @digitaliway Converting to innoDB storage engine | WordPress.org there is also a couple of plugins that will do the conversion for you. As far as MYISAM -> INNODB but for the UTF8 conversion there really isn't a lot of batch conversion information. The following in response to @Charlick may also be helpful for you.


@Charlick I'm not sure you needed to add those modes since the following is noted:

as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
SQL_MODE by default.

Unless you're using a version of 5.7 prior to 5.7.8

Do you know what might be the outcome if I don't do those convertions? it will a take a large amount of time and i have to report that to my n+1

For the utf8 conversions it's just for improved unicode support so the tables that aren't converted won't be experiencing the improved support - it doesn't look like there are huge issues.