CODEWITHSUNDEEP
mysqlsymfonydoctrine
Michael Emerson
Michael Emerson

Reputation: 1813

SQLSTATE General Error 1005 Can't create table '#sql-3d63_ca5dc9' (errno: 150)

I have a Symfony CRM project using Doctrine that I'm currently moving over to a new server, the CRM hooks up to OpenCart 2.

A few of my tables have foreign keys that allow me to reference some of the OpenCart tables from my bespoke ones in my project, such as a product id to the product table and customer id to the customer table etc.

However, when I try to run doctrine:schema:update --force I get the following error:

An exception occurred while executing 'ALTER TABLE project ADD CONSTRAINT FK_2FB3D0EE4584665A FOREIGN KEY (product_id) REFERENCES oc73_product (product_id)':

SQLSTATE[HY000]: General error: 1005 Can't create table 'oc2017_myshop.#sql-3d63_ca5dc9' (errno: 150)

The two statements it throws this error on are as follows:

ALTER TABLE project ADD CONSTRAINT FK_2FB3D0EE4584665A FOREIGN KEY (product_id) REFERENCES oc73_product (product_id);
ALTER TABLE project ADD CONSTRAINT FK_2FB3D0EEB18AFA7E FOREIGN KEY (stageName) REFERENCES stage (id);

I don't quite understand what the above error means, since I'm not creating a table called sql-3d63_ca5dc9? Can anyone shed any light on this?

If anymore info is needed in order to help with this let me know and I will include.

Upvotes: 0

Views: 1158

Answers (1)

spencer7593
spencer7593

Reputation: 108530

The error message is telling us that a foreign key constraint isn't correctly formed.

Either there's a datatype mismatch of the foreign key column and the referenced column.

Or there's not an index defined on the referenced table with the referenced column as a leading column.

Or there's rows in the project table that don't match rows in the referenced table. That is, the product_id column contains values that don't appear in the oc73_product table.

The error message doesn't distinguish between those possibilities; it's just telling us there's a problem with the foreign key.

The table name #sql-3d63_ca5dc is an intermediate name. MySQL will create a working copy of the table when the ALTER TABLE is running, and then will swap the name of the existing table onto the new copy.

For debugging this,

compare the datatypes of the product_id column in project and oc73_product

SHOW CREATE TABLE project ;
SHOW CREATE TABLE oc73_product ;

The datatypes have to be an exact match.

Verify that oc73_product has a KEY with product_id as the leading column. This can be the PRIMARY KEY, a UNIQUE KEY, or I think MySQL even lets you get away with just a KEY.

Verify that there are no rows in project that violate the constraint. There's rows that violate the foreign key constraint if this query returns any rows:

SELECT j.product_id
  FROM project j
  LEFT
  JOIN oc73_product d
    ON d.product_id = j.product_id
 WHERE d.product_id IS NULL

The result from the query using an anti-join pattern (above) is equivalent to the result from:

SELECT j.product_id
  FROM project j
 WHERE NOT EXISTS ( SELECT 1
                      FROM oc73_product d
                     WHERE d.product_id = j.product_id
                  )

FOLLOWUP

As @Shadow mentions in a comment, the output of SHOW ENGINE INNODB STATUS may include a section like this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2017-03-27 14:14:14 7fce73f9d700 Error in foreign key constraint creation
  for table `db`.`table_name`.

with details about the error condition that was encountered. The InnoDB engine knows more about the error than he is telling MySQL. MySQL is just reporting "InnoDB reported error 150", without any details of why InnoDB is reporting that error.

Upvotes: 1

Related Questions