Database Trigger Mediation Layer (DTML) – An Overview

Application Dependent Approach

This approach relies on the application (making database change) to inform ALL the other applications, either on the local node or on some remote node.It requires all the application modules to implement the following function for updating the data set in DB on which other application modules are also dependent :-

Application module makes changes to the database.

• Application module on successful commit sends –
  • point-to-point messages OR
  • multicast message

(Message to notify data changes to application modules located on the same server and other servers in the database cluster). Interested Application module implements the following function for receiving notification messages:-

• Wait for notification messages.
• Read the data set from the notified table

Application Agnostic Approach

This approach frees the application (making the database change) from the responsibility of informing the dependent applications. Hence, the application (making the change) need not be aware of other interested applications. It makes use of MySQL triggers and MySQL “sys_exec” User Defined Function

MySQL trigger: A named database object that is associated with a table and that activates when a particular event occurs for the table. A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement.

User Defined Function: A function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements (like triggers)

“Sys_exec” UDF: sys_exec takes one command string argument and executes it. sys_exec() UDF function can be used to run any shell command (via database triggers). This shell command can in turn invoke an application which may notify all the interested applications about the trigger condition

During initialization time, MySQL triggers are associated with the tables storing data which many application modules are interested.

• Application module makes changes to the MySQL database.
• MySQL database invokes the associated trigger. This trigger passes the shell command to be executed, table name and value of the primary key to “sys_exec” UDF
• sys_exec executes the shell command and pass the other parameters as argument to the shell command.
• Shell command sends –
  • point-to-point messages OR
  • multicast message

(Message to notify data changes (table and row) to application modules located on the same server and other servers in the cluster)

Interested Application module implements the following function for receiving notification messages:-

• Wait for notification messages
• Read the data set from the notified table

DTML Solution

DTML uses the application agnostic approach primarily because of three reasons:

• The application (making the change) need not be aware of other interested applications.
• It does not require any implementation in the application (making the change)
• Through associating/de-associating trigger to a DB table this functionality can be easily turned on or off for a specific dataset

DTML uses multicast approach in favor of point to point approach because:

• Adding/removing a ‘to be notified application module’ will require changes.
• Sending mechanism need to aware of the destination IP addresses of the servers in the cluster and their availability

An application may ask DTML to monitor a table for some specific condition and to notify the application (synchronously) whenever that situation is encountered. DTML has been designed to work in a clustered deployment and hence DTML generates a real time cluster wide notification and not just a local notification on the node on which changes were recorded.

DTML components

DTML solution consists of three components:

Two User Space components:

• 1Dbtr: An executable which may be triggered via “sys_exec” UDF
• Dbtrigger.so: A shared object library which provides an interface:
• ​For registering for the notifications on selected database tables
• For generating a notification for some specific database table

​​One Kernel Space component:

• Dbtrigger Kernel Module: It’s a Linux loadable kernel module which does the following tasks:
• Notifying the interested applications on the local server

• Notifying the applications on the remote server

NetLink sockets are used for communicating between user space and kernel space

Multicast group mechanism, facilitated by NetLink sockets, is used to notify all the registered applications on the local node in a single go. UDP based multicast mechanism is used to propagate the notification to the remote kernel module(s). All the kernel nodules on the various nodes of the cluster join the same multicast group. The local kernel module sends out a UDP based multicast message on the LAN. The remote kernel module(s) receives this multicast message and propagate the notification to the applications hosted locally. A separate NetLink multicast group is used for every database table (this 1:1 mapping has to be statically defined)

Deployment Architecture

How DTML works

1. C/C++ application and/or Java application and/or Operator update MySQL DB.
2. MySQL invokes associated trigger which calls sys_exec with arguments as dbtr, table name, and the value of the primary key as string (it is possible to pass any other information like the modified tuple itself)
3. dbtr send the string to kernel module (using the interface defined in DBtrigger.so library). Netlink Group associated with the table is passed through nlmsghdr and string as NLMSG_DATA.
4. dbtrigger kernel module receives NLMSG which includes the string and the group.
5. dbtrigger kernel module broadcasts the message on the identified group. Netlink Group associated with the table is passed through nlmsghdr and string as NLMSG_DATA. Linux kernel delivers the messages to all waiting application on that group.
6. Waiting C/C++ and JAVA applications receives the string, which is the value of the primary key. Application (main or child thread) gets unblocked when message is received. With table identified and value of the primary key known, application can retrieve entire row from database. It is also possible to pass the changed value as an argument in the DB Trigger message itself enabling the application to get the changed value from the message utself instead of fetching it from the database.
7. JAVA application receives the messages (via JNI mechanism)
8. dbtrigger kernel module send the NLMSG using IP multicast in internal VLAN. This multicast is received by all the nodes in the cluster except the sender. dbtrigger kernel module on receiver servers receives the NLMSG which includes the string and the group.
9. See 5, except NLMSG is not send out through IP multicast.
10. See 6.
11. See 7.

Key Limitations

• The applications and the database (one node of the database cluster) need to be collocated
• Only 32 database tables may be monitored as NetLink multicast mask has only 32 possible masks (0-31)
• The tables for which the application requires a DB Trigger are currently compile time fixed.

References

• https://dev.mysql.com/doc/refman/5.0/en/triggers.html) (https://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
• https://en.wikipedia.org/wiki/User-defined_function) (https://www.mysqludf.org/) (https://www.mysqludf.org/lib_mysqludf_sys/index.php#sys_exec
• https://qos.ittc.ku.edu/netlink/html/node1.html
• https://www.kernel.org/doc/man-pages/online/pages/man7/netlink.7.html