Database upgrade fails

Forum for questions and support relating to the 1.32.x releases only.
doubleosix
Posts: 20
Joined: Fri Jul 13, 2018 1:45 am

Database upgrade fails

Post by doubleosix »

Hi,

I updated my CentOS7 zoneminder installation to 1.32.0 from 1.30.4. After the update I ran the zmupdate.pl script but it hangs on upgrading the database to 1.31.11. I have tried running zmupdate -u root -p (password) and i edited my my.cnf file and added net_read_timeout = 6000 and net_write_timeout = 6000. Neither of these had any effect.

Terminal output follows:

Database successfully upgraded to version 1.31.10.
Upgrading DB to 1.31.11 from 1.30.4
ERROR 2013 (HY000) at line 31: Lost connection to MySQL server during query
Output: Column UpdateDiskSpace already exists in Filters
Column UpdateDiskSpace already exists in Filters
Command 'mysql -hlocalhost -uzmuser -p"zmpass" zm < /usr/share/zoneminder/db/zm_update-1.31.11.sql' exited with status: 1
User avatar
iconnor
Posts: 2880
Joined: Fri Oct 29, 2010 1:43 am
Location: Toronto
Contact:

Re: Database upgrade fails

Post by iconnor »

Well you might want to check your mysql logs to see if they say anything useful.

The failing upgrade is one that adds an index to the Logs table. Perhaps your Logs table is corrupt or just too large.
doubleosix
Posts: 20
Joined: Fri Jul 13, 2018 1:45 am

Re: Database upgrade fails

Post by doubleosix »

Where can I look for the log?

What exactly is in the database? Is it just the motion detection events? or does it have all my configuration in it too? is there a way I can just delete the database and create a new one?
User avatar
iconnor
Posts: 2880
Joined: Fri Oct 29, 2010 1:43 am
Location: Toronto
Contact:

Re: Database upgrade fails

Post by iconnor »

How should I know what's in your database?

The default config stores log entries in the log table. If something is going wrong, it could be a very large table.

mysql log entries are usually something like /var/log/mysql/error.log
doubleosix
Posts: 20
Joined: Fri Jul 13, 2018 1:45 am

Re: Database upgrade fails

Post by doubleosix »

Ok let me rephrase that. What is usually in the database? What is it used for?

If the database is only motion detection events I would be perfectly happy to delete the database and recreate it if someone can tell me how.
doubleosix
Posts: 20
Joined: Fri Jul 13, 2018 1:45 am

Re: Database upgrade fails

Post by doubleosix »

Where is the "log table" located?

I checked the /var/log/mariadb/mariadb.log and it is blank.
doubleosix
Posts: 20
Joined: Fri Jul 13, 2018 1:45 am

Re: Database upgrade fails

Post by doubleosix »

Ok, i had to create a mysql error log file. Is there anyway to recover a corrupt database or a way to create a new one?

The contents of the mysql error log follow:

180926 16:27:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180926 16:27:47 [Warning] option 'max_connections': unsigned value 200000 adjusted to 100000
180926 16:27:47 [Warning] option 'max_allowed_packet': unsigned value 1600000000000000 adjusted to 1073741824
180926 16:27:47 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 4583 ...
180926 16:27:47 [Warning] Changed limits: max_open_files: 2000 max_connections: 1190 table_cache: 400
180926 16:27:47 InnoDB: The InnoDB memory heap is disabled
180926 16:27:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180926 16:27:47 InnoDB: Compressed tables use zlib 1.2.7
180926 16:27:47 InnoDB: Using Linux native AIO
180926 16:27:47 InnoDB: Initializing buffer pool, size = 128.0M
180926 16:27:47 InnoDB: Completed initialization of buffer pool
180926 16:27:47 InnoDB: highest supported file format is Barracuda.
180926 16:27:47 InnoDB: Waiting for the background threads to start
180926 16:27:48 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 2477885231316
180926 16:27:48 [Note] Plugin 'FEEDBACK' is disabled.
180926 16:27:48 [Note] Server socket created on IP: '0.0.0.0'.
180926 16:27:48 [Note] Event Scheduler: Loaded 0 events
180926 16:27:48 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1230333.
InnoDB: You may have to recover from a backup.
180926 16:29:14 InnoDB: Page dump in ascii and hex (16384 bytes):
doubleosix
Posts: 20
Joined: Fri Jul 13, 2018 1:45 am

Re: Database upgrade fails

Post by doubleosix »

Can anyone tell me if I can backup my monitor configuration?

I would like to just backup the monitor configuration, delete and reinstall zoneminder and then just import the monitor config. Can anyone tell me if this possible?
User avatar
iconnor
Posts: 2880
Joined: Fri Oct 29, 2010 1:43 am
Location: Toronto
Contact:

Re: Database upgrade fails

Post by iconnor »

You can backup your Monitor config with something like the following:
mysqldump -u zmuser -p zm -h database -t Monitors > Monitors.sql

You will also want Zones.
mysqldump -u zmuser -p zm -h database -t Zones > Zones.sql

If you want to try dropping and re-creating the Logs table you might try

mysql -u zmuser -p zm <<EOF
DROP TABLE Logs;

CREATE TABLE `Logs` (
`TimeKey` decimal(16,6) NOT NULL,
`Component` varchar(32) NOT NULL,
`ServerId` int(10) unsigned,
`Pid` int(10) DEFAULT NULL,
`Level` tinyint(3) NOT NULL,
`Code` char(3) NOT NULL,
`Message` text NOT NULL,
`File` varchar(255) DEFAULT NULL,
`Line` smallint(5) unsigned DEFAULT NULL,
KEY `TimeKey` (`TimeKey`)
) ENGINE=InnoDB;

CREATE INDEX `Logs_TimeKey_idx` ON `Logs` (`TimeKey`);
CREATE INDEX `Logs_Level_idx` ON `Logs` (`Level`);
EOF
doubleosix
Posts: 20
Joined: Fri Jul 13, 2018 1:45 am

Re: Database upgrade fails

Post by doubleosix »

Well I did eventually get the database and zoneminder working. I used mysqldump to dump the whole zm database, recreated the database and ran zmupdate.pl. Everytime zmupdate.pl gave me an error I read the script zmupdudate was running to see which table I needed to recreate. dropped the affected table, and then I copied and pasted sections out of the zmcreate.sql script to create new .sql scripts to recreate the missing tables.

Version 1.32.0 sucks. Motion detection does not work. I keep getting an error message about not having the permissions to do anything and the pages all refresh every 10 seconds so nothing ever loads when I'm connected to my office over a VPN.

I used yum to downgrade to version 1.30.4, I am in the process of recreating my database again. I'll let you know how it turns out.
Last edited by doubleosix on Fri Sep 28, 2018 3:02 pm, edited 1 time in total.
User avatar
iconnor
Posts: 2880
Joined: Fri Oct 29, 2010 1:43 am
Location: Toronto
Contact:

Re: Database upgrade fails

Post by iconnor »

1.32.0 may have issues, but I assure you, the issues you are seeing are due to your system being badly messed up.

I would recommend that you try to have a better attitude and check in the logs for all the errors that you surely have.
doubleosix
Posts: 20
Joined: Fri Jul 13, 2018 1:45 am

Re: Database upgrade fails

Post by doubleosix »

If the problem with constantly being redirected to "permission denied" didn't seem to be a common and so far unresolved issue, I might take the time to troubleshoot the other problems. I did check my php.ini, my time zone is set correctly and matches the system timezone.

Before I downgraded I did check the logs, I was getting errors about some of the rtsp streams failing to open, but that was caused by the cameras, there were also a bunch of messages about events not being found. I did not see anything that would tell me why motion detection was not working. There was an error that said something like "got memory map file size = 0, is zmc running for this monitor?" I have no idea what that ment.

I think i'll wait for v1.32.1 to come out, then I think I'll create a new VM import my monitors table into it and see how it goes.

Just for future reference, is using InnoDB a necessity for zoneminder? If I edited zm_create.sql and the zonemider database update scripts, and replaced InnoDB with something else would ZoneMinder still work? There is probably a good reason that InnoDB was chosen as the default, but it would be nice to be able to use automated database repair tools like mysqlcheck.
User avatar
knight-of-ni
Posts: 2404
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: Database upgrade fails

Post by knight-of-ni »

If the problem with constantly being redirected to "permission denied" didn't seem to be a common and so far unresolved issue, I might take the time to troubleshoot the other problems. I did check my php.ini, my time zone is set correctly and matches the system timezone.
You are assuming this problem is being experienced by many of our user base. I can assure you it is not, nor can we duplicate the problem.

Anyone can make any kind of statement they want in this forum, followed by "me too's" from others. That in no way means the problem is common.

If you think you are just going to sit back and let someone else figure out and solve the problem you are experiencing, then you are going to be very disappointed. As of this writing, this issue will not be addressed in 1.32.1 because we have not seen anything to indicate there is some change in the source which could fix this. If you want that to change then you need to get involved.
Just for future reference, is using InnoDB a necessity for zoneminder? If I edited zm_create.sql and the zonemider database update scripts, and replaced InnoDB with something else would ZoneMinder still work? There is probably a good reason that InnoDB was chosen as the default, but it would be nice to be able to use automated database repair tools like mysqlcheck.
Huh? Innodb is the default dB engine for mysql, and I've been running mysqlcheck, every week, for years on it. Mysqlcheck does a recreate & analyze, rather than an optimize.
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/
rockedge
Posts: 1173
Joined: Fri Apr 04, 2014 1:46 pm
Location: Connecticut,USA

Re: Database upgrade fails

Post by rockedge »

You are assuming this problem is being experienced by many of our user base. I can assure you it is not, nor can we duplicate the problem.
I am experiencing it right now. And I am writing about it here : viewtopic.php?f=38&t=27524

streaming a monitor lasts about 10 seconds then the page refreshes and the url is

Code: Select all

http://localhost/zm/index.php?view=watch
when it was and should be something like

Code: Select all

http://localhost/zm/index.php?view=watch&mid=1
just try to use the url above using your server's path and check it out. I have reset and double checked the system and server date, time and timezone

with OPT_USE_AUTH not selected the monitor stream page just constantly refreshes every 2 seconds and also eventually lands on the permission denied page
User avatar
knight-of-ni
Posts: 2404
Joined: Thu Oct 18, 2007 1:55 pm
Location: Shiloh, IL

Re: Database upgrade fails

Post by knight-of-ni »

@rockedge Yeah, I am familiar with that thread. I've tried exactly the steps you've described, and I can't duplicate the problem. Neither can Isaac.

Notice that Isaac asked a question in that thread, which has gone unanswered.
This is exactly what I am talking about. If we ask questions, but don't get an answer, it becomes extremely difficult to identify whether we can even fix it on our end (i.e. looks like a local machine problem).

If you, the reader, are experiencing this issue, please do not sit back and assume someone else is going to respond to the questions.
Visit my blog for ZoneMinder related projects using the Raspberry Pi, Orange Pi, Odroid, and the ESP8266
All of these can be found at https://zoneminder.blogspot.com/
Post Reply