Thanks for the response, Yes checked that I'm running current (apt update/upgrade).
Tried the alter:
---
mysql> ALTER TABLE storage ENGINE = InnoDB; ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ERROR: Can't connect to the server
--
stopped the db, removed ib_logfiles* restarted mysql and tried again (Note, innodb_force_recovery=1 in both cases)
---
mysql> ALTER TABLE storage ENGINE = InnoDB; ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... Connection id: 12 Current database: observium
ERROR 1881 (HY000): Operation not allowed when innodb_force_recovery > 0.
---
So no luck with ether of those.
I could try the drop of the storage and recreate but will wait a bit in case Adam or anyone else has some bright ideas. Seems that there mysql/innodb is lacking in repair options or rollback options. Granted it's not an 'enterprise' product but figured it would have more structural integrity check options due to that (more people running it on home systems and such opposed to DC's). Just seems a bit fragile.
Steve
On 2022-07-10 08:45, Michael Loftis wrote:
On Sun, Jul 10, 2022 at 06:57 Steve Costaras via observium observium@observium.org wrote:
Yeah, but I seem to be running into a catch-22. I can start mysqld when I set innodb_force_recovery=1 but in that mode I can't do any commands (Operation not allowed when innodb_force_recovery > 0 )REPAIR TABLE is (kind of silly) a no-op for InnoDB, so mysqlcheck is just reporting it's results from CHECK TABLE (it issues the REPAIR TABLE if I recall, but that does nothing). So your mysqlcheck command didn't actually even attempt to repair the InnoDB ENGINE `storage` table.
First take a copy of the DB with it shutdown, because if things go awry/get worse you can go back to this copy.
Make sure you’ve got the latest MySQL version in your series - be it 8.0.x or 5.7.x - there’ve been a few, very very rarely, corruption bugs over the years.
Then start it again in recovery - Once it starts, in force recovery on, try ALTER TABLE storage ENGINE = InnoDB — an empty alter reads the table without indexes and rewrites it.
You can also try dumping the one table and restoring it (yes this can work in situations where it’s indeed index corruption)
If THAT doesn't work then try removing the ib_logfile(s), starting again, and try the alter again.
Ultimately what most likely happened is the underlying storage lies about write completion or had corruption, or you’re running with scissors by setting innodb_flush_log_at_trx_commit to a non-default value. InnoDB WALs everything and a corruption event is almost always caused elsewhere and will happen again if you don’t fix the underlying problem.
If the table can’t be repaired (and Adam, or someone can correct me here) I'm fairly certain you can DROP the storage table and CREATE it again, then re-run discovery to force it to populate, I think this may lose some alert settings (like ignore?...Adam would know better) but it'll get you back working without recreating the whole thing.
And then also getting a secondary issue that when I run commands, it appears that mysqld itself crashes when innodb hits the error) -----