MySQL Forums
Forum List  »  Install & Repo

problems upgrading from 5.6 to 5.7
Posted by: George Bingham
Date: May 02, 2016 11:40PM

Hi all,

Hope you can help! I am upgrading my mythtv box from 15.10 to 16.04 and with that it updated mysql to 5.7(.12) but my mysql installation did not complete because of an error during it's configuration.

I have changed the entries in my.cnf to "key-buffer-size" and "myisam_recover_options", so that's not a problem anymore, but while trying to figure this problem out, I think my system tables have become corrupted.

running mysql -uroot will get me into the system only when I have started mysqld with the --skip-grant-tables option, and trying to set or change the root password results in the password hash being stored in the 'plugin' field of the user table.

I am not sure what I did to mess up the system tables (or perhaps they were not updated to what 5.7 expects since the dpkg configure step keeps failing), but I am lost.

errors.log reports the following when the install attempts to configure the server:
<begin log>

2016-05-03T05:35:06.269468Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-03T05:35:06.270564Z 0 [Note] mysqld (mysqld 5.7.12-0ubuntu1) starting as process 19859 ...
2016-05-03T05:35:06.291792Z 0 [Note] InnoDB: PUNCH HOLE support available
2016-05-03T05:35:06.291918Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-03T05:35:06.291947Z 0 [Note] InnoDB: Uses event mutexes
2016-05-03T05:35:06.291971Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2016-05-03T05:35:06.291995Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-05-03T05:35:06.292018Z 0 [Note] InnoDB: Using Linux native AIO
2016-05-03T05:35:06.292658Z 0 [Note] InnoDB: Number of pools: 1
2016-05-03T05:35:06.292931Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2016-05-03T05:35:06.321775Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2016-05-03T05:35:06.355583Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016-05-03T05:35:06.360308Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2016-05-03T05:35:06.377592Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-05-03T05:35:06.453524Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016-05-03T05:35:06.453835Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016-05-03T05:35:06.637836Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2016-05-03T05:35:06.639382Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-05-03T05:35:06.639421Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-05-03T05:35:06.653313Z 0 [Note] InnoDB: 5.7.12 started; log sequence number 2652990
2016-05-03T05:35:06.655563Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2016-05-03T05:35:06.657019Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-05-03T05:35:06.673412Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2016-05-03T05:35:06.673576Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2016-05-03T05:35:06.673748Z 0 [Note] IPv6 is available.
2016-05-03T05:35:06.673775Z 0 [Note] - '::' resolves to '::';
2016-05-03T05:35:06.673885Z 0 [Note] Server socket created on IP: '::'.
2016-05-03T05:35:06.677380Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160503 0:35:06
2016-05-03T05:35:06.713395Z 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(77), found type char(141).
2016-05-03T05:35:06.713512Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2016-05-03T05:35:06.713694Z 0 [Note] Execution of init_file '/var/lib/mysql-files/tmp.tNUCd3lUwZ' started.
2016-05-03T05:35:06.715522Z 0 [Note] Execution of init_file '/var/lib/mysql-files/tmp.tNUCd3lUwZ' ended.
2016-05-03T05:35:06.715670Z 0 [Note] mysqld: ready for connections.
Version: '5.7.12-0ubuntu1' socket: '/tmp/tmp.FSDhyQg0vT/mysqld.sock' port: 3306 (Ubuntu)
2016-05-03T05:35:06.716022Z 0 [Note] Giving 0 client threads a chance to die gracefully
2016-05-03T05:35:06.716056Z 0 [Note] Shutting down slave threads
2016-05-03T05:35:06.716071Z 0 [Note] Forcefully disconnecting 0 remaining clients
2016-05-03T05:35:06.716126Z 0 [Note] Binlog end
2016-05-03T05:35:06.717070Z 0 [Note] Shutting down plugin 'ngram'
2016-05-03T05:35:06.717101Z 0 [Note] Shutting down plugin 'ARCHIVE'
2016-05-03T05:35:06.717115Z 0 [Note] Shutting down plugin 'partition'
2016-05-03T05:35:06.717128Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2016-05-03T05:35:06.717142Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2016-05-03T05:35:06.717156Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-05-03T05:35:06.717168Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-05-03T05:35:06.717180Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-05-03T05:35:06.717193Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-05-03T05:35:06.717209Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-05-03T05:35:06.717222Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-05-03T05:35:06.717235Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-05-03T05:35:06.717251Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-05-03T05:35:06.717265Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-05-03T05:35:06.717278Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-05-03T05:35:06.717291Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-05-03T05:35:06.717304Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-05-03T05:35:06.717320Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-05-03T05:35:06.717333Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-05-03T05:35:06.717346Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-05-03T05:35:06.717361Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2016-05-03T05:35:06.717374Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2016-05-03T05:35:06.717387Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-05-03T05:35:06.717400Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-05-03T05:35:06.717412Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-05-03T05:35:06.717424Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-05-03T05:35:06.717436Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-05-03T05:35:06.717450Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-05-03T05:35:06.717468Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-05-03T05:35:06.717481Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-05-03T05:35:06.717494Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2016-05-03T05:35:06.717507Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-05-03T05:35:06.717549Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-05-03T05:35:06.717562Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2016-05-03T05:35:06.717575Z 0 [Note] Shutting down plugin 'InnoDB'
2016-05-03T05:35:06.717687Z 0 [Note] InnoDB: FTS optimize thread exiting.
2016-05-03T05:35:06.717939Z 0 [Note] InnoDB: Starting shutdown...
2016-05-03T05:35:06.818347Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2016-05-03T05:35:06.818827Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 160503 0:35:06
2016-05-03T05:35:08.566717Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2653009
2016-05-03T05:35:08.571264Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2016-05-03T05:35:08.571314Z 0 [Note] Shutting down plugin 'MEMORY'
2016-05-03T05:35:08.571338Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-05-03T05:35:08.571419Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2016-05-03T05:35:08.571448Z 0 [Note] Shutting down plugin 'MyISAM'
2016-05-03T05:35:08.571481Z 0 [Note] Shutting down plugin 'CSV'
2016-05-03T05:35:08.571511Z 0 [Note] Shutting down plugin 'sha256_password'
2016-05-03T05:35:08.571532Z 0 [Note] Shutting down plugin 'mysql_native_password'
2016-05-03T05:35:08.571813Z 0 [Note] Shutting down plugin 'binlog'
2016-05-03T05:35:08.572497Z 0 [Note] mysqld: Shutdown complete

2016-05-03T05:35:09.070520Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-05-03T05:35:09.070720Z 0 [Warning] Changed limits: table_open_cache: 457 (requested 2000)
2016-05-03T05:35:09.310159Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-03T05:35:09.311244Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.12-0ubuntu1) starting as process 19933 ...
2016-05-03T05:35:09.320932Z 0 [Note] InnoDB: PUNCH HOLE support available
2016-05-03T05:35:09.321055Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-03T05:35:09.321073Z 0 [Note] InnoDB: Uses event mutexes
2016-05-03T05:35:09.321092Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2016-05-03T05:35:09.321109Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-05-03T05:35:09.321130Z 0 [Note] InnoDB: Using Linux native AIO
2016-05-03T05:35:09.321856Z 0 [Note] InnoDB: Number of pools: 1
2016-05-03T05:35:09.322147Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2016-05-03T05:35:09.357310Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2016-05-03T05:35:09.393521Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016-05-03T05:35:09.399700Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2016-05-03T05:35:09.417674Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-05-03T05:35:09.484421Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016-05-03T05:35:09.484610Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016-05-03T05:35:09.680872Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2016-05-03T05:35:09.682403Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-05-03T05:35:09.682445Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-05-03T05:35:09.684750Z 0 [Note] InnoDB: 5.7.12 started; log sequence number 2653009
2016-05-03T05:35:09.686956Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-05-03T05:35:09.690049Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2016-05-03T05:35:09.691374Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2016-05-03T05:35:09.691497Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2016-05-03T05:35:09.691613Z 0 [Note] IPv6 is available.
2016-05-03T05:35:09.691693Z 0 [Note] - '::' resolves to '::';
2016-05-03T05:35:09.691785Z 0 [Note] Server socket created on IP: '::'.
2016-05-03T05:35:09.699297Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160503 0:35:09
2016-05-03T05:35:09.713975Z 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(77), found type char(141).
2016-05-03T05:35:09.714200Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2016-05-03T05:35:09.714666Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.12-0ubuntu1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
2016-05-03T05:35:10.094886Z 3 [Note] Your password has expired. To log in you must change it using a client that supports expired passwords.
<end log>

I *think* I would like to wipe this installation's data, start over from scratch so that it creates new system tables, then restore my database from backup. Not sure how to drop all the system tables and recreate them though.

Any ideas?? My databases are backed up, so loosing data in this installation is not a problem.

Thanks,

George

Options: ReplyQuote


Subject
Written By
Posted
problems upgrading from 5.6 to 5.7
May 02, 2016 11:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.