Error mySQL : Too many connection with check alarm pl script

Support and queries relating to all previous versions of ZoneMinder
cdbao
Posts: 35
Joined: Mon Feb 26, 2007 7:51 am

Error mySQL : Too many connection with check alarm pl script

Post by cdbao » Wed Apr 11, 2007 2:31 am

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.

User avatar
cordel
Posts: 5210
Joined: Fri Mar 05, 2004 4:47 pm
Location: /USA/Washington/Seattle
Contact:

Post by cordel » Wed Apr 11, 2007 6:28 am

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 :oops:

cdbao
Posts: 35
Joined: Mon Feb 26, 2007 7:51 am

Post by cdbao » Wed Apr 11, 2007 7:21 am

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.
Posts: 108
Joined: Tue Apr 10, 2007 5:06 pm
Location: Latvia

Post by W. » Wed Apr 11, 2007 10:56 am

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
Posts: 35
Joined: Mon Feb 26, 2007 7:51 am

Post by cdbao » Thu Apr 12, 2007 2:54 am

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.
Posts: 108
Joined: Tue Apr 10, 2007 5:06 pm
Location: Latvia

Post by W. » Thu Apr 12, 2007 5:32 am

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
Posts: 35
Joined: Mon Feb 26, 2007 7:51 am

Post by cdbao » Thu Apr 12, 2007 7:02 am

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.
Posts: 108
Joined: Tue Apr 10, 2007 5:06 pm
Location: Latvia

Post by W. » Thu Apr 12, 2007 7:17 am

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
Posts: 35
Joined: Mon Feb 26, 2007 7:51 am

Post by cdbao » Thu Apr 12, 2007 7:25 am

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.
Posts: 108
Joined: Tue Apr 10, 2007 5:06 pm
Location: Latvia

Post by W. » Thu Apr 12, 2007 7:46 am

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?
if common sense is so uncommon, why is it called common then?

cdbao
Posts: 35
Joined: Mon Feb 26, 2007 7:51 am

Post by cdbao » Thu Apr 12, 2007 7:55 am

yeah!! :D
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.
Posts: 108
Joined: Tue Apr 10, 2007 5:06 pm
Location: Latvia

Post by W. » Thu Apr 12, 2007 8:23 am

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?
if common sense is so uncommon, why is it called common then?

cdbao
Posts: 35
Joined: Mon Feb 26, 2007 7:51 am

Post by cdbao » Fri Apr 13, 2007 2:29 am

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.
Posts: 108
Joined: Tue Apr 10, 2007 5:06 pm
Location: Latvia

Post by W. » Fri Apr 13, 2007 3:57 am

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.
if common sense is so uncommon, why is it called common then?

cdbao
Posts: 35
Joined: Mon Feb 26, 2007 7:51 am

Post by cdbao » Fri Apr 13, 2007 4:16 am

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

Post Reply

Who is online

Users browsing this forum: No registered users and 24 guests