TFS Mysql tables randomly crashes

[Elox]

Hello!

I've a big problem with my SQL database.
For like 1.5 month ago, the 'player_depotitems' table crashed.
I repaired it but then a friend told me to better import a new database else it will happen again. So I did what he said.
2 weeks ago, the table 'player_items' CRASHED.
I repaired it, almost everyone on my server lost their equipment

And now yesterday..
First: 'player_viplist' crashed, I repaired it (No harm done..)
and then after awhile 'houses', 'house_lists' and 'player_items', 'players' also crashed.. I repaired them, got mad as hell cause alot of characters got deleted.
2 hours after that 'player_depotitems' crashed.

I'm using TFS 0.2.13.

Got a lot of mad players ;D

Anyone got a clue what's going on?

 

[ntmr]

Do a last repair on all tables:

REPAIR TABLE `accounts` , `bans` , `groups` , `guilds` , `guild_invites` , `guild_ranks` , `houses` , `house_lists` , `players` , `player_deaths` , `player_depotitems` , `player_items` , `player_skills` , `player_spells` , `player_storage` , `player_viplist` , `tiles` , `tile_items`;

And then change all table storage engines do InnoDB:

DROP TRIGGER IF EXISTS `oncreate_players`;
DROP TRIGGER IF EXISTS `oncreate_guilds`;
DROP TRIGGER IF EXISTS `ondelete_players`;
DROP TRIGGER IF EXISTS `ondelete_guilds`;
DROP TRIGGER IF EXISTS `ondelete_accounts`;

ALTER TABLE `accounts`  ENGINE = InnoDB;
ALTER TABLE `bans`  ENGINE = InnoDB;
ALTER TABLE `groups`  ENGINE = InnoDB;
ALTER TABLE `guilds`  ENGINE = InnoDB;
ALTER TABLE `guild_invites`  ENGINE = InnoDB;
ALTER TABLE `guild_ranks`  ENGINE = InnoDB;
ALTER TABLE `houses`  ENGINE = InnoDB;
ALTER TABLE `house_lists`  ENGINE = InnoDB;
ALTER TABLE `players`  ENGINE = InnoDB;
ALTER TABLE `player_deaths`  ENGINE = InnoDB;
ALTER TABLE `player_depotitems`  ENGINE = InnoDB;
ALTER TABLE `player_items`  ENGINE = InnoDB;
ALTER TABLE `player_skills`  ENGINE = InnoDB;
ALTER TABLE `player_spells`  ENGINE = InnoDB;
ALTER TABLE `player_storage`  ENGINE = InnoDB;
ALTER TABLE `player_viplist`  ENGINE = InnoDB;
ALTER TABLE `tiles`  ENGINE = InnoDB;
ALTER TABLE `tile_items`  ENGINE = InnoDB;

DELIMITER |

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

CREATE TRIGGER `ondelete_guilds`
BEFORE DELETE
ON `guilds`
FOR EACH ROW
BEGIN
    UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);
END|

CREATE TRIGGER `ondelete_players`
BEFORE DELETE
ON `players`
FOR EACH ROW
BEGIN
    DELETE FROM `bans` WHERE `type` = 2 AND `player` = 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 ('the Leader', 3, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a Vice-Leader', 2, NEW.`id`);
    INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('a 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 ;

If you got any errors, probably your MySQL server is configured to not accept InnoDB, then you will need to open your "my.ini" (mysql configuration file), remove the following line and restart mysql server:

skip-innodb

NOTE: DO IT ALL WITH YOUR OTSERV OFFLINE!

:thumbup:

 

[Elox]

Allright, great.
Could you explain to me what it does exactly with the database and why the tables crashed like that?

Anyway, I'll try it. Thanks.

 

[ntmr]

It changes all the mysql tables storage engine to InnoDB, that is a lot more crash-proof than MyISAM.

I'm running my otserv for more than 16 months and my database never crashed using InnoDB.

 

[Elox]

Okay great. Thank you very much for the help

 

[Elox]

I deleted the "skip-innodb" line.. but still got an error

"SQL-fråga (In english: SQL-question):

DELIMITER;

MySQL sa (In english: MySQL said:
#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 'DELIMITER' at line 1 "

In the my.ini file under "skip-innodb" there was some config stuff.

#innodb_data_home_dir = "/xampplite/mysql/"
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = "/xampplite/mysql/"
#innodb_log_arch_dir = "/xampplite/mysql/"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

Maybe I should delete the "comment thingy" on all of those? (except the info texts)