Error with creating tables MySql - (errno: 150)

Question

I have problem with this script. Can someone help me?

ERROR 1005: Can't create table 'db_new.users_roles' (errno: 150)
SQL Statement:
CREATE TABLE `luxury`.`users_roles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`))

In the database there are tables "users" and "roles". "users_roles" is a table for ManyToMany Hibernate

this may help http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 — AVM, Dec 22 '14 at 11:33

score 0 · Answer 1 · edited Dec 22 '14 at 12:13

0

Try once like this,

CREATE TABLE IF NOT EXISTS `users_roles` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

edited Dec 22 '14 at 12:13

Vucko

20,555
10
56
107

answered Dec 22 '14 at 12:01

Mahendra Pumbhadiya

290
1
2
17

I removed the snippet because this isn't HTML. Use [sqlfiddle](http://sqlfiddle.com/). – Vucko Dec 22 '14 at 12:13
ERROR 1005 (HY000): Can't create table 'luxury.users_roles' (errno: 150) – Shiroga Dec 23 '14 at 12:14
above code is perfect, but i dont know why you getting error, once read this question-answer may be its useful for you: http://stackoverflow.com/questions/16227199/mysql-errno-150 – Mahendra Pumbhadiya Dec 23 '14 at 12:43

score 0 · Answer 2 · answered Dec 22 '14 at 12:29

Maybe try the following

check All your privileges for users.

select user,host from mysql.user;

find the privilege(s) granted to a particular MySQL account:

show grants for 'root'@'%';

Analyze your table

analyze table luxury

Optimize

optimize table luxury

Run your script again

score 0 · Answer 3 · answered Dec 23 '14 at 10:02

@Anele This is the result returned by your method:

mysql> select user,host from mysql.user;
+------------------+----------------+
| user             | host           |
+------------------+----------------+
| root             | %              |
| root             | 127.0.0.1      |
| root             | 95.110.190.185 |
| root             | ::1            |
| admin            | localhost      |
| debian-sys-maint | localhost      |
| root             | localhost      |
| root             | luxury         |
| root             | somehost       |
+------------------+----------------+
9 rows in set (0.00 sec)


mysql> show grants for 'root'@'%';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*CC06F61FF8BD3C6AD10D57E28E340FB097DDD' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> analyze table luxury;
+---------------+---------+----------+-------------------------------------+
| Table         | Op      | Msg_type | Msg_text                            |
+---------------+---------+----------+-------------------------------------+
| luxury.luxury | analyze | Error    | Table 'luxury.luxury' doesn't exist |
| luxury.luxury | analyze | status   | Operation failed                    |
+---------------+---------+----------+-------------------------------------+
2 rows in set (0.00 sec)

mysql> optimize table luxury;
+---------------+----------+----------+-------------------------------------+
| Table         | Op       | Msg_type | Msg_text                            |
+---------------+----------+----------+-------------------------------------+
| luxury.luxury | optimize | Error    | Table 'luxury.luxury' doesn't exist |
| luxury.luxury | optimize | status   | Operation failed                    |
+---------------+----------+----------+-------------------------------------+
2 rows in set (0.00 sec)