1

We've got a database (MySQL ) that has a client 'per channel'. When a player changes channel from, say, 1 to 2, we have to save the state of the player to the database.

Saving is done in multiple transactions (per table), which include in most cases a DELETE FROM table WHERE player = X; INSERT INTO table VALUES (...);. Some other tables require only an UPDATE statement.

The problem is as follows:

Player starts moving from channel 1 to channel 2
Channel 1 registers transition in Redis, making sure that player is bounced on Channel 2 until player is saved.
Player disconnects from channel 1
Player connects to channel 2, gets asked to reconnect (bounce)
Channel 1 received disconnection, saves character, disables bouncing
Player connects to channel 2, gets accepted
Channel 2 loads info from database --THIS IS OUTDATED INFO--

I'm about to, instead of deleting the Redis key, to change the TTL of the key to a couple seconds to prevent it from loading old info. But it doesn't guarantee anything, as the database is a system on its own...

For the record, there's a MariaDB instance that acts as a slave for backup purposes.

  • Does Redis add enough speed to justify the added complexity? Wouldn't it be a simple transaction with only a couple of queries if done in MySQL/MariaDB? – Rick James Nov 10 '17 at 16:06
  • Redis is used only for a simple key store with a TTL, exactly what it is good for and as it is synchronous, it cannot go wrong in terms of timing. – Diamondo25 Nov 11 '17 at 0:29
1

If you can install Mysql UDF Plugin, you can using lib_mysqludf_redis plugin to synchronous records between Mysql and Redis by Mysql trigger.

Here is a example:

CREATE TABLE `player_channel_log` (
  id       int         AUTO_INCREMENT PRIMARY KEY,
  date     datetime,
  player   int,
  channel  varchar(32)
);

set up a trigger for the table and call redis command by mysql udf

DELIMITER $$
CREATE TRIGGER `after_insert_player_channel_log`
AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
  DO `redis`('redis://@127.0.0.1/0/', 'SET', new.`player`, new.`channel`);
END $$
DELIMITER ;

then you can get value by player id

from mysql:

mysql>  SELECT `redis`('redis://@127.0.0.1/0/', 'GET', <player id>)\G
*************************** 1. row ***************************
`redis`('redis://@127.0.0.1/0/', 'GET', <player id>): {
        "out":  <channel>
}

from redis-cli utility:

$ redis-cli -h 127.0.0.1 -n 0 GET <player id>
"<channel>"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.