Re: Expanding a field leading to FK violations
Hm, ok. Then one more followup question, if dropping the FKs is necessary, is there any way to figure out how long such an operation will take? SHOW PROCESSLIST usually just has "copying to tmp table" or some such, does SHOW INNODB STATUS or any other commands give insight into how long it will take? Thanks again, Waynn On Feb 19, 2008 5:11 PM, Baron Schwartz <[EMAIL PROTECTED]> wrote: > Hi Wayne, > > > On Feb 19, 2008 4:52 PM, Waynn Lue <[EMAIL PROTECTED]> wrote: > > I'm trying to change the definition of a field and getting 1025 errors > > when I try. Here's a simplified reproducible test case, on MySQL 5. > > > > CREATE TABLE `Users` ( > > `UserId` int(11) NOT NULL, > > PRIMARY KEY (`UserId`) > > ) ENGINE=InnoDB; > > > > CREATE TABLE `Actions` ( > > `ActionId` int(11) NOT NULL auto_increment, > > `FromUserId` int(11) NOT NULL, > > `ToUserId` int(11) NOT NULL, > > PRIMARY KEY (`ActionId`), > > KEY `FromUserId` (`FromUserId`), > > KEY `ToUserId` (`ToUserId`), > > CONSTRAINT `Actions_ibfk_1` FOREIGN KEY (`FromUserId`) REFERENCES > > `Users` (`UserId`), > > CONSTRAINT `Actions_ibfk_2` FOREIGN KEY (`ToUserId`) REFERENCES > > `Users` (`UserId`) > > ) ENGINE=InnoDB; > > > > mysql> ALTER TABLE Users MODIFY UserId BIGINT NOT NULL; > > ERROR 1025 (HY000): Error on rename of './test/#sql-1d11_9c0fb' to > > './test/Users' (errno: 150) > > > > This is happening regardless of whether I first do SET > > FOREIGN_KEY_CHECKS=0; or not, and whether I define it as a PK in the > > ALTER TABLE statement. One way around it would be to drop the FKs > > first, but if there's a lot of data, I'm assuming that will be > > prohibitively slow. Especially since I'm just going to put it back up > > again. > > > > Anyone have ideas on how to get the ALTER TABLE statement working? > > Unfortunately you really can't do what you're trying to do without > dropping the FK and then putting it back :-( > > Baron > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Expanding a field leading to FK violations
Hi Wayne, On Feb 19, 2008 4:52 PM, Waynn Lue <[EMAIL PROTECTED]> wrote: > I'm trying to change the definition of a field and getting 1025 errors > when I try. Here's a simplified reproducible test case, on MySQL 5. > > CREATE TABLE `Users` ( > `UserId` int(11) NOT NULL, > PRIMARY KEY (`UserId`) > ) ENGINE=InnoDB; > > CREATE TABLE `Actions` ( > `ActionId` int(11) NOT NULL auto_increment, > `FromUserId` int(11) NOT NULL, > `ToUserId` int(11) NOT NULL, > PRIMARY KEY (`ActionId`), > KEY `FromUserId` (`FromUserId`), > KEY `ToUserId` (`ToUserId`), > CONSTRAINT `Actions_ibfk_1` FOREIGN KEY (`FromUserId`) REFERENCES > `Users` (`UserId`), > CONSTRAINT `Actions_ibfk_2` FOREIGN KEY (`ToUserId`) REFERENCES > `Users` (`UserId`) > ) ENGINE=InnoDB; > > mysql> ALTER TABLE Users MODIFY UserId BIGINT NOT NULL; > ERROR 1025 (HY000): Error on rename of './test/#sql-1d11_9c0fb' to > './test/Users' (errno: 150) > > This is happening regardless of whether I first do SET > FOREIGN_KEY_CHECKS=0; or not, and whether I define it as a PK in the > ALTER TABLE statement. One way around it would be to drop the FKs > first, but if there's a lot of data, I'm assuming that will be > prohibitively slow. Especially since I'm just going to put it back up > again. > > Anyone have ideas on how to get the ALTER TABLE statement working? Unfortunately you really can't do what you're trying to do without dropping the FK and then putting it back :-( Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Expanding a field leading to FK violations
I'm trying to change the definition of a field and getting 1025 errors when I try. Here's a simplified reproducible test case, on MySQL 5. CREATE TABLE `Users` ( `UserId` int(11) NOT NULL, PRIMARY KEY (`UserId`) ) ENGINE=InnoDB; CREATE TABLE `Actions` ( `ActionId` int(11) NOT NULL auto_increment, `FromUserId` int(11) NOT NULL, `ToUserId` int(11) NOT NULL, PRIMARY KEY (`ActionId`), KEY `FromUserId` (`FromUserId`), KEY `ToUserId` (`ToUserId`), CONSTRAINT `Actions_ibfk_1` FOREIGN KEY (`FromUserId`) REFERENCES `Users` (`UserId`), CONSTRAINT `Actions_ibfk_2` FOREIGN KEY (`ToUserId`) REFERENCES `Users` (`UserId`) ) ENGINE=InnoDB; mysql> ALTER TABLE Users MODIFY UserId BIGINT NOT NULL; ERROR 1025 (HY000): Error on rename of './test/#sql-1d11_9c0fb' to './test/Users' (errno: 150) This is happening regardless of whether I first do SET FOREIGN_KEY_CHECKS=0; or not, and whether I define it as a PK in the ALTER TABLE statement. One way around it would be to drop the FKs first, but if there's a lot of data, I'm assuming that will be prohibitively slow. Especially since I'm just going to put it back up again. Anyone have ideas on how to get the ALTER TABLE statement working? Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
