Persistent Fault Tolerant SQL (MySQL + DBI) connections!

[expresspotato]

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;
}

[MidLifeXis]

Update: Don't take these rather short comments as anything personal. They are purely comments on the code itself, in an effort to improve the end product, make maintenance easier, etc.

Some general comments:

• I am not certain of the benefit of using do("sql.pl") over making this a package and useing it.
• This would also allow you to use strict; use warnings; (or use commonsense; ;-) )
• You have many undeclared variables ($sql_a, $sql_r, etc), which use strict; would tell you.
• You are using the &mysub(...) style of calling functions instead of mysub(...). There are (infrequent) times to use it, but this does not appear to be one of those times.
• instead of warn "my warning message", you are using print "my warning message". warn... is the typical way to do this, as it generates line numbers and puts the information on stderr.
• Don't use exit;, use die;, as die can be trapped with an eval.
• If your eval statement in the sql subroutine failed due to a constraint in the database, wouldn't this end up causing an infinite loop?

This is not everything (I have not had my morning coffee as of yet), but it would be some good points to start asking questions.

[afoken]

I literally had to look twice at the date of this posting. I had the strong feeling that I had stumbled over one of the ancient nodes created in the early stages of perlmonks.org, perhaps as a repost of much older code from the ages of Perl 4. But that posting is dated Dec 23, 2009.

So, what's wrong with that code?

• Perl 4 is dead. Don't beat that horse, it won't run any more. Prefixing a subroutine call with an ampersand does something very different in Perl 5 (disabling subroutine prototype checks) than it did in Perl 4 (just calling the function). And most times, you don't want that to happen. Drop that ampersand.
• Of course, your code explicitly states that row_sql, get_sql, and hash_sql DO NOT take any parameters - they have an empty prototype. Still, your code reads those parameters that should not exist. This works only when you explicitly disable prototype checks by prefixing the function name with an ampersand. Drop that empty prototypes.
• Perl 5 has modules. Don't put code into scripts loaded at runtime by do, use modules. This way, the namespace of the caller is not polluted. If you need to pollute the caller's namespace, use the Exporter or newer modules like Sub::Exporter. Preferably, export (pollute) only on demand.
• No strict, no warnings. Both would have complained about several errors in the code.
• No way to setup the database connection, especially database driver, username and password, without changing the code for each and every application that wants to include this feature. I.e. you have to make a copy of this code for each application. This ends in a maintainance nightmare.
• High risk for SQL injection, because placeholders are not supported.
• Despite the effords to enable caching, the cache is poisoned by not using placeholders. So, performance will be far less than optimal.
• The code is limited to only one DB connection, without any obvious reason. Had this been written as a class with attributes instead of tons of global variables, this limit would not exist.
• print and exit instead of die prevents any attempts of error recovery. And error messages end where they don't belong: STDOUT.
• eval blocks that hide errors, e.g. while fetching data rows from the database. Again, this prevents error recovery, simply because nobody can detect errors! Either don't use eval or re-throw the errors.
• No concept of how to indent code. Instead, a wild mix of tabs, spaces, and missing newlines. This makes the code hard to read, hard to understand, hard to maintain.
• A constantly increased index counter ($c) in get_sql(). Why don't you just use push @results,$row[0]?
• Reinventing the wheel in inefficient perl code. Had you read the DBI manual, you would have found that your get_sql is a poor reimplementation of DBI's selectcol_arrayref. Of course, your hash_sql function has a superiour DBI equivalent: selectall_hashref. And your row_sql is the dumb cousin of DBI's selectrow_array. All of those DBI methods are written in fast C/XS code (with pure-perl fallback routines).
• Your code is not safe for transactions. Reconnecting to the database ends an old transaction (does it rollback or commit?) and creates a new one, damaging data.
• What is the reason for del_sql, mod_sql, and put_sql? They are all just wrappers for sql. And instead of aborting with a useful error message when called with an empty SQL statement, they print a warning and CONTINUE calling sql. sql still aborts, but gives a wrong error message.
• What's the purpose of the various sleep(1) calls?
• What's the purpose of $retry_count? It is incremented quite often, but never compared to anything.
• Why do you emit "warnings" to STDOUT using print instead of using warn? warn allows your caller to handle warnings ($SIG{__WARN__}), and unless your caller traps them, they end where they belong: STDERR.
• DBIx::AutoReconnect implements the only "new" feature in your code, i.e. automatic reconnect to the database. But DBIx::AutoReconnect implements this feature in a way that is nearly completely transparent to the application. You just add use DBIx::AutoReconnect to your application, and change DBI->connect(...) to DBIx::AutoReconnect->connect(...), and every other feature of DBI behaves as usual. Of course, it works with every database, and with all connect parameters. It adds three additional connect attributes that allow fine-tuning. So, what is the advantage of your code over the nearly five year old DBIx::AutoReconnect that's available for free at CPAN?
• And finally: Use sql.pl as you wish, its [sic] now yours. This reads like: Take this crap and don't ever ask me for help. (OK, one could also read this as "I hereby put this code into public domain.")

I would recommend to use this sql.pl only as a example of how not to write Perl code. In clear words: DO NOT USE THIS CRAP!

Code like this is responsible for Perl's bad reputation. So: --

Alexander