mysql error: Duplicate entry '2-3' for key 1

[searain]

this table, i have field 1 and field 2 as primary keys. so combine field 1 and field 2 should be unique.

But when i run the insert sql, to set the field 1 to be '2' and field 2 to be '3', it runs through.

But if I use the

echo mysql_error()

I can see the message like this

Duplicate entry '2-3' for key 1

So I still miss to do something here.

Help please!

[Sxooter]

Ummm. So, do you already have an entry of 2 and 3 for that field?

[searain]

I have '2-2', '2-1" in the table, so mysql told me i have duplicated value on key 1.

But it accepts that sql, just when i use echo mysql_error(), it gives me that message.

[Sxooter]

Could you post a "test case"? I.e. the create table and insert statements that can get me to the same place as you are right now? That would really help troubleshoot this.

[searain]

I used mysql-cc (I know it is out of date, but my desk top is on windows me, going to updaet soon. but now i have to live with mysql-cc).

Create the table called products_to_categories.

only two fields there, categories_id, and products_id. And I set both fields to be the key through mysql-cc.

I don't know if this "create table and assign the key method" through the mysql cc the same as the sql statement like this

DROP TABLE IF EXISTS products_to_categories;
CREATE TABLE products_to_categories (
products_id int(11) NOT NULL default '0',
categories_id int(11) NOT NULL default '0',
PRIMARY KEY (products_id,categories_id),
KEY idx_cat_prod_id_zen (categories_id, products_id)
) TYPE=MyISAM;

And then I just run a sql to insert categories_id, and products_id.

I will test to see if I create table through the sql statement and then to test to see if the error still there.

[searain]

1) OK, I create the table use the following sql.
It is the same as the sql from zen cart or os-commerce.

DROP TABLE IF EXISTS products_to_categories;
CREATE TABLE products_to_categories (
products_id int(11) NOT NULL default '0',
categories_id int(11) NOT NULL default '0',
PRIMARY KEY (products_id,categories_id),
KEY idx_cat_prod_id (categories_id, products_id)
) TYPE=MyISAM;

2) And then assign $categories_id=2, and the $products_id=1 and run insert sql once. And assign $categories_id=2, and the $products_id=3, run the insert sql twice.

$sql_add_products_to_categories="INSERT INTO products_to_categories (categories_id, products_id) VALUES($categories_id, $products_id)";
$add_categories_products=mysql_query($sql_add_products_to_categories);

echo mysql_error();

3) The second time, I run the sql, I saw this message from echo mysql_error(); But the valuye '2-3' is inserted anyway.

Duplicate entry '2-3' for key 1

[Sxooter]

OK, echo out what the query is that's actually being sent to mysql to make sure it really is what you think it is.

[searain]

so the whole process is perfectly fine. all i need to do is comment out the echo mysql_error() when i finish the programming.

[Sxooter]

OH, OK. I was thinking maybe it wasn't throwing exactly what you thought it was at the database.

You should definitely get an error when trying to insert a duplicate row.

[searain]

DROP TABLE IF EXISTS products_to_categories;
CREATE TABLE products_to_categories (
products_id int(11) NOT NULL default '0',
categories_id int(11) NOT NULL default '0',
PRIMARY KEY (products_id,categories_id),
KEY idx_cat_prod_id_zen (categories_id, products_id)
) TYPE=MyISAM;

does PRIMARY KEY (products_id,categories_id) mean products_id and categories_id together to be one key?

if that is the case, '1-2', '3-2' should not be considered as duplicate row, should it?

What should I do to be right here? I used not name any keys in the table products_to_categories. But when I saw zen cart and oscommerce named these two fields as primary key, I learned from their table design and name these two fields to be key in my table too.

[Sxooter]

I just ran this through mysql 5.0.3 on my system and it behaves as expected. i.e. 1,2 and 3,2 are both inserted, and any attempt at inserting the same pair again gets an error. Again, can you give is a statement by statement sql example that does what you're seeing, outside the realm of PHP? something like

create table ...
insert into ...
insert into ...
insert into ...
MYSQL Error number...

Kind of thing? I'm thinking that what happens in the mysql monitor and what's currently happening in your PHP code are two different things. If you can't duplicate it in the mysql monitor, then it is likely that you've got some strange bug in PHP, and will need to post your PHP here as well to get help. Remember, we can't see your code / database interaction from this side, you've got to show us as much as possible to get the best help.

[searain]

Thanks!

I tried out in mysql monitor, it was fine. And as you suggested, i checked out the php again.

It is my php error, I am using a loop to insert different lanaguage records to products_description table, but for the products_to_categories table, it should be just one record for all languages, so first sql executed fine, but when loop to second time, the error happens. I moved out the sql for insert the products_to_categories out of the loop, everything is fine.

It is kind a stupid mistake of mine. And I should catch it out by myself at the first look. But i was set my mind thinking that is some of mysql knowledge i don't know. or mysql-cc tool I used has bugs.

Thanks again!

[Sxooter]

Hey, we all make mistakes. As long as you learn from them, it's all good. Glad to have helped you find the problem.