Database Trigger Mediation Layer (DTML) - An Overview

December 16, 2013

Most of the server side applications are database driven these days i.e. they store the configuration in database so as to support on the fly changes without a need of restart. But this requires monitoring of all the configuration tables at regular intervals. Most of the monitoring cycles are a simple waste of CPU as the configuration changes are not going to be that frequent. This problem may be overcome to some extent by having a larger monitoring interval but in that case the database changes will not be incorporated in real time.

Database Trigger Mediation Layer (DTML) addresses the above mentioned issues in an elegant and rather conservative manner for the applications using “MySQL” as the database on a Linux box.

The above problem may be addressed in two possible ways:

  • Application dependent 
  • Application agnostic 

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 
  • 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 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. 


  • (
  • ( (



No Comments

Add Comment


We use cookies (including third party cookies) to ensure you get the best experience while visiting our website. Click "Accept All Cookies" to accept the cookie usage. Click "Cookie Settings" to adjust cookie settings.

Mandatory Cookies

These cookies cannot be disabled

These cookies are necessary for the website to function and cannot be switched off.

  • __RequestVerificationToken
  • authentication
  • dnn_IsMobile
  • language
  • LastPageId
  • NADevGDPRCookieConsent_portal_0
  • userBrowsingCookie

Analytics Cookies

These cookies allow us to monitor traffic to our website so we can improve the performance and content of our site. They help us to know which pages are the most and least popular and see how visitors move around the site. All information these cookies collect is aggregated and therefore anonymous. If you do not allow these cookies we will not know when you have visited or how you navigated around our website.

  • _ga
  • _gat
  • _gid

Functional Cookies

These cookies enable the website to provide enhanced functionality and content. They may be set by the website or by third party providers whose services we have added to our pages. If you do not allow these cookies then some or all of these services may not function properly.

  • __atuvc
  • euconsent

Targeting Cookies

These cookies may be set through our site by our advertising partners. They may be used by those companies to build a profile of your interests and show you relevant adverts on other sites. They do not store directly personal information, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less targeted advertising.


Not used.