Questionable results when diffDatabase run to compare MySQL to SLQServer databases

General Discussion
1

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?

2

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?