Mysql help - DB corrupted?

[alabamatoy]

1.37.62 on Ubuntu 22.04
Mysql has crashed and wont restart, obviously this blocks ZM from running. I frankly dont care about the data, I can recreate all of it manually...PITA, but I can do it. But I cannot get mysql to start. I set

Code: Select all

[mysqld]
innodb_force_recovery = 3

in /etc/mysql/mysql.conf.d Here's what I get when I attempt to start mysql:

Code: Select all

2024-07-10T22:36:20.131292Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.37-0ubuntu0.22.04.3) starting as process 294596
2024-07-10T22:36:20.146629Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-10T22:36:20.839540Z 0 [ERROR] [MY-012153] [InnoDB] Trying to access page number 3321888767 in space 4294967278, space name innodb_undo_002, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2024-07-10T22:36:20.839612Z 0 [ERROR] [MY-012154] [InnoDB] Server exits.
2024-07-10T22:36:20.839628Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:7525 thread 137293995247168
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.
2024-07-10T22:36:20Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=9e65b9e0a7de4a17ad5002e0653e3f1a1aa060dd
Thread pointer: 0x0
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 = 0 thread_stack 0x100000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x5d4c7166fab1]
/usr/sbin/mysqld(print_fatal_signal(int)+0x3bc) [0x5d4c70c7cfac]
/usr/sbin/mysqld(my_server_abort()+0x7e) [0x5d4c70c7d0de]
/usr/sbin/mysqld(my_abort()+0xe) [0x5d4c71669e4e]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x183) [0x5d4c71825593]
/usr/sbin/mysqld(+0x1dfe578) [0x5d4c71e7e578]
/usr/sbin/mysqld(Fil_shard::do_io(IORequest const&, bool, page_id_t const&, page_size_t const&, unsigned long, unsigned long, void*, void*)+0xcbb) [0x5d4c71931f7b]
/usr/sbin/mysqld(fil_io(IORequest const&, bool, page_id_t const&, page_size_t const&, unsigned long, unsigned long, void*, void*)+0x86) [0x5d4c71932026]
/usr/sbin/mysqld(buf_read_page_low(dberr_t*, bool, unsigned long, unsigned long, page_id_t const&, page_size_t const&, bool)+0x169) [0x5d4c71889fb9]
/usr/sbin/mysqld(buf_read_page(page_id_t const&, page_size_t const&)+0x4a) [0x5d4c7188affa]
/usr/sbin/mysqld(+0x180b102) [0x5d4c7188b102]
/usr/sbin/mysqld(Buf_fetch_normal::get(buf_block_t*&)+0x420) [0x5d4c71852a50]
/usr/sbin/mysqld(buf_page_get_gen(page_id_t const&, page_size_t const&, unsigned long, buf_block_t*, Page_fetch, ut::Location, mtr_t*, bool)+0x85a) [0x5d4c71853f8a]
/usr/sbin/mysqld(trx_undo_lists_init(trx_rseg_t*)+0x248) [0x5d4c7182a828]
/usr/sbin/mysqld(trx_rseg_init_thread(void*, unsigned long)+0xbf) [0x5d4c717ff06f]
/usr/sbin/mysqld(+0x1777ffd) [0x5d4c717f7ffd]
/lib/x86_64-linux-gnu/libstdc++.so.6(+0xdc253) [0x7cde6bcdc253]
/lib/x86_64-linux-gnu/libc.so.6(+0x94ac3) [0x7cde6b894ac3]
/lib/x86_64-linux-gnu/libc.so.6(+0x126850) [0x7cde6b926850]

I see that it tells me "please check that your my.cnf matches the ibdata files that you have in the MySQL server." I do not understand how to do this. /var/lib/mysql has lots of files related to ibdata, but I dont understand how to "match" anything with my.cnf.
Nothing else that I know of is using mysql on this server, so if I blow it away and start over, I dont care.

How do I fix this?

[iconnor]

I have almost never had any success with recover < 6. Try 6. It will be read only, but you can take a db dump.

Dropping the existing db gets hard when everything is read only. I typically apt remove --purge mysql* in order to nuke it from orbit, then re-install, and restore from backup.

[alabamatoy]

OK, with recover set at 6, here's what I get:

Code: Select all

2024-07-11T13:44:59.270788Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.37-0ubuntu0.22.04.3) starting as process 319111
2024-07-11T13:44:59.272325Z 0 [System] [MY-013951] [Server] A backtrace was processed and added to the main error-log in the appropriate format.
2024-07-11T13:44:59.285934Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-07-11T13:44:59.461508Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-07-11T13:44:59.499367Z 1 [Warning] [MY-011018] [InnoDB] Skip updating information_schema metadata in InnoDB read-only mode.
2024-07-11T13:44:59.499380Z 1 [Warning] [MY-010005] [Server] Skip re-populating collations and character sets tables in InnoDB read-only mode.
2024-07-11T13:44:59.500986Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, can't create lock file /var/run/mysqld/mysqlx.sock.lock'
2024-07-11T13:44:59.501127Z 2 [Warning] [MY-011018] [Server] Skip updating information_schema metadata in InnoDB read-only mode.
2024-07-11T13:44:59.501424Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2024-07-11T13:44:59.501431Z 0 [Warning] [MY-010970] [Server] Skipped updating resource group metadata in InnoDB read only mode.
2024-07-11T13:44:59.580478Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-07-11T13:44:59.580535Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-07-11T13:44:59.585822Z 0 [ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
2024-07-11T13:44:59.585832Z 0 [ERROR] [MY-010268] [Server] Unable to setup unix socket lock file.
2024-07-11T13:44:59.585839Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-07-11T13:45:00.117848Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.37-0ubuntu0.22.04.3)  (Ubuntu).

Attempting to start the DB server after commenting out the recover setting results in the same error as before.
what is the secret to mysqldump when the server wont run?

[iconnor]

2024-07-11T13:44:59.585822Z 0 [ERROR] [MY-010273] [Server] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.

This is the key line here. What is wrong with /var/run/mysqld/mysqld.sock.lock. ? Maybe you need to delete it.

[alabamatoy]

OK. I ripped it all out and started over. apt remove zoneminder --purge and do it all over again.

Code: Select all

apt install zoneminder

errors with

Code: Select all

Detected db service is mysql.service
mysql.service is a disabled or a static unit, not starting it.
NOTE: MySQL/MariaDB not running; please start mysql and run dpkg-reconfigure zoneminder when it is running.

So I stated mysql (no errors) and tried dpk-reconfigure zoneminder and got

Code: Select all

Detected db service is mysql.service
Checking for db
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
Cannot talk to database. You will have to create the db manually with something like:
....

So there is no use "debian-sys-maint", nor is there a "zmuser" so something went off the rails.

Any suggestions?

[iconnor]

apt remove --purge mysql*
look in /var/lib/mysql and /etc/mysql and remove anything found (or move out of the way).
apt install mysql-server (or mariadb)
apt install zoneminder

[alabamatoy]

apt remove --purge mysql*
look in /var/lib/mysql and /etc/mysql and remove anything found (or move out of the way).
apt install mysql-server (or mariadb)
apt install zoneminder

OK, so I ripped it all out and reinstalled IAW these instructions. I saw no errors during installation. And now, "systemctl status zoneminder" and same for mysql and apache2 show all 3 are running. But apparently the connection between ZM and apache2 was not made, as https://servername/zm returns error 404 not found. /usr/share/zoneminder/www is owned by root/root when I would expect it to be owned by wwwdata......right?

I listed the apache2 enabled mods and the cgi mod is in fact enabled. zoneminder.conf is listed in conf-available but not in conf-enabled (that stuff is quite confusing to me, dont really grok it). Is this the issue?

Almost, but not quite there, it would seem....

How do you manage to recover a MySQL database using a method other than recover < 6 when the database is in read-only mode, and what steps do you take to completely remove and re-install MySQL to restore from a backup?
retro bowl college

While ZM employs mysql (and potentially other similar DB type tools) these kinds of questions are somewhat off-azimuth for a Zoneminder forum. That said, links to such how-tos probably would be a good addition to the ZM wiki, which largely leaves one to ones own devices for such insight. See https://wiki.zoneminder.com/Backups

[iconnor]

zoneminder.conf has to be in conf-enabled. Running sudo a2enconf zoneminder creates a soft link to the file in conf-available.

[alabamatoy]

zoneminder.conf has to be in conf-enabled. Running sudo a2enconf zoneminder creates a soft link to the file in conf-available.

Bingo....ZM is accessible from port 443.....As always, Isaac, thanks so much for your patient assistance.