This may not be a very elegant solution, but you can do this just to get the inserts going again:
ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Just out of curiosity, have you DROPPED & re-created the table? -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification > -----Original Message----- > From: Ricardo Oliveira [mailto:[EMAIL PROTECTED] > Sent: Monday, May 16, 2005 5:00 PM > To: mysql@lists.mysql.com > Subject: auto_increment trouble (not the usual check || alter table > solution, though) > > 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]
