Table of Contents
Many of the data synchronization concepts can be understood by examining the data model. The configuration data model is stored in a set of tables updated by the user as needed to configure the system. In constrast, the set of tables for the runtime data model change constantly as the system captures data changes and records the activity to deliver them.
The configuration is entered by the user into the data model to control the behavior of what data is synchronized to which nodes. To ease management of several databases, nodes are put into groups, and groups are linked together for synchronization. A trigger captures data for a table, which can include conditions and criteria for subsets. Triggers are grouped into channels for prioritization and control of data flow.
An instance of SymmetricDS that synchronizes data with one or more nodes. Each node has a unique identifier (nodeId) that is used when communicating, as well as a domain-specific identifier (externalId) that provides context within the local system.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| node_id | varchar(50) | N | PK | Unique identifier for a node | |
| node_group_id | varchar(50) | N | FK | The node group that this node belongs to, such as "store" | |
| external_id | varchar(50) | N | A domain-specific identifier for context within the local system. For example, the retail store number. | ||
| sync_enabled | booleanint | Y | 0 | Indicates whether this node should be sent synchronization. Disabled nodes are ignored by the triggers, so no entries are made in DataEvent for the node. | |
| sync_url | varchar(2000) | Y | The URL to contact the node for synchronization. | ||
| schema_version | varchar(50) | Y | The version of the database schema this node manages. Useful for specifying synchronization by version. | ||
| symmetric_version | varchar(50) | Y | The version of SymmetricDS running at this node. | ||
| database_type | varchar(50) | Y | The database product name at this node as reported by JDBC. | ||
| database_version | varchar(50) | Y | The database product version at this node as reported by JDBC. | ||
| heartbeat_time | timestamp | Y | The last timestamp when the node sent a heartbeat, which is attempted every ten minutes by default. | ||
| timezone_offset | varchar(6) | Y | The timezone offset in RFC822 format at the time of the last heartbeat. |
Table 3.1. Node
Security features like node passwords and open registration flag are stored in the NodeSecurity table.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| node_id | varchar(50) | N | PK FK | Unique identifier for a node | |
| password | varchar(50) | N | The password used by the node to prove its identity during synchronization. | ||
| registration_enabled | booleanint | N | 0 | Indicates whether registration is open for this node. | |
| registration_time | timestamp | Y | The timestamp when this node was registered. | ||
| initial_load_enabled | booleanint | N | 0 | Indicates whether an initial load will be sent to this node. | |
| initial_load_time | timestamp | Y | The timestamp when this node started the initial load. |
Table 3.2. Node Security
After registration, this table will have one row representing the identity of the node. For a root node, the row is entered by the user.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| node_id | varchar(50) | N | PK FK | Unique identifier for a node |
Table 3.3. Node Security
A category of Nodes that synchronizes data with one or more NodeGroups. A common use of NodeGroup is to describe a level in a hierarchy of data synchronization.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| node_group_id | varchar(50) | N | PK | Unique identifier for a node group, usually named something meaningful, like "store" or "warehouse". | |
| description | varchar(50) | Y | A description of this node group. |
Table 3.4. Node Group
A source NodeGroup sends its data updates to a target NodeGroup using a pull, push, or custom technique.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| source_node_group_id | varchar(50) | N | PK FK | The node group where data changes should be captured. | |
| target_node_group_id | varchar(50) | N | PK FK | The node group where data changes will be sent. | |
| data_event_action | char(1) | N | W | The notification scheme used to send data changes to the target node group. (P = Push, W = Wait for Pull) |
Table 3.5. Node Group Link
A category of data that can be synchronized independently of other Channels. Channels allow control over the type of data flowing and prevents one type of synchronization from contending with another.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| channel_id | varchar(50) | N | PK | A unique identifer, usually named something meaningful, like "sales" or "inventory". | |
| processing_order | integer | N | 1 | Order of sequence to process channel data. | |
| max_batch_size | integer | N | 1000 | The maximum number of Data Events to process within a batch for this channel. | |
| enabled | booleanint | N | 1 | Indicates whether channel is enabled or not. | |
| description | varchar(1000) | Y | Description on the type of data carried in this channel. |
Table 3.6. Channel
The Node Channel Control is used to ignore or suspend a channel. A channel that is ignored will have its Data Events batched and they will immediately be marked as "OK" without sending them. A channel that is suspended is skipped when batching Data Events.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| node_id | varchar(50) | N | PK FK | Unique identifier for a node | |
| channel_id | varchar(50) | N | PK FK | A unique identifer, usually named something meaningful, like "sales" or "inventory". | |
| suspend_enabled | booleanint | Y | 0 | Indicates if this channel is suspended, which prevents its Data Events from being batched. | |
| ignore_enabled | booleanint | Y | 0 | Indicates if this channel is ignored, which marks its Data Events as if they were actually processed. |
Table 3.7. Node Channel Control
The database triggers that capture changes in the database are automatically generated by SymmetricDS. Configuration of which triggers are generated and how they will behave is stored in the Trigger table.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| trigger_id | integer | N | PK | Unique identifier for a trigger. | |
| source_schema_name | varchar(50) | Y | The schema name where the source table resides. | ||
| source_table_name | varchar(50) | N | The name of the source table that will have a trigger installed to watch for data changes. | ||
| target_schema_name | varchar(50) | Y | The schema name where the target table resides. | ||
| target_table_name | varchar(50) | Y | The name of the target table that will have data changes synchronized to it. | ||
| source_node_group_id | varchar(50) | N | The node group that will install this trigger to watch for data changes, | ||
| target_node_group_id | varchar(50) | N | The node group that will have data changes synchronized to it. | ||
| channel_id | varchar(50) | N | The channel that data changes will flow through. | ||
| sync_on_update | booleanint | N | 1 | Whether or not to install an update trigger. | |
| sync_on_insert | booleanint | N | 1 | Whether or not to install an insert trigger. | |
| sync_on_delete | booleanint | N | 1 | Whether or not to install a delete trigger. | |
| sync_on_incoming_batch | booleanint | N | 0 | Whether or not an incoming batch that loads data into this table should cause the triggers to capture Data Events. Be careful turning this on, because an update loop is possible. | |
| sync_column_level | booleanint | N | 0 | Perform column level synchronization for updates, so only the fields that changed are updated on the target database. When updates come from multiple sources at the same time, this enables merging of changes that do not conflict. The entire change row is still sent, which preserves the ability to fallback to an insert if the row does not exist. | |
| name_for_update_trigger | varchar(30) | Y | Override the default generated name for the update trigger. | ||
| name_for_insert_trigger | varchar(30) | Y | Override the default generated name for the insert trigger. | ||
| name_for_delete_trigger | varchar(30) | Y | Override the default generated name for the delete trigger. | ||
| sync_on_update_condition | varchar(1000) | Y | Specify a condition for the update trigger firing using an expression specific to the database. | ||
| sync_on_insert_condition | varchar(1000) | Y | Specify a condition for the insert trigger firing using an expression specific to the database. | ||
| sync_on_delete_condition | varchar(1000) | Y | Specify a condition for the delete trigger firing using an expression specific to the database. | ||
| initial_load_select | varchar(1000) | Y | Specify a where-clause for an initial load of this table. The table is aliased as "t". Replacement variables are $(nodeId), $(groupId), and $(externalId). | ||
| node_select | varchar(1000) | Y | Specify a where-clause for selecting the nodes that will receive data changes. The node table is aliased as "c". | ||
| tx_id_expression | varchar(1000) | Y | Override the default expression for the transaction identifier that groups the data changes that were committed together. | ||
| excluded_column_names | varchar(1000) | Y | Specify a comma-delimited list of columns that should not be synchronized from this table. | ||
| initial_load_order | integer | N | 1 | Order sequence of this table when an initial load is sent to a node. | |
| create_time | timestamp | N | Timestamp when this entry was created. | ||
| inactive_time | timestamp | Y | Timestamp when this entry was inactivated, which stops capturing of data changes. | ||
| last_updated_by | varchar(50) | Y | The user who last updated this entry. | ||
| last_updated_time | timestamp | N | Timestamp when a user last updated this entry. |
Table 3.8. Trigger
The Parameter table provides a way to manage most SymmetricDS settings in the database.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| external_id | varchar(50) | N | PK | Target the parameter at a specific external id. To target all nodes, use the value of 'ALL.' | |
| node_group_id | varchar(50) | N | PK FK | Target the parameter at a specific node group id. To target all groups, use the value of 'ALL.' | |
| param_key | varchar(100) | N | PK | The name of the parameter. | |
| param_value | varchar(1000) | N | The value of the parameter. |
Table 3.9. Parameter
At runtime, the configuration is used to capture data changes and route them to nodes. The data changes are placed together in a single unit called a batch that can be loaded by another node. Outgoing batches are delivered to nodes and acknowledged. Incoming batches are received and loaded. History is recorded for batch status changes and statistics.
The captured data change that occurred to a row in the database. Entries in Data are created by database triggers.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| data_id | integer | N | PK | Unique identifier for a data | |
| table_name | varchar(50) | N | The name of the table in which a change occurred that this entry records. | ||
| event_type | char(1) | Y | The type of event captured by this entry. For triggers, this is the change that occurred, which is "I" for insert, "U" for update, or "D" for delete. Other events include: "R" for reloading the entire table (or subset of the table) to the node; "S" for running dynamic SQL at the node, which is used for adhoc administration. | ||
| row_data | longvarchar | Y | The captured data change from the synchronized table. The column values are stored in comma-separated values (CSV) format. | ||
| pk_data | longvarchar | Y | The primary key values of the captured data change from the synchronized table. This data is captured for updates and deletes. The primary key values are stored in comma-separated values (CSV) format. | ||
| trigger_hist_id | integer | N | FK | The foreign key to the Trigger Hist entry that contains the primary key and column names for the table being synchronized. | |
| create_time | timestamp | Y | The timestamp when the data change was captured. |
Table 3.10. Data
A history of a table's definition and the Trigger used to capture data from the table. When a database trigger captures a data change, it references a Trigger Hist entry so it is possible to know which columns the data represents. Trigger Hist entries are made during the sync trigger process, which runs at each startup, each night in the SyncTriggersJob, or any time the syncTriggers() JMX method is manually invoked. A new entry is made when a table definition or a Trigger definition is changed, which causes a database trigger to be created or rebuilt.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| trigger_hist_id | integer | N | PK | Unique identifier for a Trigger Hist. | |
| trigger_id | integer | N | FK | Unique identifier for a Trigger. | |
| source_table_name | varchar(50) | N | The name of the source table that will have a trigger installed to watch for data changes. | ||
| source_catalog_name | varchar(50) | Y | The catalog name where the source table resides. | ||
| source_schema_name | varchar(50) | Y | The schema name where the source table resides. | ||
| name_for_insert_trigger | varchar(50) | N | The name used when the insert trigger was created. | ||
| name_for_update_trigger | varchar(50) | N | The name used when the update trigger was created. | ||
| name_for_delete_trigger | varchar(50) | N | The name used when the delete trigger was created. | ||
| table_hash | integer | N | A hash of the table definition, used to detect changes in the definition. | ||
| column_names | longvarchar | N | The column names defined on the table. The column names are stored in comma-separated values (CSV) format. | ||
| pk_column_names | longvarchar | N | The primary key column names defined on the table. The column names are stored in comma-separated values (CSV) format. | ||
| last_trigger_build_reason | char(1) | N | The following reasons for a change are possible: New trigger that has not been created before (N); Schema changes in the table were detected (S); Configuration changes in Trigger (C); Trigger was missing (T). | ||
| create_time | timestamp | N | The date and time when this entry was recorded. | ||
| inactive_time | timestamp | Y | The date and time when a Trigger was inactivated. |
Table 3.11. Trigger Hist
The Data Event represents routing of a Data to one or more Nodes. Entries in Data Event are created by database triggers.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| data_id | integer | N | PK FK | The Data that will be routed. | |
| node_id | varchar(50) | N | PK FK | The Node that will receive the Data. | |
| channel_id | varchar(50) | N | FK | The channel that this data belongs to, such as "prices" | |
| transaction_id | varchar(1000) | Y | An optional transaction identifier that links multiple data changes together as the same transaction. | ||
| batch_id | integer | Y | FK | A unique identifier for a batch that will be a unit of delivery for multiple Data Events. | |
| batched | char(1) | N | 0 | Whether or not this Data Event is prepared for a batch. |
Table 3.12. Data Event
The Outgoing Batch is used for tracking the sending a collection of Data to a Node in the system. A new Outgoing Batch is created by the Outgoing Batch Service and given a status of "NE". After sending the Outgoing Batch to its target Node, the status becomes "SE". The Node responds with either a success status of "OK" or an error status of "ER". An error while sending to the Node also results in an error status of "ER" regardless of whether the Node sends that acknowledgement.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| batch_id | integer | N | PK | A unique ID for the Batch. | |
| node_id | varchar(50) | Y | The Node that will be sent this Batch. | ||
| channel_id | varchar(50) | Y | The Channel that categorizes the Data in this Batch. | ||
| batch_type | char(2) | N | EV | Batch types include events from triggers when rows change (EV) and initial loads of data that send an entire table (IL). | |
| status | char(2) | Y | The current status of the Batch can be newly created (NE), sent to a Node (SE), acknowledged as successful (OK), and error (ER). | ||
| create_time | timestamp | Y | The date and time when the Batch was created. |
Table 3.13. Outgoing Batch
A history of status changes to the Outgoing Batch, along with statistics of the work performed.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| batch_id | integer | N | PK | A unique ID for the Batch. | |
| node_id | varchar(50) | Y | The Node that will be sent this Batch. | ||
| status | char(2) | Y | The current status of the Batch can be newly created (NE), sent to a Node (SE), acknowledged as successful (OK), or error (ER). | ||
| start_time | timestamp | Y | The date and time when the process for this entry was started. (1.4) | ||
| end_time | timestamp | Y | The date and time when the process for this entry was ended. (1.4) | ||
| data_event_count | integer | Y | The number of Data Events in the Batch. This is only populated for a new status (NE). | ||
| failed_data_id | integer | Y | For a status of error (ER), this is the Data entry that was being processed when the Batch failed. | ||
| hostname | varchar(50) | Y | The name of the machine processing the Batch, which is meaningful when running a cluster of SymmetricDS instances. (1.4) | ||
| network_millis | integer | Y | The amount of time in milliseconds spent using the network to send the Batch. This is currently unimplemented. (1.4) | ||
| filter_millis | integer | Y | The amount of time in milliseconds spent in filters. This is currently unimplemented. (1.4) | ||
| database_millis | integer | Y | The amount of time in milliseconds spent in the database. This is only populated for a status of new (NE). (1.4) | ||
| sql_state | varchar(10) | Y | For a status of error (ER), this is the XOPEN or SQL 99 SQL State. (1.4) | ||
| sql_code | integer | Y | For a status of error (ER), this is the error code from the database that is specific to the vendor. (1.4) | ||
| sql_message | varchar(50) | Y | For a status of error (ER), this is the error message that describes the error. (1.4) |
Table 3.14. Outgoing Batch Hist
The Incoming Batch is used for tracking the status of loading an Outgoing Batch from another Node. Data is loaded and commited at the batch level. The status of the Incoming Batch is either successful (OK) or error (ER).
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| batch_id | integer | N | PK | A unique ID for the Batch. | |
| node_id | varchar(50) | N | PK | The Node that sent this Batch. | |
| status | char(2) | Y | The current status of the Batch can be successfully loaded (OK) or error (ER). | ||
| create_time | timestamp | Y | The date and time when the Batch was first received. |
Table 3.15. Incoming Batch
A history of status changes to the Incoming Batch, along with statistics of the work performed.
| Column Name | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| batch_id | integer | N | PK | A unique ID for the Batch. | |
| node_id | varchar(50) | Y | The Node that sent this Batch to be loaded. | ||
| status | char(2) | Y | The current status of the Batch can be loaded successfully (OK), skipped because previously loaded (SK), or error (ER). | ||
| start_time | timestamp | Y | The date and time when the load for this entry was started. | ||
| end_time | timestamp | Y | The date and time when the load for this entry was ended. | ||
| failed_row_number | integer | Y | For a status of error (ER), this is the row number (starting with row one) that was being processed when the Batch failed. | ||
| hostname | varchar(50) | Y | The name of the machine processing the Batch, which is meaningful when running a cluster of SymmetricDS instances. | ||
| network_millis | integer | Y | The amount of time in milliseconds spent using the network to receive the Batch. This is currently unimplemented. | ||
| filter_millis | integer | Y | The amount of time in milliseconds spent in filters. This currently includes time from IDataLoaderFilters. | ||
| database_millis | integer | Y | The amount of time in milliseconds spent in the database. | ||
| sql_state | varchar(10) | Y | For a status of error (ER), this is the XOPEN or SQL 99 SQL State. (1.4) | ||
| sql_code | integer | Y | For a status of error (ER), this is the error code from the database that is specific to the vendor. (1.4) | ||
| sql_message | varchar(50) | Y | For a status of error (ER), this is the error message that describes the error. (1.4) |
Table 3.16. Incoming Batch Hist
