Opened 2 years ago

Last modified 18 months ago

#12366 assigned Bug/Something is broken

ossie mysql unexpected restart

Reported by: Jamie McClelland Owned by: JaimeV
Priority: Medium Component: Tech
Keywords: Cc:
Sensitive: no

Description

I just experience an unexpected restart of mysql while I was importing a large file.

I suspect it was related to the oom-killer, so I increased ossie's memory from 4GB to 8GB (in addition to doubling the CPU from 2 to 4).

These should take affect on the next reboot.

I then noticed a number of erros in /var/log/mysql/error.log

I'm not sure if those errors are the cause of the crashes, but should still be investigated (it looks like there are some orphaned half tables that should be cleaned up).

Change History (7)

comment:1 Changed 2 years ago by Jamie McClelland

Owner: set to JaimeV
Status: newassigned

Assigning to you Jaime - since you are doing the restart tonight.

comment:2 Changed 2 years ago by Jamie McClelland

comment:3 Changed 2 years ago by JaimeV

I did the reboot last night jamie. How should I deal with orphaned tables?

comment:4 Changed 2 years ago by Jamie McClelland

There are two tables listed in the error log with reports like this one:

161223  8:18:21  InnoDB: Error creating file './biosafet_biodb/tmpsearch.ibd'.
161223  8:18:21  InnoDB: Operating system error number 17 in a file operation.
InnoDB: Error number 17 means 'File exists'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: The file already exists though the corresponding table did not
InnoDB: exist in the InnoDB data dictionary. Have you moved InnoDB
InnoDB: .ibd files around without using the SQL commands
InnoDB: DISCARD TABLESPACE and IMPORT TABLESPACE, or did
InnoDB: mysqld crash in the middle of CREATE TABLE? You can
InnoDB: resolve the problem by removing the file './biosafet_biodb/tmpsearch.ibd'
InnoDB: under the 'datadir' of MySQL.
161223  8:41:18  InnoDB: cannot calculate statistics for table tejas_wp/wp_wfVulnScanners
InnoDB: because the .ibd file is missing.  For help, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
161223  8:41:18 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table tejas_wp/wp_wfVulnScanners does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

For the biosafet_biodb/tmpsearch.ibd table, I found the .ibd file and there was no corresponding .frmfile. Since the name implies a temporary table, I simply deleted the .ibd file as recommended.

With tejas_wp/wp_wfVulnScanners there was both an idb and a frm file and however, when I logged in via mysql, the table was not recognized.

So, I moved them both into /root/tickets/12366 on ossie.

I think that should take care of the errors, but we may need a mysql restart. I'll keep monitoring the error log...

comment:5 Changed 18 months ago by Jamie McClelland

I'm seeing errors related to wp_wfVulnScanners in /var/log/mysql/error.log again.

This thread suggests its a wordfence problem and there is no solution. Sigh.

comment:6 Changed 18 months ago by Jamie McClelland

Another post on this topic offers some suggestions.

The mysql error is:

InnoDB: The file already exists though the corresponding table did not
InnoDB: exist in the InnoDB data dictionary. Have you moved InnoDB
InnoDB: .ibd files around without using the SQL commands
InnoDB: DISCARD TABLESPACE and IMPORT TABLESPACE, or did
InnoDB: mysqld crash in the middle of CREATE TABLE? You can
InnoDB: resolve the problem by removing the file './tejas_wp/wp_wfVulnScanners.ibd'
InnoDB: under the 'datadir' of MySQL.

I tried deleteing the ibd file as suggested, then deactivating and reactivating wordfence for the given site, but when I activate wordfence I get:

WordPress database error Table '`tejas_wp`.`wp_wfVulnScanners`' already exists for query create table IF NOT EXISTS wp_wfVulnScanners (
        IP int UNSIGNED PRIMARY KEY,
        ctime int UNSIGNED NOT NULL,
        hits int UNSIGNED NOT NULL
) made by include('phar:///usr/local/bin/wp/php/boot-phar.php'), include('phar:///usr/local/bin/wp/php/wp-cli.php'), WP_CLI\Runner->start, WP_CLI\Runner->_run_command, WP_CLI\Runner->run_command, WP_CLI\Dispatcher\Subcommand->invoke, call_user_func, WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure}, call_user_func, Plugin_Command->activate, activate_plugin, do_action('activate_wordfence/wordfence.php'), call_user_func_array, wordfence::installPlugin, wordfence::runInstall, wfSchema->createAll, wfDB->queryWrite
WordPress database error Table 'tejas_wp.wp_wfVulnScanners' doesn't exist for query SHOW FIELDS FROM wp_wfVulnScanners where field = 'IP' made by include('phar:///usr/local/bin/wp/php/boot-phar.php'), include('phar:///usr/local/bin/wp/php/wp-cli.php'), WP_CLI\Runner->start, WP_CLI\Runner->_run_command, WP_CLI\Runner->run_command, WP_CLI\Dispatcher\Subcommand->invoke, call_user_func, WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure}, call_user_func, Plugin_Command->activate, activate_plugin, do_action('activate_wordfence/wordfence.php'), call_user_func_array, wordfence::installPlugin, wordfence::runInstall
Plugin 'wordfence' activated.
Success: Activated 1 of 1 plugins.

I even tried restarting mysql after removing the ibd file, but same result. Somewhere, somehow mysql thinks that table exists.

comment:7 Changed 18 months ago by Jamie McClelland

The only option seems to be to drop the entire database and reload it via backup.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.