Ungraceful shutdown SQL needs rebuilt

[asing]

Hello all, I had just got 1.35 up and going and my cameras added. I had not done a backup of the zm sql database yet as this was new.
I had an unfortunate issue with a power cable(lost power).

I am definitely not an SQL admin, but can get around.

So now when I try and navigate to ZM_IP/zm I have the following error:
Unable to connect to ZM db.SQLSTATE[HY000] [2002] No such file or directory

CLI:
mysql status
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

service zoneminder status
● zoneminder.service - ZoneMinder CCTV recording and surveillance system
Loaded: loaded (/lib/systemd/system/zoneminder.service; enabled; vendor preset: enabled)
Active: activating (auto-restart) (Result: exit-code) since Wed 2020-06-10 19:33:13 UTC; 5s ago
Process: 3342 ExecStart=/usr/bin/zmpkg.pl start (code=exited, status=255/EXCEPTION)

sudo myisamchk --silent --force --fast --update-state /var/lib/mysql/*/*.MYI
myisamchk: error: File '/var/lib/mysql/*/*.MYI' doesn't exist
myisamchk: error: File '/var/lib/mysql/*/*.MYI' doesn't exist

mysql logs:
2020-06-10T19:46:38.792547Z 1 [Warning] [MY-012928] [InnoDB] 4 threads created by InnoDB had not exited at shutdown!
2020-06-10T19:46:39.229584Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20-0ubuntu0.20.04.1) starting as process 3918
2020-06-10T19:46:39.304769Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-10T19:46:42.298633Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-10T19:46:45.697801Z 1 [Warning] [MY-012638] [InnoDB] Retry attempts for reading partial data failed.
2020-06-10T19:46:45.697985Z 1 [ERROR] [MY-012642] [InnoDB] Tried to read 16384 bytes at offset 212992, but was only able to read 0
2020-06-10T19:46:45.698080Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 5 in a file operation.
2020-06-10T19:46:45.698175Z 1 [ERROR] [MY-012596] [InnoDB] Error number 5 means 'Input/output error'
2020-06-10T19:46:45.698363Z 1 [ERROR] [MY-012646] [InnoDB] File ./mysql.ibd: 'read' returned OS error 105. Cannot continue operation
2020-06-10T19:46:45.698462Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.

[snake]

I recently had a 1.32 (older, yes) db get corrupted after a power loss. I resolved it by restoring the db from scratch.

I have had the power drop on 1.30.4 multiple times without one db corruption.

I'd say some attention should be paid to any changes that might have made the DB less resilient to power failures since 1.30.4. I'd be curious to know what code changes are responsible.

[asing]

Soooo, how do I rebuild the dbase from scratch?

[asing]

Also, I have tried using myisamchk -r -o /var/lib/mysql/* and I get an error for every file:

"myisamchk: error; 'filename' is not a MYISAM-table

I tried the same thing in the /var/lib/mysql/zm directory and same error....soooo where are the tables?

[burger]

https://wiki.zoneminder.com/MySQL

All that was necessary was to DROP database zm, and then possibly rm zm folder in /var/lib/mysql (debian). You might find, as I did, that once the zm data and db is removed, that mysql will start successfully.

After that, you can restore the DB from a backup with

Code: Select all

 mysql -u root -p zm < dbbackup.sql

If you don't have a backup, see the wiki article, I added a section for recreating DB.