Bad database import causing MySQL to crash

Wow, that's an unusual one! A Virtualmin database import just runs SQL statements to re-create tables from the original system, which I wouldn't expect can actually crash it!

Was the import from the same MySQL version?

Is it possible that some of the database files had been copied manually? As while we hadn't ever seen an issue like the above before after a typical backup/restore, we had seen issues like that when database files were manually copied from one system to another.

Joe's thought was to try deleting the domain in question, and see if MySQL starts normally at that point.

If it does, re-attempt the restore, and note the restore output.

Does MySQL work okay now, or do you see those same issues?

If you see the same issues, what is the output you received during the restore?

I've asked Joe for his thoughts on that problem.

In the meantime, would it be possible for us to access that backup to see if we can reproduce that problem on our test systems?

We'd like whatever was used immediately prior to seeing this database issue.

If this initially happened during a Virtualmin restore, we'd definitely want that exact Virtualmin backup archive that was used.

Is it possible to put it into a directory on the server that's experiencing the problem? We can download it from there.

Also, to help us in reviewing the logs, when did you first attempt the migration/restore?

Thanks!

To help us in reviewing the logs, when did you first attempt the migration/restore? That is, when did this issue all begin?

We unfortunately don't have the staffing to be able to work on an emergency/on-call basis, so I don't have an ETA for them someone will be able to review that. That's also assuming it's fixable at all.

If you are experiencing a critical issue and you need to get the system online ASAP, you may need to look into restoring the system from a previous backup. For example, if this is a VPS, if you happen to have a VPS snapshot available, that might be something to consider.

I've never seen this error before, so I'm flying pretty blindly here.

But, according to the document linked in the error you pasted above, this can happen if MySQL crashes or is otherwise interrupted during a migration (maybe OOM killer, or maybe disk space, maybe something else). And, the link also provides steps for correcting it, but I wouldn't feel comfortable attempting them or suggesting you attempt them without the system having good backups first.

I don't think this is really related to Virtualmin or Cloudmin (as there's nothing they do that should be able to make MySQL crash), I think it just happened during a move because a move does a lot of work in a short time. So, any disk/memory/kernel/MySQL issues or bugs would be most likely to be triggered at that time. (Of course, if there is a repeatable issue with moving a database, we'll fix it, but I think we would have heard about it before now, since this part of the code hasn't changed much in years, and this is a relatively old and well-supported version of MySQL.)

The link in your paste above says this:

"To work around this problem, start the mysql client with the --skip-auto-rehash option and try DROP TABLE again. (With name completion on, mysql tries to construct a list of table names, which fails when a problem such as just described exists.) "

So, I think they're saying that (starting with --skip-auto-rehash) should allow you to drop tables that exhibit this problem. So, maybe that will allow you to get things into a usable state.

Don't know or it makes sense. While you say customers waiting and so on.

Is it a idea to setup a extra new server for those and trying with the databases and data you have to get them in the air before time is running out and they are gone.? . ( even a kind of clean "empty DATAbase" could somtimes makes sense while they can go on with their work ) Yep you have more work afterwards if so to "merge") It depends how important things are in wich cases.

Also while even if mysql is running and their databases are working do you know for sure then they are not corrupted somehow or have some unrelaible data in them.?

So, you weren't able to remove the problem tables, or not able to drop the problem database after removing those tables?

What specific errors are you getting when trying to remove the problem tables and/or drop the database?

And, is it possible there's a hardware (i.e. disk) problem? This is pretty surprising behavior...MySQL is pretty reliable, I've never seen it in an unrecoverable state, at least not in the past decade or so.

Joe and/or Jamie may have some additional thoughts, but just to chime in while the west coast is sleeping --

It may be fixable, it may not be... however, if it were my server, I just wasn't having any luck resolving the database issue, and I wanted to get it up and running ASAP -- I would seriously consider doing this:

(this all assumes I have a fairly recent Virtualmin backup containing the databases)

• I'd start by making a new backup of everything possible

• Remove the mysql-server package

• Ensure that /var/lib/mysql is cleared out

• Reinstall the mysql-server package

• Restore Virtualmin backups for all my domains, but just the database feature

There's likely no reason to restore the full domains, and restoring just the database feature for all your domains should be all you'd need to get MySQL back up and running to a known good state.

Joe and/or Jamie may have some further thoughts for you on how to resolve the database corruption you're having. But as it seems like time is an issue here, if you have a way to revert to a known good state, maybe that would be a way to get things up and running again.

However, we'll hear from the other guys soon though if that's not the route you want to go :-)

Just rolling back that transaction would probably sort it out. It's possible there are other issues hiding behind that one (i.e., it's stopping as soon as it sees that problem and doesn't show others, but I think it usually complains about everything it sees wrong when starting up).

Glad you got it sorted. So, when you try the migration again, watch for errors, and let us know if something goes wrong. Make sure you've got all the latest updates and such first, though, and maybe do a basic disk check (something like badblocks--just a read only test is probably sufficient if this is the only weird problem you've seen on this hardware). Since this is a somewhat strange issue, and a crashing MySQL is possibly a sign of something wrong, you'll wanna be on the look out for what caused that crash (a regular old import from a dumped database obviously shouldn't crash MySQL!).

You might try it from the command line to insure you see all the errors.

OK, glad you got it sorted out. I would still recommend doing some testing on that server. I strongly suspect it wasn't specific to that one database, I suspect it is a systemic problem that was just triggered by the large amount of activity. Could also be a MySQL or kernel bug, and making sure you're up to date will hopefully help prevent it from happening again.