I ran the diffDatabase ant task to compare what should basically be equivalent versions of my database residing on SQLServer and MySQL but got some questionable results and hoping someone can shed some light on them
First, the entries that appear to be matches:
application_event.EVENT_DATETIME
from DATETIME to DATETIME
associate.CHANGE_TYPE
from INT to INT
Second some that should be ‘equivalent’:
action.ACTION_ID
from NUMERIC(20,0) to decimal(20,0)
action.ACTIVE
from NUMERIC(1,0) to decimal(1,0)
The second batch would make sense for a string comparison but the first batch look to be exact matches?
Okay, I found the answer to my own question on this issue. Each database has a ‘snapshot’ generated from the databasemetadata. While both of my columns were determined to be DATETIME datatype, the checkColumns() method in the Diff class has a check "targetColumn.isDifferent(baseColumn). The isDifferent() method calls isDataTypeDifferent() which checks not only the dataType but also several other values, like column size and decimal digits. If these do not ALL match, then the column is considered different.
In my case, MySQL returns a column_size=19 and MS SQL returns column_size=23 - which causes the code to consider these two columns to be different. I believe this difference comes from the fact that MS SQL DATETIME includes the “.xxx” for the millis whereas MySQL does NOT maintain the millis for a DATETIME.
Suggestion - might want to enhance the ‘diff report’ to include more of the column field values, if those are what ultimately cause the columns to not match. Currently the report shows
application_event.EVENT_DATETIME
from DATETIME to DATETIME
If the dataType is the same - maybe including the column_size and/or decimal digits as part of the results might shed more light on the true difference.
Guess the other question is - Is this really the expectation for a DATETIME field?
