Frequent MySQL Crashes
Over the past few days I've noticed my MySQL server crashes and doesn't automatically recover. I have to SSH in and restart the service. I'd appreciate any help. Here's the log. If you notice the time stamps, I restarted the service around 11:40.
my.cnf
Thanks!
150122 9:00:56 InnoDB: cannot calculate statistics for table database/wp_wfNet404s
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21 InnoDB Troubleshooting
150122 9:00:56 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table database/wp_wfNet404s does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21 InnoDB Troubleshooting
how you can resolve the problem.
150122 9:00:56 InnoDB: cannot calculate statistics for table database/wp_wfNet404s
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21 InnoDB Troubleshooting
150122 9:00:56 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table database/wp_wfNet404s does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21 InnoDB Troubleshooting
how you can resolve the problem.
150122 09:29:05 mysqld_safe Number of processes running now: 0
150122 09:29:06 mysqld_safe mysqld restarted
150122 9:29:07 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
150122 9:29:11 [Note] Plugin 'FEDERATED' is disabled.
150122 9:29:11 InnoDB: The InnoDB memory heap is disabled
150122 9:29:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150122 9:29:11 InnoDB: Compressed tables use zlib 1.2.3
150122 9:29:11 InnoDB: Using Linux native AIO
150122 9:29:15 InnoDB: Initializing buffer pool, size = 1.0G
150122 9:29:19 InnoDB: Completed initialization of buffer pool
150122 9:29:19 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 7498780996
150122 9:29:20 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 7498793437
150122 9:29:26 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
150122 9:29:26 InnoDB: Error: table 'database/wp_wfNet404s'
InnoDB: in InnoDB data dictionary has tablespace id 6681,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name ./database/wp_wfNet404s.ibd and id 6683, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.
150122 9:29:26 InnoDB: Waiting for the background threads to start
150122 9:29:27 InnoDB: 5.5.40 started; log sequence number 7498793437
150122 9:29:27 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150122 9:29:27 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150122 9:29:27 [Note] Server socket created on IP: '0.0.0.0'.
150122 9:29:27 [Warning] 'user' entry 'root@test.domain.com' ignored in --skip-name-resolve mode.
--snip--
150122 9:29:27 [Warning] 'db' entry 'database2\_wp cpses_wiu0NXOR8a@srv13.domain.com' ignored in --skip-name-resolve mode.
150122 9:29:27 [Note] Event Scheduler: Loaded 0 events
150122 9:29:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
150122 09:29:47 mysqld_safe Number of processes running now: 0
150122 09:29:47 mysqld_safe mysqld restarted
150122 9:29:49 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
150122 9:29:50 [Note] Plugin 'FEDERATED' is disabled.
150122 9:29:50 InnoDB: The InnoDB memory heap is disabled
150122 9:29:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150122 9:29:50 InnoDB: Compressed tables use zlib 1.2.3
150122 9:29:50 InnoDB: Using Linux native AIO
150122 9:29:51 InnoDB: Initializing buffer pool, size = 1.0G
150122 9:29:55 InnoDB: Completed initialization of buffer pool
150122 9:29:56 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 7498833010
150122 9:29:56 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 7498838727
150122 9:30:12 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
150122 9:30:19 InnoDB: Error: table 'database/wp_wfNet404s'
InnoDB: in InnoDB data dictionary has tablespace id 6681,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name ./database/wp_wfNet404s.ibd and id 6683, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.
150122 9:30:19 InnoDB: Waiting for the background threads to start
150122 9:30:20 InnoDB: 5.5.40 started; log sequence number 7498838727
150122 9:30:20 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150122 9:30:20 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150122 9:30:20 [Note] Server socket created on IP: '0.0.0.0'.
--same snip--
150122 9:30:20 [Note] Event Scheduler: Loaded 0 events
150122 9:30:20 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
150122 09:30:47 mysqld_safe Number of processes running now: 0
150122 09:30:47 mysqld_safe mysqld restarted
150122 9:30:48 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
150122 9:30:49 [Note] Plugin 'FEDERATED' is disabled.
150122 9:30:49 InnoDB: The InnoDB memory heap is disabled
150122 9:30:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150122 9:30:49 InnoDB: Compressed tables use zlib 1.2.3
150122 9:30:49 InnoDB: Using Linux native AIO
150122 9:30:53 InnoDB: Initializing buffer pool, size = 1.0G
150122 9:30:58 InnoDB: Completed initialization of buffer pool
150122 9:30:59 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
150122 9:30:59 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
150122 9:31:07 InnoDB: Error: table 'database/wp_wfNet404s'
InnoDB: in InnoDB data dictionary has tablespace id 6681,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name ./database/wp_wfNet404s.ibd and id 6683, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.
150122 9:31:08 InnoDB: Waiting for the background threads to start
150122 9:31:09 InnoDB: 5.5.40 started; log sequence number 7498896732
150122 9:31:09 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150122 9:31:09 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150122 9:31:09 [Note] Server socket created on IP: '0.0.0.0'.
--same snip--
150122 9:31:09 [Note] Event Scheduler: Loaded 0 events
150122 9:31:09 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
150122 09:31:18 mysqld_safe Number of processes running now: 0
150122 09:31:18 mysqld_safe mysqld restarted
150122 9:31:18 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
150122 9:31:18 [Note] Plugin 'FEDERATED' is disabled.
150122 9:31:18 InnoDB: The InnoDB memory heap is disabled
150122 9:31:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150122 9:31:18 InnoDB: Compressed tables use zlib 1.2.3
150122 9:31:18 InnoDB: Using Linux native AIO
150122 9:31:18 InnoDB: Initializing buffer pool, size = 1.0G
150122 9:31:21 InnoDB: Completed initialization of buffer pool
150122 9:31:21 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 7498896732
150122 9:31:21 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 7498921372
150122 9:31:27 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
150122 9:31:31 InnoDB: Error: table 'database/wp_wfNet404s'
InnoDB: in InnoDB data dictionary has tablespace id 6681,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name ./database/wp_wfNet404s.ibd and id 6683, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.
150122 9:31:31 InnoDB: Waiting for the background threads to start
150122 9:31:32 InnoDB: 5.5.40 started; log sequence number 7498921372
150122 9:31:32 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150122 9:31:32 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150122 9:31:32 [Note] Server socket created on IP: '0.0.0.0'.
--same snip--
150122 9:31:32 [Note] Event Scheduler: Loaded 0 events
150122 9:31:32 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
150122 09:31:47 mysqld_safe Number of processes running now: 0
150122 09:31:47 mysqld_safe mysqld restarted
150122 9:31:47 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
150122 9:31:47 [Note] Plugin 'FEDERATED' is disabled.
150122 9:31:47 InnoDB: The InnoDB memory heap is disabled
150122 9:31:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150122 9:31:47 InnoDB: Compressed tables use zlib 1.2.3
150122 9:31:47 InnoDB: Using Linux native AIO
150122 9:31:48 InnoDB: Initializing buffer pool, size = 1.0G
150122 9:31:48 InnoDB: Completed initialization of buffer pool
150122 9:31:48 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 7498983550
150122 9:31:48 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
150122 09:31:49 mysqld_safe mysqld from pid file /var/lib/mysql/server5.tnahosting.net.pid ended
150122 11:40:12 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150122 11:40:12 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
150122 11:40:12 [Note] Plugin 'FEDERATED' is disabled.
150122 11:40:12 InnoDB: The InnoDB memory heap is disabled
150122 11:40:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150122 11:40:12 InnoDB: Compressed tables use zlib 1.2.3
150122 11:40:12 InnoDB: Using Linux native AIO
150122 11:40:12 InnoDB: Initializing buffer pool, size = 1.0G
150122 11:40:12 InnoDB: Completed initialization of buffer pool
150122 11:40:12 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 7498983550
150122 11:40:12 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 7498985487
150122 11:40:12 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
150122 11:40:13 InnoDB: Error: table 'database/wp_wfNet404s'
InnoDB: in InnoDB data dictionary has tablespace id 6681,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name ./database/wp_wfNet404s.ibd and id 6683, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.
150122 11:40:13 InnoDB: Waiting for the background threads to start
150122 11:40:14 InnoDB: 5.5.40 started; log sequence number 7498985487
150122 11:40:14 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150122 11:40:14 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150122 11:40:14 [Note] Server socket created on IP: '0.0.0.0'.
--same snip--
150122 11:40:14 [Note] Event Scheduler: Loaded 0 events
150122 11:40:14 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
150122 11:41:39 InnoDB: error: space object of table 'database/wp_wfNet404s',
InnoDB: space id 6681 did not exist in memory. Retrying an open.
150122 11:41:39 InnoDB: Error: tablespace id and flags in file './database/wp_wfNet404s.ibd' are 6683 and 0, but in the InnoDB
InnoDB: data dictionary they are 6681 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations
InnoDB: for how to resolve the issue.
150122 11:41:39 InnoDB: cannot calculate statistics for table database/wp_wfNet404s
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21 InnoDB Troubleshooting
150122 11:41:39 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table database/wp_wfNet404s does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21 InnoDB Troubleshooting
how you can resolve the problem.
150122 11:41:39 InnoDB: cannot calculate statistics for table database/wp_wfNet404s
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21 InnoDB Troubleshooting
150122 11:41:39 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table database/wp_wfNet404s does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21 InnoDB Troubleshooting
how you can resolve the problem.my.cnf
[mysqld]
skip-name-resolve
myisam_use_mmap=1
max_connections=100
max_user_connections = 50
log-slow-queries=mysql-slow.log
long_query_time = 0.1
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M
join_buffer_size=2M
read_rnd_buffer_size=1M
max_allowed_packet=268435456
tmp_table_size=50M
max_heap_table_size=50M
table_open_cache = 7500
thread_cache_size = 25
open_files_limit=30000
key_buffer_size = 500M
myisam_sort_buffer_size = 256M
innodb_file_per_table=1
innodb_buffer_pool_size = 1G
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
Thanks!
-
Did you delete any files in /var/lib/mysql ? 0 -
Hello :) Did you make any changes to your system or manually remove any files before this started? Note you may find this thread helpful for some of the InnoDB errors: InnoDB Corruption Repair Guide Thank you. 0 -
Thanks for the replies. I didn't make any changes to the system before this started happening. 0 -
You may want to start by troubleshooting the InnoDB errors in your MySQL error log using the link referenced in my last post. Thank you. 0
Please sign in to leave a comment.
Comments
4 comments