Please help with error when creating a new account

[Haitashi]

Hey guys!

What does this mean?

 

1062 - Duplicate entry '1' for key 1

 

insert into address_book (customers_id, address_book_id, entry_firstname, entry_lastname, entry_street_address, entry_postcode, entry_city, entry_country_id, entry_gender, entry_company, entry_suburb, entry_zone_id, entry_state) values ('9', '1', 'Gigi', 'aVILES', '123 Strawberry Lane', '00953', 'Toa Alta', '172', 'f', 'AUtoNet', '', '0', 'PR')

 

[TEP STOP]

 

I get this error when a new user tries to create a new account.

I'm using CVS version.

 

THANKS TO EVERYONE IN ADVANCE!! :D

[logiczero]

Take a look at your address_book table. I had a bug where it had two primary keys listed (introduced in the process of upgrading, I think). In my table, I removed the 'customer_id' field being listed as a primary key (since a customer can have more than one address), and that fixed it.

 

It was a bit messy, so if you're working on a live store, I'd be very careful...

 

HTH

[Haitashi]

That solved it all right...THANX!

[fatalerror323]

I get this same error but my customer_id in address_book is not a primary key.

 

Now as i see it, from the PHP code, and from the error text, it seems like once the customer is added to the customers table, then his/her address is added to the address_book table.

 

It does so by running the following SQL:

insert into address_book (customers_id, address_book_id, entry_firstname, entry_lastname, entry_street_address, entry_postcode, entry_city, entry_country_id, entry_gender, entry_company, entry_suburb, entry_zone_id, entry_state) values ('8', '1', 'Ezequiel', 'Muns', '11 Laten Road', '2000', 'Babylon', '223', 'm', '', 'SANS KOUSI', '12', '')

 

If you look closely, the field named address_book_id is being given the value of 1 (second in the list). But hey... address_book_id is a primary key and its value is generated on the fly upon insertion. So why is it being given a value.

 

The error occurs when you already have a record in address_book with a address_book_id of 1, in which case he insertion is impossible as it would mean there'd be 2 records with the same address_book_id.

 

Does this seem right?

[justdave]

Looking at the data that's in the table, I think the dual primary key belongs there.

 

The address_book_id is not globally unique, it's unique per customer. The combination of customer+address_book_id must be unique, not either one of them individually. I think something is broken in the code that's choosing the next address_book_id to use.

[justdave]

OK, the table was missing the auto_increment, and it had a default of "1" on the address_book_id, so it was always trying to use "1" for the address_book_id, no matter what (which of course, violated the unique constraint).

 

Here's the SQL I ran to fix the increment:

 

mysql> alter table address_book change column address_book_id

-> address_book_id int(5) not null auto_increment;