Recently I was trying to setup a simple database schema in MySQL and was presented with this rather cryptic message:
Can't create table 'test.user_role' (errno: 150).
After scratching my head for long time and trying many options, I finally figured out what was wrong with my SQL script.
The following script resulted in the error mentioned above.
CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `email` varchar(255) NOT NULL, `username` varchar(20) NOT NULL, `password` varchar(50) NOT NULL, `is_password_temporary` bit(1) NOT NULL, `is_enabled` bit(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email_UNIQUE` (`email`), UNIQUE KEY `username_UNIQUE` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `role` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `description` varchar(64) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `user_role` ( `user_id` int(10) NOT NULL, `role_id` int(10) NOT NULL, PRIMARY KEY (`user_id`,`role_id`), KEY `FK_ROLE_ID` (`role_id`), KEY `FK_USER_ID` (`user_id`), CONSTRAINT `FK_USER` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`), CONSTRAINT `FK_ROLE` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The user_id
column in the user_roles
table was defined as int(10)
.
This column points to the primary key id
in the user
table, which is defined as int(10) unsigned
.
This mismatch in the datatype resulted in the error mentioned above.
The following change in the definition of user_roles
table fixed the issue.
CREATE TABLE `user_role` ( `user_id` int(10) unsigned NOT NULL, `role_id` int(10) unsigned NOT NULL, PRIMARY KEY (`user_id`,`role_id`), KEY `FK_ROLE_ID` (`role_id`), KEY `FK_USER_ID` (`user_id`), CONSTRAINT `FK_USER` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`), CONSTRAINT `FK_ROLE` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I wish the error message was little more user-friendly.
Uninstalling MySQL on Mac OS X involves manually removing following directories and files from your drive.
Assuming your MySQL is installed in /usr/local
, perform the following steps:
cd /usr/local
sudo rm mysql
removes the symbolic link to the actual mysql installation. You may be prompted for your administrator’s password at this point.sudo rm -rf mysql-5.1.47-osx10.6-x86/
removes the actual mysql installation. You may have a different version of mysql installed on your system.sudo rm -rf /Library/StartupItems/MySQLCOM
removes the MySQL startup option.rm -rf ~/Library/PreferencePanes/My*
removes preference panes.sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
I was hoping for a much simpler process for cleanly uninstalling MySQL from my system.
Thanks to Rob Allen for this tip: Rob Allen’s DevNotes