Symfony sfDoctrineGuard insert-sql error 1005

Question

I've fresh isntall of Symfony 1.4 with Doctrine ORM.

I'm looking to install the plugin sfDoctrineGuard. I followed the instructions here: http://www.symfony-project.org/plugins/sfDoctrineGuardPlugin

all works fine until this step: symfony doctrine:insert-sql

when I get an error:

SQLSTATE[HY000]: General error: 1005 Can't create table 'kickboxing.#sql-2b5b_a8f' (error 150) Failing Query "ALTER TABLE profile ADD CONSTRAINT profile_sf_guard_user_id_sf_guard_user_id FOREIGN KEY (sf_guard_user_id) REFERENCES sf_guard_user(id)

It did seem to create some tables:

profile sf_guard_forgot_password sf_guard_group sf_guard_group_permission sf_guard_permission sf_guard_remember_key sf_guard_user sf_guard_user_group sf_guard_user_permission

any ideas?

UPDATE: I'm after spotting this really has nothign to do with doctrine/symfony. The issue seems to be with mysql I ran the alter command above in MySQL and of course I get same error.

For reference on that I've Debian Squeeze installed and mysql 5.

UPDATE2 : when i run

SHOW INNODB STATUS;

I get

LATEST FOREIGN KEY ERROR

110927 7:58:35 Error in foreign key constraint of table _kickboxing/#sql-2b5b_a86: FOREIGN KEY (sf_guard_user_id) REFERENCES sf_guard_user(id): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition.

mogoman · Accepted Answer · 2011-09-27T11:52:35.577

1

Looks like a mySql issue. You can get a full (better) description of the error by logging into the mysql console and running

show innodb status

then look for the LATEST FOREIGN KEY ERROR to get an idea of what went wrong

edited Sep 27 '11 at 11:52

answered Sep 27 '11 at 10:55

mogoman

2,286
24
28

Thanks, added the info to the question. I'm still unsure why this is happening. I have root access to the server so if its a mysql config issue I can change but i can't identify where issue is. – Derek Organ Sep 27 '11 at 12:06
Did you add profile to your schema.yml as a related table? If so, the sf_guard_user_id needs to be integer(5) and not integer(4) – mogoman Sep 27 '11 at 12:21
Not really - when you add foreign constraints in mysql, the data type must match exactly. sfGuard uses integer(5) for the user id, so if you link any tables in your schema they need to also be integer(5) (or simply integer) – mogoman Sep 27 '11 at 12:37
yea but the connection problem here is between profile (schema generated by the plugin) and sf_guard_user (also generated by the plugin) – Derek Organ Sep 30 '11 at 12:48

score 0 · Answer 2 · answered Jan 18 '12 at 14:41

I am using Symfony 1.4.16 and to configure sfDoctrineGuardPlugin, I followed prettyscripts.com but the schema.yml should be as follows;

Note: user_id is integer(8) thats what the size of sf_guard's id column type is.

sfGuardUserProfile:
  tableName: sf_guard_user_profile
  actAs:        { Timestampable: ~ }
  options:      { collate: utf8_unicode_ci, charset: utf8 }
  columns:
    id:         { type: integer(8), primary: true, autoincrement: true }
    user_id:    { type: integer(8), notnull: true }
    fullname:   { type: string(80) }
    email:      { type: string(100) }
  relations:
    User:
      class: sfGuardUser
      foreign: id
      local: user_id
      type: one
      onDelete: cascade
      foreignType: one
      foreignAlias: Profile

Symfony sfDoctrineGuard insert-sql error 1005

LATEST FOREIGN KEY ERROR

2 Answers2