Thursday, 24 June 2010

Symfony / sfGuardUser: MySQL errno: 150 Failing Query "ALTER TABLE sf_guard_user......."

I've been trying to link up my symfony project (using Doctrine) to sfGuardPlugin with profiles. However, when trying to rebuild the schema and insert sql, I encountered the following error:

SQLSTATE[HY000]: General error: 1005 Can't create table 'phoenix.#sql-740_d7' (errno: 150). Failing Query: "ALTER TABLE sf_guard_user_profile ADD CONSTRAINT sf_guard_user_profile_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id)". Failing Query: ALTER TABLE sf_guard_user_profile ADD CONSTRAINT sf_guard_user_profile_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id)


This isn't an incredibly useful error message. Turns out that it's to do with the way MySQL handles cascading constraints. To cut a long story short, there's more info in the links posted below, but basically you need to remember that "type: integer" does not mean it will be stored as INTEGER in MySQL. As a matter of fact, "type: integer" in a schema.yml file translates into a "BIGINT(20)" in MySQL - which means when you add a constraint to a normal INTEGER field, it fails.

To store an integer value in schema.yml, use "type: integer(4)". This will translate into a type of INTEGER in MySQL.

Explained in a comment I found:
Something you might be interested in, that took me quarter an hour. integer(11) in schema.yml does not result in int(11) in MySQL, but instead you have to write integer(4) in your schema.yml. integer integer int/serial integer(1) tinyint smallint/serial integer(2) smallint smallint/serial integer(3) mediumint int/serial integer(4) int int/serial integer(5) bigint bigint/bigserial However, in my case integer without a number resulted in BIGINT.


More info:
http://trac.symfony-project.org/wiki/sfGuardPluginExtraDocumentation
http://mirmodynamics.com/post/2009/04/02/SQLSTATE%5BHY000%5D%3A-General-error%3A-1005-Can-t-create-table-*-%28errno%3A-150%29 (especially see the last comment)
http://bytes.com/topic/mysql/answers/865699-cant-create-table-errno-150-foreign-key-constraints

6 comments:

Online Software said...

Nice post...Thank you for sharing..

Anonymous said...

thanks a lot man!! Im here 30 minutes strugling with this.

GR Brains said...

Thank you for sharing this post about the best techniques for Symfony Development, This is very useful for Symfony Development and Symfony Development Company. Thanks again :) Symfony Development

Juacala said...

Here's an extensive list of reasons why you get the errno: 150 error code when dealing with MySQL foreign keys (it has some other errors related to foreign keys in there as well):
http://eliacom.com/wpErrNo150.php

TalentsFromIndia said...

Nice post i was looking for it :) Joomla Developers

Joe Christian said...

hi guy plz more about to sympony development in window 8.this month all most of site advertise to symfony but i don't know.what is symfony?


Auto Finance Leads