Re: Random data loss, less than 0.1% of the time - how to prevent it

[Ted Byers]

Rick James Wrote:
-------------------------------------------------------
> I am unfamiliar with Exception::Class. DBI does
> not abort, so eval{} would not 'catch' it,
> correct
>
> Suggest you write a small test case to validate
> the try-catch code. One simple suggestion is
> CREATE TABLE test (x INT PRIMARY KEY);
> INSERT INTO test VALUE (1);
> INSERT INTO test VALUE (1); -- should get
> "Duplicate key" error.
>
> Wrap each INSERT in DBI and Exception (or eval or
> try), and see if the error is 'caught'.

OK, here is some coe I ran on my Linux box. I can guarantee it runs the same as it would on Windows (but I don't have copy/paste capability on that machine over an internet connection to this one).

#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Exception::Class::TryCatch;

$| = 1;

my $db = 'test';
my $hostname = 'localhost';
my $user = 'xxxxxxxxx';
my $dbpwd = 'xxxxxxxxxx';
my $dbh = DBI->connect_cached("DBI:mysql:database=$db;host=$hostname",$user,$dbpwd,{RaiseError => 1}) or die "Failed to connect to the DB.\n";
try eval {
my $sql = "CREATE TABLE test (x INT PRIMARY KEY);";
$dbh->do($sql);
print "Creating the table is fine\n";
$sql = "INSERT INTO test VALUE (1);";
$dbh->do($sql);
print "The first INSERT is fine\n";
# $sql = "INSERT INTO test VALUE (1);"; redundant
$dbh->do($sql); # -- should get "Duplicate key" error.
print "If this line is printed, there is a problem with try/catch in relation to DBI\n";exit(1);
};
if (catch my $err) {
print "OOPS\n\t",$err->error,"\n";
}
print "DONE!\n";
exit(0);

And, here is the result:

ted@linux-jp04:~/Work/Projects/misc.tests> ./test.dbi.trycatch.pl
Creating the table is fine
The first INSERT is fine
DBD::mysql::db do failed: Duplicate entry '1' for key 'PRIMARY' at ./test.dbi.trycatch.pl line 24.
OOPS
DBD::mysql::db do failed: Duplicate entry '1' for key 'PRIMARY' at ./test.dbi.trycatch.pl line 24.

DONE!
ted@linux-jp04:~/Work/Projects/misc.tests>

Notice that the code wants to die at line 24, but because it is executed within a try/eval block, it does not, and instead control is passed to the catch block and line 25 is NOT executed. If the catch block handles the error, and it is determined the script an continue, whatever is after the catch block will be executed (illustrated by this script printing 'DONE!' at the end). The catch block, then, must determine whether or not it can continue and if not it must terminate the script in as graceful manner as possible.

Obviously, in production code, the catch block should first log the error, attempt a recovery if at all possible, and exit gracefully if the attempted recovery either is impossible or fails.

So, I have demonstrated that Exception::Class::TryCatch is reliable, at least in this case (and all cases where I wrote incorrect SQL just for the purpose of testing it - so this sample code may prove useful to you), and I can report there are no duplicate key errors corresponding to the data that was not inserted. So my problem remains.

Cheers

Ted