PortaTela Posted January 10, 2008 Share Posted January 10, 2008 Hi. I have had a weird problem today: I use a table for user info where the key is an integer. The integer exists of 3 parts: random numbers, name dependant numbers and an incrementing counter. This brings the key length to a maximum of 12 integers. While adding a new member the new key is calculated through php. Now everything worked fine until I tried to add my 43th user. I received this error: Duplicate entry '004294967295' for key 1. That while the query which was being executed tried to insert 004316238579 as key. After some testing i discovered that any integer higher than 004294967295 gets interpreted as 004294967295 by my mysql database... that means that the error mentioned for duplicate keys disappears as soon as i use 004294967294 or lower as integer key. I don't really understand this phenomenon, so is there someone who could be so kind to explain it to me? is this maximum integer something i can affect or not? is it based on the hardware specifications of the server or is it a fixed number? At the moment i solved the problem by changing the field type from int to varchar for my key. I always thought though that int keys were faster than varchar keys. Is that true and if so how significant is the difference? thanks in advance. Quote Link to comment Share on other sites More sharing options...
Cydewinder Posted January 10, 2008 Share Posted January 10, 2008 I've solved similar problems in the past by switching it to an unsigned int field. Not sure if that's helpful to you, but my understanding is that a normal INT field takes values from -X to +X, whereas an unsigned int will take values from 0 to 2X. You'll still have a limit, but it'll be higher. Quote Link to comment Share on other sites More sharing options...
PortaTela Posted January 10, 2008 Author Share Posted January 10, 2008 I've solved similar problems in the past by switching it to an unsigned int field. Not sure if that's helpful to you, but my understanding is that a normal INT field takes values from -X to +X, whereas an unsigned int will take values from 0 to 2X. You'll still have a limit, but it'll be higher. Thanks for the reply Cydewinder. I tried that before posting here, but it didnt help me out. I still received the same error, which means that the upper limit didnt change at all. Quote Link to comment Share on other sites More sharing options...
Cydewinder Posted January 10, 2008 Share Posted January 10, 2008 Have you tried bigint? I solved a problem like this back in University using bigint instead of int. It wasn't MySQL though, so I'm just speculating right now. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 10, 2008 Share Posted January 10, 2008 I refer you to this refman page. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.