Problem with SQL

Started by Ostro, January 18, 2017, 05:19:11 pm

Previous topic - Next topic

Ostro

Hello guys im new here nice to meet you all.

First of all i have installed my database and when i want to execute sql file  (RMX-0S SQL DATABASE) i get error

[Err] 1005 - Can't create table 'rmxosdb.users' (errno: 150)
[Err] -- SQL Database for RMX-OS
-- by Blizzard

START TRANSACTION;

-- these two are used to create the database and can be ommited in case the database already exists
CREATE DATABASE IF NOT EXISTS `rmxosdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE `rmxosdb`;

DROP TABLE IF EXISTS `save_data`;
DROP TABLE IF EXISTS `inbox`;
DROP TABLE IF EXISTS `buddy_list`;
DROP TABLE IF EXISTS `user_data`;
DROP TABLE IF EXISTS `guilds`;
DROP TABLE IF EXISTS `ips`;
DROP TABLE IF EXISTS `users`;

-- Registered Users

CREATE TABLE `users` (
   `user_id` int(10) unsigned NOT NULL auto_increment,
   `username` varchar(32) NOT NULL UNIQUE,
   `password` varchar(11) NOT NULL,
   `usergroup` int(10) NOT NULL default 0,
   `banned` tinyint(1) NOT NULL default 0,
   PRIMARY KEY (`user_id`)
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- IPs

CREATE TABLE `ips` (
   `user_id` int(10) unsigned NOT NULL,
   `ip` varchar(15) NOT NULL,
   PRIMARY KEY (`user_id`, `ip`),
   FOREIGN KEY (`user_id`) REFERENCES users(`user_id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Guilds

CREATE TABLE `guilds` (
   `guild_id` int(10) unsigned NOT NULL auto_increment,
   `leader_id` int(10) unsigned NOT NULL UNIQUE,
   `guildname` varchar(32) NOT NULL UNIQUE,
   `password` varchar(11) NOT NULL,
   PRIMARY KEY (`guild_id`),
   FOREIGN KEY (`leader_id`) REFERENCES users(`user_id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Special User Data

CREATE TABLE `user_data` (
   `user_id` int(10) unsigned NOT NULL,
   `lastlogin` DATETIME NOT NULL,
   `guild_id` int(10) unsigned default NULL,
   PRIMARY KEY (`user_id`),
   FOREIGN KEY (`user_id`) REFERENCES users(`user_id`) ON DELETE CASCADE,
   FOREIGN KEY (`guild_id`) REFERENCES guilds(`guild_id`) ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Buddy List

CREATE TABLE `buddy_list` (
   `user1_id` int(10) unsigned NOT NULL,
   `user2_id` int(10) unsigned NOT NULL,
   PRIMARY KEY (`user1_id`, `user2_id`),
   FOREIGN KEY (`user1_id`) REFERENCES users(`user_id`) ON DELETE CASCADE,
   FOREIGN KEY (`user2_id`) REFERENCES users(`user_id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- PM Inbox Data

CREATE TABLE `inbox` (
   `pm_id` int(10) unsigned NOT NULL auto_increment,
   `recipient_id` int(10) unsigned NOT NULL,
   `sendername` varchar(32) NOT NULL,
   `senddate` DATETIME NOT NULL,
   `message` text NOT NULL,
   `unread` tinyint(1) NOT NULL default 1,
   PRIMARY KEY (`pm_id`),
   FOREIGN KEY (`recipient_id`) REFERENCES users(`user_id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- Saved Data

CREATE TABLE `save_data` (
   `user_id` int(10) unsigned NOT NULL,
   `data_name` varchar(255) NOT NULL,
   `data_value` text NOT NULL,
   PRIMARY KEY (`user_id`, `data_name`),
   FOREIGN KEY (`user_id`) REFERENCES users(`user_id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

COMMIT;
[Msg] Finished - Unsuccessfully
--------------------------------------------------


Blizzard

When I googled errno 150, I found this: http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150
Try deleting the entire database and make sure to run the script only once. If it still doesn't work, try creating the database manually through the interface you use to connect to the server.
Check out Daygames and our games:

King of Booze 2      King of Booze: Never Ever
Drinking Game for Android      Never have I ever for Android
Drinking Game for iOS      Never have I ever for iOS


Quote from: winkioI do not speak to bricks, either as individuals or in wall form.

Quote from: Barney StinsonWhen I get sad, I stop being sad and be awesome instead. True story.

Ostro

can you explain me please how to add it manually

Blizzard

What are you using to connect to the database?
Check out Daygames and our games:

King of Booze 2      King of Booze: Never Ever
Drinking Game for Android      Never have I ever for Android
Drinking Game for iOS      Never have I ever for iOS


Quote from: winkioI do not speak to bricks, either as individuals or in wall form.

Quote from: Barney StinsonWhen I get sad, I stop being sad and be awesome instead. True story.


Blizzard

Then you have a very straight-forward "New Database" option available in the right-click menu.

Spoiler: ShowHide


Make sure to use "utf8_unicode_ci" for the character set.
Check out Daygames and our games:

King of Booze 2      King of Booze: Never Ever
Drinking Game for Android      Never have I ever for Android
Drinking Game for iOS      Never have I ever for iOS


Quote from: winkioI do not speak to bricks, either as individuals or in wall form.

Quote from: Barney StinsonWhen I get sad, I stop being sad and be awesome instead. True story.