SQL failure on upgrading

jgwilliams

Having done a search of the forums I've not found exactly this error so I thought I would post it, along with my solution, for the benefit of others.

I have just upgraded from 1.1.2 to 1.2.1. All went well except for the update to the comments table. After some investigation it turned out that the column 'imageid' in 'comments' is now called 'ownerid', but it couldn't be renamed because of a foreign key constraint. I know Transact SQL pretty well, but translating that to MySQL was a bit of a challenge. However, for anyone else who is having this trouble the following script seems to sort it out:

ALTER TABLE `photogallery`.`comments`
DROP FOREIGN KEY `comments_ibfk1`

ALTER TABLE `photogallery`.`comments`
DROP INDEX `imageid`,
CHANGE COLUMN `imageid` `ownerid` INTEGER UNSIGNED NOT NULL DEFAULT 0,
ADD INDEX `ownerid` USING BTREE(`ownerid`);

ALTER TABLE `photogallery`.`comments` ADD CONSTRAINT `comments_ibfk1` FOREIGN KEY (`ownerid`) REFERENCES `images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

sbillard

Thanks for the info. Seems there was a typo in the SQL that should have dropped this key. No need to recreate it. The new structure makes it no longer valid.