Hi, with a similar structure, you can have : mysql> select * from users where uid >=262140; +--------+----------+ | uid | nickname | +--------+----------+ | 262140 | text | | 262141 | text | | 262142 | text | | 262143 | text | | 262144 | text | +--------+----------+ 5 rows in set (0.00 sec)
it's surprising that you can insert NULL in a primary key auto_increment. second, with only 10000 values, if the auto_increment reached 2147483647 this means that you have an intensive delete, or the auto_increment had been altered. you can create table toto like users, and insert data again into toto. This will reincremente between 1 and 10xxx. Then rename toto to users. Mathias Selon Ricardo Oliveira <[EMAIL PROTECTED]>: > Hi everyone, > > First time on the list, although I've been (quietly) a (not-so)long-time > quiet observer. This isn't a FAQ and I can assure > you I've tried every procedure out there (both on and off-list) to solve it. > > A couple weeks ago, I started having problems with a MyISAM table which > holds records from user profiles. It's a very simple table, with one > auto_increment field and a bunch of other field - nothing out of the > ordinary. > To those of you familiar with slashcode, it's an old version of the > users table: > > CREATE TABLE users ( > uid int(11) NOT NULL auto_increment, > nickname varchar(20) NOT NULL default '', > realemail varchar(50) NOT NULL default '', > fakeemail varchar(50) default NULL, > homepage varchar(100) default NULL, > passwd varchar(12) NOT NULL default '', > sig varchar(160) default NULL, > seclev int(11) NOT NULL default '0', > matchname varchar(20) default NULL, > banned int(11) NOT NULL default '0', > permmod int(11) NOT NULL default '0', > PRIMARY KEY (uid), > KEY login (uid,passwd,nickname), > KEY chk4user (nickname,realemail), > KEY chk4email (realemail) > ) TYPE=MyISAM PACK_KEYS=1; > > We have about 10k lines (more precisely, 10885 lines). > Our code inserts data using a "insert into users values (NULL, .....)" > statement which hasn't changed over the last few years. Recently, we > started seeing the last record with a _way_ high uid value - more > precisely, 2147483647 (which keeps new inserts from happening). > Obviously this is a problem with the auto_increment mechanism, and we > have followed all the (documented) and traditional approaches, which follow: > > * search the row with the wrong uid and correct it: > -> upon inserting a new record, its uid will be 2147483647; > > * correct the uid and modify the auto_increment value from the table: > mysql> alter table users AUTO_INCREMENT=10900; > Query OK, 10885 rows affected (0.33 sec) > Records: 10885 Duplicates: 0 Warnings: 0 > mysql> show table status like 'users'; > +-------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+ > | Name | Type | Row_format | Rows | Avg_row_length | Data_length | > Max_data_length | Index_length | Data_free | Auto_increment | > Create_time | Update_time | Check_time | > Create_options | Comment | > +-------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+ > | users | MyISAM | Dynamic | 10885 | 68 | 748604 > | 4294967295 | 1051648 | 0 | -->2147483647<-- | > 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | > pack_keys=1 | | > +-------+--------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+ > > * dump the table, make sure it doesn't have any uid above =~10k, delete > the table and restore the table (and data) from disk: > -> data is okay, select * from users where uid>11k shows ZERO records > -> insert into users values (NULL,.....) results in a new row with > uid=2147483647 > > * several combinations of CHECK TABLE, myisamchk (-r|-o|....), dump && > remore_wrong_record && restore, drop the entire database: > -> data is OKAY, select results in no records above 11k, next insert > will have uid=2147483647 > > The table fits in about 1MB on disk, and its indexes fit in about 700kB. > > Does anyone have a clue? Any help is highly appreciated. > > Best of regards, > Ricardo Oliveira > > PS: Sorry for such a long message. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
