March 10, 2008
Fix for stupid mysqldump files I have been fighting the dreaded 'max_allowed_packet' error that mysql gives when trying to restore a dump file that has multi-line insert statements which make mysql barf. I finally just whipped up a script to fix the dump file before trying to load it.

#!/usr/bin/perl
use warnings;
use strict;

if ( scalar(@ARGV) < 1 ) { die "No filename given!" }

my $file  = shift(@ARGV);
my $file2 = "$file-new";

open( FILE, "<$file" )
    or die "can't open $file: $!";
open( FILE2, ">$file2" )
    or die "can't open $file2: $!";
my $count = 1;
while ( my $line =  ) {
    if ( $line =~ /^(INSERT INTO .*? VALUES)/ ) {
        my $insert = $1;
        $line =~ s/\),\(/\);\n$insert \(/g;
        print "line $count: ", sprintf("%.2fk\n", length($line)/1024);
    }
    $count++;
    print FILE2 $line;
}
close(FILE);
close(FILE2);

exit(0);
Posted by ed at 05:55 PM