cPanel. Upgrade MySQL 5.5 to 5.7

For example, update the MySQL server from version 5.5 to 5.7.
Before performing an update, it is imperative that you make backup copies of all databases, including the standard ones.

Open the WHM panel and select in the menu “SQL Services” – “MySQL/MariaDB Upgrade”.

I had MySQL version 5.5 installed, in the versions I chose immediately version 5.7 and clicked Next, four warnings appeared in the next window, which I carefully read and tick, briefly describe them:

1) MySQL server after the update will work in strict mode, well, I’ll write about this at the end of the article.
2) The current version of MySQL server is older by several versions, well, this is not a problem, in my case the update script automatically first updated to version 5.6, and then to 5.7, and also updated and corrected tables in databases.
3) You need to backup the databases.
4) Returning to previous versions of MySQL is not supported, that is, after updating via the WHM panel, you can’t put the versions below, they will not be listed and I don’t recommend doing this manually.

In the next window, I was asked to choose the type of update: “Automatic update” or “Interactive update”, I chose interactive, in fact they are almost the same, only in the interactive after each step you need to click “Next”, after updating MySQL one of the steps is to re-build EasyApache 4 with the current active configuration. If that, EasyApache 4 can be re-build then again through the menu “Software” – “EasyApache 4”.

In my case, re-build of EasyApache 4 was not required, the update process logs were saved to the /var/cpanel/logs/mysql_upgrade.20190509-195153/ directory, I also looked at MySQL errors in the /var/lib/mysql/SERVERNAME.err file, fortunately there were none, there were only MySQL restart logs.

Now the most interesting thing is that after the update MySQL 5.7 started to work in strict mode, that is, several parameters were added to the “sql_mode”, which I hadn’t been activated before, this did not affect most sites, for example WordPress and phpBB, but one old self-written site did not open, an error was displayed:

Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘timestamp_r’ at row 1

I connected to MySQL and looked at the value of sql_mode:

mysql -u root -p
show variables like 'sql_mode';

Then it was displayed:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

To quickly return the site to work, I executed the query:

SET GLOBAL sql_mode='NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO';

And also added to the /etc/my.cnf file:

sql_mode=NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO

And restarted MySQL server.
Done, MySQL server updated.