0

I have doing an upgrade on a magento software but getting sql error on this:

ALTER TABLE `poll` MODIFY COLUMN `store_id` smallint UNSIGNED NOT NULL default '0' COMMENT 'Store id'

Which is:

SQLSTATE[HY000]: General error: 1025 Error on rename of './seedpant_dev/#sql-266d_25a3' to './seedpant_dev/poll' (errno: 150)

I have actually been having 100's of these and been going through and resolving the indexes but this one is to modify the table but in phpmyadmin i cannot work out whats what fom teh above sql and replicating manually editing in phpmyadmin - screenshot below:

enter image description here

Can anyone shed any light? really stuck and likely to have 100's more i need to do

Thanks in advance

James

James Brandon
  • 1,350
  • 3
  • 16
  • 43
  • http://stackoverflow.com/questions/16227199/mysql-errno-150 seems to imply that you cannot use `default` for foreign keys on `ALTER` statements. However that only deals with the `ON DELETE` clause with default as action so it might be completely unrelated. The error is most certainly constraint related. Can you check what constraints are on the column? – EWit Jul 10 '14 at 17:39
  • Is `store_id` referenced as a foreign in some other table? – Barmar Jul 10 '14 at 17:42
  • When I google for that error number, I get a couple of pages full of StackOverflow results, like this one: http://stackoverflow.com/questions/5948704/help-with-error-1025-hy000-error-on-rename-of-errno-150 – GolezTrol Jul 10 '14 at 17:43
  • I guess you need to drop the foreign key on store_id before altering the column.. then reattach it. Default 0 is very strange for an id reference, much better to default NULL or get rid of the default entirely. If you have any existing NULL values for the column it may be trying to convert them to 0, which could break a foreign key. – Arth Jul 10 '14 at 17:47
  • Trying all those, nothing seems to be working – James Brandon Jul 10 '14 at 18:14
  • I say something about a parent foreign key or something but tried removing from the table store_id – James Brandon Jul 10 '14 at 18:19
  • Try running a `SHOW CREATE TABLE poll`, it'll show you any foreign keys on that table.. or you can view the relations by clicking 'relations' in the 'Structure' tab. – Arth Jul 10 '14 at 18:35
  • I ended up just removing the part in the PHP upgrade script that runs that and got to the next errors which i was all good with so that worked, i know not best thing to do but the polls are not used so was not that worried. thanks for every ones help – James Brandon Jul 11 '14 at 20:08

1 Answers1

0

ended up just removing the part in the PHP upgrade script that runs that and got to the next errors which i was all good with so that worked, i know not best thing to do but the polls are not used so was not that worried. thanks for every ones help

James Brandon
  • 1,350
  • 3
  • 16
  • 43