Re: ERROR 1062: Duplicate entry '1' for key 1

[Thomas Lund]

Sorry if this mail is sent twice - problems with our mailserver.

/Thomas


Michael Widenius wrote:
> 
> >>>>> "Thomas" == Thomas Lund <tld@stripped> writes:
> 
> Thomas> Hi
> 
> Thomas> Sorry - haven't followed this thread, but I have just filed a bug report
> Thomas> on this problem. I guess you are using 3.23.4. If you show fields from
> Thomas> tablename, then your auto_increment/primary key field has NULL as
> Thomas> default - even though you state not null in create table.
> 
> Thomas> I got the same error in my application
> Thomas> ERROR 1062: Duplicate entry '1' for key 1
> 
> Thomas> when I tried to insert into the table and expecting mysql to
> Thomas> auto_increment it automatically.
> 
> Thomas> /Thomas
> 
> Hi!
> 
> It's actually ok that an auto_increment column has NULL as default.
> (NULL means in this context the next auto_increment number).
> 
> There is no known problem with auto_increment columns and LOAD DATA..
> Any change you can post a full example of this?

Hi Monty

After a little testing I still have the problem at hand - but not for
all create tables. Something is definitly wrong, but what....

Here it goes right:

$ mysql test 
mysql> create table test (id int not null auto_increment,something
varchar(10), primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (something) values ('glof');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (something) values ('glof');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (something) values ('glof');
Query OK, 1 row affected (0.00 sec)


Here it goes wrong! It looks like the index() is f*cking it up.

$ mysql test2
mysql>  create table test2 (ordid int(8) not null auto_increment, ord
varchar(50) not null, primary key (ordid), index(ord,ordid));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test2 (ordid,ord) values (NULL,'sdj');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test2 (ordid,ord) values (NULL,'dsfg');
ERROR 1062: Duplicate entry '1' for key 1
mysql> insert into test2 (ord) values ('sear');
ERROR 1062: Duplicate entry '1' for key 1
mysql> insert into test2 (ordid,ord) values (0,'dsfg');
ERROR 1062: Duplicate entry '1' for key 1
mysql> insert into test2 (ordid,ord) values ('0','dsfg');
ERROR 1062: Duplicate entry '1' for key 1

The test3 shows it to be the index()

mysql> create table test3 (ordid int(8) not null auto_increment, ord
varchar(50) not null, primary key (ordid));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test3 (ordid,ord) values ('0','dsfg');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test3 (ordid,ord) values ('0','dsfg');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test3 (ordid,ord) values ('0','dsfg');
Query OK, 1 row affected (0.00 sec)

Because after adding the index the error showes up again!

mysql> alter table test3 add index test_idx (ord,ordid);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> insert into test3 (ordid,ord) values ('0','dsfg');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test3 (ordid,ord) values ('0','dsfg');
ERROR 1062: Duplicate entry '5' for key 1


Hope this helps!

/Thomas

-- 
Carl Bro as, Internet Design, Granskoven 8, 2600 Glostrup
Direct phone  : [phone]	Gen. phone    : [phone]
Dept. fax     : [phone]	Gen. fax      : [phone]