Auto Increment Stopped Working

[frasernm]

Hi,

I have a problem with an auto-increment column - the auto increment number keeps inserting 32767 as the value, despite the largest value being around 500. This used to works - what's gone wrong?

I've include sample data and table specification below.

Thanks,

Fraser

mysql> select * from player order by playerid ASC limit 5;
+----------+-----------+----------+----------+
| playerid | forenames | initials | surname |
+----------+-----------+----------+----------+
| -1 | NULL | NULL | unknown |
| 1 | Fraser | FN | Murray |
| 2 | George | G | Adams |
| 3 | Edward | ED | Anderson |
| 4 | Richard | R | Andrew |
+----------+-----------+----------+----------+
10 rows in set (0.00 sec)

mysql> select * from player order by playerid DESC limit 5;
+----------+-----------+----------+--------------+
| playerid | forenames | initials | surname |
+----------+-----------+----------+--------------+
| 544 | Alan | A | Eccles |
| 543 | R | RA | Vig |
| 542 | Stuart | S | Ker |
| 541 | D | D | Raul |
| 540 | Fraser | F | Gordon |
+----------+-----------+----------+--------------+
10 rows in set (0.00 sec)

mysql> describe player;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| playerid | smallint(5) | | PRI | NULL | auto_increment |
| forenames | varchar(30) | YES | | NULL | |
| initials | varchar(10) | YES | | NULL | |
| surname | varchar(30) | | | | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into player (forenames, initials, surname) values ('X', 'X', 'X');
Query OK, 1 row affected (0.00 sec)

mysql> select * from player order by playerid DESC limit 2;
+----------+-----------+----------+---------+
| playerid | forenames | initials | surname |
+----------+-----------+----------+---------+
| 32767 | X | X | X |
| 544 | Alan | A | Eccles |
+----------+-----------+----------+---------+
2 rows in set (0.00 sec)

 

[thegman]

Defining the playerid as smallint is probably your problem with the number maxing out, with regard to the numbers jumping from 500-ish to 32000-ish, then it's probably the fact that ISAM tables (default on MySQL) do not re-use auto-increment numbers, and therefore if you have inserted and deleted records, the auto-increment field will continue to go up even though you do not have a large number of records. Replacing the smallint field with int or even bigint, will probably solve your problem.

HTH

Garry

 

[frasernm]

Hi,

Thanks for that - if I were to take a copy of the data and recreate the table - I should be able to get numbers in the 500s again then?

Thanks,

Fraser

 

[thegman]

Yes, dump the table with mysqldump, make sure all the data is in the file (not sure if it defaults to just the table structure or not), then drop the table and restore from your dump.

Garry