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" <adarsh.sha...@orkash.com> 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=mis...@poczta.fm
