1

I have an Android frontend. The Android client makes a request to my NodeJS backend server and waits for a reply. The NodeJS reads a value in a MySQL database record (without send it back to the client) and waits that its value changes (an other Android client changes it with a different request in less than 20 seconds), then when it happens the NodeJS server replies to client with that new value. Now, my approach was to create a MySQL trigger and when there is an update in that table it notifies the NodeJS server, but I don't know how to do it. I thought two easiers ways with busy waiting for give you an idea:

  1. the client sends a request every 100ms and the server replies with the SELECT of that value, then when the client gets a different reply it means that the value changed;
  2. the client sends a request and the server every 100ms makes a SELECT query until it gets a different value, then it replies with value to the client.

Both are bruteforce approach, I would like to don't use them for obvious reasons. Any idea?

Thank you.

Heichou
  • 311
  • 4
  • 13

1 Answers1

0

Welcome to StackOverflow. Your question is very broad and I don't think I can give you a very detailed answer here. However, I think I can give you some hints and ideas that may help you along the road.

Mysql has no internal way to running external commands as a trigger action. To my knowledge there exists a workaround in form of external plugin (UDF) that allowes mysql to do what you want. See Invoking a PHP script from a MySQL trigger and https://patternbuffer.wordpress.com/2012/09/14/triggering-shell-script-from-mysql/

However, I think going this route is a sign of using the wrong architecture or wrong design patterns for what you want to achieve.

First idea that pops into my mind is this: Would it not be possible to introduce some sort of messaging from the second nodjs request (the one that changes the DB) to the first one (the one that needs an update when the DB value changes)? That way the the first nodejs "process" only need to query the DB upon real changes when it receives a message.

Another question would be, if you actually need to use mysql, or if some other datastore might be better suited. Redis comes to my mind, since with redis you could implement the messaging to the nodejs at the same time...

In general polling is not always the wrong choice. Especially for high load environments where you expect in each poll to collect some data. Polling makes impossible to overload the processing capacity for the data retrieving side, since this process controls the maximum throughput. With pushing you give that control to the pushing side and if there is many such pushing sides, control is hard to achieve.

If I was you I would look into redis and learn how elegantly its publish/subscribe mechanism can be used as messaging system in your context. See https://redis.io/topics/pubsub

luksch
  • 11,497
  • 6
  • 38
  • 53
  • Thank you for your reply and for the welcome! I'm working alone on this project and I'm already learning NodeJS and other languages only for this project (like kotlin), so I think that for my mind shoul be better don't learn Redis too, else it will explode :). By the way I think that messaging between two process is a great option (better than mine). But I can't do it... Can you suggest me any documentation or other stackoverflow question? Else I will try to search it by myself later. Thank you again – Heichou Apr 14 '18 at 16:23
  • If you are already overloaded with new tech, then I would recommend going with the simplest approach, which may be a polling mechanism of the first nodejs process. If you find problems later because your solution does not scale it will be time for a refactoring/redesign/reprogramming. By that time you will have gained a lot of knowledge about programming and you may have more capacity to learn more helpful tech. – luksch Apr 14 '18 at 16:29