Skip Menu |

This queue is for tickets about the Net-MySQL CPAN distribution.

Report information
The Basics
Id: 14755
Status: open
Priority: 0/
Queue: Net-MySQL
People
Owner: Nobody in particular
Requestors: bugs [...] dcmconsulting.org
Cc:
AdminCc:
Bug Information
Severity: Critical
Broken in: (no value)
Fixed in: (no value)

History Show all quoted text
  Mon Sep 26 09:52:18 2005 Guest - Ticket created
Subject: bad insert id being returned
Here is stuff about my environment: for perl version This is perl, v5.8.4 built for i386-freebsd Net::MySQL FreeBSD 4.7-RELEASE-p28 FreeBSD 4.7-RELEASE-p28 #40: Fri Mar 11 14:16:49 MST 2005 root@fc2:/usr/src/sys/compile/VKERN i386 The problem is that i call the package Net::MySQL to insert some rows into a database. When i call the method, get_insert_id() it returns the correct id, until i get to id 65536 (2^8) Here is the code sample: if ($mysql->is_error){ #get error message $toBeReturned="ERROR:".$mysql->get_error_message; open(FILE, ">>$LOG_FILE") || die("n$LOG_FILE: $!nn"); #Print to the file the name and email to the file print FILE "ERROR Customer Insert sql:$insertSQL\n"; close(FILE); } else{ $toBeReturned=$mysql->get_insert_id; } $toBeReturned equals 253 for every customer after I have passed the id 65535. Please help me resolve this problem. Thanks,
  Wed May 31 18:53:00 2006 Guest - Correspondence added
From: Asgeir
I also bumped into this by way of the DBD-mysqlPP module... mysqlPP uses Net-MySQL, so this bug is propagated into this module as well. Whenever I insert rows in a table with an auto incremented index field above 16 bit (65536), I get 253 from get_insert_id (by way of the mysqlPP property 'mysql_insertid')... My guess is that the MySQL guys changed the interface slightly at some point, to accomodate for really big indexes and Net-MySQL was never adjusted to accustom this. The relevant part of Net-MySQL looks like this: sub _get_insert_id { my $self = shift; my $packet = shift; return ord(substr $packet, 6, 1) if ord(substr $packet, 6, 1) != 0xfc; unpack 'v', substr $packet, 7, 2; } I am sure those with knowledge would instantly see what could cause this... I have no knowledge of the MySQL protocols, so it is really beyond my abilities and resources. This bug is annoying like hell. We really need to get the newly inserted index value without an additional SQL query... The alternative is to switch to DBD-MySQL of course... On Mon Sep 26 09:52:18 2005, guest wrote: Show quoted text
> Here is stuff about my environment: > > for perl version This is perl, v5.8.4 built for i386-freebsd > Net::MySQL > FreeBSD 4.7-RELEASE-p28 FreeBSD 4.7-RELEASE-p28 #40: Fri Mar 11 > 14:16:49 MST 2005 root@fc2:/usr/src/sys/compile/VKERN i386 > > > The problem is that i call the package Net::MySQL to insert some rows > into a database. When i call the method, get_insert_id() > > it returns the correct id, until i get to id 65536 (2^8) > > Here is the code sample: > if ($mysql->is_error){ > #get error message > $toBeReturned="ERROR:".$mysql->get_error_message; > open(FILE, ">>$LOG_FILE") || die("n$LOG_FILE: $!nn"); > #Print to the file the name and email to the file > print FILE "ERROR Customer Insert sql:$insertSQL\n"; > close(FILE); > } > else{ > $toBeReturned=$mysql->get_insert_id; > } > > $toBeReturned equals 253 for every customer after I have passed the id > 65535. > > Please help me resolve this problem. > > Thanks,
  Wed May 31 18:53:01 2006 The RT System itself - Status changed from 'new' to 'open'
  Thu Sep 14 04:29:24 2006 krisodb [...] gmail.com - Correspondence added
From: krisodb [...] gmail.com
Replace the function with following code. (see below) Note: I only tested till 131072 records. The last part of the if then else should get you going to 2^32. I didn't verify if it's implemented like that in mysql (0xfe and above), I just assume so. if 6th byte < 0xfc => 1byte notation if 6th byte == 0xfc => 2byte notation if 6th byte == 0xfd => 3byte notation if 6th byte == 0xfe => 4byte notation ??? if 6th byte == 0xff => 5byte notation ??? sub _get_insert_id { my $self = shift; my $packet = shift; my $index; if (ord(substr $packet, 6, 1) < 0xfc) { $index = ord(substr $packet, 6, 1) } elsif (ord(substr $packet, 6, 1) == 0xfc) { $index = unpack 'v', substr $packet, 7, 2; } elsif (ord(substr $packet, 6, 1) == 0xfd) { $index = unpack 'V', (substr $packet, 7, 3).chr(0); } else { $index = unpack 'V', substr $packet, 7, 4; } return $index; } $ ./mysqltest.pl Inserted a record, last_index == 65534 $ ./mysqltest.pl Inserted a record, last_index == 65535 $ ./mysqltest.pl Inserted a record, last_index == 65536 $ ./mysqltest.pl Inserted a record, last_index == 65537 $ ./mysqltest.pl Inserted a record, last_index == 65538 $ ./mysqltest.pl Inserted a record, last_index == 65539 $ ./mysqltest4.pl $ ./mysqltest.pl Inserted a record, last_index == 131070 $ ./mysqltest.pl Inserted a record, last_index == 131071 $ ./mysqltest.pl Inserted a record, last_index == 131072
  Sun Nov 30 15:57:02 2008 rosenfield.albert [...] gmail.com - Correspondence added
From: rosenfield.albert [...] gmail.com
According to the manual: if (server_version <= 4.0) insert_id = byte1 else if (byte1 <= 0xfa) insert_id = byte1 else if (byte1 == 0xfb) error("wrong value in this context") else if (byte1 == 0xfc) insert_id = byte1..byte2 # 2 bytes else if (byte1 == 0xfd) insert_id = byte1..byte3 # 3 bytes else if (byte1 == 0xfe) insert_id = byte1..byte8 # 8 bytes (BIGINT) else error("wrong value in this context")