Hi,
What I’m doing is loading a Changelog into an HSQL database, then diffing it against a MySQL database (which happens to be empty in this case). The diff creates a new Changelog reporting all the differences (which should be the same as the original Changelog that was loaded into HSQL), but types such as CLOB are converted into VARCHAR(1048768) in the new Changelog. Then I try to do an Update to sync all the differences that were found into the MySQL database, but it fails because MySQL doesn’t like VARCHAR(1048768)
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Column length too big for column 'BLOB_1MB' (max = 65535); use BLOB or TEXT instead
We already “fixed” the Diff comparison to get around this by doing this in the Column.isDataTypeDifferent() function:
if (this.getTable().getDatabase() instanceof HsqlDatabase) {
final int DEFAULT_HSQL_LOB_SIZE = 1048576;
if (this.getDataType() == Types.VARCHAR && this.getColumnSize() == DEFAULT_HSQL_LOB_SIZE) {
thisDataType = "CLOB";
}
if (this.getDataType() == Types.VARBINARY && this.getColumnSize() == DEFAULT_HSQL_LOB_SIZE) {
thisDataType = "BLOB";
}
}
if (otherColumn.getTable().getDatabase() instanceof HsqlDatabase) {
final int DEFAULT_HSQL_LOB_SIZE = 1048576;
if (otherColumn.getDataType() == Types.VARCHAR && otherColumn.getColumnSize() == DEFAULT_HSQL_LOB_SIZE) {
otherDataType = "CLOB";
}
if (otherColumn.getDataType() == Types.VARBINARY && otherColumn.getColumnSize() == DEFAULT_HSQL_LOB_SIZE) {
otherDataType = "BLOB";
}
}
But that only works for comparing them, but not for writing the XML changelog. I think I can fix my problem by doing the same type of thing in the code that writes the XML Changelog, but I’m not exactly sure where that code should go. Any ideas?
Then I modified DiffResult.addMissingTable(), addUnexpectedTable(), addMissingColumn(), addUnexpectedColumn() & addChangedColumn() to call fixColumnTypes() when adding the table/column:
and modified the Diff.checkTables() & checkColumns() to also pass the target database to the DiffResult functions I modified.
That seems to work, except now I just have a strange problem with Foreign Key Indexes flip flopping between adding & dropping every time I run my sync command on a database that should be in-sync with the XML changelog.
Glad you’re getting it figured out. The foreign key/index issues is something of a known issue. They are difficult to tell apart on many database types, and are sometimes the same. I’m planning to work on the diff support more in the 2.1 timeframe.
If you get a chance to look through the code and have suggestions on how to improve it, let me know.
I think I fixed the ForeignKey/Index problem now too. I thought it would be harder, but since it had both a missing ForeignKey and an unexpected Index with the same name, I just look for that in Diff.compare() and cancel them out if they’re both there. I just put this at the end of Diff.compare():
// Hack: This is a fix for Missing Foreign Keys/Unexpected Indexes. In DB's like MySQL, Indexes are
// automatically created with the same name as Foreign Keys.
SortedSet missingForeignKeys = diffResult.getMissingForeignKeys();
SortedSet unexpectedIndexes = diffResult.getUnexpectedIndexes();
SortedSet foreignKeysToRemove = new TreeSet();
SortedSet indexesToRemove = new TreeSet();
for ( ForeignKey foreignKey : missingForeignKeys ) {
for ( Index index : unexpectedIndexes ) {
if ( foreignKey.getName().equals( index.getName() ) ) {
foreignKeysToRemove.add( foreignKey );
indexesToRemove.add( index );
}
}
}
missingForeignKeys.removeAll( foreignKeysToRemove );
unexpectedIndexes.removeAll( indexesToRemove );</ol>
Now I found another bug where DiffResult.printChangeLog() writes an invalid XML changelog if there are no differences (i.e. there’s no tag):
I’m not really an expert with Java XML writing…
I’m looking at the DiffResult.printChangeLog(PrintStream out, Database targetDatabase, XmlWriter xmlWriter) function, and I don’t see it explicitly writing the tag, so I’m guessing either the Document or Element or Transform classes must normally do that when there are tags inside it, so I’m not sure why it’s not writing the tag when there are no elements?
The XML Libraries should automatically create the closing tag. I’m not sure why it is not
Is it getting into public void printChangeLog(String changeLogFile, Database targetDatabase, XmlWriter xmlWriter) which attempts to modify an existing xml file and maybe doesn’t write the closing tag correctly?
I don’t think it was modifying an existing XML file; it should be creating a new one.
Unfortunately I don’t have time to try to figure out the real problem, so I put a bandaid on it which first checks if there are any differences; if there are it does the rest as usual; if not, it skips everything and writes this manually:
