Magento

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.

mrballcb - 24 March 2010 12:01 PM

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.

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

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

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