Strange SQL results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Papago - 26 Aug 2004 22:47 GMT

I have no idea why this is happening. Whenever I run this SQL command on my
MySQL database:

insert into compData values ('3002572819', 'ALFRAMED CORP.', '72-C
INDUSTRIAL ESTATE', '', 'SIALKOT, PUNJAB', '', '', '', 'PK', 'MM', 'A',
'630-896-5211 ', '630-896-5211', 'delta1name@yahoo.com')

I get this result:

#1062 - Duplicate entry '2147483647' for key 1

How the heck can it be a duplicate entry? The number '2147483647' doesn't
even appear in my original SQL statement! And I've checked to make sure
that there is no other entry with the number '3002572819' either.

Reply to this Message

ljb - 27 Aug 2004 00:58 GMT

me@myplace.com wrote:

> I have no idea why this is happening. Whenever I run this SQL command on my
> MySQL database:
[quoted text clipped - 10 lines]
> even appear in my original SQL statement! And I've checked to make sure
> that there is no other entry with the number '3002572819' either.

I will guess your primary key field is integer (signed 32 bit), and so
the value you are trying to insert (3002572819) is too big for it.
The error message you are getting from MySQL is certainly unhelpful.
A reasonable database like PostgreSQL would say:
ERROR: value "3002572819" is out of range for type integer

Reply to this Message

Christopher Browne - 27 Aug 2004 01:11 GMT

That's not merely a feature; it's documented as such.

<http://dev.mysql.com/doc/mysql/en/Numeric_types.html>

See also sections 1.13 and 3.5, here:
<http://sql-info.de/mysql/gotchas.html>

This is one of the (many) excellent reasons to consider other database
systems if you care about your data...

Signature

wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www3.sympatico.ca/cbbrowne/rdbms.html
"Lisp is an eternal thought in the mind of God."
--Crassus, mutatis mutandi

Reply to this Message

Gene Wirchenko - 27 Aug 2004 01:16 GMT

>I have no idea why this is happening. Whenever I run this SQL command on my
>MySQL database:
[quoted text clipped - 8 lines]
>
>How the heck can it be a duplicate entry? The number '2147483647' doesn't

^^^^^^^^^^
= 2^31-1. This value is the maximum value for a 4-byte signed
integer. What is the type of the column? If 4-byte signed integer,
your insert value is too big. Maybe, it is being "corrected" to the
maximum number in the column domain. That corrected number may
already be in use for the key.

>even appear in my original SQL statement! And I've checked to make sure
>that there is no other entry with the number '3002572819' either.

You might also check for an entry number 3002572819-2^31.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Reply to this Message

Christopher Browne - 27 Aug 2004 03:53 GMT

> You might also check for an entry number 3002572819-2^31.

No, when MySQL gets data that does not conform with the field
definitions, it silently truncates, typically to MINVAL/MAXVAL, or by
some similar sort of truncation.

Evidently the vendor's customers find it more to their liking to have
their data corrupted in this fashion than to be faced with error
messages.

Which is actually very interesting; it means that the in-the-client
validation that is very much in their development doctrines needs to
exceedingly comprehensive...

Signature

(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/languages.html
"...very few phenomena can pull someone out of Deep Hack Mode, with
two noted exceptions: being struck by lightning, or worse, your
*computer* being struck by lightning." -- Matt Welsh

Reply to this Message