• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

error importing database into phpmyadmin

G

George00

propro
Joined
Jan 11, 2014
Messages
163
Solutions
1
Reaction score
10
Location
propro
trying to import schemas.mysql
Code: 
2 errors were found during analysis.

Unexpected beginning of statement. (near "EATE" at position 0)
Unrecognized statement type. (near "TABLE" at position 5)
SQL query:

EATE TABLE `groups` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL COMMENT 'group name', `flags` BIGINT UNSIGNED NOT NULL DEFAULT 0, `access` INT NOT NULL DEFAULT 0, `violation` INT NOT NULL DEFAULT 0, `maxdepotitems` INT NOT NULL, `maxviplist` INT NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EATE TABLE `groups` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARC' at line 1

seems to be nothing wrong with the file i've taken a look. what might be the problem?
 
Sort by date Sort by votes
Code: 
CREATE TABLE `groups` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'group name',

    `flags` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `access` INT NOT NULL DEFAULT 0,
    `violation` INT NOT NULL DEFAULT 0,
    `maxdepotitems` INT NOT NULL,
    `maxviplist` INT NOT NULL,

    PRIMARY KEY (`id`)
) ENGINE = InnoDB;
INSERT INTO `groups` VALUES (1, 'Player', 0, 0, 0, 2000, 100),(2, 'Tutor', 16777216, 0, 0, 2000, 100),(3, 'Sennior Tutor', 274894684160, 0, 0, 2000, 100),(4, 'Community Manager', 69681547968463, 2, 0, 1000, 100),(5, 'Game Master', 69681547968463, 2, 0, 1000, 100),(6, 'GOD/OWNER', 57171953819640, 3, 0, 2000, 100);

CREATE TABLE `accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,

    `password` VARCHAR(255) NOT NULL/* VARCHAR(32) NOT NULL COMMENT 'MD5'*//* VARCHAR(40) NOT NULL COMMENT 'SHA1'*/,
    `email` VARCHAR(255) NOT NULL DEFAULT '',
    `premend` INT UNSIGNED NOT NULL DEFAULT 0,
    `blocked` TINYINT(1) NOT NULL DEFAULT FALSE,
    `warnings` INT NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    UNIQUE (`name`)
) ENGINE = InnoDB;
INSERT INTO `accounts` VALUES (1, 'tibia', 'tibia', '', 0, 0, 0);

CREATE TABLE `players` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,
    `group_id` INT UNSIGNED NOT NULL COMMENT 'users group',

    `sex` INT UNSIGNED NOT NULL DEFAULT 0,
    `vocation` INT UNSIGNED NOT NULL DEFAULT 0,
    `experience` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `level` INT UNSIGNED NOT NULL DEFAULT 1,
    `maglevel` INT UNSIGNED NOT NULL DEFAULT 0,
    `health` INT NOT NULL DEFAULT 100,
    `healthmax` INT NOT NULL DEFAULT 100,
    `mana` INT NOT NULL DEFAULT 100,
    `manamax` INT NOT NULL DEFAULT 100,
    `manaspent` INT UNSIGNED NOT NULL DEFAULT 0,
    `soul` INT UNSIGNED NOT NULL DEFAULT 0,
    `direction` INT UNSIGNED NOT NULL DEFAULT 0,
    `lookbody` INT UNSIGNED NOT NULL DEFAULT 10,
    `lookfeet` INT UNSIGNED NOT NULL DEFAULT 10,
    `lookhead` INT UNSIGNED NOT NULL DEFAULT 10,
    `looklegs` INT UNSIGNED NOT NULL DEFAULT 10,
    `looktype` INT UNSIGNED NOT NULL DEFAULT 136,
    `lookaddons` INT UNSIGNED NOT NULL DEFAULT 0,
    `posx` INT NOT NULL DEFAULT 0,
    `posy` INT NOT NULL DEFAULT 0,
    `posz` INT NOT NULL DEFAULT 0,
    `cap` INT NOT NULL DEFAULT 0,
    `lastlogin` INT UNSIGNED NOT NULL DEFAULT 0,
    `lastlogout` INT UNSIGNED NOT NULL DEFAULT 0,
    `lastip` INT UNSIGNED NOT NULL DEFAULT 0,
    `save` TINYINT(1) NOT NULL DEFAULT TRUE,
    `conditions` BLOB NOT NULL COMMENT 'drunk, poisoned etc',
    `skull_type` INT NOT NULL DEFAULT 0,
    `skull_time` INT UNSIGNED NOT NULL DEFAULT 0,
    `loss_experience` INT NOT NULL DEFAULT 100,
    `loss_mana` INT NOT NULL DEFAULT 100,
    `loss_skills` INT NOT NULL DEFAULT 100,
    `loss_items` INT NOT NULL DEFAULT 10,
    `loss_containers` INT NOT NULL DEFAULT 100,
    `town_id` INT NOT NULL COMMENT 'old masterpos, temple spawn point position',
    `balance` INT NOT NULL DEFAULT 0 COMMENT 'money balance of the player for houses paying',
    `stamina` INT NOT NULL DEFAULT 151200000 COMMENT 'player stamina in milliseconds',
    `online` TINYINT(1) NOT NULL DEFAULT 0,
    `rank_id` INT NOT NULL COMMENT 'only if you use __OLD_GUILD_SYSTEM__',
    `guildnick` VARCHAR(255) NOT NULL COMMENT 'only if you use __OLD_GUILD_SYSTEM__',

    PRIMARY KEY (`id`),
    UNIQUE (`name`),
    KEY (`online`),
    FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
) ENGINE = InnoDB;

CREATE TABLE `guilds` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` VARCHAR(255) NOT NULL,
    `owner_id` INT UNSIGNED NOT NULL,
    `creationdate` INT NOT NULL,

    PRIMARY KEY (`id`),
    UNIQUE (`name`),
    FOREIGN KEY (`owner_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `guild_ranks` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `guild_id` INT UNSIGNED NOT NULL COMMENT 'guild',

    `name` VARCHAR(255) NOT NULL COMMENT 'rank name',
    `level` INT NOT NULL COMMENT 'rank level - leader, vice leader, member, maybe something else',

    PRIMARY KEY (`id`),
    FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `guild_members` (
    `player_id` INT UNSIGNED NOT NULL COMMENT 'if you doesnt use new guild system you are free to delete this table',
    `rank_id` INT UNSIGNED NOT NULL COMMENT 'a rank which belongs to certain guild',

    `nick` VARCHAR(255) NOT NULL DEFAULT '',

    UNIQUE (`player_id`),
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`rank_id`) REFERENCES `guild_ranks` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `guild_invites` (
    `player_id` INT UNSIGNED NOT NULL,
    `guild_id` INT UNSIGNED NOT NULL COMMENT 'guild',
   
    UNIQUE (`player_id`),
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `guild_wars` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `guild_id` INT UNSIGNED NOT NULL COMMENT 'the guild which declared the war',
    `opponent_id` INT UNSIGNED NOT NULL COMMENT 'the enemy guild at war',

    `frag_limit` INT UNSIGNED NOT NULL DEFAULT 10 COMMENT 'kills needed to win the war',
    `declaration_date` INT UNSIGNED NOT NULL,
    `end_date` INT UNSIGNED NOT NULL,
    `guild_fee` INT UNSIGNED NOT NULL DEFAULT 1000 COMMENT 'amount of money the guild has to pay if loses the war',
    `opponent_fee` INT UNSIGNED NOT NULL DEFAULT 1000 COMMENT 'amount of money the enemy guild has to pay if loses the war',
    `guild_frags` INT UNSIGNED NOT NULL DEFAULT 0,
    `opponent_frags` INT UNSIGNED NOT NULL DEFAULT 0,
    `comment` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'the guild leader can leave a message for the other guild',
    `status` INT NOT NULL DEFAULT 0 COMMENT '-1 -> will be ignored (finished or unaccepted) 0 -> to be started 1 -> started/not finished',

    PRIMARY KEY (`id`),
    FOREIGN KEY (`guild_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`opponent_id`) REFERENCES `guilds` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_viplist` (
    `player_id` INT UNSIGNED NOT NULL COMMENT 'id of player whose viplist entry it is',
    `vip_id` INT UNSIGNED NOT NULL COMMENT 'id of target player of viplist entry',

    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`vip_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_spells` (
    `player_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,

    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_storage` (
    `player_id` INT UNSIGNED NOT NULL,
    `key` INT NOT NULL,
    `value` INT NOT NULL,

    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_skills` (
    `player_id` INT UNSIGNED NOT NULL,

    `skillid` INT UNSIGNED NOT NULL,
    `value` INT UNSIGNED NOT NULL DEFAULT 0,
    `count` INT UNSIGNED NOT NULL DEFAULT 0,

    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_items` (
    `player_id` INT UNSIGNED NOT NULL,
    `sid` INT NOT NULL,
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB COMMENT 'replaces unique_id, action_id, text, special_desc',

    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    UNIQUE (`player_id`, `sid`)
) ENGINE = InnoDB;

CREATE TABLE `houses` (
    `id` INT UNSIGNED NOT NULL,
    `townid` INT UNSIGNED NOT NULL DEFAULT 0,

    `name` VARCHAR(100) NOT NULL,
    `rent` INT UNSIGNED NOT NULL DEFAULT 0,
    `guildhall` TINYINT(1) NOT NULL DEFAULT 0,
    `tiles` INT UNSIGNED NOT NULL DEFAULT 0,
    `doors` INT UNSIGNED NOT NULL DEFAULT 0,
    `beds` INT UNSIGNED NOT NULL DEFAULT 0,
    `owner` INT NOT NULL DEFAULT 0,
    `paid` INT UNSIGNED NOT NULL DEFAULT 0,
    `clear` TINYINT(1) NOT NULL DEFAULT 0,
    `warnings` INT NOT NULL DEFAULT 0,
    `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `house_auctions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `house_id` INT UNSIGNED NOT NULL,
    `player_id` INT UNSIGNED NOT NULL,

    `bid` INT UNSIGNED NOT NULL DEFAULT 0,
    `limit` INT UNSIGNED NOT NULL DEFAULT 0,
    `endtime` INT UNSIGNED NOT NULL DEFAULT 0,

    FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `house_lists` (
    `house_id` INT UNSIGNED NOT NULL,
    `listid` INT NOT NULL,
    `list` TEXT NOT NULL,

    FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `bans` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` INT NOT NULL COMMENT 'this field defines if its ip, account, player, or any else ban',
    `value` INT UNSIGNED NOT NULL COMMENT 'ip, player guid, account number',
    `param` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'mask',
    `active` TINYINT(1) NOT NULL DEFAULT TRUE,
    `expires` INT NOT NULL,
    `added` INT UNSIGNED NOT NULL,
    `admin_id` INT UNSIGNED,
    `comment` VARCHAR(1024) NOT NULL DEFAULT '',
    `reason` INT UNSIGNED NOT NULL DEFAULT 0,
    `action` INT UNSIGNED NOT NULL DEFAULT 0,
    `statement` VARCHAR(255) NOT NULL DEFAULT '',

    PRIMARY KEY  (`id`),
    KEY (`type`, `value`),
    KEY (`expires`),
    FOREIGN KEY (`admin_id`) REFERENCES `players` (`id`) ON DELETE SET NULL
) ENGINE = InnoDB;

CREATE TABLE `tiles` (
    `id` INT UNSIGNED NOT NULL,
    `house_id` INT UNSIGNED NOT NULL DEFAULT 0,
    `x` INT(5) UNSIGNED NOT NULL,
    `y` INT(5) UNSIGNED NOT NULL,
    `z` INT(2) UNSIGNED NOT NULL,

    PRIMARY KEY(`id`),
    KEY(`x`, `y`, `z`),
    FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE NO ACTION
) ENGINE = InnoDB;

CREATE TABLE `tile_items` (
    `tile_id` INT UNSIGNED NOT NULL,
    `sid` INT NOT NULL,
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB NOT NULL,
   
    INDEX (`sid`),
    FOREIGN KEY (`tile_id`) REFERENCES `tiles` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `map_store` (
    `house_id` INT UNSIGNED NOT NULL,
    `data` LONGBLOB NOT NULL,

    KEY(`house_id`)
) ENGINE = InnoDB;

CREATE TABLE `player_deaths` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `player_id` INT UNSIGNED NOT NULL,
    `date` INT UNSIGNED NOT NULL,
    `level` INT NOT NULL,

    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    PRIMARY KEY(`id`),
    INDEX(`date`)
) ENGINE = InnoDB;

CREATE TABLE `killers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `death_id` INT UNSIGNED NOT NULL,
    `final_hit` TINYINT(1) NOT NULL DEFAULT 1,

    PRIMARY KEY(`id`),
    FOREIGN KEY (`death_id`) REFERENCES `player_deaths` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `environment_killers` (
    `kill_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,

    PRIMARY KEY (`kill_id`, `name`),
    FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_killers` (
    `kill_id` INT UNSIGNED NOT NULL,
    `player_id` INT UNSIGNED NOT NULL,
    `unjustified` TINYINT(1) NOT NULL DEFAULT 0,

    PRIMARY KEY (`kill_id`, `player_id`),
    FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE `player_depotitems` (
    `player_id` INT UNSIGNED NOT NULL,
    `sid` INT NOT NULL COMMENT 'any given range eg 0-100 will be reserved for depot lockers and all > 100 will be then normal items inside depots',
    `pid` INT NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `count` INT NOT NULL DEFAULT 0,
    `attributes` BLOB NOT NULL,

    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    UNIQUE (`player_id`, `sid`)
) ENGINE = InnoDB;

CREATE TABLE `market_offers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `player_id` INT UNSIGNED NOT NULL,

    `sale` TINYINT(1) NOT NULL DEFAULT 0,
    `itemtype` INT NOT NULL,
    `amount` INT NOT NULL,

    `created` INT NOT NULL,
    `anonymous` TINYINT(1) DEFAULT 0,
    `price` INT NOT NULL DEFAULT 0,

    PRIMARY KEY (`id`),
    KEY (`sale`, `itemtype`),
    KEY (`created`),
    FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `market_statistics` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

    `sale` TINYINT(1) NOT NULL,
    `itemtype` INT NOT NULL,
    `price` INT NOT NULL,
    `amount` INT NOT NULL,
    `when` INT NOT NULL,

    PRIMARY KEY(`id`),
    KEY(`sale`, `itemtype`, `when`)
) ENGINE = InnoDB;

CREATE TABLE `global_storage` (
    `key` INT UNSIGNED NOT NULL,
    `value` INT NOT NULL,

    PRIMARY KEY(`key`)
) ENGINE = InnoDB;

CREATE TABLE `schema_info` (
    `name` VARCHAR(255) NOT NULL,
    `value` VARCHAR(255) NOT NULL,

    PRIMARY KEY (`name`)
) ENGINE = InnoDB;

INSERT INTO `schema_info` (`name`, `value`) VALUES ('version', 24);

DELIMITER |

CREATE TRIGGER `ondelete_accounts`
BEFORE DELETE
ON `accounts`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 3 AND `value` = OLD.`id`;
END|

CREATE TRIGGER `ondelete_players`
BEFORE DELETE
ON `players`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 2 AND `value` = OLD.`id`;
    UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;
END|

CREATE TRIGGER `oncreate_guilds`
AFTER INSERT
ON `guilds`
FOR EACH ROW
BEGIN
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Leader', 3, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`);
END|

CREATE TRIGGER `oncreate_players`
AFTER INSERT
ON `players`
FOR EACH ROW
BEGIN
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);
    INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);
END|

DELIMITER ;
INSERT INTO `players` VALUES (1, 'Administrator', 1, 6, 2, 1, 0, 1, 0, 185, 185, 35, 35, 0, 100, 2, 10, 10, 10, 10, 75, 0, 200, 200, 6, 435, 0, 0, 1, 1, '', 0, 0, 100, 100, 100, 10, 100, 1, 0, 151200000, 0, 0, '');
INSERT INTO `players` VALUES (2, 'Player', 1, 1, 1, 1, 0, 1, 0, 185, 185, 35, 35, 0, 100, 2, 10, 10, 10, 10, 75, 0, 200, 200, 6, 435, 0, 0, 1, 1, '', 0, 0, 100, 100, 100, 10, 100, 1, 0, 151200000, 0, 0, '');

# to add your own privileges for players/gms please use this flag generator http://hem.bredband.net/johannesrosen/playerflags.html
 
Upvote 0 Downvote

Similar threads

A
  • Question
House dont saved when purchased
Replies
0
Views
181
Azerty
A
A
  • Question
Bounty Hunter System mysql error
Replies
1
Views
288
Dakos
D
abdala ragab
  • Question
TFS 1.X+ I'm trying to add a VIP system Tfs 1.5
Replies
5
Views
293
Evalor
Evalor
J
  • Question
Myacc tfs 1.5 problem
Replies
8
Views
387
jareczekjsp
J
johnsamir
  • Question
how to solve these errors that has appeared in my database
Replies
2
Views
600
johnsamir
johnsamir
M
  • Question
Error MySQL
Replies
0
Views
141
Mokk
M
Back
Top