Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Duplicate key and foreign key name
 
mrballcb
Jr. Member
 
Total Posts:  6
Joined:  2008-05-04
 

We’re upgrading from 1.1.8 to 1.3.2.4.  We copied data from a live site running 1.1.8 to our test system running 1.3.2.4.  The upgrade-in-place begins but fails with:
ERROR 1005 (HY000): Can’t create table ‘magento_ws_39045.#sql-482f_201’ (errno: 121)

The command it fails on is:
ALTER TABLE `sales_flat_quote` ADD CONSTRAINT `FK_SALES_QUOTE_STORE` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE;

The table already has a KEY named FK_SALES_QUOTE_STORE:
KEY `FK_SALES_QUOTE_STORE` (`store_id`),

...and the database won’t let it create a foreign key with the same name.  What’s really odd, is that we have two other environments where each database server DOES let you create this foreign key.  I have verified that the table is in fact InnoDB on all 3 servers.  Has anybody ever seen this before?

When I run the ALTER TABLE command from the mysql commandline, it gives the same error, so it is the mysql server doing it, thus does not appear to be some php issue.

We’re in the process right now of reloading the database (it is a test system after all) to bring it to a known starting point.  If it’s still failing the upgrade after restoring the data, then I’m not sure what to check next.  If anybody has any suggestions, I’d be most appreciative.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mrballcb
Jr. Member
 
Total Posts:  6
Joined:  2008-05-04
 
mrballcb - 24 March 2010 11:01 AM

We’re upgrading from 1.1.8 to 1.3.2.4.  We copied data from a live site running 1.1.8 to our test system running 1.3.2.4.  The upgrade-in-place begins but fails with:
ERROR 1005 (HY000): Can’t create table ‘magento_ws_39045.#sql-482f_201’ (errno: 121)

The command it fails on is:
ALTER TABLE `sales_flat_quote` ADD CONSTRAINT `FK_SALES_QUOTE_STORE` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE;

The table already has a KEY named FK_SALES_QUOTE_STORE:
KEY `FK_SALES_QUOTE_STORE` (`store_id`),

...and the database won’t let it create a foreign key with the same name.  What’s really odd, is that we have two other environments where each database server DOES let you create this foreign key.  I have verified that the table is in fact InnoDB on all 3 servers.  Has anybody ever seen this before?

When I run the ALTER TABLE command from the mysql commandline, it gives the same error, so it is the mysql server doing it, thus does not appear to be some php issue.

We’re in the process right now of reloading the database (it is a test system after all) to bring it to a known starting point.  If it’s still failing the upgrade after restoring the data, then I’m not sure what to check next.  If anybody has any suggestions, I’d be most appreciative.

 
Magento Community Magento Community
Magento Community
Magento Community
 
aminut
Member
 
Total Posts:  35
Joined:  2009-08-10
 

FOREIGN KEY Error:
I have had the same issue of FOREIGN_KEY and couldn’t find a solution.
Thought this post will help others.
After I’ve rebuilt the database I found that the when backing up the database through PhpMyAdmin you should disable
tick the line “Disable foreign key checks” on the right hand side under option and tick the sql on the left hand side under export.

However I have compared between the two files afterwards.
If you accidentally have exported the DB file
without ticking the line “Disable foreign key checks” and you get errors when trying to import
the sql file through PhpMyAdmin do the following.

1. Open the sql file with EditPadLite editor.

After the following lines:
-- Host: localhost
-- Generation Time: Apr 30, 2010 at 08:27 AM
-- Server version: 5.1.30
-- PHP Version: 5.2.4

Add the line:
SET FOREIGN_KEY_CHECKS=0;

2. scroll down and add the following at last:

SET FOREIGN_KEY_CHECKS=1;

In the middle of the sql you wil see line looks like:
“ADD CONSTRAINT `FK_admin_rule` FOREIGN KEY......” and so on.
don’t touch them

3. Save the file.
4. Import

Good luck

 
Magento Community Magento Community
Magento Community
Magento Community
 
playm77
Jr. Member
 
Total Posts:  1
Joined:  2010-05-03
 

Trying to create a table, and getting something like this?

ERROR 1005 (HY000): Can’t create table ‘<db>.<table>’ (errno: 121)

Discovered that perror 121 says this?

MySQL error code 121: Duplicate key on write or update

Really confused how you might get a duplicate key error while creating a table?

If the table you’re trying to create includes a foreign key constraint, and you’ve provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:

SELECT
constraint_name,
table_name
FROM
information_schema.table_constraints
WHERE
constraint_type = ‘FOREIGN KEY’
AND table_schema = DATABASE()
ORDER BY
constraint_name;

(If you’re still on 4.1 or earlier,

mysqldump --no-data yourDbName | grep CONSTRAINT

to get a similar list)

Thanks to [raymond] on Freenode.

Ford Windstar Parts

 
Magento Community Magento Community
Magento Community
Magento Community
 
Shaun Ohagan
Member
 
Total Posts:  34
Joined:  2010-02-21
UK
 

I migrated over a magento 1.4 database onto a new server and unzipped a 1.5.0.1 release.  Running up admin to do an auto-update gave

Base table or view already exists: 1050 Table ‘core_resource’ already exists.  The sql script used to build the db was exported using phpmyadmin.  I added the disable code for foreign key checks but still got the errors.

The answer in the end for me was that the original 1.4 installation specified that magento use a table name prefix.  Therefore all my table names in the sql file had this prefix.  I removed them all and created new database and the 1.5 admin screen came straight up.

Thought that may help someone else that struggles with this endless struggle to upgrade magento without pain.

Shaun O’Hagan

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top