Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Monitoring mysql tables in perl

by kamal (Sexton)
on Jan 10, 2013 at 14:34 UTC ( #1012670=perlquestion: print w/ replies, xml ) Need Help??
kamal has asked for the wisdom of the Perl Monks concerning the following question:

So here is what i want to do: Monitor a bunch of tables in a MySQL database, append time stamps to WHEN the values changed, and then render the result in a graph.

1. Is there a tool/utility that somewhat does this already ( I could not find it ) 2. i used DBI, and was able to connect to the database and get some table column values:
#!/usr/bin/env perl use strict; use warnings; use Net::SSH::Perl; use DBI; my $database = 'co_sysdev'; my $server = 'devqa_dbm.corp'; my $user = 'username'; my $passwd = 'password'; my $row = ""; my $homologs = DBI->connect("dbi:mysql:database=co_sysdev;host=devqa_d +bm.corp;port=3310",'username','password'); # prepare an SQL statement #my $query_params = "select * from system_params"; # render the result in a graph format # save it locally in a csv format, to be used later for report my $query_coding_event = "select * from coding_event"; my $query_params = "select * from co_event"; #my $query = "select * from n_batch where status = 'started'"; #my $query = "select * from n_batch"; #my $query = "select id,customer_id,location_id,submitter,n_notes,hand +le from submission_batch where id is not null"; #my $query = "select id,customer_id,location_id,submitter,n_notes,hand +le from submission_batch where id is not null"; #my $query = "describe submission_batch"; #my $query = "show tables"; my $sql_params = $homologs->prepare($query_params); # execute an SQL statement $sql_params->execute(); # retrieve and print results while (my $row = $sql_params->fetchrow_arrayref) { #print join("\t", @$row), "\n"; print join(@$row), "\n"; print @$row . " \n"; } # Break connection with MySQL database $homologs->disconnect; exit;
This seems to be such a common use case, that i am surprised there arnt many scripts/packages available for the same.

Comment on Monitoring mysql tables in perl
Download Code
Re: Monitoring mysql tables in perl
by sundialsvc4 (Abbot) on Jan 10, 2013 at 14:54 UTC
Re: Monitoring mysql tables in perl
by blue_cowdawg (Monsignor) on Jan 10, 2013 at 14:56 UTC
        So here is what i want to do: Monitor a bunch of tables in a MySQL database, append time stamps to WHEN the values changed, and then render the result in a graph.

    Dear Monk,
    I'm a bit confused as to what you are trying to do. Let me step through your code though and offer some commentary.

    my $database = 'co_sysdev'; my $server = 'devqa_dbm.hq.co.corp'; my $user = 'web'; my $passwd = '3br!'; my $row = "";
    Hope those aren't really production passwords. Never post such to this site as Google does a fine job of spidering this site from time to time making this sort of thing easy to find.
    my $homologs = DBI->connect("dbi:mysql:database=co_sysdev;host=devqa_d +bm.corp;port=3310",'username','password');
    So.. what were those other lines for?
    #my $query_params = "select * from system_params"; # render the result in a graph format # save it locally in a csv format, to be used later for report my $query_coding_event = "select * from coding_event"; my $query_params = "select * from co_event"; #my $query = "select * from n_batch where status = 'started'"; #my $query = "select * from n_batch"; #my $query = "select id,customer_id,location_id,submitter,n_notes,hand +le from submission_batch where id is not null"; #my $query = "select id,customer_id,location_id,submitter,n_notes,hand +le from submission_batch where id is not null"; #my $query = "describe submission_batch"; #my $query = "show tables"; my $sql_params = $homologs->prepare($query_params);
    when posting code to the PM remember to remove unnecessary lines for readability. This is what it should have looked like IMHO:
    my $query_coding_event = "select * from coding_event"; my $query_params = "select * from co_event"; my $sql_params = $homologs->prepare($query_params);
    so we can see what you are trying to run.
    while (my $row = $sql_params->fetchrow_arrayref) { #print join("\t", @$row), "\n"; print join(@$row), "\n"; print @$row . " \n"; }
    Let's start with the line print join(@row),"\n";. Is this doing what you expect? What is going to happen is join is going to take the first element in your array and use it for a separator for the rest of the array. Here's an example of what I am saying:
    $ perl -e 'print join(qw/a b c d e f/),"\n"' bacadaeaf
    Not sure that's what you were after. Then you do print $@row . "\n";, why? Looks to me like you are repeating yourself.

        i used DBI, and was able to connect to the database and get some table column values:
    So.. what are you trying to do with those values? What have you tried? What was the result?


    Peter L. Berghold -- Unix Professional
    Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
      no those are made up usernames and passwords, and this is not for production. as far as what i am trying to do is to get reuslts like: staus| time s 10:55 k 12:20 e 13:10 so the table contains char's which change as a result of some other process, i just want to be able to store these values in a csv format, so i can render it in a graph. Hope this makes a bit more sense
            i just want to be able to store these values in a csv format, so i can render it in a graph.

        Rendering an array of data into a CSV row is fairly simple. Here's one example:

        #!/usr/bin/perl use strict; my $data = [ [ qw/ a b c d /], [ qw/ e f g h /], [ qw/ i j k l /] ]; my $outfile = "/path/to/my/file.csv"; open FOUT,"> $outfile" or die "$outfile: $!"; foreach my $row (@$data){ printf FOUT "%s\n',join(",",@$row); } close FOUT;
        There are many many other ways of doing this but there is one way.


        Peter L. Berghold -- Unix Professional
        Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
Re: Monitoring mysql tables in perl
by Neighbour (Friar) on Jan 10, 2013 at 15:33 UTC
    Or you could add a field to the table you're monitoring which does the whole thing for you (column definition follows):
    `last_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAM +P
Re: Monitoring mysql tables in perl
by karlgoethebier (Curate) on Jan 10, 2013 at 19:40 UTC

    Please see also:

    Best regards, Karl

    P.S: Don't bother yourself with Nagios related modules from CPAN. IMHO they are inferior.

    «The Crux of the Biscuit is the Apostrophe»

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1012670]
Approved by blue_cowdawg
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2014-09-21 02:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (166 votes), past polls