Upgrading mysql from version 5.6 to 8.0

Photo by Markus Winkler on Unsplash

Recently we upgraded our mysql databases from version 5.6 to 8.0. It was a great learning experience so i wanted to share all the challenges we faced while upgrading our mysql database along with their potential solutions.

The Upgrade path

According to mysql documentation we should not directly upgrade from mysql 5.6 to 8.0.First we should upgrade from mysql 5.6 to 5.7 and then from 5.7 to 8.0 you can read more about upgrade paths at : https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html

Strict SQL Mode

from mysql documentation

“Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE”

for more information on sql strict mode please refer : https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

The problem is mysql 8.0 has sql strict mode enabled by default which is not the case in mysql 5.6 this means queries which does not produce an error on mysql 5.6 may fail on mysql 8.0.

Let’s try to identify these queries and understand why they may fail.

let’s say we have a table with the following structure

CREATE TABLE employee(
 id INT NOT NULL, 
 name VARCHAR(40) NOT NULL,
 address VARCHAR(200) NOT NULL
);

Note that all the columns have NOT NULL constraint with no default value.

now let’s have a look at the insert query which we will be using.

INSERT INTO employee (id,name) VALUES(“1”,”emp-1”); 

when strict mode is enabled this query will produce the following error

ERROR 1364 (HY000): Field ‘address’ doesn’t have a default value

let’s try to understand the problem. when a column has NOT NULL constraint mysql expects that column to have a value. when strict mode is disabled and no default value is specified(in the table schema) mysql will chose a default value and insert it in the respective column but when strict mode is enabled and no default value is specified
mysql gives an error.

Solution

depending on the business logic, we can either have a default value for the column or have the column specified as nullable.

Let’s have a look at another insert query.

INSERT INTO employee (id,name,address) VALUES(“1”,”aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa”,”address”);

the max length for column name is 40 chars but in our query we have given 41 chars, let’s see how our query behaves in different sql modes.

when mysql mode is disabled our insert query will insert only the first 40 characters which will cause data truncation.

when sql strict mode is enabled mysql gives the following error.

ERROR 1406 (22001): Data too long for column ‘name’ at row 1

Solution :

Increase the column length’s if the queries are failing.
If data truncation is not an issue you can truncate the data at application level (before saving it in DB).

Let’s look at some of the modes of sql strict mode , what do they mean and potential errors which we could face if these modes are enabled.

ONLY_FULL_GROUP_BY

from mysql docs

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

let’s try to understand this with an example.

Consider the following table structure.

CREATE TABLE `student` (
`id` INT DEFAULT NULL,
`subject` VARCHAR(50) DEFAULT NULL,
`marks` INT DEFAULT NULL
);

if we use the following query.

select id,subject,marks from student group by id;

we will get this error.

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.student.subject’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

As you already know by now, when we are using group by the columns in select and having query either be
* directly mentioned in the group by clause.
* passed to aggregate function (ex MAX(marks))
* functionally dependent on columns mentioned in the group by clause.

How to find out if columns are functionally dependent ?
If a columns specified in group by clause are primary keys or non null unique columns of that table then we can say that all the other columns in that table are functionally dependent on these columns.
you can read more about it at : https://dev.mysql.com/doc/refman/8.0/en/group-by-functional-dependence.html

NO_ZERO_IN_DATE

If this mode and strict mode are enabled, dates with zero parts(‘0000–00–00’) are not permitted and inserts produce an error.

Solution :
remove ‘0000–00–00’ if it’s a default value in any table schema and ensure that the queries formed are not having zero value for date.

ERROR_FOR_DIVISION_BY_ZERO

If this mode and strict mode are enabled, division by zero produces an error.

Solution :
this can be avoided using flow control functions you can read more about it at
https://dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html

Default Character Set is UTF8MB4
If we create a new database in mysql 8.0 it will be created with default character set of UTF8Mb4 so if a new table is created in such database without specifying the default character set the table will be created with UTF8Mb4 encoding if this is not the desired behavior you can specify default character set while creating any new database.

CREATE DATABASE db_name CHARACTER SET latin1;

to learn more about how mysql determines the default character set if its not provided follow this link : https://dev.mysql.com/doc/refman/8.0/en/charset-database.html

We have looked at some problems which we can face while upgrading the database, This will hopefully help you identify and reduce the errors while upgrading your database.