Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

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

by Perl300 (Friar)
on Oct 02, 2018 at 21:57 UTC ( #1223430=perlquestion: print w/replies, xml ) Need Help??

Perl300 has asked for the wisdom of the Perl Monks concerning the following question:

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.

Replies are listed 'Best First'.
Re: How to insert 30-50K rows into MySQL DB using DBI?
by huck (Prior) on Oct 03, 2018 at 00:01 UTC

    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

      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.

Re: How to insert 30-50K rows into MySQL DB using DBI?
by marto (Cardinal) on Oct 03, 2018 at 09:29 UTC

    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.

Re: How to insert 30-50K rows into MySQL DB using DBI?
by talexb (Chancellor) on Oct 03, 2018 at 14:13 UTC

    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.

    Alex / talexb / Toronto

    Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Re: How to insert 30-50K rows into MySQL DB using DBI?
by Perl300 (Friar) on Oct 03, 2018 at 19:13 UTC

    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 :-)

      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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1223430]
Approved by 1nickt
Front-paged by 1nickt
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (3)
As of 2023-01-27 08:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?