MySQL :: Upgrading to MySQL 8.0? Here is what you need to know...

In my previous blog post, I had described the steps to perform an in-place upgrade for upgrading from MySQL 5.7 to MySQL 8.0. In this blog post, I will discuss about the considerations that needs to be taken into account while upgrading to MySQL 8.0

The general in-place upgrade requirement is that MySQL 8.0 will understand the disk image produced by MySQL 5.7. This is generally true, for example MySQL 8.0 is able to read the MySQL 5.7 FRM files and create the new data dictionary based on the MySQL 5.7 image. In most cases, the upgrade from MySQL 5.7 to MySQL 8.0 is seamless.

However, there are a few things we decided to discontinue in MySQL 8.0 (explained in-depth in the next section). When the MySQL 8.0 binary is started on the MySQL 5.7 image, it will check for these incompatibilities and terminate the upgrade process if one or more of these incompatibilities are found in the MySQL 5.7 image. Note that the MySQL 8.0 binary will terminate without having done any changes to the MySQL 5.7 image, so the user can restart MySQL 5.7 and fix the reported issues and then start the upgrade process again.

In my previous blog post, I had described how the Upgrade Checker utility that comes with the MySQL Shell 8.0 can be run against a MySQL 5.7 server to check for upgrade preparedness. The Upgrade Checker will perform the most of the checks that in-place MySQL 8.0 upgrade does and if it does not find any issues, the upgrade to MySQL 8.0 will succeed.

Another thing to be aware of is that MySQL 8.0 has introduced an auto-upgrade mechanism. As of MySQL 8.0.11, the server version is written to the data dictionary tablespace. When the server starts, it will compare the server version number found in the mysql.ibd file (source) with its own server version number (target) and will perform an in-place upgrade only if it supports the source-target combination. The intention here is to automate the upgrade process, i.e. remove the need for the mysql_upgrade script in the future. The immediate consequence for MySQL 8.0.11 is that it refuses to upgrade from MySQL 8.0 DMRs (8.0.1, 8.0.1, 8.0.2, 8.0.3, 8.0.4) to MySQL 8.0.11 because this path is not safe. It will accept upgrade from any MySQL 5.7 GA release to MySQL 8.0.11, and it will accept upgrade to upcoming MySQL 8.0 releases (8.0.12, 8.0.13, etc.).

But now, let us take a look at the incompatibilities between MySQL 5.7 and MySQL 8.0, most of which are caught by the Upgrade Checker as well as checked when a MySQL 8.0 server starts on a MySQL 5.7 image.

Following is the list that needs to be reviewed and cleaned up before upgrading to MySQL 8.0 (done automatically by the Upgrade Checker):

The transactional data dictionary(DD) support is introduced in MySQL 8.0 for which several new DD tables are created in the mysql schema. Hence user tables with the conflicting names in the mysql schema should be dropped or renamed prior to upgrade. The DD table names are mentioned below:‘catalogs’, ‘character_sets’, ‘collations’, ‘column_statistics’, ‘column_type_elements’, ‘columns’, ‘dd_properties’, ‘events’, ‘foreign_key_column_usage’, ‘foreign_keys’, ‘index_column_usage’, ‘index_partitions’, ‘index_stats’, ‘indexes’, ‘parameter_type_elements’, ‘parameters’, ‘resource_groups’, ‘routines’, ‘schemata’, ‘st_spatial_reference_systems’, ‘table_partition_values’, ‘table_partitions’, ‘table_stats’, ‘tables’, ‘tablespace_files’, ‘tablespaces’, ‘triggers’, ‘view_routine_usage’, ‘view_table_usage’. The tables that require to be renamed or dropped can be identified by running the following query in MySQL 5.7. (In this example the catalogs is a user table present in the mysql schema which conflicts with the DD name.)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE LOWER(TABLE_SCHEMA) = 'mysql'
    -> and LOWER(TABLE_NAME) IN
    -> (
    -> 'catalogs',
    -> 'character_sets',
    -> 'collations',
    -> 'column_statistics',
    -> 'column_type_elements',
    -> 'columns',
    -> 'dd_properties',
    -> 'events',
    -> 'foreign_key_column_usage',
    -> 'foreign_keys',
    -> 'index_column_usage',
    -> 'index_partitions',
    -> 'index_stats',
    -> 'indexes',
    -> 'parameter_type_elements',
    -> 'parameters',
    -> 'resource_groups',
    -> 'routines',
    -> 'schemata',
    -> 'st_spatial_reference_systems',
    -> 'table_partition_values',
    -> 'table_partitions',
    -> 'table_stats',
    -> 'tables',
    -> 'tablespace_files',
    -> 'tablespaces',
    -> 'triggers',
    -> 'view_routine_usage',
    -> 'view_table_usage'
    -> );

+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| mysql        | catalogs   |
+--------------+------------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

-> WHERE LOWER(TABLE_SCHEMA) = 'mysql'

-> and LOWER(TABLE_NAME) IN

-> (

-> 'catalogs',

-> 'character_sets',

-> 'collations',

-> 'column_statistics',

-> 'column_type_elements',

-> 'columns',

-> 'dd_properties',

-> 'events',

-> 'foreign_key_column_usage',

-> 'foreign_keys',

-> 'index_column_usage',

-> 'index_partitions',

-> 'index_stats',

-> 'indexes',

-> 'parameter_type_elements',

-> 'parameters',

-> 'resource_groups',

-> 'routines',

-> 'schemata',

-> 'st_spatial_reference_systems',

-> 'table_partition_values',

-> 'table_partitions',

-> 'table_stats',

-> 'tables',

-> 'tablespace_files',

-> 'tablespaces',

-> 'triggers',

-> 'view_routine_usage',

-> 'view_table_usage'

-> );

+--------------+------------+

| TABLE_SCHEMA | TABLE_NAME |

+--------------+------------+

| mysql | catalogs |

+--------------+------------+

1 row in set (0.00 sec)

mysql>

Hence such user tables should be renamed or dropped prior to upgrade:

mysql>ALTER TABLE catalogs RENAME user_catalogs;
Query OK, 0 rows affected (0.05 sec)

OR

mysql> DROP TABLE catalogs;
Query OK, 0 rows affected (0.06 sec)

mysql>ALTER TABLE catalogs RENAME user_catalogs;

Query OK, 0 rows affected (0.05 sec)

mysql> DROP TABLE catalogs;

Query OK, 0 rows affected (0.06 sec)

An attempt to upgrade with any one of the above mentioned tables results in an error:

2018-04-16T04:29:09.4186062Z 1 [ERROR] [MY-010781] Found ./mysql/catalogs.frm file in mysql schema. DD will create .ibd file with same name. Please rename table and start upgrade process again.
2018-04-16T04:29:09.4186322Z 1 [ERROR] [MY-010336] Found .frm file with same name as one of the Dictionary Tables.
2018-04-16T04:29:09.4188822Z 0 [ERROR] [MY-010020] Data Dictionary initialization failed.
2018-04-16T04:29:09.4188972Z 0 [ERROR] [MY-010119] Aborting
2018—04-16T04:29:11.0519222Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql-8.0/dbg-8.0/sql/mysqld: Shutdown complete.

2018-04-16T04:29:09.4186062Z 1 [ERROR] [MY-010781] Found ./mysql/catalogs.frm file in mysql schema. DD will create .ibd file with same name. Please rename table and start upgrade process again.

2018-04-16T04:29:09.4186322Z 1 [ERROR] [MY-010336] Found .frm file with same name as one of the Dictionary Tables.

2018-04-16T04:29:09.4188822Z 0 [ERROR] [MY-010020] Data Dictionary initialization failed.

2018-04-16T04:29:09.4188972Z 0 [ERROR] [MY-010119] Aborting

2018—04-16T04:29:11.0519222Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql-8.0/dbg-8.0/sql/mysqld: Shutdown complete.

Data types like old style decimals, old style varchar, old style TIME/DATETIME and TIMESTAMP types which became obsolete in MySQL 5.1, MySQL 5.0 and MySQL 5.6 respectively, that have persisted up until MySQL 5.7 due to the binary upgrade will not be supported in MySQL 8.0. These tables can be identified by running CHECK TABLE…FOR UPGRADE or mysqlcheck with check-upgrade option in MySQL 5.7 prior to upgrade. In addition tables using old style TIME/DATETIME and TIMESTAMP can be identified by enabling a session variable which is discussed in the following blog.

mysql> check table 41_decimal for upgrade;
+-----------------+-------+----------+-------------------------------------------------------------------------------------+
| Table           | Op    | Msg_type | Msg_text                                                                            |
+-----------------+-------+----------+-------------------------------------------------------------------------------------+
| test.41_decimal | check | error    | Table upgrade required for `test`.`41_decimal`. Please dump/reload table to fix it! |
+-----------------+-------+----------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> check table 55_temporal for upgrade;
+------------------+-------+----------+------------------------------------------------------------------------------------------+
| Table            | Op    | Msg_type | Msg_text                                                                                 |
+------------------+-------+----------+------------------------------------------------------------------------------------------+
| test.55_temporal | check | error    | Table upgrade required. Please do "REPAIR TABLE `55_temporal`" or dump/reload to fix it! |
+------------------+-------+----------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7/client/mysqlcheck --user=root --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --databases test --check-upgrade
error    : Table upgrade required for `test`.`41_decimal`. Please dump/reload table to fix it!
test.55_temporal
error    : Table upgrade required. Please do "REPAIR TABLE `55_temporal`" or dump/reload to fix it!
test.child                                         OK
test.geom                                          OK
test.jemp                                          OK
test.jemp_myisam                                   OK
test.opening_lines                                 OK
test.parent                                        OK
test.t_blackhole                                   OK
test.t_blob                                        OK
test.t_blob_myisam                                 OK
test.t_compressed                                  OK
test.t_compressed2                                 OK
test.t_compressed3                                 OK
test.t_dynamic                                     OK
test.t_gen_stored                                  OK
test.t_gen_stored_myisam                           OK
test.t_gen_stored_myisam2                          OK
test.t_index                                       OK
test.t_json                                        OK
test.t_myisam_compressed                           OK
test.t_myisam_compressed2                          OK
test.t_myisam_compressed3                          OK
test.t_sc~!@#$%^&*(                                OK
test.vt2                                           OK
nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$

mysql> check table 41_decimal for upgrade;

+-----------------+-------+----------+-------------------------------------------------------------------------------------+

+-----------------+-------+----------+-------------------------------------------------------------------------------------+

+-----------------+-------+----------+-------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> check table 55_temporal for upgrade;

+------------------+-------+----------+------------------------------------------------------------------------------------------+

+------------------+-------+----------+------------------------------------------------------------------------------------------+

+------------------+-------+----------+------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7/client/mysqlcheck --user=root --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --databases test --check-upgrade

error : Table upgrade required for `test`.`41_decimal`. Please dump/reload table to fix it!

test.55_temporal

error : Table upgrade required. Please do "REPAIR TABLE `55_temporal`" or dump/reload to fix it!

test.child OK

test.geom OK

test.jemp OK

test.jemp_myisam OK

test.opening_lines OK

test.parent OK

test.t_blackhole OK

test.t_blob OK

test.t_blob_myisam OK

test.t_compressed OK

test.t_compressed2 OK

test.t_compressed3 OK

test.t_dynamic OK

test.t_gen_stored OK

test.t_gen_stored_myisam OK

test.t_gen_stored_myisam2 OK

test.t_index OK

test.t_json OK

test.t_myisam_compressed OK

test.t_myisam_compressed2 OK

test.t_myisam_compressed3 OK

test.t_sc~!@#$%^&*( OK

test.vt2 OK

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$

Tables using such data types cannot be upgraded and should be fixed through REPAIR TABLE and dump/reload for old style varchar/old style decimal:

mysql> REPAIR TABLE 55_temporal;

+------------------+--------+----------+-------------------------------------------------------------------------------------+
| Table            | Op     | Msg_type | Msg_text                                                                            |
+------------------+--------+----------+-------------------------------------------------------------------------------------+
| test.55_temporal | repair | Note     | TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. |
| test.55_temporal | repair | status   | OK                                                                                  |
+------------------+--------+----------+-------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> 

Dump: 
nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$./client/mysqldump --databases test --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --user=root>test.sql

Restore:
mysql> .\ /home/nisha/workspace1/mysql-5.7/dbg-5.7/test.sql

mysql> REPAIR TABLE 55_temporal;

+------------------+--------+----------+-------------------------------------------------------------------------------------+

+------------------+--------+----------+-------------------------------------------------------------------------------------+

+------------------+--------+----------+-------------------------------------------------------------------------------------+

2 rows in set (0.01 sec)

mysql>

Dump:

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$./client/mysqldump --databases test --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --user=root>test.sql

Restore:

mysql> .\ /home/nisha/workspace1/mysql-5.7/dbg-5.7/test.sql

Upgrading to MySQL 8.0 when such tables exists will result in an error:

2018-04-13T10:43:08.980677Z 2 [ERROR] [MY-011082] Table upgrade required for ‘test‘.‘41_decimal‘. Please dump/reload table to fix it!
2018-04-13T10:43:08.980813Z 2 [ERROR] [MY-010923] Table upgrade required. Please do "REPAIR TABLE ‘55_temporal " or dump/reload to fix it!
2018-04-13T10:43:10.771457Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.
2018-04-13T10:43:10.771479Z 0 [ERROR] [MY-010119] Aborting
2018-04-13T10:43:12.408765Z 0 [System] [MY-010910] /home/nisha/workspacel/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

2018-04-13T10:43:08.980677Z 2 [ERROR] [MY-011082] Table upgrade required for ‘test‘.‘41_decimal‘. Please dump/reload table to fix it!

2018-04-13T10:43:08.980813Z 2 [ERROR] [MY-010923] Table upgrade required. Please do "REPAIR TABLE ‘55_temporal " or dump/reload to fix it!

2018-04-13T10:43:10.771457Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.

2018-04-13T10:43:10.771479Z 0 [ERROR] [MY-010119] Aborting

2018-04-13T10:43:12.408765Z 0 [System] [MY-010910] /home/nisha/workspacel/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

Support for partitioned tables using non-native partitioning was deprecated in MySQL 5.7 and is removed in MySQL 8.0. These tables can be identified in MySQL 5.7 prior to upgrade by using the following query or by running mysqlcheck with check-upgrade option.

mysql> SELECT TABLE_SCHEMA, TABLE_NAME
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
    -> AND CREATE_OPTIONS LIKE '%partitioned%';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| partitions   | p1_key     |
| partitions   | p2_range   |
| partitions   | p3_list    |
| partitions   | p4_hash    |
| partitions   | p5_sub     |
+--------------+------------+
5 rows in set, 5 warnings (0.03 sec)

mysql> show create table p5_sub;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                    |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| p5_sub | CREATE TABLE `p5_sub` (
  `id` int(11) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                 |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | The partition engine, used by table 'partitions.p5_sub', is deprecated and will be removed in a future release. Please use native partitioning instead. |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 



nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$ ./client/mysqlcheck --user=root --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --databases partitions --check-upgrade 
partitions.p1_key                                  OK
warning  : The partition engine, used by table 'partitions.p1_key', is deprecated and will be removed in a future release. Please use native partitioning instead.
partitions.p2_range                                OK
warning  : The partition engine, used by table 'partitions.p2_range', is deprecated and will be removed in a future release. Please use native partitioning instead.
partitions.p3_list                                 OK
warning  : The partition engine, used by table 'partitions.p3_list', is deprecated and will be removed in a future release. Please use native partitioning instead.
partitions.p4_hash                                 OK
warning  : The partition engine, used by table 'partitions.p4_hash', is deprecated and will be removed in a future release. Please use native partitioning instead.
partitions.p5_sub                                  OK
warning  : The partition engine, used by table 'partitions.p5_sub', is deprecated and will be removed in a future release. Please use native partitioning instead.
nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$

mysql> SELECT TABLE_SCHEMA, TABLE_NAME

-> FROM INFORMATION_SCHEMA.TABLES

-> WHERE ENGINE NOT IN ('innodb', 'ndbcluster')

-> AND CREATE_OPTIONS LIKE '%partitioned%';

+--------------+------------+

| TABLE_SCHEMA | TABLE_NAME |

+--------------+------------+

| partitions | p1_key |

| partitions | p2_range |

| partitions | p3_list |

| partitions | p4_hash |

| partitions | p5_sub |

+--------------+------------+

5 rows in set, 5 warnings (0.03 sec)

mysql> show create table p5_sub;

+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| p5_sub | CREATE TABLE `p5_sub` (

`id` int(11) DEFAULT NULL,

`purchased` date DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE ( YEAR(purchased))

SUBPARTITION BY HASH ( TO_DAYS(purchased))

SUBPARTITIONS 2

(PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,

PARTITION p1 VALUES LESS THAN (2000) ENGINE = MyISAM,

PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ |

1 row in set, 1 warning (0.00 sec)

mysql> show warnings;

+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message |

+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+

| Warning | 1287 | The partition engine, used by table 'partitions.p5_sub', is deprecated and will be removed in a future release. Please use native partitioning instead. |

+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$ ./client/mysqlcheck --user=root --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --databases partitions --check-upgrade

partitions.p1_key OK

warning : The partition engine, used by table 'partitions.p1_key', is deprecated and will be removed in a future release. Please use native partitioning instead.

partitions.p2_range OK

warning : The partition engine, used by table 'partitions.p2_range', is deprecated and will be removed in a future release. Please use native partitioning instead.

partitions.p3_list OK

warning : The partition engine, used by table 'partitions.p3_list', is deprecated and will be removed in a future release. Please use native partitioning instead.

partitions.p4_hash OK

warning : The partition engine, used by table 'partitions.p4_hash', is deprecated and will be removed in a future release. Please use native partitioning instead.

partitions.p5_sub OK

warning : The partition engine, used by table 'partitions.p5_sub', is deprecated and will be removed in a future release. Please use native partitioning instead.

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$

Such tables should be altered to use an engine which supports native partitioning like InnoDB or the partitions should be removed.

<mysql>ALTER TABLE p5_sub ENGINE = INNODB;
Query OK, 0 rows affected (0.09 sec)

OR

<mysql>ALTER TABLE p5_sub REMOVE PARTITIONING;
Query OK, 0 rows affected (0.06 sec)

<mysql>ALTER TABLE p5_sub ENGINE = INNODB;

Query OK, 0 rows affected (0.09 sec)

<mysql>ALTER TABLE p5_sub REMOVE PARTITIONING;

Query OK, 0 rows affected (0.06 sec)

Trying to upgrade to MySQL 8.0 without performing the cleanup will result in an error:

2018-04-13T10:31:37.515052Z 2 [ERROR] [MY-003110] /home/nisha/workspace1/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: The 'partitioning'
feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1'
2018-04-13T10:31:37.515070Z 2 [ERROR] [MY-010348] Error in reading file ./partitions/p5_sub.frm
2018-04-13T10:31:37.515081Z 2 [ERROR] [MY-010758] Error in creating TABLE_SHARE from p5_sub.frm file.
2018-04-13T10:31:40.590114Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.
2018-04-13T10:31:40.590137Z 0 [ERROR] [MY-010119] Aborting
2018-04-13T10:31:42.101320Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

2018-04-13T10:31:37.515052Z 2 [ERROR] [MY-003110] /home/nisha/workspace1/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: The 'partitioning'

feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1'

2018-04-13T10:31:37.515070Z 2 [ERROR] [MY-010348] Error in reading file ./partitions/p5_sub.frm

2018-04-13T10:31:37.515081Z 2 [ERROR] [MY-010758] Error in creating TABLE_SHARE from p5_sub.frm file.

2018-04-13T10:31:40.590114Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.

2018-04-13T10:31:40.590137Z 0 [ERROR] [MY-010119] Aborting

2018-04-13T10:31:42.101320Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

CREATE TRIGGER prior to MySQL 5.0.17 did not support definer attribute. Such trigger definitions with missing/empty definer attribute or an invalid creation context (i.e character_set_client, collation_collection, database collation attributes) which have persisted until MySQL 5.7 cannot be upgraded. These triggers can be identified by running mysqlcheck with check-upgrade option or CHECK TABLE in MySQL 5.7.

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$./client/mysqlcheck --user=root --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --databases triggers --check-upgrade 
triggers.t1
Warning  : No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning  : No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning  : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning  : No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning  : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning  : No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning  : No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning  : No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
Warning  : No definer attribute for trigger 'triggers'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
status   : OK
triggers.t2                                        OK
nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$ 

mysql> check table t1;
+-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                                                                                                                                                                                         |
+-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.   |
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.                  |
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.  |
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.    |
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.  |
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.   |
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.    |
| triggers.t1 | check | Warning  | No definer attribute for trigger 'triggers'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.                   |
| triggers.t1 | check | status   | OK                                                                                                                                                                                                               |
+-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)


mysql> select definer, trigger_name from INFORMATION_SCHEMA.TRIGGERS where definer='';
+---------+------------------------+
| definer | trigger_name           |
+---------+------------------------+
|         | trg_t1_before_insert   |
|         | t1_bi                  |
|         | trg_t1_after_insert_1  |
|         | trg_t1_after_insert    |
|         | trg_t1_after_insert_3  |
|         | trg_t1_before_update_3 |
|         | trg_t1_before_update   |
|         | trg_t1_after_update    |
|         | trg1                   | 
+---------+------------------------+
 9 rows in set (0.02 sec)
mysql>

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$./client/mysqlcheck --user=root --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --databases triggers --check-upgrade

triggers.t1

Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

Warning : No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

Warning : No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

Warning : No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

Warning : No definer attribute for trigger 'triggers'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.

status : OK

triggers.t2 OK

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$

mysql> check table t1;

+-------------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'t1_bi'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert_1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_insert_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_update_3'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_before_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg_t1_after_update'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

| triggers.t1 | check | Warning | No definer attribute for trigger 'triggers'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. |

10 rows in set (0.01 sec)

mysql> select definer, trigger_name from INFORMATION_SCHEMA.TRIGGERS where definer='';

+---------+------------------------+

| definer | trigger_name |

+---------+------------------------+

| | trg_t1_before_insert |

| | t1_bi |

| | trg_t1_after_insert_1 |

| | trg_t1_after_insert |

| | trg_t1_after_insert_3 |

| | trg_t1_before_update_3 |

| | trg_t1_before_update |

| | trg_t1_after_update |

| | trg1 |

+---------+------------------------+

9 rows in set (0.02 sec)

mysql>

Such triggers should be dumped/reloaded to fix the issue:

Dump:
nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$./client/mysqldump --databases triggers --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --user=root>triggers.sql

Restore:
mysql> .\ /home/nisha/workspace1/mysql-5.7/dbg-5.7/triggers.sql

Dump:

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$./client/mysqldump --databases triggers --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --user=root>triggers.sql

Restore:

mysql> .\ /home/nisha/workspace1/mysql-5.7/dbg-5.7/triggers.sql

Attempting to upgrade such triggers will result an error:

2018-04-16T05:47:19.005847Z 2 [ERROR] [MY-010061] Definer clause is missing in Trigger of Table t1. Rebuild Trigger to fix definer.
2018-04-16T05:47:19.005877Z 2 [Warning] [MY-010197] Error in reading t1.TRG file.
2018-04-16T05:47:20.377528Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.
2018-04-16T05:47:20.377554Z 0 [ERROR] [MY-010119] Aborting
2018-04-16T05:47:22.279575Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql-8.0/dbg-8.0/sql/mysqld: Shutdown complete.

2018-04-16T05:47:19.005847Z 2 [ERROR] [MY-010061] Definer clause is missing in Trigger of Table t1. Rebuild Trigger to fix definer.

2018-04-16T05:47:19.005877Z 2 [Warning] [MY-010197] Error in reading t1.TRG file.

2018-04-16T05:47:20.377528Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.

2018-04-16T05:47:20.377554Z 0 [ERROR] [MY-010119] Aborting

2018-04-16T05:47:22.279575Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql-8.0/dbg-8.0/sql/mysqld: Shutdown complete.

In versions prior to MySQL 8.0, InnoDB auto generates foreign key constraint name by appending the table name with ‘_ibfk_X’ where X is a digit, when it is not explicitly specified by the user. If the table name is a multi byte 64 characters, like the cyrillian table name ‘имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк’ used in the example below, then the auto generated foreign key constraint name exceeds 64 chars.

mysql> show create table имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк;
+--------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                                                                                                              | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк                                                   | CREATE TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` (
  `fld1` int(11) DEFAULT NULL,
  `fld2` int(11) NOT NULL,
  PRIMARY KEY (`fld2`),
  CONSTRAINT `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

mysql> show create table имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк;

+--------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк | CREATE TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` (

`fld1` int(11) DEFAULT NULL,

`fld2` int(11) NOT NULL,

PRIMARY KEY (`fld2`),

CONSTRAINT `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.01 sec)

mysql>

In MySQL 8.0, tables with foreign key constraint where the constraint name exceeds 64 chars is not supported in order to adhere to the maximum identifier length of database objects. Such tables can identified by running the following query:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN
(SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE
LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

+--------------+--------------------------------------------------------------------------------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME                                                                                                         |
+--------------+--------------------------------------------------------------------------------------------------------------------+
| test         | имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк                                                   |
+--------------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN

(SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)

FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE

LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

+--------------+--------------------------------------------------------------------------------------------------------------------+

| TABLE_SCHEMA | TABLE_NAME |

+--------------+--------------------------------------------------------------------------------------------------------------------+

| test | имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк |

+--------------+--------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

These tables should be altered by dropping the constraint and adding the constraint with an explicit constraint name by ensuring the foreign key constraint name does not exceed 64 chars.

mysql> ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` DROP FOREIGN KEY `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк_ibfk_1`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` ADD CONSTRAINT FOREIGN KEY FK1 (fld2) REFERENCES t1(fld1);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

mysql> ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` DROP FOREIGN KEY `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк_ibfk_1`;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE `имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк` ADD CONSTRAINT FOREIGN KEY FK1 (fld2) REFERENCES t1(fld1);

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

Attempting to upgrade these tables without fixing them will result in an error:

2018-04-16T05:39:50.419683Z 2 [ERROR] [MY-001059] /home/nisha/workspacel/mysql-8.0/dbg-8.0/sql/mysqld: Identifier name 'имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_базы_в_кодировк' is too long
2018-04-16T05:39:50.419728Z 2 [ERROR] [MY-011066] InnoDB: Foreign key name:test/имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_базы_в_кодировк_ibfk_1 is too long, for the table
имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_базы_в_кодировк. Please ALTER the foreign key name to use less than 64 characters and try upgrade again.
2018-04-16T05:39:SO.4197962 2 [ERROR] [MY-010767] Error in fixing SE data for test.имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_базы_в_кодировк
2018-04-16T05:39:52.074421Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.
2018-04-16T05:39:52.0744572 0 [ERROR] [MY-010119] Aborting
2018-04-16T05:39:54.3568252 0 [System] [MY-010910] /home/nisha/workspacel/mysql-8.0/dbg-8.0/sql/mysqld: Shutdown complete.

2018-04-16T05:39:50.419683Z 2 [ERROR] [MY-001059] /home/nisha/workspacel/mysql-8.0/dbg-8.0/sql/mysqld: Identifier name 'имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_базы_в_кодировк' is too long

2018-04-16T05:39:50.419728Z 2 [ERROR] [MY-011066] InnoDB: Foreign key name:test/имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_базы_в_кодировк_ibfk_1 is too long, for the table

имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_базы_в_кодировк. Please ALTER the foreign key name to use less than 64 characters and try upgrade again.

2018-04-16T05:39:SO.4197962 2 [ERROR] [MY-010767] Error in fixing SE data for test.имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_базы_в_кодировк

2018-04-16T05:39:52.074421Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.

2018-04-16T05:39:52.0744572 0 [ERROR] [MY-010119] Aborting

2018-04-16T05:39:54.3568252 0 [System] [MY-010910] /home/nisha/workspacel/mysql-8.0/dbg-8.0/sql/mysqld: Shutdown complete.

Prior to MySQL 8.0, users could create views with explicit column name up to 255 chars. To adhere to the maximum length of column name, views having explicit column name greater than 64 chars is not supported in MySQL 8.0. Currently these views can be identified only by doing a SHOW CREATE VIEW in MySQL 5.7.

mysql> SHOW CREATE VIEW v1;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                  | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a123456789012345678901234567890123456789012345678901234567890123456789` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql>

mysql> SHOW CREATE VIEW v1;

+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `a123456789012345678901234567890123456789012345678901234567890123456789` | utf8 | utf8_general_ci |

1 row in set (0.00 sec)

mysql>

These views should be altered to fix the column name prior to upgrade.

mysql> ALTER VIEW v1(a12345678901234567890) AS SELECT 1;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> ALTER VIEW v1(a12345678901234567890) AS SELECT 1;

Query OK, 0 rows affected (0.01 sec)

mysql>

Attempting to upgrade without fixing these views will result an error:

2018-04-13T10:48:OS.1349662 2 [ERROR] [MY-001166] /home/nisha/workspacel/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Incorrect column na
me 'a123456789012345678901234567890123456789012345678901234567890123456789'
2018-04-13T10248:OS.1350012 2 [ERROR] [MY-011081] Upgrade of view 'test.v1' failed. Re-create the view with the explicit column name lesser the
n 64 characters.
2018-04-13T10248:OS.136664Z 2 [ERROR] [MY-010023] Error in Creating View test.v1
2018-04-13T10:48105.1384472 0 [ERROR] [MY-010022] Failed to Populate DD tables.
2018-04-13T10148:OS.138463Z o [ERROR] [MY-010119] Aborting
2018-04-13T10:48:07.092384Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql~8.0/dbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

2018-04-13T10:48:OS.1349662 2 [ERROR] [MY-001166] /home/nisha/workspacel/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Incorrect column na

me 'a123456789012345678901234567890123456789012345678901234567890123456789'

2018-04-13T10248:OS.1350012 2 [ERROR] [MY-011081] Upgrade of view 'test.v1' failed. Re-create the view with the explicit column name lesser the

n 64 characters.

2018-04-13T10248:OS.136664Z 2 [ERROR] [MY-010023] Error in Creating View test.v1

2018-04-13T10:48105.1384472 0 [ERROR] [MY-010022] Failed to Populate DD tables.

2018-04-13T10148:OS.138463Z o [ERROR] [MY-010119] Aborting

2018-04-13T10:48:07.092384Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql~8.0/dbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

Prior to MySQL 8.0, the total length of all enum elements could go up to approximately 64k since it was stored in 2 bytes in the FRM file. Hence tables and stored procedures could be created with enum elements greater than 255 chars. However in MySQL 8.0, tables or stored procedures containing too long enum literals(i.e greater 255 chars) are not supported.

mysql> show create table t_long_enum;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_long_enum | CREATE TABLE `t_long_enum` (
  `i` int(11) NOT NULL,
  `b` enum('\n         1         2         3         4         5         6         7         8         9         0\n                                                                                                   0\n                                                                                                   0') DEFAULT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> show create table t_long_enum;

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| t_long_enum | CREATE TABLE `t_long_enum` (

`i` int(11) NOT NULL,

`b` enum('\n 1 2 3 4 5 6 7 8 9 0\n 0\n 0') DEFAULT NULL,

PRIMARY KEY (`i`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)

mysql>

Such tables or stored procedures should be altered before upgrading to MySQL 8.0.

mysql> ALTER TABLE t_long_enum MODIFY b enum('\n         1         2         3         4\n');
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

mysql> ALTER TABLE t_long_enum MODIFY b enum('\n 1 2 3 4\n');

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

Any attempt to perform the upgrade without fixing such tables or stored procedures will result in an error:

2018-04-13T1048:04.512211Z 2 [ERROR] [MY-003505] /home/nisha/workspacel/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Too long enumeration/set value for column b.

2018-04-13T10:48:04.512322Z 2 [ERROR] [MY-010765] Error in Creating DD entry for test.t_long_enum
2018-04-13T10:48:04.734533Z 2 [ERROR] [MY-010768] Error in creating stored program 'test.p_long_enum'
2018-04-13T10:48:05.138447Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.
2018-04-13T10:48:05.138463Z 0 [ERROR] [MY-010119] Aborting
2018-04-13T10:48:07.092384Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

2018-04-13T1048:04.512211Z 2 [ERROR] [MY-003505] /home/nisha/workspacel/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Too long enumeration/set value for column b.

2018-04-13T10:48:04.512322Z 2 [ERROR] [MY-010765] Error in Creating DD entry for test.t_long_enum

2018-04-13T10:48:04.734533Z 2 [ERROR] [MY-010768] Error in creating stored program 'test.p_long_enum'

2018-04-13T10:48:05.138447Z 0 [ERROR] [MY-010022] Failed to Populate DD tables.

2018-04-13T10:48:05.138463Z 0 [ERROR] [MY-010119] Aborting

2018-04-13T10:48:07.092384Z 0 [System] [MY-010910] /home/nisha/workspace1/mysql-8.0/dbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

Attempting to upgrade tables with mis-matched meta data information in the ‘frm’ file and InnoDB dictionary results in an error. This could be triggered due to frm corruption. Such tables should be fixed through dump and restore before upgrade.

Dump: 
nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$./client/mysqldump --databases mismatch_frms --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --user=root>mismatch.sql 

Restore: 
mysql> .\ /home/nisha/workspace1/mysql-5.7/dbg-5.7/mismatch.sql

Dump:

nisha@nisha-PORTEGE-Z30-A:~/workspace1/mysql-5.7/dbg-5.7$./client/mysqldump --databases mismatch_frms --socket=/home/nisha/workspace1/mysql-5.7/dbg-5.7/data/mysql.sock --user=root>mismatch.sql

Restore:

mysql> .\ /home/nisha/workspace1/mysql-5.7/dbg-5.7/mismatch.sql

Attempting to upgrade without fixing this issue will result in an error:

2018-04-13T10:47:57.338648Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t5_index_cols
2018-04-13T10:47:57.350472Z 2 [ERROR] [MY-011066] InnoDB: Num of Indexes in InnoDB doesn't match with Indexes from server
2018-04-13T10:47:57.350507Z 2 [ERROR] [MY-011066] InnoDB: Indexes from InnoDB: 3 Indexes from Server: 2
2018-04-13T10:47:57.350652Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t6_indexes_num
2018-04-13T10:47:57.378661Z 2 [ERROR] [MY-011066] InnoDB: Index name: ‘kl‘ type mismatches: from server: 0 from InnoDB: 2
2018-04-13T10:47:57.378710Z 2 [ERROR] [MY-011066] InnoDB: Index name: ‘k2‘ type mismatches: from server: 0 from InnoDB: 2
2018-04-13T10:47:57.378865Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t7_indexes_unique
2018-04-13T10:47:57.389900Z 2 [ERROR] [MY-011066] InnoDB: Column precision type mismatch(i.e NULLs, SIGNED/UNSIGNED etc) for col: b
2018-04-13T10:47:57.389952Z 2 [ERROR] [MY-011066] InnoDB: Column b for table: ‘test_mismatch . t8_col_type_nulls‘ mismatches with InnoDB Dictio
nary
2018-04-13T10:47:57.390105Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t8_col_type_nulls
2018-04-13T10:47:57.404689Z 2 [ERROR] [MY-011066] InnoDB: In Index: ‘k1‘ prefix_len mismatches: from server: 45 from InnoDB: 15
2018-04-13T10:47:57.404734Z 2 [ERROR] [MY-011066] InnoDB: In Index: ‘k2‘ prefix_len mismatches: from server: 10 from InnoDB: 30
2018-04-13T10:47:57.404887Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t9_index_prefix_len
2018-04-13T10:47:57.762288Z 2 [ERROR] [MY-010022] Failed to Populate DD tables.
2018-04-13T10:47:58.762312Z 0 [ERROR] [MY-010119] Aborting
2018-04-13T10:48:58.269227Z 0 [ERROR] [System] [MY-010910] /home/nisha/workspacel/mysql-8.0ldbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

2018-04-13T10:47:57.338648Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t5_index_cols

2018-04-13T10:47:57.350472Z 2 [ERROR] [MY-011066] InnoDB: Num of Indexes in InnoDB doesn't match with Indexes from server

2018-04-13T10:47:57.350507Z 2 [ERROR] [MY-011066] InnoDB: Indexes from InnoDB: 3 Indexes from Server: 2

2018-04-13T10:47:57.350652Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t6_indexes_num

2018-04-13T10:47:57.378661Z 2 [ERROR] [MY-011066] InnoDB: Index name: ‘kl‘ type mismatches: from server: 0 from InnoDB: 2

2018-04-13T10:47:57.378710Z 2 [ERROR] [MY-011066] InnoDB: Index name: ‘k2‘ type mismatches: from server: 0 from InnoDB: 2

2018-04-13T10:47:57.378865Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t7_indexes_unique

2018-04-13T10:47:57.389900Z 2 [ERROR] [MY-011066] InnoDB: Column precision type mismatch(i.e NULLs, SIGNED/UNSIGNED etc) for col: b

2018-04-13T10:47:57.389952Z 2 [ERROR] [MY-011066] InnoDB: Column b for table: ‘test_mismatch . t8_col_type_nulls‘ mismatches with InnoDB Dictio

nary

2018-04-13T10:47:57.390105Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t8_col_type_nulls

2018-04-13T10:47:57.404689Z 2 [ERROR] [MY-011066] InnoDB: In Index: ‘k1‘ prefix_len mismatches: from server: 45 from InnoDB: 15

2018-04-13T10:47:57.404734Z 2 [ERROR] [MY-011066] InnoDB: In Index: ‘k2‘ prefix_len mismatches: from server: 10 from InnoDB: 30

2018-04-13T10:47:57.404887Z 2 [ERROR] [MY-010767] Error in fixing SE data for test_mismatch.t9_index_prefix_len

2018-04-13T10:47:57.762288Z 2 [ERROR] [MY-010022] Failed to Populate DD tables.

2018-04-13T10:47:58.762312Z 0 [ERROR] [MY-010119] Aborting

2018-04-13T10:48:58.269227Z 0 [ERROR] [System] [MY-010910] /home/nisha/workspacel/mysql-8.0ldbg-8.0/runtime_output_directory/mysqld: Shutdown complete.

In MySQL 5.7, several spatial functions available under multiple names were deprecated. Example PointFromText:

mysql> CREATE TABLE t_gcol_dep (fid INTEGER NOT NULL PRIMARY KEY, g POINT GENERATED ALWAYS AS (PointFromText(POINT(10, 10))));
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                    |
+---------+------+------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'POINTFROMTEXT' is deprecated and will be removed in a future release. Please use ST_POINTFROMTEXT instead |
+---------+------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t_gcol_dep (fid INTEGER NOT NULL PRIMARY KEY, g POINT GENERATED ALWAYS AS (PointFromText(POINT(10, 10))));

Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> show warnings;

+---------+------+------------------------------------------------------------------------------------------------------------+

| Level | Code | Message |

+---------+------+------------------------------------------------------------------------------------------------------------+

| Warning | 1287 | 'POINTFROMTEXT' is deprecated and will be removed in a future release. Please use ST_POINTFROMTEXT instead |

+---------+------+------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

These spatial functions were removed in MySQL 8.0 due to spatial function namespace change. The change helps the naming convention to remain consistent i.e functions begin with ‘ST_’ if it performs an exact operation, or with ‘MBR’ if it performs an operation based on minimum bounding rectangles. Generated columns using such functions should be altered prior to upgrade. The list of removed spatial functions can be found in the following documentation. The generated columns have to be altered to use the corresponding ‘ST_’or ‘MBR’ functions.

mysql> ALTER TABLE t_gcol_dep MODIFY g POINT GENERATED ALWAYS AS (ST_POINTFROMTEXT(POINT(10, 10)));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

mysql> ALTER TABLE t_gcol_dep MODIFY g POINT GENERATED ALWAYS AS (ST_POINTFROMTEXT(POINT(10, 10)));

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

The use of removed spatial functions in generated column definitions will cause an upgrade error:

2018-04-13T10:48:04.5099332 2 [ERROR] [MY-010764] Error in processing generated columns for table test.t_gcol_dep
2018-04-13T10:48:05.1384632 o [ERROR] [MY-010119] Aborting
2018-04-13T10:48:07.092384Z 0 [System] [MY-010910] /home/nisha/workspacel/mysql-8.0/dbg-8.0/runtime_output_directory/mysq1d: Shutdown complete.

2018-04-13T10:48:04.5099332 2 [ERROR] [MY-010764] Error in processing generated columns for table test.t_gcol_dep

2018-04-13T10:48:05.1384632 o [ERROR] [MY-010119] Aborting

2018-04-13T10:48:07.092384Z 0 [System] [MY-010910] /home/nisha/workspacel/mysql-8.0/dbg-8.0/runtime_output_directory/mysq1d: Shutdown complete.

Before upgrade, the MySQL 5.7 server must be shut down with innodb_fast_shutdown value set as 0 or 1. This will make sure that InnoDB redo logs are empty. Upgrading to MySQL 8.0 with MySQL 5.7 redo logs which are not empty will result in an error:

2018-04-16T04:50:55.0356912 1 [ERROR] [MY-011066] InnoDB: Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.22. Please follow the instructions at http://dev.mysql.com/doc/refman/B.O/en/upgrading.html
2018-04-16T04:50:55.03S7312 1 [ERROR] [MY-011066] InnoDB: Plugin initialization aborted at srvOstart.cc[2153] with error Generic error
2018-04-16T04:50:55.6369922 1 [ERROR] [MY-011013] Failed to initialize DD Storage Engine.
2018-04-16T04:50:55.6378562 O [ERROR] [MY-010020] Data Dictionary initialization failed.
2018-04-16TO4:50:55.6379112 o [ERROR] [MY-010119] Aborting
2018-04-16TO4:50:55.6410822 0 [System] [MY-010910] /home/nisha/workspacel/mysql-8.0/dbg-8.0/sql/mysqld: Shutdown complete.

2018-04-16T04:50:55.0356912 1 [ERROR] [MY-011066] InnoDB: Upgrade after a crash is not supported. This redo log was created with MySQL 5.7.22. Please follow the instructions at http://dev.mysql.com/doc/refman/B.O/en/upgrading.html

2018-04-16T04:50:55.03S7312 1 [ERROR] [MY-011066] InnoDB: Plugin initialization aborted at srvOstart.cc[2153] with error Generic error

2018-04-16T04:50:55.6369922 1 [ERROR] [MY-011013] Failed to initialize DD Storage Engine.

2018-04-16T04:50:55.6378562 O [ERROR] [MY-010020] Data Dictionary initialization failed.

2018-04-16TO4:50:55.6379112 o [ERROR] [MY-010119] Aborting

2018-04-16TO4:50:55.6410822 0 [System] [MY-010910] /home/nisha/workspacel/mysql-8.0/dbg-8.0/sql/mysqld: Shutdown complete.

If the upgrade fails due to any of the above errors (1-10), the server reverts all changes to the data directory. Following which all the redo log files should removed and the MySQL 5.7 server should be started on same data directory to fix the above errors. Once the errors are fixed, a slow shutdown of the 5.7 server should be performed and an in-place upgrade to MySQL 8.0 should be attempted again. Once the server has started, ‘mysql_upgrade’ should be run on the server which marks the completion of upgrade to 8.0 .

Few checks which are not enforced during upgrade and needs to be taken into account before upgrade are:

The default/preferred authentication plugin has been changed to ‘caching_sha2_password’ since it provides more secure password encryption ‘mysql_native_password’ plugin and better performance than the ‘sha256_password’. This may cause some of the applications to throw errors related to ‘caching_sha2_password’ plugin after upgrade to MySQL 8.0. This maybe due to the fact the clients/connectors are yet to support ‘caching_sha2_password’ plugin. More information about on how to resolve this can be found in the following blog.
GIS support has also undergone substantial changes in MySQL 8.0. The following presentation by Norvald highlights the changes made in MySQL 8.0: GIS Overview . The possible issues that can be encountered while upgrading has been discussed in the presentation under the upgrade section.
Several of the defaults have been changed in MySQL 8.0. The most important change in defaults has been the change in the default value of ‘character_set_server’ and ‘character_set_database’ from ‘latin1’ to ‘utf8mb4’. Also the default value of ‘collation_server’ and ‘collation_database’ has been changed from ‘latin1_swedish_ci’ to ‘utf8mb4_0900_ai_ci’. Since the defaults have been changed, there is a possibility of noticing a change in the behavior after upgrading to MySQL 8.0. See New Defaults in MySQL 8.0. Information about variables that have been added, deprecated and removed can be found in the MySQL documentation.
The undo log is moved out of the system tablespace as part of the upgrade and the innodb_undo_tablespaces is set to a minimum and default value of ‘two’ undo tablespaces. The system tablespace cannot be used for InnoDb undo log in MySQL 8.0. This provides flexibility to the user to configure the undo log tablespaces and also truncate the undo log tablespaces without stopping the server.
Upgrade from MySQL 5.7 which contains encrypted data files requires an additional step: ALTER INSTANCE ROTATE INNODB MASTER KEY should be executed to ensure it works well with replication.

Few checks which are not enforced during upgrade and needs to be taken into account before upgrade are:

Further reading: