MonitorPresets SQL INSERT problem

[rakhbari]

Hi,

Before I go on let me just say that I followed the rules, did several searches on the subject here, checked the patches section of the Wiki and didn't find anything about this. So if I "missed the memo" somewhere here or on the Wiki on this, please forgive me.

It appears (at least on my Centos/MySQL installation), the INSERT statements for MonitorPresets contained in db/zm_update-1.21.4.sql and 1.22.0.sql don't work. What I get is this:

Code: Select all

ERROR 1062 (23000) at line 529: Duplicate entry '0' for key 1

Here's my installation's info:

Code: Select all

$ uname -a
Linux video01 2.6.9-34.ELsmp #1 SMP Wed Mar 8 00:27:03 CST 2006 i686 i686 i386 GNU/Linux

$ mysqladmin version
Server version          5.0.27-standard
Protocol version        10

ZoneMinder v1.22.3 built from sources (not an RPM or LiveCD install)

My command line for executing the script from the db/ subdir:

Code: Select all

$ mysql -u root -p < zm_create.sql

It looks like the problem my MySQL installation is having is with the 1st value in the MonitorPresets INSERT statement, which is supposed to be for the Auto-Increment Id field:

Code: Select all

INSERT INTO MonitorPresets VALUES ('', ...);

If I simply replace that empty single quotes '' with NULL, then everything works fine:

Code: Select all

INSERT INTO MonitorPresets VALUES (NULL, ...);

So I edited the resulting zm_create.sql file and replaced the single quotes with NULL in those INSERT statements and the entire script executes flawlessly. Oddly enough if I submit the same untouched INSERT stmt using my Windows SqlYog client (a MySQL GUI client utility) it works fine, but attempting to execute the same stmts from the Linux prompt doesn't work.

As I said before, I checked the Wiki's Patches section and saw a patch relating to MonitorPresets, but that patch is a fix for zm_html_view_monitor.php and not the table create/insert statements, so it appears unrelated to the problem I mention here. In any case, if someone could verify that this is a real problem it would be great. Or let me know if I really did miss some other patch?

Cheers,

Ramin

[cordel]

It appears (at least on my Centos/MySQL installation), the INSERT statements for MonitorPresets contained in db/zm_update-1.21.4.sql and 1.22.0.sql don't work. What I get is this:

Code: Select all

ERROR 1062 (23000) at line 529: Duplicate entry '0' for key 1

So I take that you are trying to update an installation?
The update files are meant to be invoked by zmupdate.pl and not run by hand as documented in the README under the heading Upgrading.

Here's my installation's info:

Code: Select all

$ uname -a
Linux video01 2.6.9-34.ELsmp #1 SMP Wed Mar 8 00:27:03 CST 2006 i686 i686 i386 GNU/Linux

$ mysqladmin version
Server version          5.0.27-standard
Protocol version        10

ZoneMinder v1.22.3 built from sources (not an RPM or LiveCD install)

My command line for executing the script from the db/ subdir:

Code: Select all

$ mysql -u root -p < zm_create.sql

This should only be used with a fresh install to populate the database and for nothing else.
So it's just to create a database when one does not exist.

It looks like the problem my MySQL installation is having is with the 1st value in the MonitorPresets INSERT statement, which is supposed to be for the Auto-Increment Id field:

Code: Select all

INSERT INTO MonitorPresets VALUES ('', ...);

If I simply replace that empty single quotes '' with NULL, then everything works fine:

Code: Select all

INSERT INTO MonitorPresets VALUES (NULL, ...);

Maybe you should expain what it is your trying to do or accomplish as none of this maks any since.
Why are you running the update segments?
Why are you trying to reinstall the database at all?

[rakhbari]

So I take that you are trying to update an installation?
The update files are meant to be invoked by zmupdate.pl and not run by hand as documented in the README under the heading Upgrading.

No, not updating an installation. Perhaps I should've explained a little further. I originally got that error when I first was trying to go through the standard (ZM-approved) process of configure/build/install from sources. Once I got that error, then I started to look into the internals of ZM to try and figure out what the cause could be. What I reported above is what I found out. So no, I'm not trying to do anything "strange". I was simply trying to install ZM from sources.

This should only be used with a fresh install to populate the database and for nothing else.
So it's just to create a database when one does not exist.

That's preceisely what I was trying to do: a fresh install.

I got past the problem of course by just editing the zm_create.sql file and re-running it manually as I stated earlier and my copy of ZM is operating just fine. I just wanted to report the issue, that's all. Sorry this wasn't clear before.

Thanks for your help and once again, thank you for an excellent piece of software. Now if we can build in RTSP support, it would be even better. Maybe that'll be my contrib if I can get a little time to put into it.

Cheers,

Ramin

[cordel]

The point is in a fresh install there should not be anything in the database to cause a duplicate entry. That error suggests you already have tables and entries in the database.

[rakhbari]

The point is in a fresh install there should not be anything in the database to cause a duplicate entry. That error suggests you already have tables and entries in the database.

I can guarantee you this was a fresh install. I've tried it several times testing with different non-existing DB names like "zm1", "zm2", "zm3", etc, and every single time, the resulting zm_create.sql script fails at line 529. If you take a look at the sequence of INSERT statements, you'll see how the error can happen:

Excerpt from zm_create.sql:

Code: Select all

528: INSERT INTO MonitorPresets VALUES ('','BTTV Video, PAL, 320x240','Local' ...
529: INSERT INTO MonitorPresets VALUES ('','BTTV Video, PAL, 320x240, max 5 FPS','Local'...
.
.
.

Line 528's INSERT statement succeeds. Line 529 fails however because for whatever reason MySQL's auto-increment is trying to insert the same value as the record before it. This could very well be a MySQL auto-increment bug but I just wanted to report and let you know. Like I said, I went around it by simply replacing all the single quotes '' to NULL in all those statements. After that, it works like a charm.