Migration perl dbi script leading DBD::mysql::st execute failed: MySQL server has gone away at

0
votes

I have a migration script, where we are migrating the data from one db to another db.

my $dbh1 = DBI->connect($dsn1,$userid,$password, {mysql_enable_utf8 => 1} ) or die $DBI::errstr;
my $dbh2 = DBI->connect($dsn2,$userid,$password, {mysql_enable_utf8 => 1} ) or die $DBI::errstr;

where migrating the data from dbh1 to dbh2. while running the script. we are getting error as : DBD::mysql::st execute failed: MySQL server has gone away at

If connection is failed, we want to reconnect and continue our script. How can we handle it?

$select = $dbh1->prepare("SELECT * FROM ADAP");
$insert = $dbh2->prepare("INSERT INTO ADAP VALUES (?,?,?,?,?,?,?)");
$select->execute;
while ( my ($ID,$STA,$UR,$TY) = $select->fetchrow_array ) {
    $insert->execute($ID,$STA,$UR,$TY,'NULL','N','N');
 if(!$insert->err)
 {
   $rows2+=1;
 }
}
$rows1 = $select->rows();
print "\n Total $rows2 copied out of $rows1 \n";
$rows2=0;

while executing above loop, if disconnected, then we would reconnect and continue the loop. how can we achieve this?

1
mysqlperldbi
Wouldn't it be better to find out WHY the 'MySQL server has gone away'? I found I sometimes have issues like this when my inserts are bigger than max_allowed_packet in the my.cnf. - MichielB

1 Answers

0
votes

Use eval to catch errors:

$select = $dbh1->prepare("SELECT * FROM ADAP");
$insert = $dbh2->prepare("INSERT INTO ADAP VALUES (?,?,?,?,?,?,?)");
$select->execute;
my @row = $select->fetchrow_array();
while(@row) {
  eval { # suggest a fxn call, but you get it...
    $insert->execute($row[0],$row[1],$row[2],$row[3],'NULL','N','N');
    ++$rows2 if !$insert->err;
  }; if($@) {
    reconnect... etc...
  }
  eval {
    @row = $select->fetchrow_array();
  }; if($@) {
    reconnect... etc...
  }
}

You should also batch your work:

SELECT * FROM ADP WHERE id > __ ORDER BY id ASC LIMIT 1000; # assumed indexed...
MAKE 1 single INSERT STATEMENT w/ the 1k rows to insert
EXECUTE the insert statement
repeat next batch