Americans spent $135.5 billion on back-to-school and back-to-college shopping in 2023, making the season a …
Cloud-native POS platform for seamless omnichannel customer experience.
A single hub for all promotions campaigns.
The most advanced synchronization solution for databases and file systems.
Data configuration and batch automation across different disparate systems and vendors.
Americans spent $135.5 billion on back-to-school and back-to-college shopping in 2023, making the season a …
Google may have introduced the concept of micro-moments almost ten years ago, but year after …
We’ve all heard the words omnichannel and seamless shopping by now…but achieving it can be …
Sybase ASE (more recently known as SAP Adaptive Server Enterprise), announced its end of mainstream …
Azure Blob Storage has become a popular option for storing files in the cloud. And …
SymmetricDS now supports loading data and changes into RabbitMQ, so you can capture changes in …
Jumpmind Powers Point of Sale and Promotions Execution for Landmark Retail, One of the Largest …
Retail Technology Leader Jumpmind to Enable Mobile Point of Sale and Inventory Management for DTLR/VILLA …
The retailer is charting its next chapter with retail technology modernization to power inspired omnichannel …
Jumpmind Powers Point of Sale and Promotions Execution for Landmark Retail, One of the Largest …
Jumpmind Launches The Jumpmind Cloud, Powered by Amazon Web Services New Offering Provides Newfound Application …
Retail Technology Leader Jumpmind to Enable Mobile Point of Sale and Inventory Management for DTLR/VILLA …
Cloud-native POS platform for seamless omnichannel customer experience.
A single hub for all promotions campaigns.
The most advanced synchronization solution for databases and file systems.
Data configuration and batch automation across different disparate systems and vendors.
Supporting date time and timestamp values in MySQL can be challenging while replicating from a another database that uses different rules. In this article we walk through some issues with syncing date time values into MySQL and how to filter them out so that your replication stay on track.
SQL Modes and Invalid Dates
MySQL uses SQL Modes to determine how date and time’s will be accepted in a MySQL database. Often combinations of these settings can cause issues when replicating to and from MySQL if the other side is not using the same rules.
Some SQL Modes that might affect replication
How to determine what SQL Mode you have running
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
Timestamp Ranges
MIN : ‘1970-01-01 00:00:01’
MAX : ‘2038-01-19 03:14:07’
Datetime Ranges
MIN : ‘1000-01-01 00:00:00’
MAX : ‘9999-12-31 23:59:59’
SymmetricDS Load Filter
Here is a sample load filter that can be configured to clean any invalid TIMESTAMP or DATETIME data values that are out of range.
org.jumpmind.db.model.Table targetTable = engine.getDatabasePlatform().getTableFromCache(table.getCatalog(), table.getSchema(), table.getName(), false);
List timestampColumnIndexes = new ArrayList();
List datetimeColumnIndexes = new ArrayList();
int i = 0;
for (org.jumpmind.db.model.Column column : targetTable.getColumns()) {
if (column.getPlatformColumns() != null) {
for (Map.Entry entry : column.getPlatformColumns().entrySet()) {
if (entry.getValue().getType() != null && entry.getValue().getType().equals("TIMESTAMP")) {
timestampColumnIndexes.add(i);
} else if (entry.getValue().getType() != null && entry.getValue().getType().equals("DATETIME")) {
datetimeColumnIndexes.add(i);
}
}
}
i++;
}
String[] values = new String[0];
if (timestampColumnIndexes.size() > 0 || datetimeColumnIndexes > 0) {
values = data.getParsedData(org.jumpmind.symmetric.io.data.CsvData.ROW_DATA);
}
if (values != null) {
for (int j : timestampColumnIndexes) {
if (values[j] != null && (values[j].compareTo("1970-01-01 00:00:01") < 0 || values[j].compareTo("2038-01-19 03:14:07") > 0)) {
values[j] = null;
}
}
for (int j : datetimeColumnIndexes) {
if (values[j] != null && (values[j].compareTo("1000-01-01 00:00:00") < 0 || values[j].compareTo("9999-12-31 23:59:59") > 0)) {
values[j] = null;
}
}
}
return true;
Josh has worked in the IT industry for over 20 years as a developer, tech lead, and sales engineer. He has worked across a variety of businesses including retail, telecommunications, education, and healthcare. When he is not developing he enjoys whatever sporting event might be taking place.