At 4:47 PM +0200 7/25/01, Peter Wiherkoski wrote:
>Hi, got a problem:
>My table (part of it) looks like this:
>CREATE TABLE cust (
>id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>name CHAR(30) NOT NULL,
>PRIMARY KEY (id)
>When i type
>INSERT INTO cust VALUES(NULL,"Peter");
>the value of id will increase by 1 every time i insert a record.
>By "misstake" i typed
>INSERT INTO cust VALUES(200000,"Maria");
>the id (200000) then was "downsized" to the top limit of SMALLINT - 65535.
>Next time i type
>INSERT INTO cust VALUES(NULL,"Joe");
>the AUTO_INCREMENT tries to increase id by 1, but it cant because
>AUTO_INCREMENT for id starts with 65535 and cant go any higher.
>Even if i delete the record where id=65535
>DELETE FROM cust WHERE id=65535
>AUTO_INCREMENT starts with 65535 and i cant add any more records.
>How do i get out of this mess?
Reset the sequence value down:
ALTER TABLE cust AUTO_INCREMENT = 0
Then it will revert to using the smallest unused value.
I'm assuming you're using MySQL 3.23.xx; this won't work in earlier
Paul DuBois, paul@stripped