MySQL listen notify equivalent

Is there an equivalent of PostgresQL's notify and listen in MySQL? Basically, I need to listen to triggers in my Java application server.

Solution 1:

Ok, so what I found is that you can make UDF functions in mysql that can do anything but need to be written in C/C++. They can be then called from triggers on updates in database and notify your application when update happened. I saw that there are some security concerns. I did not use it myself but from what I can see it looks like something that could accomplish what you want to do and more.

http://dev.mysql.com/doc/refman/5.6/en/adding-udf.html

Solution 2:

The github project mysql-notification provides a MySQL user defined function MySQLNotification() as a plugin to MySQL that will send notification events via a socket interface. This project includes a sample NodeJS test server that receives the notification events that could be adapted for Java or any other socket service.

Example use:

$ DELIMITER @@
$ CREATE TRIGGER <triggerName> AFTER INSERT ON <table> 
  FOR EACH ROW 
  BEGIN 
     SELECT MySQLNotification(NEW.id, 2) INTO @x; 
  END@@

Project includes full source code and installation instructions for OSX and Linux. License is GNU v3.

Solution 3:

No, there aren't any built-in functions like these yet. You need to "ping" (every 1-5 seconds) database with selecting with premade flag like "read" 0/1. After

SELECT * FROM mytable WHERE read = 0

update it with read = 1

Solution 4:

I needed to do this, so I designed my application to send the update notices itself.

E.g. --Scenario-- User A is looking at record 1 User B saves an update to record 1 while User A has it open.

Process:

I wrote my own socket server as a Windows Service. I designed a que like system which is basically,

EntityType EntityID NoticeType

Where the EntityType is the type of Poco in my data layer that needs to send out notices, EntityID is the primary key value of the row that changed in sql (the values of the poco), and NoticeType is 1 Updated, 2 Inserted, and 3 Deleted.

The socket server accepts connections from the server side application code on a secure connection "meaning client side code cannot make requests designed to be sent by the server side application code"

The socket server accepts a message like

900 1 1023 1

Which would mean the server needs to notify concerned client connections that Entity Type 1 "Person" with ID 1023 was Updated.

The server knows what users need to be notified because when User's look at a record, they are registered in the socket server as having an interest in the record and the record's ID which is done by the web socket code in the client side javascript.

Record 1 is a POCO in my app code that has an IsNew and IsDirty field. "Using EntityFrameWork6 and MySql" If UserB's save caused an actual change (and not just saving existing data) IsDirty will be true on the postback on UserB's POCO.

The application code see's the record is dirty then notifies the socket server with a server side sent socket "which will be allowed" that says Entity 1 with ID 1023 was Updated.

The socket server sees it, and puts it in the que.

Being .Net, I have a class for concerned users that uses the same pocos from the data layer running in the Socket Server window service. I use linq to select users who are working with an entity matching the entity type and primary key id of the entity in the que.

It then loops through those users and sends them a socket like 901 1 1023 1 letting them know the entity was updated.

The javascript in the client side receives it causing users B's page to do an ajax postback on Record 1, But what happens with UserA's is different.

If user A was in the process of making a change, they will get a pop up to show them what changed, and what their new value will be if they click save and asks them which change they want to keep. If UserA doesn't have a change it does an ajax postback with a notification bar at the top that says "Record Change: Refreshed Automatically" that expires after a few seconds.

The Cons to this, 1. It's very complex 2. It won't catch insert/update/delete operations from outside of the application.

In my case, 2 won't happen and if 2 does happen it's by myself or another dev who knows how to manually create the notify que requests "building an admin page for that".