It looks like when mysql coerces character strings into integers, it turns them into signed int's. Obviously if the column is unsigned, this is a problem. Don't use quotes you say. Problem is that the perl DBI API seems to put quotes around everything. So when I grab a really really large integer from the db using the perl api, and then try to get a child record referencing the same large integer ID, the DB doesn't give me anything because it's coercing a large chunk of text into a signed integer and truncating it.
You don't indicate when it is that DBI is putting "quotes around everything", but if what you mean is that values bound to placeholders get quoted, you can suppress that. perldoc DBI shows this information:
Data Types for Placeholders
The "\%attr" parameter can be used to hint at the data type the
placeholder should have. Typically, the driver is only interested
in knowing if the placeholder should be bound as a number or a
string.
$sth->bind_param(1, $value, { TYPE => SQL_INTEGER });
As a short-cut for the common case, the data type can be passed
directly, in place of the "\%attr" hash reference. This example is
equivalent to the one above:
$sth->bind_param(1, $value, SQL_INTEGER);
The "TYPE" value indicates the standard (non-driver-specific) type
for this parameter. To specify the driver-specific type, the driver
may support a driver-specific attribute, such as "{ ora_type => 97
}".
The SQL_INTEGER and other related constants can be imported using
use DBI qw(:sql_types);
See "DBI Constants" for more information.
Another not-really-a-bug but definitely a pitfall. And it sucks because after not being able to use md5 hashes to index my records using BINARY(16) because binary isn't really binary because it cuts off spaces, I'm losing a digit of my next-best-thing thanks to unsigned integers which are actually signed.
Don't make me go spend my life savings on Oracle!
Here's an example in case you're really bored. The problem below exists because 9358082631434058695 > 2^63
##First with no quotes around the large integer: mysql> select job_id from wordbarrel_9a where job_id=9358082631434058695; +---------------------+ | job_id | +---------------------+ | 9358082631434058695 | +---------------------+ 1 row in set (0.00 sec)
##Then with quotes: mysql> select job_id from wordbarrel_9a where job_id='9358082631434058695'; Empty set (0.00 sec)
mysql> desc wordbarrel_9a; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | job_id | bigint(20) unsigned | | PRI | 0 | | +----------+---------------------+------+-----+---------+-------+
-- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
