Listen to database changes in MySQL

Question

I have a webpage and I make some data manipulations in MySQL table using CRUD operations. There are a lot of running instances of desktop client application which communicates with the same database and every time I make changes in this database through the webpage all these clients need to be notified about changes. And I do not really know the best way how to handle this. The desktop client application is written in C#. Also I know that there is no way to asynchronously notify desktop clients about changes that happened in database at least in MySQL using ADO.NET provider.

There is an option when the client application creates database connection periodically and checks for changes MySQL table. But what if we have 1000 of running instances and each one creates connection and reads the same table every 2 seconds? Wouldn't it be very database server resource consuming an so on?

Also we can write triggers on insert/update/delete which populate another table about changes made and after that we are going to poll this new "changes_made" table.

Also there is an option to create socket server which polls the "changes_made" table and if changes are made then send changes data to all desktop clients which are socket listeners in this context. Thus we will get rid of polling made by clients and significantly reduce an overall amount of database connection and load on the database.

Here I want to know what is the most correct and scalable approach to resolve such tasks.

Answer 1

Well, it is not entirely impossible to notify clients of changes done to the database, although there is no built-in solution for this.

In mysql you can create compiled user defined functions (UDFs) in C/C++. These can extend the functionality of mysql pretty much any way you want it - such as sending messages over the network. You can create your own UDF or use libraries available on mysqludf.org to send messages over the network. You could start with using the STOMP library on mysqludf.org to send STOMP messages. Since STOMP is language-agnostic, you can use it from .NET environment as well, see this SO topic (if search the Internet for .net STOMP server, then you will find more examples).

The client application has to implement a STOMP server that is able to receive the STOMP messages from the server.

In the database you need to create a subscription table that holds the information which client subscribes for what notifications and how to connect to it (IP address / host name and port number as a minimum).

You also need to create triggers on all those tables and events that you want to send notifications about.

I would also create a stored procedure or function that receives the modified data or the fact that certain table has been changed from the triggers, checks the subscription table, creates the STOMP message and then calls the compiled UDF that send the STOMP message to all the subscribed clients. The triggers would call this stored procedure or function instead of implementing all these functionality in each of the triggers.

The clients need to be modified to process the notifications and take further action or prompt the user.

Disclaimer: I do not claim that this is the most adequate and scalable solution for your particular case. However, this way you can implement a notification system on your own avoiding the need to poll your database.