[MODERNAAC]Missing tables etc[Tutorial]

Amiroslo · Jul 24, 2012

Ever had those errors?
Do u want to get rid of them?
Tried OTLand support board and no one feeds u?

Examples:

A Database Error Occurred
Error Number: 1146

Table 'servername.poll' doesn't exist

A Database Error Occurred
Error Number: 1146

Table 'servername.news' doesn't exist

SELECT * FROM (`news`) ORDER BY `id` desc LIMIT 10

etc etc etc

What u have to do is to search here by using CTRL+F and enter the table name or whatever is missing!

SQL:

ALTER TABLE `accounts` ADD `page_access` int(11);
ALTER TABLE `accounts` ADD `page_lastday` int(11);
ALTER TABLE `accounts` ADD `email_new` varchar(255);
ALTER TABLE `accounts` ADD `email_new_time` int(15);
ALTER TABLE `accounts` ADD `rlname` varchar(255);
ALTER TABLE `accounts` ADD `location` varchar(255);
ALTER TABLE `accounts` ADD `created` int(16);
ALTER TABLE `accounts` ADD `email_code` varchar(255);
ALTER TABLE `accounts` ADD `next_email` int(11);
ALTER TABLE `accounts` ADD `premium_points` int(11);
ALTER TABLE `accounts` ADD `nickname` char(48);
ALTER TABLE `accounts` ADD avatar char(48);
ALTER TABLE `accounts` ADD about_me text;
ALTER TABLE `accounts` ADD `nickname` varchar(255) not null default '';
ALTER TABLE `accounts` CHANGE  `key`  `key` VARCHAR( 128 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT  '';
ALTER TABLE `players` ADD `old_name` varchar(255);
ALTER TABLE `players` ADD `hide_char` int(11);
ALTER TABLE `players` ADD `worldtransfer` int(11);
ALTER TABLE `players` ADD `created` int(16);
ALTER TABLE `players` ADD `nick_verify` int(11);
ALTER TABLE `players` ADD `comment` text;
UPDATE `players` SET `hide_char` = 0;
UPDATE `accounts` SET `key` = '';

INSERT INTO `players` (`name`, `world_id`, `group_id`, `account_id`, `level`, `vocation`, `health`, `healthmax`, `experience`, `lookbody`, `lookfeet`, `lookhead`, `looklegs`, `looktype`, `lookaddons`, `maglevel`, `mana`, `manamax`, `manaspent`, `soul`, `town_id`, `posx`, `posy`, `posz`, `conditions`, `cap`, `sex`, `lastlogin`, `lastip`, `save`, `skull`, `skulltime`, `rank_id`, `guildnick`, `lastlogout`, `blessings`, `balance`, `stamina`, `direction`, `loss_experience`, `loss_mana`, `loss_skills`, `loss_containers`, `loss_items`, `premend`, `online`, `marriage`, `promotion`, `deleted`, `description`, `created`, `nick_verify`, `old_name`, `hide_char`, `worldtransfer`, `comment`) VALUES
('Rook Sample', 0, 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 50, 50, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, '', 0, 0, '', 0, 0, 'This player has no comment at this moment.'),
('Sorcerer Sample', 0, 1, 1, 8, 1, 185, 185, 4200, 0, 0, 0, 0, 110, 0, 0, 35, 35, 0, 0, 1, 50, 50, 7, '', 440, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, '', 0, 0, '', 0, 0, 'This player has no comment at this moment.'),
('Druid Sample', 0, 1, 1, 8, 2, 185, 185, 4200, 0, 0, 0, 0, 110, 0, 0, 35, 35, 0, 0, 1, 50, 50, 7, '', 440, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, '', 0, 0, '', 0, 0, 'This player has no comment at this moment.'),
('Paladin Sample', 0, 1, 1, 8, 3, 185, 185, 4200, 0, 0, 0, 0, 110, 0, 0, 35, 35, 0, 0, 1, 50, 50, 7, '', 440, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, '', 0, 0, '', 0, 0, 'This player has no comment at this moment.'),
('Knight Sample', 0, 1, 1, 8, 4, 185, 185, 4200, 0, 0, 0, 0, 110, 0, 0, 35, 35, 0, 0, 1, 50, 50, 7, '', 440, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0, 201660000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, '', 0, 0, '', 0, 0, 'This player has no comment at this moment.');

CREATE TABLE IF NOT EXISTS `bugtracker` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` int(3) NOT NULL,
  `time` int(11),
  `author` int(11) NOT NULL,
  `text` text,
  `title` varchar(120),
  `done` tinyint(3),
  `priority` tinyint(3),
  `closed` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`author`) REFERENCES `players` (`id`) ON DELETE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `with` int(11),
  `friend` int(11),
  `time` int(11),
  `active` tinyint(1),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`with`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`friend`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from` int(11),
  `to` int(11),
  `title` varchar(120),
  `text` tinytext,
  `time` int(11),
  `delete_from` tinyint(1),
  `delete_to` tinyint(1),
  `unread` tinyint(1),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`from`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`to`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) DEFAULT '',
  `body` text,
  `time` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `news_id` int(11),
  `body` text,
  `time` int(11) DEFAULT '0',
  `author` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`news_id`) REFERENCES `news` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`author`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `forums` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120),
  `description` tinytext,
  `access` smallint(5) DEFAULT '1' COMMENT 'Min. access to see the board',
  `closed` tinyint(1),
  `moderators` tinytext,
  `order` int(6),
  `requireLogin` tinyint(1),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `forums` ADD `guild` int(11);

CREATE TABLE IF NOT EXISTS `threads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120),
  `sticked` tinyint(1),
  `closed` tinyint(1),
  `author` varchar(64),
  `time` int(11),
  `board_id` int(11),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`board_id`) REFERENCES `forums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(120) ,
  `text` text,
  `time` int(11),
  `author` varchar(64),
  `board_id` int(11),
  `thread_id` int(11),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`board_id`) REFERENCES `forums` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`thread_id`) REFERENCES `threads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author` int(11) ,
  `title` varchar(120),
  `description` tinytext,
  `youtube` varchar(45),
  `views` int(11),
  `time` int(11),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`author`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `video_comments` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `author` int(11),
  `video` int(11),
  `time` int(11),
  `text` tinytext,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`video`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`author`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `poll` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` varchar(150) ,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_start` datetime ,
  `date_end` datetime ,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `question` (`question`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `poll_answer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_id` int(11) NOT NULL,
  `answer` varchar(150) ,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`poll_id`) REFERENCES `poll` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `poll_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `answer_id` int(11) ,
  `poll_id` int(11) ,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`answer_id`) REFERENCES `poll_answer` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`poll_id`) REFERENCES `poll` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)  ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

This will help a lot of noobs, trust me

NEW ERRORS:

no such table: z_ots_comunication (SELECT * FROM z_ots_comunication WHERE "type" = 'login'

ANSWER:

Code:

CREATE TABLE `z_ots_comunication` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`type` VARCHAR(255) NOT NULL,
`action` VARCHAR(255) NOT NULL,
`param1` VARCHAR(255) NOT NULL,
`param2` VARCHAR(255) NOT NULL,
`param3` VARCHAR(255) NOT NULL,
`param4` VARCHAR(255) NOT NULL,
`param5` VARCHAR(255) NOT NULL,
`param6` VARCHAR(255) NOT NULL,
`param7` VARCHAR(255) NOT NULL,
`delete_it` INT(2) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

reniz · Jul 24, 2012

hahaha dude i got much more error when im trying to create account and characters and more hahahah

- - - Updated - - -

like this

PHP:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'nick_verify' in 'where clause'' in C:\xampp\htdocs\system\application\libraries\POT\OTS_Player.php:146 Stack trace: #0 C:\xampp\htdocs\system\application\libraries\POT\OTS_Player.php(146): PDO->query('SELECT `id` FRO...') #1 C:\xampp\htdocs\system\application\controllers\character.php(137): OTS_Player->find('Sorcerer Sample') #2 [internal function]: Character->create_character('1') #3 C:\xampp\htdocs\system\codeigniter\CodeIgniter.php(236): call_user_func_array(Array, Array) #4 C:\xampp\htdocs\index.php(165): require_once('C:\xampp\htdocs...') #5 {main} thrown in C:\xampp\htdocs\system\application\libraries\POT\OTS_Player.php on line 146

Amiroslo · Jul 25, 2012

@up
try searching, hope this help
http://otland.net/f16/help-acc-maker-43533/

@thread
added new error

Demnish · Sep 5, 2012

I get error no 150 when trying to add poll_votes

Supports transactions, row-level locking, and foreign keys

Amiroslo · Sep 5, 2012

Demnish said:
I get error no 150 when trying to add poll_votes

thats a xampp error
try to look here
How to Enable MySQL InnoDB Storage Engine on XAMPP | All About Web & Mobile Application Development

Demnish · Sep 7, 2012

amiroslo said:
thats a xampp error
try to look here
How to Enable MySQL InnoDB Storage Engine on XAMPP | All About Web & Mobile Application Development

Many thanks for that answer! GM bless you.

EDIT: Tho I have a problem cuz I use UniServ and cannot find a solution. I've tried modify the primary ID key, but some foreign keys are messing, I dunno what to do.. :S

Amiroslo · Sep 7, 2012

I do not think it is the Uniform Server. I use KEYs a lot, and mostly PRIMARY KEYs but everything works fine. It may also depend on the version of MySQL running... Please look into that.. or test it with an example from the MySQL site/doc.

Demnish · Sep 7, 2012

amiroslo said:
I do not think it is the Uniform Server. I use KEYs a lot, and mostly PRIMARY KEYs but everything works fine. It may also depend on the version of MySQL running... Please look into that.. or test it with an example from the MySQL site/doc.

I fixed the problem, but I have major difficulties now when I finally can access the webpage, I have errors in accounts (deleted), guilds(world_id), highscore(promotion), who is online (world_id) and houses (town)... so far. :S

EDIT: Is there anyway to get a hold of an already existing database which has no errors? Like a database for an 8.6 server for ModernACC? (including guild wars and other fancy stuff)

Amiroslo · Sep 7, 2012

U should ask someone that have a 8.6 ot and war system and using modernaac

xD
Cuz I dont have one sorry

Demnish · Sep 7, 2012

Ye ok, thanks.

Amiroslo · Sep 12, 2012

No problem

City · Sep 15, 2012

No clue why this would come in handy to anyone, I mean, if they have this problem, they've downloaded a datapack and they only have half the script. Adding the tables will remove the error, sure, but it's just random clutter and isn't needed. Just remove the script instead.

Amiroslo · Sep 16, 2012

@up
scumbag

City · Sep 18, 2012

Don't blame me because you're horny for

^_^

Amiroslo · Sep 18, 2012

<3 :d

potatis · Jan 8, 2013

Rly helpfull ^^ TY

Stellow · Jan 11, 2013

You solved me a lot of problems! I rep you

Amiroslo · Jan 11, 2013

Glad to hear that guys

ssewok · Mar 4, 2013

How do u add it in to the database? how to create a table correctly? can u send me the poll.frm or something?

Amiroslo · Jul 5, 2013

You go into ur phpmyadmin >ur db> sql and paste it there

[MODERNAAC]Missing tables etc[Tutorial]

Excellent OT User

New Member

Excellent OT User

Tibian Hero

Excellent OT User

Tibian Hero

Excellent OT User

Tibian Hero

Excellent OT User

Tibian Hero

Excellent OT User

Active Member

Excellent OT User

Active Member

Excellent OT User

New Member

C++/C#/PHP/LUA

Excellent OT User

New Member

Excellent OT User

Similar threads