This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use Stack Overflow.

sfGuardUser and sfGuardUserProfile with Doctrine sql fail

Questions relating to sfGuardPlugin or other user management plugins

sfGuardUser and sfGuardUserProfile with Doctrine sql fail

by dvandersluys » Mon Apr 11, 2011 10:20 pm

Good-day to all,

I a trying to save some extra properties for an user, for this I am using sfGuardUser as my user plug-in, and tying to extend in my schema.yml with sfGuardUserProfile

Code: Select all
# ./config/doctrine/schema.yml
sfGuardUserProfile:
  tableName:     sf_guard_user_profile
  columns:
    user_id:  { type: integer(4), primary: true }
    address: { type: string(1023), notnull: true }
    zip: { type: string(31), notnull: true }
    city: { type: string(255), notnull: true }
    country: { type: string(2), notnull: true, default: 'NL' }
    msisdn: { type: string(20), notnull: true, default: '+31' }
  relations:
    User: { class: sfGuardUser, local: user_id, foreign: id, type: one, foreignType: one, foreignAlias: Profile }


No problems with
Code: Select all
./symfony cc
./symfony doctrine:build-model
./symfony doctrine:build-sql


Yet whenever I run
Code: Select all
./symfony doctrine:insert-sql


An sql error is returned trying to set foreign constraint
Code: Select all
SQLSTATE[HY000]: General error: 1005 Can't create table 'dbname.#sql-4d0_b8' (errno: 121). Failing Query: "ALTER TABLE sf_guard_remember_key ADD CONSTRAINT sf_guard_remember_key_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE". Failing Query: ALTER TABLE sf_guard_remember_key ADD CONSTRAINT sf_guard_remember_key_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE

Even though I never altered anything with the sf_guard_remember_key table, it says it fails setting the constraint. When looking into the database both tables exist, and the columns that are being used are identical.

After googling over the internet I found some sfGuardUser documentation, and several blogs regarding several similar issues, therefore I changed the user_id to integer(4), primary: true
http://trac.symfony-project.org/wiki/sfGuardPluginExtraDocumentation
http://thebigbyte.blogspot.com/2010/06/symfony-sfguarduser-mysql-errno-150.html
http://forum.symfony-project.org/viewtopic.php?f=16&t=29226

But nowhere a clear overview of the possibilities, or good documentation (Perhaps add Jobeet addition would be a good idea). Is there any one that can point me into to good direction, tutorial, or solution to this problem.
dvandersluys
Junior Member
 
Posts: 2
Joined: Mon Apr 11, 2011 9:55 pm

Re: sfGuardUser and sfGuardUserProfile with Doctrine sql fai

by dvandersluys » Tue Apr 12, 2011 8:27 pm

After some more fiddlings I noticed the when dropping the database first, creating an new database, there error did not occur on the first run. Second run of rebuilding how ever, gave the same problems.

After some browsing of the source, I landed in this file: ./lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/task/sfDoctrineInsertSqlTask.class.php. Where I go into Doctrine source, what it in basics comes down to is that the doctrine:insert-sql task insert the sql as generated, and silently discards the create table for tables that are already there (No create if not exists). Because own modifications are now reverted or altered in anyway. The unclear MySQL error is pointing to a duplicate foreign key.

So adding the following solves the problem for me now.
Code: Select all
./symfony doctrine:drop-db --no-confirmation
./symfony doctrine:build-db

Rember this drop's your database so all your data will be lost
dvandersluys
Junior Member
 
Posts: 2
Joined: Mon Apr 11, 2011 9:55 pm

Re: sfGuardUser and sfGuardUserProfile with Doctrine sql fai

by paulinabalk » Thu Mar 15, 2012 10:22 pm

dvandersluys wrote:
So adding the following solves the problem for me now. Law of Attraction
Code: Select all
./symfony doctrine:drop-db --no-confirmation
./symfony doctrine:build-db

Rember this drop's your database so all your data will be lost


Thanks, it really did the trick and thanks for the tip about backing up the DB.
paulinabalk
Junior Member
 
Posts: 1
Joined: Thu Mar 15, 2012 10:17 pm

Re: sfGuardUser and sfGuardUserProfile with Doctrine sql fai

by GarryPaul » Tue Sep 18, 2012 6:29 am

I have also applied the same procedure and my problem solved. It helped me to backup my database too.
GarryPaul
Junior Member
 
Posts: 1
Joined: Tue Sep 18, 2012 6:21 am