Returns are one of the most common problems faced by retailers. Returns management software can …
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.
Returns are one of the most common problems faced by retailers. Returns management software can …
All sales start with a search. A retailer can do everything right – offering attractive …
In this blazing-fast DevOps world, pushing Point of Sale software updates out to devices in …
With NoSQL databases popping up all over the place, there seems to be a database …
The timestamp data type can cause compatibility issues for cross-platform data replication because database …
When looking into the different types of data storage options in today’s market, one type …
Jumpmind Partners with American Eagle Outfitters to Launch POS of the Future Columbus, Ohio – …
Jumpmind, a leading retail commerce provider, announces the availability of Jumpmind Commerce 4.0 with in-store …
We are thrilled to announce the addition of Clifford Perlman to our leadership team as …
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.