MySQL server crashes intermittently after upgrading to 5.6

Question

I have a new instance (lightsail 2GB/AMI) that I am testing with a single wordpress site. I installed mysql (sudo yum install mysql-server) on it and imported an already existing db into it, it gave me a bunch of errors on import... long story, I decided to uninstall and install the version recommended on Amazon help (sudo yum install -y mysql56-server). The db imported correctly this time.

I went into wordpress and enabled the W3 Total Cache plugin. Ever Since I did that, MySQL is crashing intermittently.

mysqld -v gives me this (error log below);

[ec2-user@ip- lib]$ mysqld -v
2017-04-16 07:28:39 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-16 07:28:39 0 [Warning] Can't create test file /var/lib/mysql/ip-172-xx3.lower-test
2017-04-16 07:28:39 0 [Note] mysqld (mysqld 5.6.35) starting as process 16301 ...
2017-04-16 07:28:39 16301 [Warning] Can't create test file /var/lib/mysql/ip-172xx3.lower-test
2017-04-16 07:28:39 16301 [Warning] Can't create test file /var/lib/mysql/ip-172-2xx3.lower-test
2017-04-16 07:28:39 16301 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2017-04-16 07:28:39 16301 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2017-04-16 07:28:39 16301 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2017-04-16 07:28:39 16301 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-04-16 07:28:39 16301 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-04-16 07:28:39 16301 [Note] InnoDB: The InnoDB memory heap is disabled
2017-04-16 07:28:39 16301 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-04-16 07:28:39 16301 [Note] InnoDB: Memory barrier is not used
2017-04-16 07:28:39 16301 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-04-16 07:28:39 16301 [Note] InnoDB: Using Linux native AIO
2017-04-16 07:28:39 16301 [Note] InnoDB: Using CPU crc32 instructions
2017-04-16 07:28:39 16301 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-04-16 07:28:39 16301 [Note] InnoDB: Completed initialization of buffer pool
2017-04-16 07:28:39 16301 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2017-04-16 07:28:39 16301 [ERROR] InnoDB: The system tablespace must be writable!
2017-04-16 07:28:39 16301 [ERROR] Plugin 'InnoDB' init function returned error.
2017-04-16 07:28:39 16301 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-04-16 07:28:39 16301 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-04-16 07:28:39 16301 [ERROR] Aborting

2017-04-16 07:28:39 16301 [Note] Binlog end
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'partition'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'ARCHIVE'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_METRICS'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_CMP'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_LOCKS'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'INNODB_TRX'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'BLACKHOLE'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'CSV'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'MEMORY'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'MyISAM'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'MRG_MYISAM'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'sha256_password'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'mysql_old_password'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'mysql_native_password'
2017-04-16 07:28:39 16301 [Note] Shutting down plugin 'binlog'
2017-04-16 07:28:39 16301 [Note] mysqld: Shutdown complete

/vr/log/mysql.log has this;

2017-04-16 07:05:53 16067 [Note] Plugin 'FEDERATED' is disabled.
2017-04-16 07:05:53 16067 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-04-16 07:05:53 16067 [Note] InnoDB: The InnoDB memory heap is disabled
2017-04-16 07:05:53 16067 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-04-16 07:05:53 16067 [Note] InnoDB: Memory barrier is not used
2017-04-16 07:05:53 16067 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-04-16 07:05:53 16067 [Note] InnoDB: Using Linux native AIO
2017-04-16 07:05:53 16067 [Note] InnoDB: Using CPU crc32 instructions
2017-04-16 07:05:53 16067 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2017-04-16 07:05:53 16067 [Note] InnoDB: Completed initialization of buffer pool
2017-04-16 07:05:53 16067 [Note] InnoDB: Highest supported file format is Barracuda.
2017-04-16 07:05:53 16067 [Note] InnoDB: The log sequence numbers 5067505 and 5067505 in ibdata files do not match the log sequence number 5071459 in the ib_logfiles!
2017-04-16 07:05:53 16067 [Note] InnoDB: Database was not shutdown normally!
2017-04-16 07:05:53 16067 [Note] InnoDB: Starting crash recovery.
2017-04-16 07:05:53 16067 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-04-16 07:05:54 16067 [Note] InnoDB: Restoring possible half-written data pages 
2017-04-16 07:05:54 16067 [Note] InnoDB: from the doublewrite buffer...
2017-04-16 07:05:54 16067 [Note] InnoDB: 128 rollback segment(s) are active.
2017-04-16 07:05:54 16067 [Note] InnoDB: Waiting for purge to start
2017-04-16 07:05:54 16067 [Note] InnoDB: 5.6.35 started; log sequence number 5071459
2017-04-16 07:05:54 16067 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2017-04-16 07:05:54 16067 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2017-04-16 07:05:54 16067 [Note] Server hostname (bind-address): '*'; port: 3306
2017-04-16 07:05:54 16067 [Note] IPv6 is available.
2017-04-16 07:05:54 16067 [Note]   - '::' resolves to '::';
2017-04-16 07:05:54 16067 [Note] Server socket created on IP: '::'.
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.events_waits_current is wrong. Expected 19, found 16. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.events_waits_history is wrong. Expected 19, found 16. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.events_waits_history_long is wrong. Expected 19, found 16. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_thread_by_event_name: expected column 'THREAD_ID' at position 0 to have type bigint(20), found type int(11).
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.file_summary_by_event_name is wrong. Expected 23, found 5. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.file_summary_by_instance is wrong. Expected 25, found 6. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11).
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_type' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Incorrect definition of table performance_schema.rwlock_instances: expected column 'WRITE_LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11).
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_index_usage' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_table' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'table_lock_waits_summary_by_table' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.threads is wrong. Expected 14, found 3. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_stages_current' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_stages_history' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_stages_history_long' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_thread_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_account_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_user_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_host_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_stages_summary_global_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_current' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_history' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_history_long' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_thread_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_account_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
2017-04-16 07:05:54 16067 [Note] Event Scheduler: Loaded 0 events
2017-04-16 07:05:54 16067 [Note] /usr/libexec/mysql56/mysqld: ready for connections.
Version: '5.6.35'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

you might need to run mysql_upgrade to upgrade the system tables and then restart mysqld instance after — jerichorivera, Apr 16 '17 at 13:32
This is what I get when I run the command: [ec2-user@ip ~]$ sudo mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Error: Failed while fetching Server version! Could be due to unauthorized access . FATAL ERROR: Upgrade failed — Waqas Tariq, Apr 17 '17 at 01:17
That worked, thank you. I will update if the issues was resolved. — Waqas Tariq, Apr 17 '17 at 01:23
@jerichorivera that fixed it for me, no crashes. How do I mark your answer as an answer? — Waqas Tariq, Apr 17 '17 at 14:47
@jerichorivera should post the comment as an answer so it can be marked as an answer. If he doesn't then you can post the answer yourself, then accept your own answer after 24 hours. Doing either of these is good because it means people don't have to come in here to try to answer the question. — Tim, Apr 18 '17 at 21:42

score 1 · Accepted Answer · answered Apr 18 '17 at 21:48

These errors indicate that mysql_upgrade has to be executed:

2017-04-16 07:28:39 16301 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.events_waits_current is wrong. Expected 19, found 16. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.events_waits_history is wrong. Expected 19, found 16. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.events_waits_history_long is wrong. Expected 19, found 16. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Incorrect definition of table performance_schema.events_waits_summary_by_thread_by_event_name: expected column 'THREAD_ID' at position 0 to have type bigint(20), found type int(11).
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.file_summary_by_event_name is wrong. Expected 23, found 5. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Column count of mysql.file_summary_by_instance is wrong. Expected 25, found 6. Created with MySQL 50554, now running 50635. Please use mysql_upgrade to fix this error.
2017-04-16 07:05:54 16067 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
2017-04-16 07:05:54 16067 [ERROR] Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to have type bigint(20), found type int(11).

While these errors indicate an issue about file permissions:

2017-04-16 07:28:39 16301 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2017-04-16 07:28:39 16301 [ERROR] InnoDB: The system tablespace must be writable!
2017-04-16 07:28:39 16301 [ERROR] Plugin 'InnoDB' init function returned error.
2017-04-16 07:28:39 16301 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-04-16 07:28:39 16301 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-04-16 07:28:39 16301 [ERROR] Aborting

The first issue can be resolved by running the following:

mysql_upgrade --upgrade-system-tables -uroot -p

While the second issue can be resolved by changing file ownership to mysql:mysql

chown -R mysql:mysql /path/to/datadir

MySQL server crashes intermittently after upgrading to 5.6

1 Answers1