Loading ...
Sorry, an error occurred while loading the content.

Problems with dynamic events

Expand Messages
  • lortherin
    I m having a problem with dynamic events and maybe someone can tell me what I m doing wrong, or if this is a bug. My events contain the station number, date
    Message 1 of 4 , May 28, 2012
    View Source
    • 0 Attachment
      I'm having a problem with dynamic events and maybe someone can tell me what I'm doing wrong, or if this is a bug.

      My events contain the station number, date and hour of a playlist table entry that was inserted or deleted. Each hour the main on-air program registers to see the current and next hours information. Changes to the table outside those two hours are blissfully ignored.

      The problem is that after approximately 2 weeks worth of these events, the server will crash! Changing the length of the event names gives a couple more days, but I need forever.

      Operating system : CentOS 5.8 (but tested on 5.5)
      Firebird versions: Tested on 2.0.6, 2.0.7, 2.1.4

      Server log:

      ~>tail /opt/firebird/firebird.log

      serv1-0000.sbcglobal.net (Client) Mon May 28 14:26:04 2012
      /opt/firebird/bin/fbguard: /opt/firebird/bin/fbserver terminated abnormally (-1)



      serv1-0000.sbcglobal.net (Client) Mon May 28 14:26:04 2012
      /opt/firebird/bin/fbguard: guardian starting bin/fbserver



      -- ------------------------------------------------------------
      -- ISQL scriptlet:

      create database '/tmp/event_fail.sql';

      -- A table with the bare minimum to show this problem:
      create table playlist (
      station_number integer not null,
      scheduled_date date not null,
      scheduled_time varchar(12) not null
      );

      -- Two triggers to go with it:
      set term ;!!

      create trigger pe_playlist_deleted for playlist
      active after delete position 0 as
      begin
      post_event 'PLAYLIST_DELETED_' || old.station_number || '_' ||
      old.scheduled_date || '_' || substring(old.scheduled_time from 1 for 2);
      end !!

      create trigger pe_playlist_inserted for playlist
      active after insert position 0 as
      begin
      post_event 'PLAYLIST_INSERTED_' || new.station_number || '_' ||
      new.scheduled_date || '_' || substring(new.scheduled_time from 1 for 2);
      end !!

      -- Two stored procedure with same params as our regular ones
      create procedure insert_log_entry (
      station_num integer,
      dt date,
      tm varchar(12),
      id varchar(250),
      cluster integer,
      mandatory varchar(1))
      returns
      (curr integer)
      as
      begin
      insert into playlist values (:station_num, :dt, :tm);
      curr = 0;
      end !!

      -- Unused in perl script, my pascal calls on it, though
      create procedure remove_log_entry (
      station_num integer,
      curr integer)
      returns
      (next integer)
      as
      begin
      delete from playlist where station_number = :station_num;
      next = 0;
      end !!

      set term ;!!

      commit;


      And a perl script which will show the error. I know this is probably horrible perl-fu.

      #!/usr/bin/perl -w
      #
      # Note: This needs DBI::InterBase. It might work with DBI::Firebird
      # but I haven't tried it.
      #
      # You probably want to add an alias for te
      #
      # Usage: $0 dbserver:database

      use strict;
      use DBI;
      use POSIX qw(strftime mktime);

      my $dsn = shift; # $ARGV[0] - Specified as dbhost:dbname

      my $DB_USER = 'sysdba';
      my $DB_PASS = 'masterkey';

      my ($dbhost, $dbname) = split ':', $dsn;

      my $db = "dbi:InterBase:db=$dbname;host=$dbhost;ib_dialect=3";

      my $dbi = DBI->connect($db, $DB_USER, $DB_PASS, { PrintError => 0,
      RaiseError => 0,
      AutoCommit => 1 })
      or die "FATAL ERROR: DBI->connect to $dsn failed";

      # Counter for events.
      my $want_events = 0;

      # -----------------------------------------------------
      # Event callback function
      # -----------------------------------------------------
      sub handle_event {
      my $pe = shift;

      while (my ($k, $v) = each %$pe) {
      print "Got event $k\n";
      }
      $want_events--;
      1;
      }

      my $station = 1;
      my $curr_date = mktime(0, 0, 0, 1, 0, 110); # 01 Jan 2010 @00:00
      my $end_date = mktime(0, 0, 0, 1, 0, 111); # 01 Jan 2011 @00:00

      my $sql;

      my $ev1 = "";
      my $ev2 = "";

      while ($curr_date < $end_date) {

      my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday) = localtime($curr_date);

      my $dd = strftime ('%F', localtime($curr_date));
      my $hh = sprintf ('%.2d', $hour);
      my $tt = "${hh}:000:00.00";

      # Only using one here. Kylix test program (and real application)
      # use both of these, so this script registers for both.
      $ev1 = "PLAYLIST_INSERTED_${station}_${dd}_${hh}";
      $ev2 = "PLAYLIST_DELETED_${station}_${dd}_${hh}";

      my $evh = $dbi->func($ev1, $ev2, 'ib_init_event');

      # Set the callback
      $dbi->func($evh, \&handle_event, 'ib_register_callback');

      $want_events = 1;
      $sql = "execute procedure insert_log_entry (?, ?, ?, ?, ?, ?)";
      my $sth = $dbi->prepare($sql);

      my $res = $sth->execute($station, $dd, $tt, 'Event Test Entry', 0, 'N');

      print "Inserted $dd $hh\n";

      $sth->finish;

      while ($want_events > 0) {
      sleep 1;
      }

      $dbi->func($evh, 'ib_cancel_callback');

      # Bump to next hour and repeat
      $curr_date += 3600;

      }

      $dbi->disconnect;


      When I run the above perl, I get this as output:


      ~ $perl event_test.pl "serv1-0000:fail.fdb"
      Inserted 2010-01-01 00
      Got event PLAYLIST_INSERTED_1_2010-01-01_00
      Inserted 2010-01-01 01
      Got event PLAYLIST_INSERTED_1_2010-01-01_01
      ............much elided.....
      Inserted 2010-01-13 17
      Got event PLAYLIST_INSERTED_1_2010-01-13_17
      Inserted 2010-01-13 18
      Got event PLAYLIST_INSERTED_1_2010-01-13_18
      DBD::InterBase::db::ib_register_callback() -- ev is not a blessed SV reference at event_test.pl line 91.
      Can't call method "execute" on an undefined value at event_test.pl line 97.
      Broken pipe

      and over on the server is another instance of terminating abnormally.

      Is there something wrong with the triggers above?

      Things I've tried:

      a). Making the event names shorter as in PI_{date}_{hour}

      b). Table modification to store hour as an integer value and
      having the trigger use that instead of calling on substring.

      Both of those changes were for naught.

      Best regards,
      Pete C.
    • Pete Cervasio
      ... Following up: This also happens on the 2.1.5 release candidate. This server crash doesn t appear to happen with Firebird 2.5.1, if anyone was interested.
      Message 2 of 4 , May 30, 2012
      View Source
      • 0 Attachment
        On Monday 28 May 2012 03:07:49 pm lortherin wrote:
        > I'm having a problem with dynamic events and maybe someone can tell me what
        > I'm doing wrong, or if this is a bug.

        Following up: This also happens on the 2.1.5 release candidate.

        This server crash doesn't appear to happen with Firebird 2.5.1, if anyone was
        interested. That's not much help for CentOS 5 (and older) systems, though.
        I'm still interested in knowing if my way of using dynamic events should
        work, or if it's a bug I've uncovered, or what?

        Best regards,
        Pete C.

        --
        =====================================================================
        On Air Digital USA http://www.onairusa.com
        Dallas, Texas a division of Smarts Broadcast Systems

        If we can hit that bull's-eye, the rest of the dominoes will fall
        like a house of cards... Checkmate!
        =====================================================================
      • Mark Rotteveel
        ... From your description it sounds like a bug. The best advice I can give you is to create a ticket at http://tracker.firebirdsql.org/ -- Mark Rotteveel
        Message 3 of 4 , May 30, 2012
        View Source
        • 0 Attachment
          On 30-5-2012 18:38, Pete Cervasio wrote:
          > On Monday 28 May 2012 03:07:49 pm lortherin wrote:
          >> I'm having a problem with dynamic events and maybe someone can tell me what
          >> I'm doing wrong, or if this is a bug.
          >
          > Following up: This also happens on the 2.1.5 release candidate.
          >
          > This server crash doesn't appear to happen with Firebird 2.5.1, if anyone was
          > interested. That's not much help for CentOS 5 (and older) systems, though.
          > I'm still interested in knowing if my way of using dynamic events should
          > work, or if it's a bug I've uncovered, or what?

          From your description it sounds like a bug. The best advice I can give
          you is to create a ticket at http://tracker.firebirdsql.org/

          --
          Mark Rotteveel
        • Michael Ludwig
          ... At this point, it should be: set term !! ; ... Looks good to me. But I couldn t get it to work on Windows and using DBD::Firebird. (DBD::InterBase didn t
          Message 4 of 4 , May 30, 2012
          View Source
          • 0 Attachment
            lortherin schrieb am 28.05.2012 um 20:07 (-0000):

            > -- ISQL scriptlet:

            > -- Two triggers to go with it:
            > set term ;!!

            At this point, it should be:

            set term !! ;

            > And a perl script which will show the error. I know this is probably
            > horrible perl-fu.

            Looks good to me. But I couldn't get it to work on Windows and using
            DBD::Firebird. (DBD::InterBase didn't even compile, and it seems to
            have been superseded by DBD::Firebird.) I got all sorts of strange
            error, not reliably reproducible. Looked like concurrency issues to
            me, but then I don't know much about them.

            The synchronous method (ib_wait_event instead of ib_register_callback
            and ib_cancel_callback) worked fine, though:


            use strict;
            use warnings;
            use DBI;
            use POSIX qw(strftime mktime);
            use Time::HiRes 'time';

            my $db = shift or die 'database!';
            my $user = shift or die 'username!';
            my $pass = shift or die 'password!';

            my $dbi = DBI->connect('dbi:Firebird:' . $db, $user, $pass,
            { PrintError => 0, RaiseError => 1, AutoCommit => 1 });

            my $station = 1;
            my $curr_date = mktime(0, 0, 0, 1, 0, 110); # 01 Jan 2010 @00:00
            my $end_date = mktime(0, 0, 0, 1, 0, 111); # 01 Jan 2011 @00:00

            my $insert_count = 0;
            my $t0 = time;

            my $sql = "execute procedure insert_log_entry (?, ?, ?, ?, ?, ?)";
            my $sth = $dbi->prepare($sql);

            while ($curr_date < $end_date) {
            my @lt_curr_date = localtime $curr_date;
            my $dd = strftime '%Y-%m-%d', @lt_curr_date;
            my $hh = sprintf '%.2d', $lt_curr_date[2]; # hour
            my $tt = "${hh}:000:00.00";

            my $ev1 = "PLAYLIST_INSERTED_${station}_${dd}_${hh}";
            my $ev2 = "PLAYLIST_DELETED_${station}_${dd}_${hh}";
            my $evh = $dbi->func($ev1, $ev2, 'ib_init_event');

            my $res = $sth->execute($station, $dd, $tt, 'Event Test Entry', 0, 'N');
            $sth->finish; # call needed to trigger event!
            $insert_count++;
            printf "%5u - Inserted $dd $hh\n", $insert_count;

            my $events = $dbi->func($evh, 'ib_wait_event');
            if ( $events ) {
            while ( my($evnam, $evcount) = each %$events ) {
            printf "%50s %2u\n", $evnam, $evcount;
            }
            }

            $curr_date += 3600; # Bump to next hour and repeat
            }

            $dbi->disconnect;
            printf "inserted $insert_count records in %.2f seconds\n", time - $t0;
            __END__


            Note that I didn't get any Firebird server errors as I tested against
            the latest 2.5.1 (Win32 build on Win7/64).
            --
            Michael Ludwig
          Your message has been successfully submitted and would be delivered to recipients shortly.