Duplicate entry '0' for key 1 error???

cemlouis

Hi,

I have a table which is like:

[MYSQL]
CREATE TABLE word (
wordno DECIMAL(6) NOT NULL,
lemma VARCHAR(70) NOT NULL
);
ALTER TABLE word ADD CONSTRAINT pk_word PRIMARY KEY(wordno);
[/MYSQL]

and I have a word.sql data file like below:

[MYSQL]
(0, 'entity')
(1, 'physical thing')
(2, 'thing')
--and goes on
[/MYSQL]

I am trying to fill the db with data and use the command:

LOAD DATA INFILE 'word.sql' INTO TABLE word;

but I am getting the error:

#1062 - Duplicate entry '0' for key 1

Why I am getting this error? Is this because of PHP or MySQL version? I am running MySQL ver 3.23.49 with PHP 4.3.6

Thank you,
Cem

herve

The format of world.sql seems a bit strange ?

You get an error because you can't input twice the same value for the 'wordno' column
since it's a primary key. I guess that you don't have twice the same value, so I bet on
some misinterpretation of the input format.

Have you read : http://dev.mysql.com/doc/refman/5.0/en/load-data.html ?

Roger_Ramjet

I notice that you are using decimal data type to store an integer. Why?

cemlouis

Actually being an integer or decimal does not change the thing.... The format is ok according to mysql docs and I don't know why it is causing an error???

cemlouis

Ok, actually I am trying to fill the right side data in the db structure that is on left which is on this page: http://www.srcf.ucam.org/~rb432/wordnet2sql.infocity.cjb.net/download.html

but couldn't accomplish???

Roger_Ramjet

Well, I've had a look at the data file from there - word-0.99.3.mysql - and you need to extend your load data infile statement with a FIELDS clause as the defaults do NOT apply to this data. It is TAB-delimited, yes, but the data strings are not enclosed by anything so at a minimum you need to include the OPTIONALLY ENCLOSED BY '' statement

Also, the line-terminator is going to depend on your platform: UNIX = \n hex 0A but Wins DOS format = \r\n hex 0D 0A. This file is not in DOS format as downloaded but that can change as soon as you open it in a text editor.

You can also specify IGNORE to overcome the duplicate key error; get at least some of the data into the table and then see what is causing the problem.

As to using DECIMAL - yes, it is OK but not efficient. Your decimal column is actually going to be stored as a string and take up 7 bytes, while an int would only take up 4 bytes. Size is important with indexes as they need to be loaded into memory to be used - smaller column size means less I/O and faster processing. Also, and this is most important, integer indexes are far faster than other data types to process. That is because they are an absolute binary number and can be compared in one operation at machine code level, while a sting will need multiple comparisons byte by byte. Now, I'm sure that mysql will do a silent type conversion from stored-string to binary-interger for a decimal index before it processes it, but that still requires processing cycles. So, integer indexes win every time.