Rathena: Sql_Query & crashed table (which require recovery) will freeze the server.

[anacondaq]

Hi, this bugreport related to all revisions and to current (latest one) for pre-re or re (both).
This bugreport related to SQL_Query function and communication with sql database.
This issue opened for fixing / refactoring sql based functions which will work with a little bit corrupted data / tables / etc and avoid map-server freezes.

Information About the problem

Today i got map-server freeze when did something related to items. After a lot of debugging with VS 2015 i found that the reason of the server freeze happened while Sql_Querry inserting data in corrupted (crashed myisam) table to database (in my case it were related to log_pick function, and function string below)

if( SQL_ERROR == Sql_Query(logmysql_handle, LOG_QUERY " INTO %s ... bla bla bla querries.

SQL query is correct, the problem in Sql_Query function, which will not finish or return any problem without 3rd party actions from me, and will keep server freeze (no reaction for any actions at all from client, but at the same time map-server not crashed, just do not responce for anything (impossible to login, impossible to logout, impossible to do any action in the game)) until SQL query will not be executed completely and error will not return if mysql has error.

In Other words Sql_Query tries to insert data in table which need to repair, but do not receive any errors from mysql-server. (mysql-server 5.6) But if you try to restart myslq-server while this freeze, you will get at map-server error message like bla bla bla DB Error table bla bla bla corrupted.

How have i found it? Again, after a lot of debugging and manipulations & sql server manipulations (shutting down / restarting it while map / char / log servers do not response). When i did it and connection between map-server & sql-server were destroyed -> i got error in map-server something like:

DB bla bla bla is marked as crashed and should be repaired

Sounds hard right? Sorry for my english then =)
But it's not so hard to understand, just follow next steps below, and you will understand what i mean:

Steps how reproduce

1. Broke any table but for example broke picklog table via:

• http://stackoverflow.com/questions/5140019/how-to-intentionally-crash-mysql-table
• tl;tr in MysqlTable run OPTIMIZE TABLE picklog and kill while it runs, after that manipulation table will be marked as crashed.

1. Go to your game-server and try to do anything related to Sql_Query function and logging to mysql data, for example drop item to the ground (sure, you should have enabled drop logs in your config).

2. Voila -> your server will not react for anything, your character will be hold in the game, and you can't do anything in the game (moving / clicking / etc), because whole process holded by waiting for error from mysql, which will not appear until removing manually mysql connection.

Suggestions how to optimize it (example)

maybe need to add some timer function, which will automatically drop querry and return error code if response from mysql server not receiving for very long time, or delays of inserting query to server very big.

Or, better, if it possible manually check table's on server start / end and tell that tables are broken and need to repair them.

Note

This situation VERY rare, it's not happened everyday, i just got this error today after a lot of shut-downing server (source edits), and maybe after some shutdown mysql were corrupt table, or it were happened after restart, anyway details like this not important. Important just thing, that rAthena right now a little bit do not know what to do with tables which marked as crashed and which should be repaired and rAthena will freeze.

What do you think?

[LiamKarlMitchell]

Checking table & db health could be done in a script prior to rAthena loading?

mysqlcheck
http://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html

tl; tr

mysqlcheck [options] --databases db_name yourDBName

But timing checks for long running queries could be worthwhile?

[aleos89]

The mysqlcheck is a program ran outside of MySQL. We have some checks in place right now that see if the require tables to play are inserted. I don't see why we can't add some checks of:

CHECK TABLE `%s`;

And from that we can get the Msg_type to see if a table is corrupted to force the server from loading.

[anacondaq]

Very strange, this error happened time to time. Today i have worked with DB, and after new recompilation i have started emulator, then i tried to create item with @item command, and boom, server frozen. So, i quckly opened mysql console and entered check command to see what is going on, and i got next problem:

mysql> check table `picklog`;
Current database: rathena_clean_log

+---------------------------+-------+----------+-------------------------------------------------------+
| Table                     | Op    | Msg_type | Msg_text                                              |
+---------------------------+-------+----------+-------------------------------------------------------+
| rathena_clean_log.picklog | check | warning  | 1 client is using or hasn't closed the table properly |
| rathena_clean_log.picklog | check | status   | OK                                                    |
+---------------------------+-------+----------+-------------------------------------------------------+
2 rows in set (0.02 sec)

After trying to enter check table picklog command few times, i got next output (after few seconds when i entered command above)

mysql> check table `picklog`;
+---------------------------+-------+----------+----------+
| Table                     | Op    | Msg_type | Msg_text |
+---------------------------+-------+----------+----------+
| rathena_clean_log.picklog | check | status   | OK       |
+---------------------------+-------+----------+----------+
1 row in set (0.00 sec)

So, if summarize, table were locked, but it's very strange. Something locking picklog table, or not closing properly (but do not damage table at all)... Investigating... If i will find a reason what is trying to lock mysql table, i will post results here.

[kedare]

One solution would be to just ditch the dead MyISAM and switch to InnoDB to get ACID compliance and something more reliable ? (No more crashed tables)
There should be no performance issues if used correctly.

[anacondaq]

A lot of things were wrotten in *athena with MyISAM in mind. Simple switch from MyISAM to InnoDB it's not solution at all, and it will provoke much more issues than benefits (resource consumption, speed, not 1 by 1 querries).

Example: https://rathena.org/board/topic/89016-switch-engine-to-innodb/
I did the same like on link above in 2015, and i can confirm that performance is very bad on middle game-server in production, and a lot of micro-freezes on powerful hardware on server with around 500 online, i have switched back to MyISAM because of such issues with InnoDB.