// archives

MySQL

This tag is associated with 2 posts

MySQL Tip: Can’t create table ‘‘ (errno: 150) message

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.

MAC OS X Tip: Uninstalling MySQL on Mac OS X

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:

  1. cd /usr/local
  2. sudo rm mysql removes the symbolic link to the actual mysql installation. You may be prompted for your administrator’s password at this point.
  3. 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.
  4. sudo rm -rf /Library/StartupItems/MySQLCOM removes the MySQL startup option.
  5. rm -rf ~/Library/PreferencePanes/My* removes preference panes.
  6. sudo rm -rf /Library/Receipts/mysql*
  7. 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

Categories