Duplicate entry errors

clkao

I'm trying to create a MySQL user table that stores a randomly
generated 10-digit user id as its primary key. I'm going nuts though
cuz I keep getting 'Duplicate entry' errors even on numbers I know
are not in the table!

Here's what I've done. First I created the table with the index like
this:

CREATE TABLE user(
user_id INTEGER(10) UNSIGNED NOT NULL, [... rest of columns],
primary key(user_id)
);

Second, I generate a 10-digit random number with mtsrand then I run a
select statement on the user table to see if the number already
exists. If so, I generate another. Otherwise, I insert it into the
table.

The problem is after every 3 or 4 inserts, I get a 'Duplicate entry'
error on the primary key. But when I echo the random number I
generated, I see it's not in the column at all!

For example, I got 4294967295 to insert correctly. But then
6406330925 gives me an error when I know it's not an existing index.
Some numbers work and some don't. I can't figure out why that is.

I've tried dropping and recreating the tables. I've even tried
myisamchk to see if the tables are corrupt. But it still happens.

Can someone help?

barand

From mySQL manual

When asked to store a value in a numeric column that is outside the column type's allowable
range, MySQL clips the value to the appropriate endpoint of the range and stores the
resulting value instead.
For example, the range of an INT column is -2147483648 to 2147483647. If you try to insert
-9999999999 into an INT column, the value is clipped to the lower endpoint of the range,
and -2147483648 is stored instead. Similarly, if you try to insert 9999999999, 2147483647
is stored instead.
If the INT column is UNSIGNED, the size of the column's range is the same but its endpoints
shift up to 0 and 4294967295. If you try to store -9999999999 and 9999999999, the values
stored in the column become 0 and 4294967296.

I think you may be bursting the bounds of the field capacity. Try generatin shorter numbers

clkao

I was trying to insert numbers outside the integer range. If I try BIGINT it should work.