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, [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
