Corrupt : Duplicate entry xxx for key 1
Posted by: shin soungman
Date: January 27, 2010 01:52AM
Date: January 27, 2010 01:52AM
I use mysql5.0 and MyISAM engine.
My .net program usually execute 20000 insert query in a day.
And 2400 select query in a day.
I installed this system 41pc.
6 of them was corrupted.
1. table schema
mysql> desc psdcs.log;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| INDX | int(11) | NO | PRI | NULL | auto_increment |
| DATETIME | datetime | NO | | NULL | |
| EVENT_CODE | smallint(5) unsigned | NO | | NULL | |
| NTYPE | smallint(5) unsigned | YES | | NULL | |
| UPDOWN | smallint(5) unsigned | NO | | NULL | |
| ALARM_LEVEL | smallint(5) unsigned | NO | | NULL | |
| SENDER_ID | smallint(5) unsigned | NO | | NULL | |
| RESERVED0 | smallint(5) unsigned | NO | | NULL | |
| RESERVED1 | smallint(5) unsigned | NO | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+
9 rows in set (0.03 sec)
2. After corrupt table index was 296630.
mysql> select * from PSDCS.LOG;
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
| INDX | DATETIME | EVENT_CODE | NTYPE | UPDOWN | ALARM_LEVEL | SENDER_ID | RESERVED0 | RESERVED1 |
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
(~~)
| 296628 | 2010-01-15 04:09:52 | 10 | 6 | 1 | 0 | 32 | 0 | 0 |
| 296629 | 2010-01-15 04:09:52 | 71 | 7 | 1 | 2 | 32 | 0 | 0 |
| 296630 | 2010-01-15 04:09:52 | 15 | 6 | 1 | 0 | 32 | 0 | 0 |
3. At that time, When I executed insert query, this error occurred
mysql> INSERT INTO PSDCS.LOG VALUES(0, NOW(), 15, 6, 1, 0, 32, 0, 0);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: *** NONE ***
ERROR 1062 (23000): Duplicate entry '296631' for key 1
4. So I executed check table.
mysql> check table psdcs.log;
+-----------+-------+----------+---------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+---------------------------------------------------------------------------------+
| psdcs.log | check | warning | 5 clients are using or haven't closed the table properly |
| psdcs.log | check | warning | Size of datafile is: 8014869 Should be: 8009010 |
| psdcs.log | check | error | Invalid key block position: 3045376 key block size: 1024 file_length: 3045376 |
| psdcs.log | check | error | Corrupt |
+-----------+-------+----------+---------------------------------------------------------------------------------+
4 rows in set (0.06 sec)
5. And I Repair the table.
mysql> repair table PSDCS.LOG;
+-----------+--------+----------+----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+--------+----------+----------------------------------------------+
| psdcs.LOG | repair | warning | Number of rows changed from 296630 to 296847 |
| psdcs.log | repair | status | OK |
+-----------+--------+----------+----------------------------------------------+
2 rows in set (0.28 sec)
6. After that table is repaired. but table index is increased
mysql> select * from PSDCS.LOG;
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
| INDX | DATETIME | EVENT_CODE | NTYPE | UPDOWN | ALARM_LEVEL | SENDER_ID | RESERVED0 | RESERVED1 |
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
(~생략~)
| 296629 | 2010-01-15 04:09:52 | 71 | 7 | 1 | 2 | 32 | 0 | 0 |
| 296630 | 2010-01-15 04:09:52 | 15 | 6 | 1 | 0 | 32 | 0 | 0 |
| 296631 | 2010-01-15 04:09:55 | 2 | 6 | 1 | 0 | 9 | 0 | 0 |
( ~중략~)
| 296847 | 2010-01-15 04:09:58 | 15 | 6 | 1 | 0 | 31 | 0 | 0 |
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
9. This is my error log
091029 17:39:06 [Note] D:\mysql 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.86-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server - Pro Edition (GPL)
091029 17:44:57 [Note] D:\mysql 5.0\bin\mysqld-nt: Normal shutdown
091029 17:44:57 [Note] D:\mysql 5.0\bin\mysqld-nt: Shutdown complete
091029 17:44:59 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091029 17:44:59 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091029 17:44:59 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091029 17:44:59 [Note] D:\mysql 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.86-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server - Pro Edition (GPL)
091126 15:51:40 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091126 15:51:40 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091126 15:51:40 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:37:07 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:37:07 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:37:07 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:37:08 [Note] D:\mysql 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.86-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server - Pro Edition (GPL)
100126 13:37:45 [Note] D:\mysql 5.0\bin\mysqld-nt: Normal shutdown
100126 13:37:45 [Note] D:\mysql 5.0\bin\mysqld-nt: Shutdown complete
100126 13:38:57 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:38:57 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:38:57 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:38:58 [Note] D:\mysql 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.86-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server - Pro Edition (GPL)
100126 14:15:22 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:15:22 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:15:22 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:19:01 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Sort aborted
100126 14:20:06 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:20:06 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:20:06 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:24:24 [Note] D:\mysql 5.0\bin\mysqld-nt: Normal shutdown
100126 14:24:26 [Warning] D:\mysql 5.0\bin\mysqld-nt: Forcing close of thread 20 user: 'root'
100126 14:24:26 [Note] D:\mysql 5.0\bin\mysqld-nt: Shutdown complete
8. What makes this problem?
Help me,plz.
Have a nice day.
Edited 1 time(s). Last edit at 01/27/2010 02:04AM by shin soungman.
My .net program usually execute 20000 insert query in a day.
And 2400 select query in a day.
I installed this system 41pc.
6 of them was corrupted.
1. table schema
mysql> desc psdcs.log;
+-------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+----------------+
| INDX | int(11) | NO | PRI | NULL | auto_increment |
| DATETIME | datetime | NO | | NULL | |
| EVENT_CODE | smallint(5) unsigned | NO | | NULL | |
| NTYPE | smallint(5) unsigned | YES | | NULL | |
| UPDOWN | smallint(5) unsigned | NO | | NULL | |
| ALARM_LEVEL | smallint(5) unsigned | NO | | NULL | |
| SENDER_ID | smallint(5) unsigned | NO | | NULL | |
| RESERVED0 | smallint(5) unsigned | NO | | NULL | |
| RESERVED1 | smallint(5) unsigned | NO | | NULL | |
+-------------+----------------------+------+-----+---------+----------------+
9 rows in set (0.03 sec)
2. After corrupt table index was 296630.
mysql> select * from PSDCS.LOG;
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
| INDX | DATETIME | EVENT_CODE | NTYPE | UPDOWN | ALARM_LEVEL | SENDER_ID | RESERVED0 | RESERVED1 |
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
(~~)
| 296628 | 2010-01-15 04:09:52 | 10 | 6 | 1 | 0 | 32 | 0 | 0 |
| 296629 | 2010-01-15 04:09:52 | 71 | 7 | 1 | 2 | 32 | 0 | 0 |
| 296630 | 2010-01-15 04:09:52 | 15 | 6 | 1 | 0 | 32 | 0 | 0 |
3. At that time, When I executed insert query, this error occurred
mysql> INSERT INTO PSDCS.LOG VALUES(0, NOW(), 15, 6, 1, 0, 32, 0, 0);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: *** NONE ***
ERROR 1062 (23000): Duplicate entry '296631' for key 1
4. So I executed check table.
mysql> check table psdcs.log;
+-----------+-------+----------+---------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+---------------------------------------------------------------------------------+
| psdcs.log | check | warning | 5 clients are using or haven't closed the table properly |
| psdcs.log | check | warning | Size of datafile is: 8014869 Should be: 8009010 |
| psdcs.log | check | error | Invalid key block position: 3045376 key block size: 1024 file_length: 3045376 |
| psdcs.log | check | error | Corrupt |
+-----------+-------+----------+---------------------------------------------------------------------------------+
4 rows in set (0.06 sec)
5. And I Repair the table.
mysql> repair table PSDCS.LOG;
+-----------+--------+----------+----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+--------+----------+----------------------------------------------+
| psdcs.LOG | repair | warning | Number of rows changed from 296630 to 296847 |
| psdcs.log | repair | status | OK |
+-----------+--------+----------+----------------------------------------------+
2 rows in set (0.28 sec)
6. After that table is repaired. but table index is increased
mysql> select * from PSDCS.LOG;
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
| INDX | DATETIME | EVENT_CODE | NTYPE | UPDOWN | ALARM_LEVEL | SENDER_ID | RESERVED0 | RESERVED1 |
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
(~생략~)
| 296629 | 2010-01-15 04:09:52 | 71 | 7 | 1 | 2 | 32 | 0 | 0 |
| 296630 | 2010-01-15 04:09:52 | 15 | 6 | 1 | 0 | 32 | 0 | 0 |
| 296631 | 2010-01-15 04:09:55 | 2 | 6 | 1 | 0 | 9 | 0 | 0 |
( ~중략~)
| 296847 | 2010-01-15 04:09:58 | 15 | 6 | 1 | 0 | 31 | 0 | 0 |
+--------+---------------------+------------+-------+--------+-------------+-----------+-----------+-----------+
9. This is my error log
091029 17:39:06 [Note] D:\mysql 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.86-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server - Pro Edition (GPL)
091029 17:44:57 [Note] D:\mysql 5.0\bin\mysqld-nt: Normal shutdown
091029 17:44:57 [Note] D:\mysql 5.0\bin\mysqld-nt: Shutdown complete
091029 17:44:59 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091029 17:44:59 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091029 17:44:59 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091029 17:44:59 [Note] D:\mysql 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.86-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server - Pro Edition (GPL)
091126 15:51:40 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091126 15:51:40 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
091126 15:51:40 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:37:07 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:37:07 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:37:07 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:37:08 [Note] D:\mysql 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.86-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server - Pro Edition (GPL)
100126 13:37:45 [Note] D:\mysql 5.0\bin\mysqld-nt: Normal shutdown
100126 13:37:45 [Note] D:\mysql 5.0\bin\mysqld-nt: Shutdown complete
100126 13:38:57 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:38:57 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:38:57 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 13:38:58 [Note] D:\mysql 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.86-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server - Pro Edition (GPL)
100126 14:15:22 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:15:22 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:15:22 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:19:01 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Sort aborted
100126 14:20:06 [Warning] 'db' entry 'psdcs psdcs@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:20:06 [Warning] 'db' entry 'psdcs psdcs@%' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:20:06 [Warning] 'db' entry 'psdcs hdel@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:21:40 [ERROR] D:\mysql 5.0\bin\mysqld-nt: Table '.\psdcs\log_smrt6' is marked as crashed and should be repaired
100126 14:24:24 [Note] D:\mysql 5.0\bin\mysqld-nt: Normal shutdown
100126 14:24:26 [Warning] D:\mysql 5.0\bin\mysqld-nt: Forcing close of thread 20 user: 'root'
100126 14:24:26 [Note] D:\mysql 5.0\bin\mysqld-nt: Shutdown complete
8. What makes this problem?
Help me,plz.
Have a nice day.
Edited 1 time(s). Last edit at 01/27/2010 02:04AM by shin soungman.
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.