Error mySQL : Too many connection with check alarm pl script

[cdbao]

I want to check status "Alarm" by perl scripts :

#!/usr/bin/perl

use strict;
use DBI;
use File::Copy;

my $dbh = DBI->connect( "DBI:mysql:database=zminder;host=localhost", "user", "pass" );

my $sth = $dbh->prepare("select MonitorId from Events");
my $numrows = $sth->execute;
$sth->finish;
my $currows;

while (1) {
$sth = $dbh->prepare("UPDATE `Monitors` SET `Function` = 'Modect' WHERE `Monitors`.`Id` =1 LIMIT 1;");
$currows = $sth->execute;
if ($currows > $numrows) {
MY ACTION ();
}
$sth->finish;
$numrows= $currows;
sleep(1);
}

but when excute this scripts for few minutes and alarm continuously mySQL database have ERROR : to many connection.
How can i check Alarm events without connect database in loop..
Anybody can help me this task!!
Thanks and Regard.

[cordel]

First let me say that I'm not very good with perl, that being said;
It looks to me that your while loop keeps opening new connections and never closes till it exits. Should it not close each connection at the end of the loop instead of after it exits?

Like so?

Code: Select all

while (1) {
$sth = $dbh->prepare("UPDATE `Monitors` SET `Function` = 'Modect' WHERE `Monitors`.`Id` =1 LIMIT 1;");
$currows = $sth->execute;
if ($currows > $numrows) {
          MY ACTION ();
          $sth->finish; 
          } 

[Edit]
Never mind, I missed the braced if statement and you do have it closing at the end of the loop. disregard you can see it better when properly formated

[cdbao]

so !! could we can detect alarm by another systax query?
I try reseach with zmtrigger !! but not clear how did it can excute a action when have alarm!!
Thanks for support!!
Regard.

[W.]

State of monitor is not written to the DB, thus you can not check it by querying DB. It is written in shared memory belonging to monitor though and there is even special function in ZoneMinder::SharedMem library that is called zmIsAlarmed and takes monitor Id as an argument. Look at zmtrack.pl script for an example.

I am no expert in perl either and no expert in coding as such, so do not take anything I write here for granted

[cdbao]

yes !! i see!! but when i use this code :

#!/usr/bin/perl -w

use strict;

use ZoneMinder;

$| = 1;

zmDbgInit( "myscript", level=>0, to_log=>0, to_syslog=>0, to_term=>1 );

my $dbh = DBI->connect( "DBI:mysql:database=".ZM_DB_NAME.";host=".ZM_DB_HOST, ZM_DB_USER, ZM_DB_PASS );

my $sql = "select M.*, max(E.Id) as LastEventId from Monitors as M left join Events as E on M.Id = E.MonitorId where M.Function != 'None' group by (E.MonitorId)";
my $sth = $dbh->prepare_cached( $sql ) or die( "Can't prepare '$sql': ".$dbh->errstr() );

my $res = $sth->execute() or die( "Can't execute '$sql': ".$sth->errstr() );
my @monitors;
while ( my $monitor = $sth->fetchrow_hashref() )
{
push( @monitors, $monitor );
}

while( 1 )
{
foreach my $monitor ( @monitors )
{
next if ( !zmShmVerify( $monitor ) );

if ( my $last_event_id = zmHasAlarmed( $monitor, $monitor->{LastEventId} ) )
{
$monitor->{LastEventId} = $last_event_id;
print( "Monitor ".$monitor->{Name}." has alarmed\n" );
#
# Do your stuff here
#
}
}
sleep( 1 );
}

save as : /usr/alarm.pl
when i run this script : perl /usr/alarm.pl
it said :

Use of uninitialized value in numeric ne (!=) at /usr/lib/perl5/site_perl/5.8.8/ZoneMinder/SharedMem.pm line 503.

how can i fix this error!!
Thanks any way!!
Regard.

[W.]

Code: Select all

if ( !zmShmVerify( $monitor ) );

semicolon at the end of this line just discards your check if Shm exists for your monitor and continues to run your script like if there was shared memory for given monitor. But in your case there is no shared memory for monitor in question available, because you are querying monitors that have function 'None', hence are inactive.

[cdbao]

many many thanks for support me!!
and try to help me that : all camera in Modect function.
error still repeat :

Use of uninitialized value in numeric ne (!=) at /usr/lib/perl5/site_perl/5.8.8/ZoneMinder/SharedMem.pm line 503.

again many thank guy!!
regard.

[W.]

because you are querying monitors that have function 'None'

I was wrong about this one

Can you post what your sql query is returning? and what version of ZM is that.

[cdbao]

yup!!
I'm not clear in SQL query because in my perl script!! don't have SQL query!!

my $sql = "select M.*, max(E.Id) as LastEventId from Monitors as M left join Events as E on M.Id = E.MonitorId where M.Function != 'None' group by (E.MonitorId)";

when i run it show :

Use of uninitialized value in numeric ne (!=) at /usr/lib/perl5/site_perl/5.8.8/ZoneMinder/SharedMem.pm line 503.
Monitor Cam1 has alarmed

my ZM version : 1.22.3
I think some problem in this code.[/quote]

[W.]

I'm not clear in SQL query because in my perl script!! don't have SQL query!!

yes, you do

select M.*, max(E.Id) as LastEventId from Monitors as M left join Events as E on M.Id = E.MonitorId where M.Function != 'None' group by (E.MonitorId)

this is sql query. what does it return? what is resultset when you execute this query?

One interesting thing about all this is - there is no != comparison on line 503 in SharedMem.pm. Have you ever had 1.22.2 or older ZM installed on that box? might it be that you have old versions of libraries installed?

[cdbao]

yeah!!
but I'm make and install from ZM 1.22.3 source. not update from 1.22.2
may i update SharedMem.pm !!
and how can i do??
thanks you very much

[W.]

heh, ok, I am learning Perl on the way as well

error refers to this code block in SharedMem.pm (which has not changed since 1.22.2)

Code: Select all

if ( $state == STATE_ALARM || $state == STATE_ALERT )
	{
		return( $last_event );
	}
	elsif( $last_event != $last_event_id )
	{
		return( $last_event );
	}

and the culprit is elsif( $last_event != $last_event_id ) which means that either $last_event is undefined or $last_event_id is. $last_event_id is the one that you pass as a parameter and $last_event is fetched from shared memory. Now what is the value of $monitor->{LastEventId} that you pass to the zmHasAlarmed function?

[cdbao]

hihi dear W.
I already check this code , but I can't clear what's the value of $monitor->{LastEventId}
So you can show me !! that can I do!! and I have a question!!
when the "while function" looping in this code , maybe consume resource of system??
could we have another solution check this task??

i also recev alarm ouput but error still display!! :

Use of uninitialized value in numeric ne (!=) at /usr/lib/perl5/site_perl/5.8.8/ZoneMinder/SharedMem.pm line 503.
Monitor Cam1 has alarmed
Use of uninitialized value in numeric ne (!=) at /usr/lib/perl5/site_perl/5.8.8/ZoneMinder/SharedMem.pm line 503.

thanks u so much !!
Regard

[W.]

try this. i dont have any ZM box at hand right now, so it is untested

Code: Select all

#!/usr/bin/perl -w

use strict;

use ZoneMinder;

$| = 1;

zmDbgInit( "myscript", level=>0, to_log=>0, to_syslog=>0, to_term=>1 );

my $dbh = DBI->connect( "DBI:mysql:database=".ZM_DB_NAME.";host=".ZM_DB_HOST, ZM_DB_USER, ZM_DB_PASS );

my $sql = "select M.*, max(E.Id) as LastEventId from Monitors as M left join Events as E on M.Id = E.MonitorId where M.Function != 'None' group by (E.MonitorId)";
my $sth = $dbh->prepare_cached( $sql ) or die( "Can't prepare '$sql': ".$dbh->errstr() );

my $res = $sth->execute() or die( "Can't execute '$sql': ".$sth->errstr() );
my @monitors;
while ( my $monitor = $sth->fetchrow_hashref() )
{
push( @monitors, $monitor );
}

while( 1 )
{
	foreach my $monitor ( @monitors )
	{	
	next if ( !zmShmVerify( $monitor ) );

		if ( defined( zmShmRead( $monitor, "shared_data:last_event" )) ) {
			if ( !defined( $monitor->{LastEventId} ) || ( my $last_event_id = zmHasAlarmed( $monitor, $monitor->{LastEventId} ) ) )
			{
				$monitor->{LastEventId} = $last_event_id;
				print( "Monitor ".$monitor->{Name}." has alarmed\n" );
				#
				# Do your stuff here
				#
			}
		}
	}
		
	sleep( 1 );
}

scripts that come with ZM are good source for inspiration (thanks Philip ), look at them.

[cdbao]

oh!!! that's great!! it's runing!! thanks so much .W and everybody..
hug..hug