Using Linux 2.2.x, Perl 5.6
MySQL 3.23.x (several versions).
We have been using LAST_INSERT_ID to do sequence tables to generate unique
IDs for use in several other tables.
Sequence table:
CREATE TABLE seq_table ( id_val bigint(20) unsigned );
We use SQL like this: "UPDATE seq_table SET id_val=LAST_INSERT_ID(id_val + 1)"
This works fine.
We then use Perl DBI to get the last_update_id:
$dbh->{'mysql_insertid'}
This works great until the value of id_val is over that allowed by a 32-bit
integer. We use bigint fields for IDs.
We recently did a test where the ID field was more than 4 billion. The $dbh
line above returned a negative number where it should have returned a value
around 6 billion.
We look in the sequence table and see '6000000051'. We got back a fairly
large negative number (-678294 or something, I don't have it handy) from the
Perl code above.
We changed our Perl code to use SQL to retrieve the ID:
$sql = "SELECT LAST_INSERT_ID()"
$sth = $dbh->prepare($sql);
$sth->execute();
my ($id) = $sth->fetchrow_array();
This works and correctly returns the result of the UPDATE statement above.
It appears that there is a problem in DBI or with the libmysql code. We are
using one of the most recent versions of DBI (the newest has only doc bug
fixes as far as we could see). If the value will fit in 32 bits, it seems to
work. If not, it won't. Actually, it appears that one of these two pieces
of code (DBI or libmysql) uses a signed 32-bit integer somewhere. Alpha
machines may not be effected by this.
Can someone help on this? I have seen Tim Bunce on the list occassionally...
We are in the process of changing our code over to the longer method, but
this seems like it is a bug.
Best,
Kyle
--
Kyle Hayes
Quicknet Technologies t: +1 415 864 5225
520 Townsend St. Suite D f: +1 415 864 8388
San Francisco, CA 94103 w: http://www.quicknet.net
USA
*******************************************************************************
"HEAR THE DIFFERENCE" with a live MICROTELCO demo at:
Computer Telephony EXPO, Mar 6-8, Los Angeles, CA
MicroTelco is a revolutionary service that brings multiple Internet
Telephony Service Providers (ITSPs) together in a convenient,
simple to use account center for greater reliability and flexibility.
For more information visit: http://www.microtelco.com.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
