Error Code: 1005 Can't create table users (errno: 150)

Question

When I run this query:

DROP TABLE IF EXISTS `users`;

Then:

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL DEFAULT '4',
  `email` varchar(120) NOT NULL,
  `username` varchar(30) NOT NULL DEFAULT '',
  `password_hash` char(60) NOT NULL,
  `reset_hash` varchar(40) DEFAULT NULL,
  `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_ip` varchar(40) NOT NULL DEFAULT '',
  `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `reset_by` int(10) DEFAULT NULL,
  `banned` tinyint(1) NOT NULL DEFAULT '0',
  `ban_message` varchar(255) DEFAULT NULL,
  `display_name` varchar(255) DEFAULT '',
  `display_name_changed` date DEFAULT NULL,
  `timezone` char(4) NOT NULL DEFAULT 'UM6',
  `language` varchar(20) NOT NULL DEFAULT 'english',
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `activate_hash` varchar(40) NOT NULL DEFAULT '',
  `password_iterations` int(4) NOT NULL,
  `force_password_reset` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

I get error:

Error Code: 1005
Can't create table 'admin_sc.users' (errno: 150)

Checked /var/lib/mysql/admin_sc directory for permission - can create table with other name, but can't create table users

Any can help ? No any foreign keys in my query.

possible duplicate of [Error Code: 1005. Can't create table '...' (errno: 150)](http://stackoverflow.com/questions/9018584/error-code-1005-cant-create-table-errno-150) — Jens, Jul 23 '14 at 09:36
Please read [this link](http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/) or [this one](http://blog.mclaughlinsoftware.com/2010/01/30/decoding-1005-on-mysql/) — Salathiel Genese, Jul 23 '14 at 09:39

Tõnu Samuel · Answer 1 · 2014-07-23T09:42:02.520

$ perror 105
OS error code 105:  No buffer space available
$

Are you sure you drop "users", not "bf_users" ?

Also maybe just InnoDB tablespace is broken. I would try same on another machine to see if problem is repeatable. At least works for me:

MariaDB [(none)]> create database testt;
Query OK, 1 row affected (0.04 sec)

MariaDB [(none)]> use testt;
Database changed
MariaDB [testt]> CREATE TABLE `users` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `role_id` int(11) NOT NULL DEFAULT '4',
    ->   `email` varchar(120) NOT NULL,
    ->   `username` varchar(30) NOT NULL DEFAULT '',
    ->   `password_hash` char(60) NOT NULL,
    ->   `reset_hash` varchar(40) DEFAULT NULL,
    ->   `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `last_ip` varchar(40) NOT NULL DEFAULT '',
    ->   `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `deleted` tinyint(1) NOT NULL DEFAULT '0',
    ->   `reset_by` int(10) DEFAULT NULL,
    ->   `banned` tinyint(1) NOT NULL DEFAULT '0',
    ->   `ban_message` varchar(255) DEFAULT NULL,
    ->   `display_name` varchar(255) DEFAULT '',
    ->   `display_name_changed` date DEFAULT NULL,
    ->   `timezone` char(4) NOT NULL DEFAULT 'UM6',
    ->   `language` varchar(20) NOT NULL DEFAULT 'english',
    ->   `active` tinyint(1) NOT NULL DEFAULT '0',
    ->   `activate_hash` varchar(40) NOT NULL DEFAULT '',
    ->   `password_iterations` int(4) NOT NULL,
    ->   `force_password_reset` tinyint(1) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`),
    ->   KEY `email` (`email`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.33 sec)

MariaDB [testt]>

You right, i published this query with error, but i tried many possible options - don't work (((. Now i find external key from other table to table users, but not found until now. — CETb, Jul 23 '14 at 09:41

score 0 · Answer 2 · answered Jul 23 '14 at 09:42

0

I Suggest that you execute a script that drops the table "users" ( to check if it's not already exist ) and then Recreate the table

Or as Nishanthi said .. try to Create this table with a different name , it might work

answered Jul 23 '14 at 09:42

Rodrigue Abn

3
2

score -1 · Answer 3 · answered Jul 23 '14 at 09:34

-1

In my opinion,It could be because users table is reserved for some system related tables.

Either change the table name from users to something else.
Or change your drop table command to DROP TABLE IF EXISTS users and then create the table.

answered Jul 23 '14 at 09:34

ngrashia

9,869
5
43
58

I did it. No results. No file with name users.frm in directory: /var/lib/mysql/admin_sc – CETb Jul 23 '14 at 09:35
I can create table `users1` , but table `users` can't create – CETb Jul 23 '14 at 09:39
Then, it is because 'users' is reserved table. If you drop it, may be the DB itself will corrupt and so, you do not have permissions for dropping it / creating in such a name. – ngrashia Jul 23 '14 at 09:40
Try my first option provided. You can upvote and accept as answer, since it helped you – ngrashia Jul 23 '14 at 09:41
No, 'users' it not reserved. – Tõnu Samuel Jul 23 '14 at 09:43
@TõnuSamuel: I just said, It COULD BE because it is reserved. I have also given alternatives what has to be done. Why downvote? – ngrashia Jul 23 '14 at 09:46

Error Code: 1005 Can't create table users (errno: 150)

3 Answers3