[SOLVED]: How to insert 30-50K rows into MySQL DB using DBI?

[Perl300]

Greetings monks!

I am trying to insert around 30-50K rows using DBI into a MySQL DB (5.5.56-MariaDB). I am trying to use following code (say insert1.pl) which is working for almost 6-7K rows.

#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dumper;
use POSIX qw( strftime );

my $database = 'db_name';
my $db_user = 'user';
my $db_password = 'pwd';
my $db_hostname = 'db_hostname';

my $dbh2 = DBI->connect("DBI:mysql:database=$database:host=$db_hostnam
+e",$db_user,$db_password,
    { RaiseError => 1, AutoCommit => 1, mysql_auto_reconnect => 1 } # 
+Added AutoCommit => 1, mysql_auto_reconnect => 1 while trying to make
+ it work
);
die "unable to connect to server $DBI::errstr" unless $dbh2;

my $sql2 = q^
    INSERT INTO table_name (col_1, col_2, col_3, col_4, col_5)
    VALUES ^;

while(my @row = $sth1->fetchrow_array){
    if ($row[3])
    {
        $sql2 .= "          ('" . $row[0] . "', '" . $row[1] . "', '" 
+. $row[2] . "', '" . $row[3] . "', '" . $row[4] . "'),\n";
    }
    else
    {
        $sql2 .= "          ('" . $row[0] . "', '" . $row[1] . "', '" 
+. $row[2] . "', null, '" . $row[4] . "'),\n";
    }
}
$sth1->finish();

$sql2 .= "          ('last_dummy', 'last_dummy', 'last_dummy', 'last_d
+ummy', 'last_dummy')\n";
print localtime() . "\tThe sql2:\n$sql2\n";
my $sth2 = $dbh2->prepare($sql2);
sleep 10; # Added this later while trying to make it work
$sth2->execute(); # I get for execute.
sleep 10; # Added this later while trying to make it work
$sth2->finish();

my $sql3 = "DELETE FROM table_name WHERE col_1 = 'last_dummy'";
print localtime() . "\tThe sql3:\n$sql3\n";
my $sth3 = $dbh2->prepare($sql3);
$sth3->execute();
$sth3->finish();

$dbh2->disconnect();

But when I try to insert all the rows, which at present are 27K, I see error in log like:

          ('host1', '123.456.789', 'string1', 'string4', 'Root (root@l
+ocalhost) (configure /etc/snmp/snmp.local.conf)'),
          ('host2', '123.456.743', 'string2', 'string5', 'Root (root@l
+ocalhost) (configure /etc/snmpDBD::mysql::st execute failed: MySQL se
+rver has gone away at insert1.pl line 38.
DBD::mysql::st execute failed: MySQL server has gone away at insert1.p
+l line 38.
/snmp.local.conf)'),
          ('host3', '499.456.789', 'string3', 'string6', 'Root (root@l
+ocalhost) (configure /etc/snmp/snmp.local.conf)'),

I have tried to add option for AutoCommit and auto_reconnect as I got those as suggetions while searching for reason behind the error MySQL server has gone away at

I did try to super search here for 'bulk insert', 'mysql bulk' & 'DBI insert' but didn't get what I am looking for. I also tried use diagnostics but it adds only one line in the error: Uncaught exception from user code: Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle database=telemetry:host=<IP> at insert1.pl line 38.

I am pretty sure that there is better way to do this than how I am doing it here.

UPDATE_1:Added note after trying use diagnostics.

UPDATE_2:The 'Root (root@localhost) (configure /etc/snmp/snmp.local.conf)' is coming from data. So that is not the problem here.

UPDATE_3: Updated title to mark it as SOLVED.

[huck]

I am pretty sure that there is better way to do this than how I am doing it here.

yea

#!/usr/bin/perl -w
use warnings; 
use strict;
use DBI;
use Data::Dumper;
use POSIX qw( strftime );

my $database = 'db_name';
my $db_user = 'user';
my $db_password = 'pwd';
my $db_hostname = 'db_hostname';

my $dbh2 = DBI->connect("DBI:mysql:database=$database:host=$db_hostnam
+e",$db_user,$db_password,
    { RaiseError => 1, AutoCommit => 1, mysql_auto_reconnect => 1 } # 
+Added AutoCommit => 1, mysql_auto_reconnect => 1 while trying to make
+ it work
);
die "unable to connect to server $DBI::errstr" unless $dbh2;

my $sql2_1 = q^
    INSERT INTO table_name (col_1, col_2, col_3, col_4, col_5)
    VALUES (?,?,?,?,?) ^;
my $sth2_1 = $dbh2->prepare($sql2_1);
my $grp=0; 
$dbh2->begin_work; 

while(my @row = $sth1->fetchrow_array){
    $grp++; 
    if ($grp>1000) {$dbh2->commit;$dbh2->begin_work; $grp=0;} 
    unless ($row[3]) {$row[3]=undef;} 
    $sth2_1->execute($row[0],$row[1],$row[2],$row[3],$row[4]); 
}
$dbh2->commit;
$sth1->finish();
$dbh2->disconnect();

notice the begin_work and commit calls, and the batching of inserts before calling commit then begin work again

as for why the placeholders(?), you did not take into account proper mysql quoting or http://bobby-tables.com/

But even this may be too slow for 50K rows, ill be back

[huck]

ill be back

When i needed to insert 10's of K records across a network interface i found the above method still too slow since there was an overhead for each sql call. so i went with something like this instead.

#!/usr/bin/perl -w
use warnings; 
use strict;
use DBI;
use Data::Dumper;
use POSIX qw( strftime );

my $database = 'db_name';
my $db_user = 'user';
my $db_password = 'pwd';
my $db_hostname = 'db_hostname';

my $dbh2 = DBI->connect("DBI:mysql:database=$database:host=$db_hostnam
+e",$db_user,$db_password,
    { RaiseError => 1, AutoCommit => 1, mysql_auto_reconnect => 1 } # 
+Added AutoCommit => 1, mysql_auto_reconnect => 1 while trying to make
+ it work
);
die "unable to connect to server $DBI::errstr" unless $dbh2;

my $sql2_1000 = q^
    INSERT INTO table_name (col_1, col_2, col_3, col_4, col_5)
    VALUES  ^;

$sql2_1000.=' (?,?,?,?,?) 'x1000; 
my $sth2_1000 = $dbh2->prepare($sql2_1000);
my $grp=0; 
$dbh2->begin_work; 
my @list=(); 
while(my @row = $sth1->fetchrow_array){
    $grp++; 
    if ($grp>1000) {
        $dbh2->commit;
        $dbh2->begin_work; 
        $sth2_1000->execute(@list); 
        $grp=1;
        @list=(); # edit/add 
    } 
    unless ($row[3]) {$row[3]=undef;} 
    push @list,$row[0],$row[1],$row[2],$row[3],$row[4]; 
}
$dbh2->commit;
my $sql2_n = q^
    INSERT INTO table_name (col_1, col_2, col_3, col_4, col_5)
    VALUES  ^;
my $n=scalar(@list)/5; 
$sql2_n.=' (?,?,?,?,?) ' x $n; 
my $sth2_n = $dbh2->prepare($sql2_n);
$dbh2->begin_work; 
$sth2_n->execute(@list); 
$dbh2->commit;
$sth1->finish();
$dbh2->disconnect();

notice the batching of rows per sql call, and the final creation of a insert of the exact length.

Both of these are untested code for example sake only, your mileage may vary.

[marto]

You may be interested in DBI Advanced Tutorial linked from the DBI documentation. IIRC one of the comments Tim makes is "Proprietary bulk-load is almost always faster than Perl.", something you may want to factor into your profiling.

[talexb]

I don't know if there's an equivalent to bcp that I used with Ingres about twenty years ago -- if not, someone should write one.

Update Ah, after looking more closely at the wikipedia page, now I remember the LOAD DATA INFILE command. That might be useful to you.

[Perl300]

Thank you for all your comments and suggestions.

I tried what huck suggested in second reply and it works perfect. I just had to add comma at the end of (?,?,?,?,?) to make it (?,?,?,?,?), to get rid of SQL syntax error.

Special thank you huck :-)

[huck]

Opps sorry, my actual code looks closer to

sub makeinsert{ 
  die "no table" unless ($args{table}); 
  my $table  =$args{table}; 
  die "no table" unless ($args{dbh}); 
  my $dbh    =$args{dbh}; 
  die "no varlist" unless ($args{varlist}); 
  my @vl=@{$args{varlist}}; 
  my $repeat =$args{repeat}; 
  $repeat=1 unless ($repeat); 

          my @q=map{'?'} @vl; 
          my $plist=' ('.join (',',@q).') ';
          my @aplist; 
          for my $ii (1..$repeat) { push @aplist,$plist; } 
          my $plistn=join (",\n",@aplist); 
          my $sql='INSERT INTO '.$table.'
                      ('.join (',',@vl).' ) values '.$plistn;    
          my $handle=$dbh->prepare($sql);
          die "Couldn't prepare ".$table." insert; aborting"
                unless defined $handle ; 
          return $handle; 
} 

my $sth2_1000=makeinsert(dbh     =>$dbh2
                        ,table   =>'table_name'
                        ,repeat  =>1000
                        ,varlist =>[qw/col_1 col_2 col_3 col_4 col_5/]
                        );

And i missed that comma, noticing only the \n, in my haste to simplify it for you.