Question 20918 in Mysql Question posted in
The official MySQL documentation can be found here.

In C# How can I get a event from MySQL when somebody Insert, Delete or Modify a record?

I am developing a program in WPF.Net, and I need to know when somebody makes a change over any table of the database.

The idea is receive a event from the database when it was changed. I was reading a lot of articles but I can’t find a method to resolve my problem.

Kind Regards

Tags:

2

Answers


  1. 0

    I don’t think this is possible with MySQL, DBs like MondgoDB have this sort of feature.

    You may like to use the method described in this answer.

    Essentially have date/time fields on rows where you can pull data since a certain date time. Or you could use a CQRS/Event stratagem and maybe use a message queue.

    Login or Signup to reply.
  2. 0

    The best solution is to use a message queue. After your app commits a change to the database, the app also publishes a message on the message queue. Other clients then just wait for notifications on that message queue.

    There are a few other common solutions, but all of them have disadvantages.

    Polling. If a client is interested in recent changes, they run a query searching for new data every N seconds.

    The downside is you have to keep polling even during times when there are no changes. You might have to poll very frequently, depending on how promptly you need to notice the changes. This adds to database load just to support the polling queries.

    Also it costs more if you have many clients all polling for queries. In one system I supported, the database was struggling to process 30,000 queries per second just for clients running polling.

    Change Data Capture. Using the binary log as a de facto message queue, because it records all the changes. Use a client tool such as Debezium, or write your own binlog tail client (this is a lot of work).

    The downside is the binlog records all changes, not just those you want to be notified about. You have to filter it somehow. Also you have to learn how to use Debezium or equivalent tool.

    Triggers. Write a trigger on the table that invokes a UDF to post notification outside the database. This is a bad idea, because the trigger executes when your insert/update/delete executes, not when the transaction commits. Clients could be notified of changes before the changes are committed, so if they go query the database right after they get the notification, the change is not visible to them yet.

    Also a disadvantage because it requires you install a UDF extension in MySQL Server. MySQL doesn’t normally have any way of posting an external notification.

    I’m not a C# developer so I can’t suggest specific code. But the general methods above are similar regardless of which language the app is written in.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search