Jump to content

MariaDB Corruption Help - Zabbix Pi Monitoring

robyholmes

By robyholmes
in *nix

 Share

Recommended Posts

robyholmes

robyholmes

Posted
Posted

Hi All,

 

Could I ask for assistance from any Linux experts to help me resolve a problem with my Pi 4 Zabbix system. I've been building it for a while now and it's been going very well. Yesterday I finished the configuration and updated to Zabbix 6.0. Zabbix started fine and it was back monitoring before I left work. I was already running late so I didn't back up the SD card as I planned on doing that this morning. However around midnight the Zabbix database stopped and now won't start.

 

MariaDB is reporting the following now:

 

May 20 10:01:30 raspberrypi systemd[1]: Stopped MariaDB 10.5.15 database server.
░░ Subject: A stop job for unit mariadb.service has finished
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A stop job for unit mariadb.service has finished.
░░
░░ The job identifier is 44146 and the job result is done.
May 20 10:01:30 raspberrypi systemd[1]: Starting MariaDB 10.5.15 database server...
░░ Subject: A start job for unit mariadb.service has begun execution
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A start job for unit mariadb.service has begun execution.
░░
░░ The job identifier is 44146.
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.15-MariaDB-0+deb11u1) starting as process 11820 ...
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] InnoDB: Uses event mutexes
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] InnoDB: Number of pools: 1
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] InnoDB: Using generic crc32 instructions
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] InnoDB: Using Linux native AIO
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] InnoDB: Completed initialization of buffer pool
May 20 10:01:30 raspberrypi mariadbd[11820]: 2022-05-20 10:01:30 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=65124827976,65124827976
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [Note] InnoDB: 2 transaction(s) which must be rolled back or cleaned up in total 350 row operations to undo
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [Note] InnoDB: Trx id counter is 48717101
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [Note] InnoDB: Starting final batch to recover 935 pages from redo log.
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [Note] InnoDB: 128 rollback segments are active.
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [Note] InnoDB: Starting in background the rollback of recovered transactions
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [Note] InnoDB: Creating shared tablespace for temporary tables
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
May 20 10:01:31 raspberrypi mariadbd[11820]: 2022-05-20 10:01:31 0 [ERROR] [FATAL] InnoDB: InnoDB is trying to free page [page id: space=76, page number=64] though it is already marked as >
May 20 10:01:31 raspberrypi mariadbd[11820]: 220520 10:01:31 [ERROR] mysqld got signal 6 ;
May 20 10:01:31 raspberrypi mariadbd[11820]: This could be because you hit a bug. It is also possible that this binary
May 20 10:01:31 raspberrypi mariadbd[11820]: or one of the libraries it was linked against is corrupt, improperly built,
May 20 10:01:31 raspberrypi mariadbd[11820]: or misconfigured. This error can also be caused by malfunctioning hardware.
May 20 10:01:31 raspberrypi mariadbd[11820]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
May 20 10:01:31 raspberrypi mariadbd[11820]: We will try our best to scrape up some info that will hopefully help
May 20 10:01:31 raspberrypi mariadbd[11820]: diagnose the problem, but since we have already crashed,
May 20 10:01:31 raspberrypi mariadbd[11820]: something is definitely wrong and this may fail.
May 20 10:01:31 raspberrypi mariadbd[11820]: Server version: 10.5.15-MariaDB-0+deb11u1
May 20 10:01:31 raspberrypi mariadbd[11820]: key_buffer_size=134217728
May 20 10:01:31 raspberrypi mariadbd[11820]: read_buffer_size=131072
May 20 10:01:31 raspberrypi mariadbd[11820]: max_used_connections=0
May 20 10:01:31 raspberrypi mariadbd[11820]: max_threads=153
May 20 10:01:31 raspberrypi mariadbd[11820]: thread_count=0
May 20 10:01:31 raspberrypi mariadbd[11820]: It is possible that mysqld could use up to
May 20 10:01:31 raspberrypi mariadbd[11820]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 466524 K  bytes of memory
May 20 10:01:31 raspberrypi mariadbd[11820]: Hope that's ok; if not, decrease some variables in the equation.
May 20 10:01:31 raspberrypi mariadbd[11820]: Thread pointer: 0x0
May 20 10:01:31 raspberrypi mariadbd[11820]: Attempting backtrace. You can use the following information to find out
May 20 10:01:31 raspberrypi mariadbd[11820]: where mysqld died. If you see no messages after this, something went
May 20 10:01:31 raspberrypi mariadbd[11820]: terribly wrong...
May 20 10:01:31 raspberrypi mariadbd[11820]: stack_bottom = 0x0 thread_stack 0x49000
May 20 10:01:31 raspberrypi mariadbd[11820]: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
May 20 10:01:31 raspberrypi mariadbd[11820]: information that should help you find out what is causing the crash.
May 20 10:01:31 raspberrypi mariadbd[11820]: Writing a core file...
May 20 10:01:31 raspberrypi mariadbd[11820]: Working directory at /var/lib/mysql
May 20 10:01:31 raspberrypi mariadbd[11820]: Resource Limits:
May 20 10:01:31 raspberrypi mariadbd[11820]: Limit                     Soft Limit           Hard Limit           Units
May 20 10:01:31 raspberrypi mariadbd[11820]: Max cpu time              unlimited            unlimited            seconds
May 20 10:01:31 raspberrypi mariadbd[11820]: Max file size             unlimited            unlimited            bytes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max data size             unlimited            unlimited            bytes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max stack size            8388608              unlimited            bytes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max core file size        0                    unlimited            bytes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max resident set          unlimited            unlimited            bytes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max processes             12402                12402                processes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max open files            32768                32768                files
May 20 10:01:31 raspberrypi mariadbd[11820]: Max locked memory         65536                65536                bytes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max address space         unlimited            unlimited            bytes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max file locks            unlimited            unlimited            locks
May 20 10:01:31 raspberrypi mariadbd[11820]: Max pending signals       12402                12402                signals
May 20 10:01:31 raspberrypi mariadbd[11820]: Max msgqueue size         819200               819200               bytes
May 20 10:01:31 raspberrypi mariadbd[11820]: Max nice priority         0                    0
May 20 10:01:31 raspberrypi mariadbd[11820]: Max realtime priority     0                    0
May 20 10:01:31 raspberrypi mariadbd[11820]: Max realtime timeout      unlimited            unlimited            us
May 20 10:01:31 raspberrypi mariadbd[11820]: Core pattern: core
May 20 10:01:31 raspberrypi systemd[1]: mariadb.service: Main process exited, code=killed, status=6/ABRT
░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ An ExecStart= process belonging to unit mariadb.service has exited.
░░
░░ The process' exit code is 'killed' and its exit status is 6.
May 20 10:01:31 raspberrypi systemd[1]: mariadb.service: Failed with result 'signal'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ The unit mariadb.service has entered the 'failed' state with result 'signal'.
May 20 10:01:31 raspberrypi systemd[1]: Failed to start MariaDB 10.5.15 database server.
░░ Subject: A start job for unit mariadb.service has failed
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A start job for unit mariadb.service has finished with a failure.
░░

 

So it looks like I have corruption in my database, but google doesn't seem to be helping me as I think I need to drop the tables and re-import? Would someone be willing to offer advice or PM me to get in touch and go from there?

 

Thank you,

Rob Holmes

dmj

dmj

Posted
Posted

Try and start it with innodb_force_recovery

put this in your ini file:

[mysqld]

innodb_force_recovery= 1

  • Thanks 1
robyholmes

robyholmes

Posted
  • Author
Posted
Try and start it with innodb_force_recovery

put this in your ini file:

 

I did read this a few times but I couldn't work out where to put it. All the paths I found the file was either blank or didn't exist.

dmj

dmj

Posted
Posted
I did read this a few times but I couldn't work out where to put it. All the paths I found the file was either blank or didn't exist.

 

It's probably something like 

/etc/mysql/mysql.conf.d/mysqld.cnf

 

even if it starts you'll probably have to rebuild the tables.

My guess is the SD card is corrupt.

  • Thanks 1
robyholmes

robyholmes

Posted
  • Author
Posted (edited) 

May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.15-MariaDB-0+deb11u1) starting as process 14825 ...
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: !!! innodb_force_recovery is set to 1 !!!
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: Uses event mutexes
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: Number of pools: 1
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: Using generic crc32 instructions
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: Using Linux native AIO
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: Completed initialization of buffer pool
May 20 12:40:19 raspberrypi mariadbd[14825]: 2022-05-20 12:40:19 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=65124827976,65124827976
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: 2 transaction(s) which must be rolled back or cleaned up in total 350 row operations to undo
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: Trx id counter is 48717101
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: Starting final batch to recover 935 pages from redo log.
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: 128 rollback segments are active.
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: Starting in background the rollback of recovered transactions
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: Creating shared tablespace for temporary tables
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
May 20 12:40:20 raspberrypi mariadbd[14825]: 2022-05-20 12:40:20 0 [ERROR] [FATAL] InnoDB: InnoDB is trying to free page [page id: space=76, page number=64] though it is already marked as free in the tablespace! The tablespace free space info is corrupt. You may need to dump your tables and recreate the whole database!Please refer to https://mariadb.com/kb/en/library/innodb-rec>
May 20 12:40:20 raspberrypi mariadbd[14825]: 220520 12:40:20 [ERROR] mysqld got signal 6 ;
May 20 12:40:20 raspberrypi mariadbd[14825]: This could be because you hit a bug. It is also possible that this binary
May 20 12:40:20 raspberrypi mariadbd[14825]: or one of the libraries it was linked against is corrupt, improperly built,
May 20 12:40:20 raspberrypi mariadbd[14825]: or misconfigured. This error can also be caused by malfunctioning hardware.
May 20 12:40:20 raspberrypi mariadbd[14825]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
May 20 12:40:20 raspberrypi mariadbd[14825]: We will try our best to scrape up some info that will hopefully help
May 20 12:40:20 raspberrypi mariadbd[14825]: diagnose the problem, but since we have already crashed,
May 20 12:40:20 raspberrypi mariadbd[14825]: something is definitely wrong and this may fail.
May 20 12:40:20 raspberrypi mariadbd[14825]: Server version: 10.5.15-MariaDB-0+deb11u1
May 20 12:40:20 raspberrypi mariadbd[14825]: key_buffer_size=134217728
May 20 12:40:20 raspberrypi mariadbd[14825]: read_buffer_size=131072
May 20 12:40:20 raspberrypi mariadbd[14825]: max_used_connections=0
May 20 12:40:20 raspberrypi mariadbd[14825]: max_threads=153
May 20 12:40:20 raspberrypi mariadbd[14825]: thread_count=0
May 20 12:40:20 raspberrypi mariadbd[14825]: It is possible that mysqld could use up to
May 20 12:40:20 raspberrypi mariadbd[14825]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 466524 K  bytes of memory
May 20 12:40:20 raspberrypi mariadbd[14825]: Hope that's ok; if not, decrease some variables in the equation.
May 20 12:40:20 raspberrypi mariadbd[14825]: Thread pointer: 0x0
May 20 12:40:20 raspberrypi mariadbd[14825]: Attempting backtrace. You can use the following information to find out
May 20 12:40:20 raspberrypi mariadbd[14825]: where mysqld died. If you see no messages after this, something went
May 20 12:40:20 raspberrypi mariadbd[14825]: terribly wrong...
May 20 12:40:20 raspberrypi mariadbd[14825]: stack_bottom = 0x0 thread_stack 0x49000
May 20 12:40:20 raspberrypi mariadbd[14825]: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
May 20 12:40:20 raspberrypi mariadbd[14825]: information that should help you find out what is causing the crash.
May 20 12:40:20 raspberrypi mariadbd[14825]: Writing a core file...
May 20 12:40:20 raspberrypi mariadbd[14825]: Working directory at /var/lib/mysql
May 20 12:40:20 raspberrypi mariadbd[14825]: Resource Limits:
May 20 12:40:20 raspberrypi mariadbd[14825]: Limit                     Soft Limit           Hard Limit           Units
May 20 12:40:20 raspberrypi mariadbd[14825]: Max cpu time              unlimited            unlimited            seconds
May 20 12:40:20 raspberrypi mariadbd[14825]: Max file size             unlimited            unlimited            bytes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max data size             unlimited            unlimited            bytes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max stack size            8388608              unlimited            bytes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max core file size        0                    unlimited            bytes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max resident set          unlimited            unlimited            bytes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max processes             12402                12402                processes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max open files            32768                32768                files
May 20 12:40:20 raspberrypi mariadbd[14825]: Max locked memory         65536                65536                bytes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max address space         unlimited            unlimited            bytes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max file locks            unlimited            unlimited            locks
May 20 12:40:20 raspberrypi mariadbd[14825]: Max pending signals       12402                12402                signals
May 20 12:40:20 raspberrypi mariadbd[14825]: Max msgqueue size         819200               819200               bytes
May 20 12:40:20 raspberrypi mariadbd[14825]: Max nice priority         0                    0
May 20 12:40:20 raspberrypi mariadbd[14825]: Max realtime priority     0                    0
May 20 12:40:20 raspberrypi mariadbd[14825]: Max realtime timeout      unlimited            unlimited            us
May 20 12:40:20 raspberrypi mariadbd[14825]: Core pattern: core
May 20 12:40:20 raspberrypi sudo[14840]:       pi : TTY=pts/0 ; PWD=/etc/mysql ; USER=root ; COMMAND=/bin/systemctl start mariadb
May 20 12:40:20 raspberrypi sudo[14840]: pam_unix(sudo:session): session opened for user root(uid=0) by pi(uid=1000)
May 20 12:40:20 raspberrypi systemd[1]: mariadb.service: Main process exited, code=killed, status=6/ABRT

 

Edit: Found the file /etc/ mysql/ my.cnf and put the recovery mode in that but it's not solved it I'm afraid.

Edited by robyholmes
Cloudflare was blocking me from posting
robyholmes

robyholmes

Posted
  • Author
Posted

Recovery Mode 3 has got it started by the look of it, but Zabbix can't connect.

 

░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ An ExecStart= process belonging to unit mariadb.service has exited.
░░
░░ The process' exit code is 'killed' and its exit status is 11.
May 20 12:59:57 raspberrypi systemd[1]: mariadb.service: Failed with result 'signal'.
░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ The unit mariadb.service has entered the 'failed' state with result 'signal'.
May 20 12:59:57 raspberrypi systemd[1]: mariadb.service: Consumed 1.292s CPU time.
░░ Subject: Resources consumed by unit runtime
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ The unit mariadb.service completed and consumed the indicated resources.
May 20 13:00:02 raspberrypi systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 874.
░░ Subject: Automatic restarting of a unit has been scheduled
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ Automatic restarting of the unit mariadb.service has been scheduled, as the result for
░░ the configured Restart= setting for the unit.
May 20 13:00:02 raspberrypi systemd[1]: Stopped MariaDB 10.5.15 database server.
░░ Subject: A stop job for unit mariadb.service has finished
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A stop job for unit mariadb.service has finished.
░░
░░ The job identifier is 58978 and the job result is done.
May 20 13:00:02 raspberrypi systemd[1]: mariadb.service: Consumed 1.292s CPU time.
░░ Subject: Resources consumed by unit runtime
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ The unit mariadb.service completed and consumed the indicated resources.
May 20 13:00:02 raspberrypi systemd[1]: Starting MariaDB 10.5.15 database server...
░░ Subject: A start job for unit mariadb.service has begun execution
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A start job for unit mariadb.service has begun execution.
░░
░░ The job identifier is 58978.
May 20 13:00:02 raspberrypi mariadbd[27166]: 2022-05-20 13:00:02 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.15-MariaDB-0+deb11u1) starting as process 27166 ...
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: !!! innodb_force_recovery is set to 3 !!!
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Uses event mutexes
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Number of pools: 1
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Using generic crc32 instructions
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Using Linux native AIO
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Completed initialization of buffer pool
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=65124827976,65124827976
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: 2 transaction(s) which must be rolled back or cleaned up in total 350 row operations to undo
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Trx id counter is 48717236
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Starting final batch to recover 1146 pages from redo log.
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: 128 rollback segments are active.
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Creating shared tablespace for temporary tables
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: 10.5.15 started; log sequence number 65126821965; transaction id 48717236
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] Plugin 'FEEDBACK' is disabled.
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] Server socket created on IP: '127.0.0.1'.
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] Reading of all Master_info entries succeeded
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] Added new Master_info '' to hash table
May 20 13:00:03 raspberrypi mariadbd[27166]: 2022-05-20 13:00:03 0 [Note] /usr/sbin/mariadbd: ready for connections.
May 20 13:00:03 raspberrypi mariadbd[27166]: Version: '10.5.15-MariaDB-0+deb11u1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Raspbian 11
May 20 13:00:03 raspberrypi systemd[1]: Started MariaDB 10.5.15 database server.
░░ Subject: A start job for unit mariadb.service has finished successfully
░░ Defined-By: systemd
░░ Support: https://www.debian.org/support
░░
░░ A start job for unit mariadb.service has finished successfully.
░░
░░ The job identifier is 58978.
May 20 13:00:03 raspberrypi /etc/mysql/debian-start[27188]: Upgrading MySQL tables if necessary.
May 20 13:00:03 raspberrypi /etc/mysql/debian-start[27191]: /usr/bin/mysql_upgrade: unknown variable 'innodb_force_recovery=3'
May 20 13:00:03 raspberrypi /etc/mysql/debian-start[27192]: Checking for insecure root accounts.
May 20 13:00:04 raspberrypi mariadbd[27166]: 2022-05-20 13:00:04 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
May 20 13:00:04 raspberrypi debian-start[27195]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
May 20 13:00:04 raspberrypi mariadbd[27166]: 2022-05-20 13:00:04 0 [Note] InnoDB: Buffer pool(s) load completed at 220520 13:00:04

dmj

dmj

Posted
Posted

If it's started you should backup the database before doing anything.

 

something like 

mysqldump --skip-lock-tables --order-by-primary -h localhost > /mnt/databasebackup.sql

You might need to add user "-u" and "-p" options.

 

I'd then try and run the zabbit migration scripts on the existing database (to recreate the tables). This will create the tables that zabbix needs to run.

You can always use the corrupt backup file to get back where you started.

  • Thanks 1
robyholmes

robyholmes

Posted
  • Author
Posted
If it's started you should backup the database before doing anything.

 

something like 

mysqldump --skip-lock-tables --order-by-primary -h localhost > /mnt/databasebackup.sql

You might need to add user "-u" and "-p" options.

 

I'd then try and run the zabbit migration scripts on the existing database (to recreate the tables). This will create the tables that zabbix needs to run.

You can always use the corrupt backup file to get back where you started.

 

Thank you @dmj

 

I've managed to get a SQLdump but now I have to get it off the Pi. I don't have a monitor connection for it and I'm failing at getting SSH to copy it for me. I've really reached the end of my knowledge, especially on a friday!

 

Zabbix does work very a short while if you reboot the Pi, but soon stops again.

dmj

dmj

Posted
Posted
Thank you @dmj

 

I've managed to get a SQLdump but now I have to get it off the Pi. I don't have a monitor connection for it and I'm failing at getting SSH to copy it for me. I've really reached the end of my knowledge, especially on a friday!

 

scp user@host:/path/filename.sql /local/path

Zabbix does work very a short while if you reboot the Pi, but soon stops again.

 

 

I'd guess it's missing something from the database. Try re-running the initialisation script ontop of the database. It might work. from the docs:

[color=#25282F][font=monospace]# zcat /usr/share/doc/zabbix-sql-scripts/mysql/server.sql.gz | mysql -uzabbix -p zabbix[/font][/color]

 

https://www.zabbix.com/download

 

Ultimately you probably need to replace the sd card (and setup DB replication so if it goes down you can switch over to the replica DB!)

  • Thanks 1
robyholmes

robyholmes

Posted
  • Author
Posted
Don't use an SD card for a database though, that's just asking for trouble, run a VM/container on a full sized computer

 

I'd rather not have it running on my VM Host as it's meant to be monitoring the host cluster and alerting me if anything goes wrong. I've had a Pi monitoring system for a while now running Nems Linux and switched to Zabbix in the last month. I think it'll work better longer term but just a shame something, be that the OS or SD Card itself has screwed up before I backed it up.

robyholmes

robyholmes

Posted
  • Author
Posted
I'm thinking a USB 3.0 to SATA adapter and then I can use one of my many 2.5" SSDs for it. I have plenty of space to put this and makes backing it up (Clone of SDD) easy as well using my existing HDD dock.
Techno-umesh

Techno-umesh

Posted
Posted

I suggest you try "mysqlcheck" to repair your MariaDB database.

mysqlcheck is a maintenance tool that allows you to check, repair, analyze and optimize multiple tables from the command line.

It is essentially a command line interface to the CHECK TABLE, REPAIR TABLE, ANALYZE TABLE and OPTIMIZE TABLE commands, and so, unlike myisamchk and aria_chk, requires the server to be running.

This tool does not work with partitioned tables.

Or

"Stellar Repair for MySQl" is a good software to repair MariaDB databases within a few clicks. The best part, you can view your recoverable data before saving. 98.6% success rate.

robyholmes

robyholmes

Posted
  • Author
Posted
Thanks for everyone suggestions. I've pretty much given up on recovery now, it's just not responding to a lot of commands I've giving it so clearly something is amiss, most likely the SD card. So I've got a 2.5" enclosure on order so I can use a SATA SSD and I'll re-build from scratch next half term. Making sure I backup the SSD along the way.
  • Thanks 1
IrritableTech

IrritableTech

Posted
Posted

I may be too late but after a recent experience with Zabbix I’ll ask… have you run out of space on the SD card?

 

If you can start the pi but stop zabbix-server see what logs you can prune and free up space. That may give you enough head room to back up the MySQL database to removable storage.

 

Reinstalling zabbix on new storage and importing the sql should have you pretty much where you were if my experience is anything to go by.

 

All the time this also leaves your sdcard practically untouched (bar the space making) and gives you plenty of second attempts.

robyholmes

robyholmes

Posted
  • Author
Posted
I may be too late but after a recent experience with Zabbix I’ll ask… have you run out of space on the SD card?

 

If you can start the pi but stop zabbix-server see what logs you can prune and free up space. That may give you enough head room to back up the MySQL database to removable storage.

 

Reinstalling zabbix on new storage and importing the sql should have you pretty much where you were if my experience is anything to go by.

 

All the time this also leaves your sdcard practically untouched (bar the space making) and gives you plenty of second attempts.

 

I did get the database backed up thanks to @dmj but I've not been able to copy it off the SD card and Zabbix-Server won't stop (Command just hangs). I'll keep the SD card safe while I get a new OS installed on an SSD, then see if I can copy the database backup off the SD card. Worth a go.

  • Thanks 1

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing


×
×
  • Create New...