Using SymmetricDS for Multi Variant Database Replication

Last time when i used MySQL 5.6 Database Replication from Master to Slave kind of things was pretty awesome, straight-forward, reliable and work as it is. I was using it for my first priority on backup database, and using mysqldump from the slave db as second backup priority, even though still doesn’t solve the issue of high available server application because it required a down time to switch between master to slave. I can leave it this issue for just for now, but I’m facing another issue when the application requires customization on the replication type of each tables.

For example we have to two databases need to be replicate, database A and B, database A has several tables to do the one way replication to database B, database  B also has several tables need to the the replication one way to database A, and both databases has the remaining tables that need to be replicate bi-directional way. Database A as central server that sit somewhere in the cloud and it has public ip and can be access from every where; database B sit in local shop some where, it has limited and unreliable internet connection, and it doesn’t have any public ip. Database A as a central database has a big capacity CPU and Memory, it handles most of the transaction data, but once there is an record update relate to table that belong to database B, it will push only for the particular record. In this case the replication require functionality to do the filtering record and also communication some how with http or tcp pulling type because the database can be inaccessible from outside.


One of the solution out there is by using the SymmetricDS ( replication framework, is an open source software for database synchronization with support for multi master replication, filtered synchronization, and transformation. It runs on java with minimal version 7, support for multi variant databases like MySQL, MongoDB, Microsoft SQL Server, Oracle, SQLite, H2, etc. SymmetricDS is using JDBC to communicate with external database.


The symmetricDS treat our application as a node, each node has their own presentation of their database, each node has unique id called External Id and it group by how it will replicate the data via http pull or http push. And this software will create their system tables which defined by put prefix sym_ inside our database, is make our database became messy, one thing that i don’t like about SymmetricDS.


The way of SymmetricDS works is by allowing the user to select which user’s table need to be replicate, and define how the table replication works. The SymmetricDS will scan the selected tables and trigger the replication when there is a data changed inside, the replication data it self has a previous and new value of the particular record and store as CSV Format. Until now, I don’t fully understand the detail about how the SymmetricDS validate and scan each of the table, but I have found it put all the change history , create a hash value and store into their system table.

After SymmetricDS capture the changed record, it will define the destination node based on the route table that we (user) defined at the beginning, in this case from database A to B, so we can called route a_2_b with source node A and target node B. SymmetricDS extract the data into CSV format and transform the record data when is need, and create as a batch data file before it transfer into the destination node by http transport. SymmetricDS is using Restful framework to do the communication with other node, so we don’t worry about the security any more for the data transportation by putting the ssl on the top of the communication, and after the batch message  is received by destination node, it will load into their database.

The good thing about SymmetricDS because it run with Java, so it can run for any OS like Linux or Windows, it also has a small size of application sit in the memory. One of the example implementation by using this kind of technology is Point Of Sale / Cashier Machine that require to synchronize the data into the central database server.



Design for Lacak Platform

Lacak is a tracker platform to serve many variant GPS device vendors with many protocols supported like Gt06, Teltonika, G15c, and etc. Currently the platform is using the monolithic type of application in a single box server. There is a need to move to cluster-wide design of application because the GPS devices are growing from time to time. The cluster-wide idea is to group the GPS devices and each of the group will use the same tracker server, so it must be many tracker servers provided and it the same time all the transaction will go to the same location. The transaction itself store the location of GPS device within the period of time, it has Device Id, Device Time, Longitude, Longitude, Latitude, Speed, Course, etc.

The issue with cluster-wide design is we need to have centralized, cluster-wide, persistent, messaging, and streaming framework. So we came with Apache Kafka which is a Distributed Streaming Framework as a solution. We put the Kafka as a central application that run to deliver each of the  GPS transaction.

We are using Splunk for data analyzer and we build “Lacak-Pusher” to grep all the transaction from Kafka and deliver to the external third party applications include Splunk Server.

Please see the design below :



Concurrent Requests Inside HTTP API Broker

When we design the API for our application, we need to make it clean and easy to access. One of the option that we can provide is to make it centralize, by means it only have one point target as API Server. But this idea seems not easy to enough when we face with many internal applications that sit in many box servers.


For example like diagram above, client provided access to list all the user’s contact profiles via API Broker. The API Broker itself allow to access the users application to get detail information about the specific user details, and allow to access the contacts application that has two API: to get list of user’s contacts and to get user contact profile details.

What happening  is when Client create new request “Get List User Contact Profiles” to API Broker, the broker will create new request “Get User Profile” to Users Application and when it received the use profile details successfully, it will continue to create new request “Get List User Contacts” to Contacts Application. The API Broker will receive the list of contact reference identifiers based on the user’s identifier that already pass to the Contacts Application. And the list of contact reference identifiers will required to read all the contact details.

The problem that we are going to face is there will be a latency issue, because based on the list of customer reference identifiers we need to do the iteration and each of the iteration need to create new request “Get Contact Profile”.

We can get rid the sequential iteration by put a concurrent with queue and workers module inside, it means that there will be a several worker that run concurrently to process message that pass thru the queue. And there will be mechanism to split message became several parts and recompose the parts back became an original message with additional information.




Traccar Tracker Server As Proxy

Traccar Server is the best Tracker Server is open source and has solid performance to track every gps devices with many variant protocol supported. It works as location server, accept and track every location transaction from gps devices, it show the real time movement of the devices, with map layer that we can customized, and it has user management roles with customized device identifier.


Above is the sample of tracker server that we implemented for several Gps devices like FM1120, G15c, and Gt06. Each of the vendor has their own protocols , and Traccar support most of them, but for special device like G15c, we can build our own protocol on it and plug into the Traccar Server.

The question that we have right now is how to make this tracker server flexible enough as a proxy server, so that location request from gps devices can be forwarded to another tracker server (other than Traccar). Yes, it’s possible.

Each of the protocol inside Traccar is interpreted with a base socket decode/encode stacked modules came from JBoss Netty, and we can put new module called forwarder/proxy before it jump into decode/encode stacked module.

Inside the forward module will only read the packets data without any corruption, because the data will read by encode/decode stacked module in the end. The forward module will act as forwarder, to bring the packet data as it is to the other third party tracker server.


In this example we going to add proxy for Gt06 Protocol for all Gt06 Gps Devices, by adding new module called Gt06ProtocolForwarder, so that every packet inbound data will go thru will forward first into third party if is enabled.

To validate which protocol can be forward enable or not, we can use existing traccar configuration file like below


The full source code you can find is in here


Full time or freelance?

Full time or freelance? Permanent employee or contractor? Cubicle or home office?

I have tried all these employment options. For me, there’s a clear winner. After spending a couple decades at in-house software development in Singapore, I dared to convert my small time moonlighting into full time contract work as a freelance web application developer. When I went freelance for good. This meant I had an extra two months of paid vacation. I used the time to round up clients, and I have never wanted to go back. No more commute, no more TPS reports, and no more pro forma meetings. I now had complete control over my career and my success.

I am often asked if I miss the cubicle world. Absolutely not! Perhaps the greatest enjoyment of contract work is the appreciation I receive from my clients. In the corporate world, the overlords take for granted that you will do their bidding, and they rarely ask for your opinion. But to my contract clients, I am the expert engineer. My advice and experience are valued. After I fulfill the clients’ business needs on time, under budget, and with quality panache, they express gratitude!

Of course, nothing is perfect, and freelancing has a large downside (and I’m not talking about the lack of paid benefits!). A contractor must keep the work “pipeline” full. That requires a significant amount of time and effort. Establishing a consistent pay rate that matches your market value is difficult, because not all clients can afford a senior engineer. Sure, there are lots of job boards that act as dating services between contractors and clients, but they have about the same success rate as real dating services.

The job boards simply haven’t figured out the correct business model yet. The better way to connect clients with contractors is to have a pre-vetted pool of contractors and retain them with repeated project success. Vetting a new contractor must be technically thorough, and done by other engineers. It can’t be the slipshod qualifying that is done by the typical, technology-illiterate headhunter. That’s where Toptal comes in. Toptal is a broker for clients and contractors, like the job boards, but Toptal maintains a community of elite engineers and developers that have qualified themselves with multiple levels of skill tests. This formula is a win-win for clients and contractors. Clients have a higher probability of project success, and contractors have a steady supply of work at consistent rates.

To answer the question I posed at the beginning of this article: as a Toptal member, the clear winner in the choice of permanent employee or contractor is me!

Messaging Basic Module

Since i read messaging ebook like “Enterprise Integration Pattern”, it give new perspective about how to build good messaging platform. In the mean time i worked as Senior Java Developer for advertising messaging platform, it force me to build robust and yet flexible application.

There are two level applications to do with messaging platform, one is enterprise level like Java Messaging Service (JMS) with Enterprise Messaging Queue and the other is low level like DIY application.

Both has pro and cons, and Im doing at the DIY one, it require simple J2SE Runtime, low level and common libraries. It run with small heap memory, and it fast. It design to handle smaller amount of message traffic.

The more deep into the messaging platforn, i found several pattern than we can build as library module and it can use by other module.

Now I introduce to you the “Messaging Basic Module”, this a generic module, has light depencies, with some list following features below :
– It formed as jar library
– It shall has persistent feature
– It shall provide internal api
– It shall has load restrictions

This module can be use at enterprise level.

MyAdv Application

Since I have been developed the messaging for advertising from 2008, I need to build a new fresh advertising application, yet still related to messaging platform but more into specific concern.

I got the idea to make the application to reach their customers and manage the data easly for small company or personal used. The reached can be from short message with interaction or enhanced and bring the stuff to the next level, something more richer and details.

The main module like customer management, interaction message, shortener link, and landing page are remaining there, but the scope just to make it easy to access and stupid proof for anyone can use it.


Its like the sketch that i draw, im just using with my mobile device, i can manage the customer table, short text, shareable short link, and landing page. All these items i can share to all my customers.