Ungraceful shutdown SQL needs rebuilt

Previous development branch now released as 1.36
Locked
asing
Posts: 3
Joined: Wed Jun 10, 2020 7:27 pm

Ungraceful shutdown SQL needs rebuilt

Post by 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.
User avatar
snake
Posts: 337
Joined: Sat May 21, 2016 2:20 am

Re: Ungraceful shutdown SQL needs rebuilt

Post by 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
Posts: 3
Joined: Wed Jun 10, 2020 7:27 pm

Re: Ungraceful shutdown SQL needs rebuilt

Post by asing »

Soooo, how do I rebuild the dbase from scratch?
asing
Posts: 3
Joined: Wed Jun 10, 2020 7:27 pm

Re: Ungraceful shutdown SQL needs rebuilt

Post by 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?
User avatar
burger
Posts: 390
Joined: Mon May 11, 2020 4:32 pm

Re: Ungraceful shutdown SQL needs rebuilt

Post by 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.
fastest way to test streams:
ffmpeg -i rtsp://<user>:<pass>@<ipaddress>:554/path ./output.mp4 (if terminal only)
ffplay rtsp://<user>:<pass>@<ipaddress>:554/path (gui)
find paths on ispydb or in zm hcl

If you are new to security software, read:
https://wiki.zoneminder.com/Dummies_Guide
Locked