0

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)
Improve this question
6
  • you might need to run mysql_upgrade to upgrade the system tables and then restart mysqld instance after Commented Apr 16, 2017 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 Commented Apr 17, 2017 at 1:17
  • run mysql_upgrade --upgrade-system-tables -uroot -p Commented Apr 17, 2017 at 1:18
  • That worked, thank you. I will update if the issues was resolved. Commented Apr 17, 2017 at 1:23
  • @jerichorivera that fixed it for me, no crashes. How do I mark your answer as an answer? Commented Apr 17, 2017 at 14:47

1 Answer 1

Reset to default
1

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
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.