Failed to upgrade MySQL from 5.7 to 8.0

by AMF   Last Updated October 10, 2019 00:00 AM

Ran the mysql upgrade shell to conduct a check before upgrading from 5.7 to 8.0. Only errors found was my use of three system variables that will be removed: 1

3) Removed system variables
  Error: Following system variables that were detected as being used will be
    removed. Please update your system to not rely on them before the upgrade.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

  query_cache_limit - is set and will be removed
  query_cache_size - is set and will be removed
  query_cache_type - is set and will be removed

So I removed these, and all looked good.

But upon installing the binaries and an attempt to start mysql server, this failure occurs every time and the new 8.0 server will not start. The error is: Data Dictionary upgrade from MySQL 5.7 in progress

Active: failed (Result: exit-code) since Wed 2019-10-09 22:52:36 UTC; 10ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3022 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 2986 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 3022 (code=exited, status=1/FAILURE)
   Status: "Data Dictionary upgrade from MySQL 5.7 in progress"

Oct 09 22:52:33 db-1-staging systemd[1]: Starting MySQL Community Server...
Oct 09 22:52:33 db-1-staging mysql-systemd-start[2986]: /usr/share/mysql-8.0/mysql-systemd-start: line 39: /lib/apparmor/profile-load: No such file or directory
Oct 09 22:52:36 db-1-staging systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Oct 09 22:52:36 db-1-staging systemd[1]: mysql.service: Failed with result 'exit-code'.
Oct 09 22:52:36 db-1-staging systemd[1]: Failed to start MySQL Community Server.
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

FYI here is the full output of the upgrade shell. As you can see, there doesn't appear to be any relevant/blocking errors.

mysqlsh> util.checkForServerUpgrade()

The MySQL server at 127.0.0.1, version 5.7.26-log - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.16...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with reserved keywords in 8.0
  No issues found

3) 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.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

  elstats_app_MA--orig_ids - schema's default character set: utf8
  tester - schema's default character set: utf8
  elstats_app.ap_candidates.TestFlag - column's default character set: utf8
  elstats_app.ap_candidates.FirstName - column's default character set: utf8
  elstats_app.ap_candidates.MiddleName - column's default character set: utf8
  [.... etc etc ....]

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Foreign key constraint names longer than 64 characters
  No issues found

6) Usage of obsolete MAXDB sql_mode flag
  No issues found

7) Usage of obsolete sql_mode flags
  No issues found

8) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

9) Usage of partitioned tables in shared tablespaces
  No issues found

10) Usage of removed functions
  No issues found

11) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

12) Removed system variables for error logging to the system log configuration
  No issues found

13) Removed system variables
  Error: Following system variables that were detected as being used will be
    removed. Please update your system to not rely on them before the upgrade.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

  query_cache_limit - is set and will be removed
  query_cache_size - is set and will be removed
  query_cache_type - is set and will be removed

14) System variables with new default values
  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

  back_log - default value will change
  character_set_server - default value will change from latin1 to utf8mb4
  collation_server - default value will change from latin1_swedish_ci to
    utf8mb4_0900_ai_ci
  event_scheduler - default value will change from OFF to ON
  explicit_defaults_for_timestamp - default value will change from OFF to ON
  innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to
    2 (interleaved)
  innodb_flush_method - default value will change from NULL to fsync (Unix),
    unbuffered (Windows)
  innodb_flush_neighbors - default value will change from 1 (enable) to 0
    (disable)
  innodb_max_dirty_pages_pct - default value will change from 75 (%)  90 (%)
  innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10
    (%)
  innodb_undo_log_truncate - default value will change from OFF to ON
  innodb_undo_tablespaces - default value will change from 0 to 2
  log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)
  log_slave_updates - default value will change from OFF to ON
  master_info_repository - default value will change from FILE to TABLE
  max_error_count - default value will change from 64 to 1024
  optimizer_trace_max_mem_size - default value will change from 16KB to 1MB
  performance_schema_consumer_events_transactions_current - default value will
    change from OFF to ON
  performance_schema_consumer_events_transactions_history - default value will
    change from OFF to ON
  relay_log_info_repository - default value will change from FILE to TABLE
  server_id - default value will change from 0 to 1
  slave_rows_search_algorithms - default value will change from 'INDEX_SCAN,
    TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'
  table_open_cache - default value will change from 2000 to 4000
  transaction_write_set_extraction - default value will change from OFF to
    XXHASH64

15) Schema inconsistencies resulting from file removal or corruption
  No issues found

16) Issues reported by 'check table x for upgrade' command
  No issues found

17) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:

    [mysqld]
    default_authentication_plugin=mysql_native_password

    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-cachin...
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-cachin...

Errors:   3
Warnings: 428
Notices:  0
Tags : mysql mysql8


Related Questions


Updated December 26, 2018 15:00 PM

Updated January 07, 2019 03:00 AM

Updated January 27, 2019 15:00 PM