List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 25 2001 4:33pm
Subject:Re: AUTO_INCREMENT problem in table
View as plain text  
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?
>
>/Peter

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
versions.

-- 
Paul DuBois, paul@stripped
Thread
AUTO_INCREMENT problem in tablePeter Wiherkoski25 Jul
  • Re: AUTO_INCREMENT problem in tablePaul DuBois25 Jul
  • Re: AUTO_INCREMENT problem in tableStefan Hinz25 Jul
    • RE: AUTO_INCREMENT problem in tableCarsten H. Pedersen25 Jul
  • Re: AUTO_INCREMENT problem in tableryc25 Jul