The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Frequent MySQL Crashes

Discussion in 'Database Discussions' started by vb543, Jan 22, 2015.

  1. vb543

    vb543 Member

    Joined:
    Jan 22, 2015
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    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.
    Code:
    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[/url]
    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[/url]
    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[/url]
    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[/url]
    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[/url]
    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 [email]cpses_wiu0NXOR8a@srv13.domain.com[/email]' 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[/url]
    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[/url]
    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[/url]
    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[/url]
    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[/url]
    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[/url]
    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[/url]
    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[/url]
    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[/url]
    how you can resolve the problem.
    my.cnf
    Code:
    [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!
     
  2. feldon27

    feldon27 Well-Known Member

    Joined:
    Mar 12, 2003
    Messages:
    111
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Houston, TX
    Did you delete any files in /var/lib/mysql ?
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,784
    Likes Received:
    665
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  4. vb543

    vb543 Member

    Joined:
    Jan 22, 2015
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thanks for the replies. I didn't make any changes to the system before this started happening.
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,784
    Likes Received:
    665
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page