Bug #54155 for Net-MySQL: big querys (>16MB) fail; "packets out of order" error

we use net::mysql to insert logdata into mysql. doing that with huge insert statements works quite well and fast, unless you have query commands bigger than 16MB. in that case the server resets the connection and sends a "packets out of order" error. the mysql protocol specifies a packet header consisting of 3 bytes for length, 1 byte packet counter. the body is 1 byte command, then data. http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#The_Packet_Header unfortunately this is not implemented in net::mysql 0.9; instead it looks like a 4 byte length field is used and no packet counter or query packetization happens at all. my $message = pack('V', length($sql) + 1). $command. $sql; $mysql->send($message, 0); attached is a patch that overcomes this by creating proper mysql packets in a loop before. the patch also contains my proposed fix for "affected_rows_length inaccurate after error" (https://rt.cpan.org/Public/Bug/Display.html?id=53558) and the use of Digest::SHA::PurePerl instead of Digest::SHA1.

--- MySQL.pm.orig 2006-04-01 08:14:08.000000000 +0200 +++ MySQL.pm 2010-01-31 00:01:59.363068634 +0100 @@ -5,7 +5,7 @@ use Carp; use vars qw($VERSION $DEBUG); use strict; -$VERSION = '0.09'; +$VERSION = '0.09.1'; use constant COMMAND_SLEEP => "\x00"; use constant COMMAND_QUIT => "\x01"; @@ -31,6 +31,7 @@ use constant DEFAULT_PORT_NUMBER => 3306; use constant BUFFER_LENGTH => 1460; +use constant MAX_PACKET_LENGTH => 16777215; use constant DEFAULT_UNIX_SOCKET => '/tmp/mysql.sock'; @@ -289,9 +290,20 @@ my $sql = shift; my $mysql = $self->{socket}; - my $message = pack('V', length($sql) + 1). $command. $sql; - $mysql->send($message, 0); - $self->_dump_packet($message) if Net::MySQL->debug; + my $max = MAX_PACKET_LENGTH; + my $packet_number = 0; + my $msg_body = $command.$sql; + + do { + my $msg_body_part = substr($msg_body, $packet_number*$max, $max); + my $length = length($msg_body_part); + my $msg_header = substr(pack('V',$length),0,3).pack('c',$packet_number); + my $message = $msg_header.$msg_body_part; + $mysql->send($message, 0); + $self->_dump_packet($message) if Net::MySQL->debug; + $packet_number++; + } while(length($msg_body) > $packet_number*$max); + printf "Sent command in %s packets\n", $packet_number if Net::MySQL->debug; my $result; $mysql->recv($result, BUFFER_LENGTH, 0); @@ -449,6 +461,7 @@ $self->{server_message} = ''; $self->{error_code} = undef; $self->{selected_record} = undef; + $self->{affected_rows_length} = 0; } @@ -678,7 +691,8 @@ package Net::MySQL::Password; use strict; -use Digest::SHA1; +#use Digest::SHA1; +use Digest::SHA::PurePerl; sub scramble { my $class = shift; @@ -694,7 +708,8 @@ my $message = shift; my $password = shift; - my $ctx = Digest::SHA1->new; + #my $ctx = Digest::SHA1->new; + my $ctx = Digest::SHA::PurePerl->new; $ctx->reset; $ctx->add($password); my $stage1 = $ctx->digest;