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]
