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
- ALLOW_INVALID_DATES
- NO_ZERO_DATE
- NO_ZERO_IN_DATE
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;