Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
Finally... After running into various issues with DBI and the SQL process simply cutting out, I decided to write up a solution. When an SQL server looses an autorization packet or goes down for a reboot the results can be disasterous. User or system entries half added or half retrieved can leave the database integrity questionable. Commits really *really* help with this problem, allowing semi-posted data to be discarded if the insert(s) or update(s) aren't successful. But what about "one up" simple queries where a commit really isn't practical? You should always use commits when possible, but again what about perserving the SQL call? Welcome to Persistent Fault Tolerant SQL! Making SQL easier requires your own design, with subroutines to delete, update, insert or return rows as you'd like. I've done the work for you.

sql.pl Preable

Using this code is really simple and ensures that in the event of a query or sql server failure, it is persistently retried until it is successful. You may want to add a timeout for such a thing or a number or retries but I really see no point as this was designed for Apache::DBI and back-end thread processing nodes where a blocking lock is practical. When a user cancels a page load, the resulting SQL connection would terminate anyways. This type of solution is also extremely practical for background ajax requests when returning an error simply wouldn't be visible or recognized.
This script is designed to be separated from the main program and then accessed through a do("sql.pl"); routine. This allows the subs defined within to be used elsewhere in the program. It is also designed to run in combination with Apache::DBI, this is why there are no disconnects.

sql.pl Non-Returning Subroutines

&del_sql Calls &sql
&mod_sql Calls &sql
&put_sql Calls &sql

sql.pl Returning Subroutines

&row_sql Return a single ROW from a table
&get_sql Return a single COLUMN from a table
&hash_sql Return an ARRAY of HASHES for ALL RESULTS.
Use sql.pl as you wish, its now yours.

sql.pl Accessing Subroutines

my @results = &row_sql(qq~ select * from table where ...; ~); my @results = &get_sql(qq~ select id from table where ...; ~); my $rows = &hash_sql(qq~ select * from table; ~); foreach my $row (@$rows){ print "$row->{id}, $row->{somecolumn}\n"; }

Rolling your own persistent-ness

The important thing when rolling your own persistent connections is simple. Its constructed with:
- Until
- (While retrying) - Sleep for a second and possibly warn - Until
- - Eval
- - (While retrying) - Sleep for a second and possibly warn
until ( $dbh=DBI->connect_cached($connectionInfo,$user,$passwd, {PrintErro +r=>0} ) ) { if ($sqlw){print "Retrying SQL Connect ($DBI::errstr) ...";} s +leep(1); } until ( eval { $sth=$dbh->prepare($insert); if ($sth->execute()) { $sth->finish; } } ) { if ($sqlw){print "Retrying SQL Insert ($DBI::errstr)...";} sle +ep (1); $retry_count++;}

sql.pl :: Source Code

use DBI; &sql_setup; $sqlw = 0; #Don't print SQL warnings sub sql_setup{ if ($_ eq ""){ $db="YOUR DATABASE"; }else{ $db = $_[0]; } $user="YOUR USER"; $passwd="YOUR PASSWORD"; $host="YOUR SERVER:3306"; $connectionInfo="dbi:mysql:$db;$host"; $sql = 1; } sub del_sql{ if ($_[0] eq ""){print "Empty Delete";} &sql($_[0]); } sub mod_sql{ if ($_[0] eq ""){print "Empty Modify";} &sql($_[0]); } sub put_sql{ if ($_[0] eq ""){print "Empty Put";} &sql($_[0]); } sub row_sql(){ $sql_r++; my $select = $_[0]; my @row,$dbh,$sth,$retry_count; if ($select eq ""){print "Empty Select."; exit;} until ( $dbh=DBI->connect_cached($connectionInfo,$user,$passwd, {PrintErro +r=>0} ) ) { if ($sqlw){print "Retrying SQL Connect ($DBI::errstr) ...";} s +leep(1); } until ( eval { $sth=$dbh->prepare($select); if ($sth->execute()) { @row=$sth->fetchrow_array(); $sth->finish; } } ) { if ($sqlw){print "Retrying SQL Row ($DBI::errstr)...";} sleep +(1); $retry_count++;} return @row; } sub get_sql(){ $sql_g++; my $select = $_[0]; my $c = 0; my @results,@row,$dbh,$sth; if ($select eq ""){print "Empty Select."; exit;} until ( $dbh=DBI->connect_cached($connectionInfo,$user,$passwd, {PrintErro +r=>0} ) ) { if ($sqlw){print "Retrying SQL Connect ($DBI::errstr) ...";} s +leep(1); } until ( eval { $sth = $dbh->prepare($select); if ($sth->execute()) { while (@row=$sth->fetchrow_array()) { @results[$c] = @row[0]; $c++; } $sth->finish; } } ) { if ($sqlw){print "Retrying SQL Get ($DBI::errstr)...";} sleep +(1); $retry_count++;} return (@results); } sub hash_sql(){ $sql_h++; my $select = $_[0]; my $dbh,$sth,$retry_count,$rows; if ($select eq ""){print "Empty Select."; exit;} until ( $dbh=DBI->connect_cached($connectionInfo,$user,$passwd, {PrintErro +r=>0} ) ) { if ($sqlw){print "Retrying SQL Connect ($DBI::errstr) ...";} s +leep(1); } until ( eval { $rows = $dbh->selectall_arrayref( $select, { Slice => {} } ); } ) { if ($sqlw){print "Retrying SQL Hash Select ($DBI::errstr)...";} +sleep (1); $retry_count++;} return ($rows); } sub sql{ $sql_a++; my @row,$dbh,$sth,$retry_count; my $insert = $_[0]; if ($insert eq ""){print "Empty insert."; exit;} until ( $dbh=DBI->connect_cached($connectionInfo,$user,$passwd, {PrintErro +r=>0} ) ) { if ($sqlw){print "Retrying SQL Connect ($DBI::errstr) ...";} s +leep(1); } until ( eval { $sth=$dbh->prepare($insert); if ($sth->execute()) { $sth->finish; } } ) { if ($sqlw){print "Retrying SQL Insert ($DBI::errstr)...";} sle +ep (1); $retry_count++;} return 1; }


In reply to Persistent Fault Tolerant SQL (MySQL + DBI) connections! by expresspotato

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!
  • 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, 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, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            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.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others surveying the Monastery: (11)
    As of 2014-12-26 15:04 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      Is guessing a good strategy for surviving in the IT business?





      Results (171 votes), past polls