MySQL Bugs: #86640: Upgrade from MySQL 5.1.37 to MySQL 5.7.18 Ubuntu

Bug #86640	Upgrade from MySQL 5.1.37 to MySQL 5.7.18 Ubuntu
Submitted:	9 Jun 2017 17:16	Modified:	10 Jul 2017 19:50
Reporter:	Greg Brugger	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S1 (Critical)
Version:	5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We have an older Ubuntu Server that runs mysql 5.1.37 and everything there works fine. I deployed a new Ubuntu Server (17.04) and installed mysql 5.7.18. I then transfered all the databases over using mysqldump - no problem. I restarted the mysql service after dumping all the databases and the service fails to start. I see this in the log files: 

2017-06-09T17:15:19.858260Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-06-09T17:15:19.858349Z 0 [Warning] Changed limits: max_connections: 214 (requested 500)
2017-06-09T17:15:19.858357Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2000)
2017-06-09T17:15:20.040587Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-06-09T17:15:20.044432Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.18-0ubuntu0.17.04.1) starting as process 3952 ...
2017-06-09T17:15:20.053371Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-06-09T17:15:20.053416Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-06-09T17:15:20.053437Z 0 [Note] InnoDB: Uses event mutexes
2017-06-09T17:15:20.053451Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-06-09T17:15:20.053465Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2017-06-09T17:15:20.053477Z 0 [Note] InnoDB: Using Linux native AIO
2017-06-09T17:15:20.053956Z 0 [Note] InnoDB: Number of pools: 1
2017-06-09T17:15:20.054165Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-06-09T17:15:20.056995Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-06-09T17:15:20.071761Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-06-09T17:15:20.075709Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-06-09T17:15:20.088574Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-09T17:15:20.123420Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-06-09T17:15:20.123562Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-06-09T17:15:20.175527Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-06-09T17:15:20.177608Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-06-09T17:15:20.177643Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-06-09T17:15:20.178640Z 0 [Note] InnoDB: Waiting for purge to start
2017-06-09T17:15:20.228848Z 0 [Note] InnoDB: 5.7.18 started; log sequence number 8388684873
2017-06-09T17:15:20.229957Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-06-09T17:15:20.235145Z 0 [Warning] System table 'plugin' is expected to be transactional.
2017-06-09T17:15:20.236306Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysqldata/mysql/ib_buffer_pool
2017-06-09T17:15:20.237839Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170609 13:15:20
2017-06-09T17:15:20.238402Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-06-09T17:15:20.238436Z 0 [Note] Server hostname (bind-address): '192.168.1.13'; port: 3306
2017-06-09T17:15:20.238458Z 0 [Note]   - '192.168.1.13' resolves to '192.168.1.13';
2017-06-09T17:15:20.238517Z 0 [Note] Server socket created on IP: '192.168.1.13'.
2017-06-09T17:15:20.240235Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.
2017-06-09T17:15:20.240375Z 0 [ERROR] Aborting

2017-06-09T17:15:20.240447Z 0 [Note] Binlog end
2017-06-09T17:15:20.240592Z 0 [Note] Shutting down plugin 'ngram'
2017-06-09T17:15:20.240614Z 0 [Note] Shutting down plugin 'partition'
2017-06-09T17:15:20.240623Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2017-06-09T17:15:20.240633Z 0 [Note] Shutting down plugin 'ARCHIVE'
2017-06-09T17:15:20.240642Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-06-09T17:15:20.240735Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2017-06-09T17:15:20.240746Z 0 [Note] Shutting down plugin 'MyISAM'
2017-06-09T17:15:20.240759Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2017-06-09T17:15:20.240769Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017-06-09T17:15:20.240777Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017-06-09T17:15:20.240786Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017-06-09T17:15:20.240816Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017-06-09T17:15:20.240826Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017-06-09T17:15:20.240835Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017-06-09T17:15:20.240844Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017-06-09T17:15:20.240853Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017-06-09T17:15:20.240861Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017-06-09T17:15:20.240870Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017-06-09T17:15:20.240879Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017-06-09T17:15:20.240887Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017-06-09T17:15:20.240896Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017-06-09T17:15:20.240905Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017-06-09T17:15:20.240913Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017-06-09T17:15:20.240922Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2017-06-09T17:15:20.240930Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2017-06-09T17:15:20.240939Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017-06-09T17:15:20.240948Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017-06-09T17:15:20.240956Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017-06-09T17:15:20.240965Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017-06-09T17:15:20.240973Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017-06-09T17:15:20.240982Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017-06-09T17:15:20.240991Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017-06-09T17:15:20.240999Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017-06-09T17:15:20.241008Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2017-06-09T17:15:20.241017Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017-06-09T17:15:20.241025Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2017-06-09T17:15:20.241034Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2017-06-09T17:15:20.241042Z 0 [Note] Shutting down plugin 'InnoDB'
2017-06-09T17:15:20.241168Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017-06-09T17:15:20.241471Z 0 [Note] InnoDB: Starting shutdown...
2017-06-09T17:15:20.341852Z 0 [Note] InnoDB: Dumping buffer pool(s) to /mysqldata/mysql/ib_buffer_pool
2017-06-09T17:15:20.342199Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 170609 13:15:20
2017-06-09T17:15:21.853831Z 0 [Note] InnoDB: Shutdown completed; log sequence number 8388684892
2017-06-09T17:15:21.856811Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-06-09T17:15:21.856845Z 0 [Note] Shutting down plugin 'MEMORY'
2017-06-09T17:15:21.856859Z 0 [Note] Shutting down plugin 'CSV'
2017-06-09T17:15:21.856871Z 0 [Note] Shutting down plugin 'sha256_password'
2017-06-09T17:15:21.856881Z 0 [Note] Shutting down plugin 'mysql_native_password'
2017-06-09T17:15:21.857134Z 0 [Note] Shutting down plugin 'binlog'
2017-06-09T17:15:21.857468Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

How to repeat:
Stated this in the description.

Thank you for the bug report. You imported the mysql (grant tables) with the dump created with 5.1, so the below error message meant something:

2017-06-09T17:15:20.240235Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.
2017-06-09T17:15:20.240375Z 0 [ERROR] Aborting

Thanks.

I also noticed the same as Miguel did, but IMO the server should still start so that it is possible to run 'mysql_upgrade' in this case where upgrade is from "next-lower" to "next-higher" version family (ie. from 5.6.x to 5.7.x).

-- Peter
-- not a MySQL/Oracle person

The MySQL service will not start. When I try to restart it it displays this error. Is there a way to restart the MySQL serivice and by pass the grant tables?

Well you don't answered the prior question if you imported the mysql database then there isn't bug here instead a wrong upgrade procedure which needs to be done according Peter mentioned running upgrade from "next-lower" to "next-higher" version family (ie. from 5.6.x to 5.7.x). You asked to skip the grant tables, you could use --skip-grant-tables.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".