Had observium running on a system for a bit under ubuntu 20.04. Had a power issue where it seemed to screw the pooch on the server.   don't see filesystem errors that I can detect, however do find that the mysql instance doesn't want to start back up.

When trying to do a repair on the database, get the following:

(edited my.cnf with innodb_force_recovery so as to get mysqld to run)

mysqlcheck -u root -p --auto-repair --all-databases

---cut

observium.slas                                     OK observium.snmp_errors                              OK observium.status                                   OK observium.storage                                  To be repaired, cause follows: Server issued Warning  : InnoDB: The B-tree of index PRIMARY is corrupted. Warning  : InnoDB: Index index_unique is marked as corrupted Warning  : InnoDB: Index device_id is marked as corrupted error    : Corrupt observium.syslog                                   OK observium.syslog_alerts                            OK

---cut

Only the observium.storage table seems to be messed up (or only one throwing errors at least at this point).   Now this is thankfully not the main deployment this is in my test lab (reason why it didn't have good power backup).  So I /could/ just re-do the entire deployment, that being said, I would like, if possible to be able to recover this just to save about a years worth of data in the lab, and to see how to do this in case it actually happened on the production system.

anyone have suggestions?

If not, second question would be has anyone tested the install scripts with ubuntu 22.04 yet?  If I have to do it from scratch may as well use the newer version.

Thanks

steve

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 )

And then also getting a secondary issue that when I run commands, it appears that mysqld itself crashes when innodb hits the error)

-----

InnoDB: Page may be an index page where index id is 2129 2022-07-10T12:45:03.529567Z 0 [ERROR] [MY-011937] [InnoDB] [FATAL] Apparent corruption of an index page [page id: space=754, page number=4] to be written to data file. We intentionally crash the server to prevent corrupt data from ending up in data files. 2022-07-10T12:45:03.529614Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: buf0dblwr.cc:1031:ib::fatal triggered thread 139965364348672 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 12:45:03 UTC - mysqld got signal 6 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. Thread pointer: 0x7f4c44363410 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f4c39d279b0 thread_stack 0x100000 /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x559acf4f9ad1] /usr/sbin/mysqld(print_fatal_signal(int)+0x2fb) [0x559ace39a7db] /usr/sbin/mysqld(my_server_abort()+0x76) [0x559ace39a926] /usr/sbin/mysqld(my_abort()+0xe) [0x559acf4f3ace] /usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x349) [0x559acf76f7b9] /usr/sbin/mysqld(ib::fatal::~fatal()+0xd5) [0x559acf771c75] /usr/sbin/mysqld(Double_write::croak(buf_block_t const*)+0x25e) [0x559acf7e551e] /usr/sbin/mysqld(Double_write::prepare(buf_page_t const*, void**, unsigned int*)+0x15d) [0x559acf7e575d] /usr/sbin/mysqld(Double_write::enqueue(buf_flush_t, buf_page_t*, file::Block const*)+0x97) [0x559acf7ecfc7] /usr/sbin/mysqld(dblwr::write(buf_flush_t, buf_page_t*, bool)+0x1fb) [0x559acf7ed80b] /usr/sbin/mysqld(buf_flush_page(buf_pool_t*, buf_page_t*, buf_flush_t, bool)+0x277) [0x559acf7f7a67] /usr/sbin/mysqld(+0x2591657) [0x559acf7fa657] /usr/sbin/mysqld(buf_flush_do_batch(buf_pool_t*, buf_flush_t, unsigned long, unsigned long, unsigned long*)+0x653) [0x559acf7fb583] /usr/sbin/mysqld(buf_flush_lists(unsigned long, unsigned long, unsigned long*)+0xc7) [0x559acf7fc907] /usr/sbin/mysqld(+0x2595ee4) [0x559acf7feee4] /usr/sbin/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)()> > >::_M_run()+0xbc) [0x559acf60308c] /usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0xd6de4) [0x7f4c5f824de4] /lib/x86_64-linux-gnu/libpthread.so.0(+0x8609) [0x7f4c602c1609] /lib/x86_64-linux-gnu/libc.so.6(clone+0x43) [0x7f4c5f511133]

Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 0 Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash.

------

Is there a 'trick' around the catch-22 (still googling but so far haven't turned up anything).

Steve

On 2022-07-10 04:06, Adam Armstrong via observium wrote:

Seems like it’s just the indexes that are corrupted?

Try: https://blog.ghost3k.net/articles/mysql/169/fixing-mysql-innodb-index-corrup...

Adam.

Sent from my iPhone

...

On 10 Jul 2022, at 01:15, Steve Costaras via observium observium@observium.org wrote:

 Had observium running on a system for a bit under ubuntu 20.04. Had a power issue where it seemed to screw the pooch on the server.   don't see filesystem errors that I can detect, however do find that the mysql instance doesn't want to start back up.

When trying to do a repair on the database, get the following:

(edited my.cnf with innodb_force_recovery so as to get mysqld to run)

mysqlcheck -u root -p --auto-repair --all-databases

---cut

observium.slas                                     OK observium.snmp_errors                              OK observium.status                                   OK observium.storage                                  To be repaired, cause follows: Server issued Warning  : InnoDB: The B-tree of index PRIMARY is corrupted. Warning  : InnoDB: Index index_unique is marked as corrupted Warning  : InnoDB: Index device_id is marked as corrupted error    : Corrupt observium.syslog                                   OK observium.syslog_alerts                            OK

---cut

Only the observium.storage table seems to be messed up (or only one throwing errors at least at this point). Now this is thankfully not the main deployment this is in my test lab (reason why it didn't have good power backup).  So I /could/ just re-do the entire deployment, that being said, I would like, if possible to be able to recover this just to save about a years worth of data in the lab, and to see how to do this in case it actually happened on the production system.

anyone have suggestions?

If not, second question would be has anyone tested the install scripts with ubuntu 22.04 yet?  If I have to do it from scratch may as well use the newer version.

Thanks

steve

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

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)

-----

I'll try both methods as I finished a full backup of the server, so first I'll do the simple method and drop the table and rebuild with an empty table from observium/update/db_schema_mysql.sql .  If that doesn't work then I can rebuild from scratch and try and pull the table from the new build and import into the old.

That being said, noticed that the observium_installation.sh script for debian/ubuntu was updated with 21.04/22.04 headers but the script itself still shows the same version number of 0.2.  (more just a comment).

Thanks for suggestions.

On 2022-07-10 12:16, Adam Armstrong via observium wrote:

Try making a new install elsewhere, and replacing the broken storage table files with ones from a clean install?

Discovery will recollect the data, there’s no configuration or history in the storage tables

I’m not sure how possible it is to just shove an empty file in there.

Adam.

*From:*observium observium-bounces@observium.org *On Behalf Of *Steve Costaras via observium *Sent:* 10 July 2022 17:24 *To:* Michael Loftis mloftis@wgops.com; Observium observium@observium.org *Cc:* Steve Costaras stevecs@chaven.com *Subject:* Re: [Observium] corrupted mysql database after a power outage

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)

    -----

--

Steve Costaras      Apprentice codger stevecs@chaven.com https://join.skype.com/invite/r5vO5SB8c17s

"Wealth consists not in having great possessions, but in having few wants." (Epictetus)  ----------------------------------------------

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

LOL.  Yes someone was thinking at least at some point:

--

#!/bin/bash set -e

VERSION="0.2" --

BTW, the table drop and recreate seems to be working, just finished a rediscovery and table appears to be populating.

Steve

On 2022-07-10 17:25, Adam Armstrong via observium wrote:

The script has version numbers? :D

Adam.

*From:*observium observium-bounces@observium.org *On Behalf Of *Steve Costaras via observium *Sent:* 10 July 2022 22:45 *To:* observium@observium.org *Cc:* Steve Costaras stevecs@chaven.com *Subject:* Re: [Observium] corrupted mysql database after a power outage

I'll try both methods as I finished a full backup of the server, so first I'll do the simple method and drop the table and rebuild with an empty table from observium/update/db_schema_mysql.sql .  If that doesn't work then I can rebuild from scratch and try and pull the table from the new build and import into the old.

That being said, noticed that the observium_installation.sh script for debian/ubuntu was updated with 21.04/22.04 headers but the script itself still shows the same version number of 0.2. (more just a comment).

Thanks for suggestions.

On 2022-07-10 12:16, Adam Armstrong via observium wrote:

Try making a new install elsewhere, and replacing the broken
storage table files with ones from a clean install?

Discovery will recollect the data, there’s no configuration or
history in the storage tables

I’m not sure how possible it is to just shove an empty file in there.

Adam.

*From:*observium <observium-bounces@observium.org>
<mailto:observium-bounces@observium.org> *On Behalf Of *Steve
Costaras via observium
*Sent:* 10 July 2022 17:24
*To:* Michael Loftis <mloftis@wgops.com>
<mailto:mloftis@wgops.com>; Observium <observium@observium.org>
<mailto:observium@observium.org>
*Cc:* Steve Costaras <stevecs@chaven.com> <mailto:stevecs@chaven.com>
*Subject:* Re: [Observium] corrupted mysql database after a power
outage

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)

        -----

-- 

----------------------------------------------

Steve Costaras      Apprentice codger

stevecs@chaven.com   

https://join.skype.com/invite/r5vO5SB8c17s

-

"Wealth consists not in having great possessions,

but in having few wants." (Epictetus)

  ----------------------------------------------



_______________________________________________

observium mailing list

observium@observium.org

http://postman.memetic.org/cgi-bin/mailman/listinfo/observium

--

Steve Costaras      Apprentice codger stevecs@chaven.com https://join.skype.com/invite/r5vO5SB8c17s

"Wealth consists not in having great possessions, but in having few wants." (Epictetus)  ----------------------------------------------

---

observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium