ERROR 1062 (23000): Duplicate entry "31592" for key "PRIMARY"

Dear all,

I have a login table in the database whose definition is as :

CREATE TABLE `login` (
`log_id` int(4) NOT NULL AUTO_INCREMENT,
`user_id` int(4) DEFAULT NULL,
`log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`log_status` int(4) DEFAULT NULL,
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=31592 DEFAULT CHARSET=latin1

Today don't know why below error occurs when i am going insert some
data in it :

mysql> insert into login(user_id,log_status) values(2,1);
ERROR 1062 (23000): Duplicate entry '31592' for key 'PRIMARY'

I check the latest entries as :

mysql> select * from login order by log_id desc limit 10;
+--------+---------+---------------------+------------+
| log_id | user_id | log_time | log_status |
+--------+---------+---------------------+------------+
| 31591 | 2 | 2011-04-27 18:00:07 | 0 |
| 31590 | 25 | 2011-04-27 17:55:25 | 0 |
| 31589 | 40 | 2011-04-27 15:16:25 | 0 |
| 31588 | 18 | 2011-04-27 14:40:47 | 0 |
| 31587 | 30 | 2011-04-27 09:46:56 | 1 |
| 31586 | 17 | 2011-04-27 09:25:29 | 1 |
| 31585 | 8 | 2011-04-27 09:25:22 | 1 |
| 31584 | 57 | 2011-04-27 09:16:09 | 1 |
| 31583 | 40 | 2011-04-27 09:15:43 | 1 |
| 31582 | 59 | 2011-04-27 09:14:01 | 1 |
+--------+---------+---------------------+------------+
10 rows in set (0.00 sec)

I don't know why it refuses to insert new row in that table yet it is
auto-increment mentioned in schema definition

I researched a lot but donot find any thing about it. Please guide me
how to trouble shoot it.

Here is some useful information that might help :

mysql> select count(*) from login;
+----------+
| count(*) |
+----------+
| 31581 |
+----------+
1 row in set (0.00 sec)

mysql> insert into login(user_id,log_status) values(2,1);
ERROR 1062 (23000): Duplicate entry '31592' for key 'PRIMARY'

mysql> select max(log_id) from login;
+-------------+
| max(log_id) |
+-------------+
| 31591 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(log_id) from login;
+-------------+
| min(log_id) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)

mysql>

mysql> select * from login where log_id=31592;
Empty set (0.00 sec)

Don't know how things got wrong. Please guide


Thanks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: ERROR 1062 (23000): Duplicate entry "31592" for key "PRIMARY"

--------------060507080302060404050801
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Thanks , but there is no trigger on tables.

Even I solved the problem after googling a link but cannot understand
below mentioned things :- Please have some comments on them

misiaq wrote:
> Any triggers on this table..?
>
> "Adarsh Sharma" pisze:
>
>> Dear all,
>>
>> I have a login table in the database whose definition is as :
>>
>> CREATE TABLE `login` (
>> `log_id` int(4) NOT NULL AUTO_INCREMENT,
>> `user_id` int(4) DEFAULT NULL,
>> `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
>> `log_status` int(4) DEFAULT NULL,
>> PRIMARY KEY (`log_id`),
>> KEY `user_id` (`user_id`)
>> ) ENGINE=MyISAM AUTO_INCREMENT=31592 DEFAULT CHARSET=latin1
>>
>> Today don't know why below error occurs when i am going insert some
>> data in it :
>>
>> mysql> insert into login(user_id,log_status) values(2,1);
>> ERROR 1062 (23000): Duplicate entry '31592' for key 'PRIMARY'
>>
>> I check the latest entries as :
>>
>> mysql> select * from login order by log_id desc limit 10;
>> +--------+---------+---------------------+------------+
>> | log_id | user_id | log_time | log_status |
>> +--------+---------+---------------------+------------+
>> | 31591 | 2 | 2011-04-27 18:00:07 | 0 |
>> | 31590 | 25 | 2011-04-27 17:55:25 | 0 |
>> | 31589 | 40 | 2011-04-27 15:16:25 | 0 |
>> | 31588 | 18 | 2011-04-27 14:40:47 | 0 |
>> | 31587 | 30 | 2011-04-27 09:46:56 | 1 |
>> | 31586 | 17 | 2011-04-27 09:25:29 | 1 |
>> | 31585 | 8 | 2011-04-27 09:25:22 | 1 |
>> | 31584 | 57 | 2011-04-27 09:16:09 | 1 |
>> | 31583 | 40 | 2011-04-27 09:15:43 | 1 |
>> | 31582 | 59 | 2011-04-27 09:14:01 | 1 |
>> +--------+---------+---------------------+------------+
>> 10 rows in set (0.00 sec)
>>
>> I don't know why it refuses to insert new row in that table yet it is
>> auto-increment mentioned in schema definition
>>
>> I researched a lot but donot find any thing about it. Please guide me
>> how to trouble shoot it.
>>
>> Here is some useful information that might help :
>>
>>
************************************************************ ***************************************
>> mysql> select count(*) from login;
>> +----------+
>> | count(*) |
>> +----------+
>> | 31581 |
>> +----------+
>> 1 row in set (0.00 sec)
>>
>> mysql> insert into login(user_id,log_status) values(2,1);
>> ERROR 1062 (23000): Duplicate entry '31592' for key 'PRIMARY'
>>
>> mysql> select max(log_id) from login;
>> +-------------+
>> | max(log_id) |
>> +-------------+
>> | 31591 |
>> +-------------+
>> 1 row in set (0.00 sec)
>>
************************************************************ ********************************************

It means there are 31581 rows but max(log_id suggests 10 more , how this
could be possible, may be some rows deleted.

I solved the error by repairing the table

> repair table login;
mysql> repair table login;
+------------------+--------+----------+-------------------- ------------------------+
| Table | Op | Msg_type |
Msg_text |
+------------------+--------+----------+-------------------- ------------------------+
| attendance.login | repair | warning | Number of rows changed from
31581 to 31583 |
| attendance.login | repair | status |
OK |
+------------------+--------+----------+-------------------- ------------------------+
2 rows in set (0.29 sec)

Then all insert statements works .

Can u please explain the root cause & solution too.

Thanks
>> mysql> select min(log_id) from login;
>> +-------------+
>> | min(log_id) |
>> +-------------+
>> | 1 |
>> +-------------+
>> 1 row in set (0.00 sec)
>>
>> mysql>
>>
>> mysql> select * from login where log_id=31592;
>> Empty set (0.00 sec)
>>
>> Don't know how things got wrong. Please guide
>>
>>
>> Thanks
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=misiaq@poczta.fm
>>
>>
>>
>
>


--------------060507080302060404050801--

Re: ERROR 1062 (23000): Duplicate entry "31592" for key "PRIMARY"

Corrupted table and / or index.=20
A number of reasons could cause this issue:
http://dev.mysql.com/doc/refman/5.1/en/corrupted-myisam-tabl es.html

Regards,
m


"Adarsh Sharma" pisze:
> Thanks , but there is no trigger on tables.
>=20
> Even I solved the problem after googling a link but cannot understand=20
> below mentioned things :- Please have some comments on them
>=20

(... cut ...)

>=20
> It means there are 31581 rows but max(log_id suggests 10 more , how this=20
> could be possible, may be some rows deleted.
>=20
> I solved the error by repairing the table
>=20
> > repair table login;
> mysql> repair table login;
> +------------------+--------+----------+-------------------- -------------=
-----------+
> | Table | Op | Msg_type |=20
> Msg_text |
> +------------------+--------+----------+-------------------- -------------=
-----------+
> | attendance.login | repair | warning | Number of rows changed from=20
> 31581 to 31583 |
> | attendance.login | repair | status |=20
> OK |
> +------------------+--------+----------+-------------------- -------------=
-----------+
> 2 rows in set (0.29 sec)
>=20
> Then all insert statements works .
>=20
> Can u please explain the root cause & solution too.
>=20
> Thanks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg