MySQL Bugs: #64166: dublicate key

Bug #64166	dublicate key
Submitted:	30 Jan 2012 15:20	Modified:	1 Mar 2012 12:59
Reporter:	Igor L	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	Server version: 5.1.60 Source distributi	OS:	Linux (Fedora 2.6.35.14-106.fc14.x86_64)
Assigned to:		CPU Architecture:	Any

Description:
don't addition record in table

How to repeat:
Exists tables:

create table records (
    id bigint not null primary key auto_increment,
    ts bigint not null,
    device integer not null,
    car integer not null,
    lat real not null,
    lng real not null,
    speed integer not null,
    course integer not null,
    hdop integer not null,
    hgeo integer not null,
    hmet integer not null,
    a1 integer not null,
    a2 integer not null,
    a3 integer not null,
    a4 integer not null,
    tsensor smallint default -32768,
    length real not null,
    enabled boolean not null default false
);

create index records0 on records(ts);
create unique index records1 on records(device,ts);
create unique index records2 on records(car,ts);

and more fill records, and process:

mysql> use gps_master
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into records (device,car) values (0, 0);
ERROR 1062 (23000): Duplicate entry '5352218' for key 'PRIMARY'
mysql> drop index records1 on records;
Query OK, 2375622 rows affected (8.77 sec)
Records: 2375622  Duplicates: 0  Warnings: 0

mysql> drop index records2 on records;
Query OK, 2375622 rows affected (5.38 sec)
Records: 2375622  Duplicates: 0  Warnings: 0

mysql> insert into records (device,car) values (0, 0);
Query OK, 1 row affected, 13 warnings (0.00 sec)

mysql> create unique index records1 on records (ts,device);
Query OK, 2375623 rows affected (8.42 sec)
Records: 2375623  Duplicates: 0  Warnings: 0

mysql> create unique index records2 on records (ts,car);
Query OK, 2375623 rows affected (13.20 sec)
Records: 2375623  Duplicates: 0  Warnings: 0

mysql> insert into records (device,car) values (0, 0);
ERROR 1062 (23000): Duplicate entry '0-0' for key 'records1'
mysql> insert into records (ts, device,car) values (1, 0, 0);
Query OK, 1 row affected, 12 warnings (0.00 sec)

why need kill indexes for addition new record ??? !!!!

web server always addition records from devices in database and for any records one error: ERROR 1062 (23000): Duplicate entry '5352218' for key 'PRIMARY'

I kill indexes and create again, and mysql-server inserted all records without errors.

Please, send the output of

show table status like 'records'\G

from your environment.

mysql> show table status like 'records'\G
*************************** 1. row ***************************
           Name: records
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 2375624
 Avg_row_length: 88
    Data_length: 209054912
Max_data_length: 24769797950537727
   Index_length: 136322048
      Data_free: 0
 Auto_increment: 5352220
    Create_time: 2012-01-30 19:05:13
    Update_time: 2012-01-30 19:05:44
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

So, this is MyISAM table. Please, send the output of:

show variables like '%pointer%';

and

myisamchk -dvvv records.MYI

mysql> show variables like '%pointer%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| myisam_data_pointer_size | 6     |
+--------------------------+-------+
1 row in set (0.00 sec)

# myisamchk -dvvv records.MYI

MyISAM file:         records.MYI
Record format:       Fixed length
Character set:       utf8_general_ci (33)
File-version:        1
Creation time:       2012-01-30 19:05:13
Status:              open,changed
Auto increment key:              1  Last value:               5352564
Data records:              2375969  Deleted blocks:                 0
Datafile parts:            2375969  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        5
Datafile length:         209085272  Keyfile length:         136339456
Max datafile length: 24769797950537726  Max keyfile length: 1125899906841599
Recordlength:                   88

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   2     8   unique  longlong                       1      7746560       1024
2   10    8   unique  longlong                       0      4161536       1024
    18    4           long                           1
3   10    8   unique  longlong                       0      4165632       1024
    22    4           long                           1

Field Start Length Nullpos Nullbit Type
1     1     1                                                         
2     2     8                                                         
3     10    8                                                         
4     18    4                                                         
5     22    4                                                         
6     26    8                                                         
7     34    8                                                         
8     42    4                                                         
9     46    4                                                         
10    50    4                                                         
11    54    4                                                         
12    58    4                                                         
13    62    4                                                         
14    66    4                                                         
15    70    4                                                         
16    74    4                                                         
17    78    2      1       2                                          
18    80    8                                                         
19    88    1

Thank you for the feedback.

I can not repeat described behavior. Please run CHECK TABLE records; to be sure it is not corrupted.

If table is not corrupted is it possible that you can upload table *frm, *MYD and *MYI files compressed, so we can check this anomaly?

Please also specify options you used when configured MySQL server.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".