Doing a select * and trying to dump the user table to repair it causes MySQL to always crash at the same row.
*
Steps to reproduce the behavior:
Switch the power off to your server until the data is corrupted
*
So after many hours of troubleshooting I figured out that my users table has become corrupted due to a power failure and my server ungracefully shutting down.
I’ve tried to dump the table as a way of repairing it, but I can’t even get it to dump the table even with innodb_force_recovery=1. It keeps crashing MySQL at exactly the same row every time.
I have around 23000 users in the table and have with trial and error managed to get a successful SELECT to run up to around id=19200, after which it causes MySQL to crash.
The plan now is to dump "WHERE id<19201" and then drop the table and restore the users table.
My question is, what else do I have to consider, since there will be a few relational issues where other tables reference the users that are about to be lost.
I would presume that the roles_users and groups_users would simply require me to delete the rows that reference the users I’ve lost.
What about tickets? I haven’t checked yet but assume it also has a user_id column, can that be set to null? What would be the correct way be to still have the tickets in the system but not delete them?
I’ve managed to get my Zammad to work again. It was quite a battle…
I used mysqlcheck to figure out which table was causing MySQL to crash.
Because I was unable to dump the users table (as it failed and crashed MySQL at a specific row every time) it seemed at this stage that the data was corrupted and I was stuck/screwed.
I then tried to run a SELECT on the users table WHERE id < [the row where it crashed]. That ran successfully, I also tried to see if I could select data after the corrupted row but was unsuccessful.
I then first decided to see if a fsck repair run would do any good, so I ran that from an Ubuntu live USB on the drive where Zammad lives.
It didn’t fix my problem, but I then noticed that I could actually select rows after the corrupted row. I ended up fiddling and testing and eventually could run SELECT * FROM users WHERE id < 19212 OR id > 19259.
Any users between id 19212 and 19259 would crash the MySQL server. I then dumped the table using the --where flag and the above values.
After that I restored the dump and the users table was now not corrupted anymore.
My Zammad was back to working condition after all this. So all in all, I lost 47’ish users of almost 24 000, I can live with that!
Personally, before switching to postgresql (which is technically a complex process), I’d suggest to get an UPS to reduce dangers of power losses. At least as long as the fault was not on someone pulling a plug.
I would definitely do that with any new installation, but stuck with MySQL now.
Don’t think it’s worth the time and effort to get it moved to Postgres, unless there’s some simple way of migrating…
There is a UPS connected, power went off during the night and UPS only lasts about 10 minutes.
Would need to get the UPS to trigger shutdown, but have not figured that out with the UPS multiple PC setup we have…
I don’t know how zammad adress the multi-backend (postgresql, mysql, etc.) and cannot say anything about the migration on this context. However for mysql to postgresql migration it’s usualy straightforward as MySQL is a simple engine with well-known workaround situations adressed correctly by PostgreSQL.
