sf_guard_user_profile id and user_id are messed up

If you use symfony’s sfDoctrineApply plugin and for some reason, the ‘id’ and ‘user_id’ in sf_guard_user_profile are messed up, you can try the below SQL.

The SQL would update sf_guard_user’s id to match the sf_guard_user_profile’s id.

Take note that this is provided that your other application tables are using sf_guard_user_profile‘s id and not the sf_guard_user‘s id.

-- drop constraints
ALTER TABLE `sf_guard_forgot_password`
  DROP FOREIGN KEY `sf_guard_forgot_password_user_id_sf_guard_user_id`;
  
ALTER TABLE `sf_guard_remember_key`
  DROP FOREIGN KEY `sf_guard_remember_key_user_id_sf_guard_user_id`;
  
ALTER TABLE `sf_guard_user_group`
  DROP FOREIGN KEY `sf_guard_user_group_user_id_sf_guard_user_id`;
  
ALTER TABLE `sf_guard_user_permission`
  DROP FOREIGN KEY `sf_guard_user_permission_user_id_sf_guard_user_id`;
  
ALTER TABLE `sf_guard_user_profile`
  DROP FOREIGN KEY `sf_guard_user_profile_user_id_sf_guard_user_id`;  

-- remove auto increment
ALTER TABLE `sf_guard_user` CHANGE `id` `id` BIGINT( 20 ) NOT NULL;

-- update id in sf_guard_user
update `sf_guard_user` set id = id - 1;

-- add auto increment
ALTER TABLE `sf_guard_user` CHANGE `id` `id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT;

-- update sf guard user profile
update `sf_guard_user_profile` set user_id = user_id - 1;

-- reset session
TRUNCATE TABLE `sf_guard_remember_key`;

-- reset user group
TRUNCATE TABLE `sf_guard_user_group`;


-- add back constraints
ALTER TABLE `sf_guard_forgot_password`
  ADD CONSTRAINT `sf_guard_forgot_password_user_id_sf_guard_user_id` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`) ON DELETE CASCADE;

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;

ALTER TABLE `sf_guard_user_group`
  ADD CONSTRAINT `sf_guard_user_group_user_id_sf_guard_user_id` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`) ON DELETE CASCADE;

ALTER TABLE `sf_guard_user_permission`
  ADD CONSTRAINT `sf_guard_user_permission_user_id_sf_guard_user_id` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`) ON DELETE CASCADE;

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`) ON DELETE CASCADE;