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

Question

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?

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
– MichielB, Commented Sep 28, 2016 at 7:45

gfunk · Accepted Answer · 2016-10-08 02:29:02Z

0

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

answered Oct 8, 2016 at 2:29

gfunk

3812 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related