Monks,

I have inherited some SQL and would like some input.

I am processing some cable modem stats. Client machine is Windows Server 2008 R2, ActivePerl 5.8, using the ODBC Data Source Administrator control panel.

Script runs fine, but occasionally it exits with the following:

DBD::ODBC::st execute failed: [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Lost connection to MySQL server during query (SQL-08S01) at load_modem.wpl line 255.

Please note: I respect the Monastery's preference for actual code, but in interest of inappropriate disclosure, I have modified the following slightly. The logic has not been altered.

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect("dbi:ODBC:MySQL_for_modem");

$dbh->{LongReadLen} = 512 * 1024;
$dbh->{LongTruncOk} = 1;

my $sql_region_id = "select distinct region_id 
        from config.object ";

my $sql_statement = "select info from database";

unless ( $region_cursor = $dbh->prepare( "$sql_region_id" )){
   exit 1;
}

unless ( $region_cursor->execute ) {
   exit 2;
}

$region_cursor->bind_columns(\($region_id));

while( $region_cursor->fetch() ){

    unless ( $cursor = $dbh->prepare( "$sql_statement" )) {
        exit 1;
    }
    
    unless ( $cursor->execute($region_id) ) { # <-- line 255 in the sc
+ript
    exit 2;
    }
    
    $cursor->bind_columns(\( $modem_ip, etc... ));
    
    while( $cursor->fetch() ) {
    
        process the stats...
    }
}

[download]

Given that I have no control over the server that I am querying, what is the best way to have the script carry on? ie: instead of the exits (or dies if I replace them), what is a clean way to reconnect and and continue?

As always, thanks for any help and references...

Scott...

In reply to Lost connection to MySQL server by spstansbury

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.