mzakaria February 2016

ERROR 1062 (23000): Duplicate entry though entries are not duplicate

Table definition:

CREATE TABLE PositionalDataNGS ( Date DATE, Time TIME(3) , X FLOAT(5), Y FLOAT(5), D FLOAT(5) , V FLOAT(5),  OnField BOOL, GameID_GSIS INT, TeamID INT, PlayerID_GSIS CHAR(10), PRIMARY KEY(Time) );

This creates the needed table, I need to fill it with entries such as:

    Data, Time, X, Y, D, V, OnFeild, GameID, TeamID,  PlayerID_GSIS  
 2015-11-15 ,17:56:45.300 ,  93.32,  8.6,  4.57,  0.45 , true,   2015111500  , 5110  , 22-0026189 
 2015-11-15 ,17:56:45.400 ,  93.77,  8.48,  4.55,  0.47 , true,   2015111500  , 5110  , 02-0022289 
 2015-11-15 ,17:56:45.500 ,  94.23,  8.36,  4.53,  0.47 , true,   2015111500  , 5110  , 03-0026179 
 2015-11-15 ,17:56:45.600 ,  94.67,  8.23,  4.51,  0.46 , true,   2015111500  , 5110  , 30-0026180 

Clearly the time is not duplicate, but when trying to fill the csv table to to the data base:

LOAD DATA INFILE "/PositionalDataNGS_2.csv" INTO TABLE PositionalDataNGS COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;

I get this error: ERROR 1062 (23000): Duplicate entry '17:56:45.300' for key 'PRIMARY'

this is MySQL 5.6.28 so it should be able to handle time with fractions of a second. Thanks!

Answers


wchiquito February 2016

I can't reproduce the problem. In the file PositionalDataNGS_2.csv there are no duplicate values?

Test:

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.6.28                  |
+-------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `PositionalDataNGS`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `PositionalDataNGS`(
    ->   `Date` DATE,
    ->   `Time` TIME(3),
    ->   `X` FLOAT(5),
    ->   `Y` FLOAT(5),
    ->   `D` FLOAT(5),
    ->   `V` FLOAT(5),
    ->   `OnField` BOOL,
    ->   `GameID_GSIS` INT,
    ->   `TeamID` INT,
    ->   `PlayerID_GSIS` CHAR(10),
    ->   PRIMARY KEY(`Time`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `PositionalDataNGS` (
    ->   `Date`,
    ->   `Time`,
    ->   `X`,
    ->   `Y`,
    ->   `D`,
    ->   `V`,
    ->   `OnField`,
    ->   `GameID_GSIS`,
    ->   `TeamID`,
    ->   `PlayerID_GSIS`
    -> )
    -> VALUES
    -> ('2015-11-15', '17:56:45.300', 93.32, 8.6, 4.57, 0.45, true, '2015111500', 5110, '22-0026189'),
    -> ('2015-11-15', '17:56:45.400', 93.77, 8.48, 4.55, 0.47, true, '2015111500', 5110, '02-0022289'),
    -> ('2015-11-15', '17:56:45.500', 94.23, 8.36, 4.53, 0.47, true, '2015111500', 5110, '03-0026179'),
    -> ('2015-11-15', '17:56:45.600', 94.67, 8.23, 4.51, 0.46, true, '2015111500', 5110, '30-0026180');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `Date`,
    ->   `Time`,
    ->   `X`,
    ->   `Y`,
    ->   `D`,
    ->   `V`,
    ->   `OnField`,
    ->   `GameID_GSIS`,
    ->   `TeamID`,
    ->   `PlayerID_GSIS`
    -> FROM
    ->   `PositionalDataNGS`;
+------------+--------------+-------+------+------+------+---------+-- 

Post Status

Asked in February 2016
Viewed 2,782 times
Voted 6
Answered 1 times

Search




Leave an answer