InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes

This blog is powered by WordPress. When I first start blogging, few had heard the term blog. I hadn’t. I recall being asked, why don’t you use blogging software for The FreeBSD Diary. The answer was simple: it didn’t exist when I started. I am making headway on converting the diary to WordPress, and that might happen before the end of the year.

Today, this blog got its very own FreeBSD jail. There is nothing in that jail but WordPress and only this blog. While doing that transfer, I encountered this while doing a backup:

mysqldump: Error 1682: Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
when dumping table `events_waits_summary_by_thread_by_event_name` at robackup of performance_schema failed

Research led me to stop the database server and restart it. It failed to start.

Looking in /var/db/mysql/mysql.err found this:

141216 21:53:19 mysqld_safe mysqld from pid file /var/db/mysql/mysql.pid ended
141216 21:53:24 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
141216 21:53:24 InnoDB: The InnoDB memory heap is disabled
141216 21:53:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141216 21:53:24 InnoDB: Compressed tables use zlib 1.2.8
141216 21:53:24 InnoDB: Initializing buffer pool, size = 128.0M
141216 21:53:24 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
141216 21:53:24 [ERROR] Plugin 'InnoDB' init function returned error.
141216 21:53:24 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
141216 21:53:24 [ERROR] Unknown/unsupported storage engine: InnoDB
141216 21:53:24 [ERROR] Aborting

Searching led me to the following recommendation:

 # cat /var/db/mysql/my.cnf
[mysqld]
innodb_log_file_size=5M

To tempt fate, I also upgraded from MySQL 5.5.41 to 5.6.22.

Starting the database server after this upgrade, I got:

141216 22:10:09 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2014-12-16 22:10:09 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-12-16 22:10:09 82269 [Note] Plugin 'FEDERATED' is disabled.
2014-12-16 22:10:09 82269 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-12-16 22:10:09 82269 [Note] InnoDB: The InnoDB memory heap is disabled
2014-12-16 22:10:09 82269 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-12-16 22:10:09 82269 [Note] InnoDB: Memory barrier is not used
2014-12-16 22:10:09 82269 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-12-16 22:10:09 82269 [Note] InnoDB: Using CPU crc32 instructions
2014-12-16 22:10:09 82269 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-12-16 22:10:09 82269 [Note] InnoDB: Completed initialization of buffer pool
2014-12-16 22:10:09 82269 [Note] InnoDB: Highest supported file format is Barracuda.
2014-12-16 22:10:09 82269 [Note] InnoDB: 128 rollback segment(s) are active.
2014-12-16 22:10:09 82269 [Note] InnoDB: Waiting for purge to start
2014-12-16 22:10:09 82269 [Note] InnoDB: 5.6.22 started; log sequence number 19402005
2014-12-16 22:10:09 82269 [Note] Server hostname (bind-address): '*'; port: 3306
2014-12-16 22:10:09 82269 [Note] IPv6 is not available.
2014-12-16 22:10:09 82269 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2014-12-16 22:10:09 82269 [Note] Server socket created on IP: '0.0.0.0'.
2014-12-16 22:10:09 82269 [Note] Event Scheduler: Loaded 0 events
2014-12-16 22:10:09 82269 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.6.22'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

Then I ran mysql_upgrade:

# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
wordpress_danlangilleorg.wp_commentmeta            OK
wordpress_danlangilleorg.wp_comments               OK
wordpress_danlangilleorg.wp_dmec                   OK
wordpress_danlangilleorg.wp_links                  OK
wordpress_danlangilleorg.wp_options                OK
wordpress_danlangilleorg.wp_postmeta               OK
wordpress_danlangilleorg.wp_posts                  OK
wordpress_danlangilleorg.wp_simple_login_log       OK
wordpress_danlangilleorg.wp_term_relationships     OK
wordpress_danlangilleorg.wp_term_taxonomy          OK
wordpress_danlangilleorg.wp_terms                  OK
wordpress_danlangilleorg.wp_usermeta               OK
wordpress_danlangilleorg.wp_users                  OK
wordpress_newsfreshportsorg.wp_commentmeta         OK
wordpress_newsfreshportsorg.wp_comments            OK
wordpress_newsfreshportsorg.wp_links               OK
wordpress_newsfreshportsorg.wp_options             OK
wordpress_newsfreshportsorg.wp_postmeta            OK
wordpress_newsfreshportsorg.wp_posts               OK
wordpress_newsfreshportsorg.wp_simple_login_log    OK
wordpress_newsfreshportsorg.wp_term_relationships  OK
wordpress_newsfreshportsorg.wp_term_taxonomy       OK
wordpress_newsfreshportsorg.wp_terms               OK
wordpress_newsfreshportsorg.wp_usermeta            OK
wordpress_newsfreshportsorg.wp_users               OK
wordpress_wwwfreebsddiaryorg.wp_commentmeta        OK
wordpress_wwwfreebsddiaryorg.wp_comments           OK
wordpress_wwwfreebsddiaryorg.wp_links              OK
wordpress_wwwfreebsddiaryorg.wp_options            OK
wordpress_wwwfreebsddiaryorg.wp_postmeta           OK
wordpress_wwwfreebsddiaryorg.wp_posts              OK
wordpress_wwwfreebsddiaryorg.wp_simple_login_log   OK
wordpress_wwwfreebsddiaryorg.wp_term_relationships OK
wordpress_wwwfreebsddiaryorg.wp_term_taxonomy      OK
wordpress_wwwfreebsddiaryorg.wp_terms              OK
wordpress_wwwfreebsddiaryorg.wp_usermeta           OK
wordpress_wwwfreebsddiaryorg.wp_users              OK
OK

Better still, when I ran my backups, the error message changed to:

mysqldump: Couldn't execute 'show events': Access denied for user 'root'@'localhost' to database 'performance_schema' (1044)
backup of performance_schema failed

After reading, I no longer bother backing up performance_schema.