SQLServer: no exception thrown when statement fails

[rekonvald]

We faced with such problem. When one of patch commands fails to execute properly(not first command), flyway does not throw any exceptions, and marks this patch as executed in schema_version table.
It can be reproduced in such way: just create patch with statements(should fail on ) to flyway-samle project and run it.
INSERT INTO test_user (name1) VALUES ('test');
INSERT INTO test_user (name1) VALUES ('test');
Patch should fail but it doesn't.
We investigated this problem and found reason of bug. On executing statement, JdbcTemplate uses execute method. But it would be better to use http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeBatch%28%29

[axelfontaine]

Thanks for the report? Which database are you using? Why would executeBatch() fix this?

[rekonvald]

I use MSSQL.We also wondered why query passes without exceptions even if script is incorrect. The problem occured when we tried to create foreign key with wrong data type. Flyway ran patch, foreign key was not added to the table, but patch was marked as executed in schema_version table. Then I tried to use execute batch, because according to the spec it throws BatchUpdateException (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
Further research showed that execute does not throw exception if first query in the patch ran successfully, as far as I see it happens if foreign key problem occurs.
Did you try to reproduce this bug adding new patch to flyway-samle project?

[rekonvald]

It happens only with MSSQL, for hsqldb and mysql it is ok.

[rekonvald]

Could you please advice, what should be done to make it work in MSSQL? If we do a local changes, we will not have possibility to update flyway, because updated version will still contain this bug....

[cc-rock]

I am facing exactly the same problem, using FlyWay 3.0 with SQL Azure (microsoft JDBC driver).
It seems that if you execute multiple sql commands in the same jdbc statement (which happens if you don't separate each sql command with GO), and an error occurs in some DML command (for example INSERT) that is not the first one in the batch, then the MS jdbc driver does not throw any exception if you don't call getMoreResults() on the jdbc statement, until the result of the failing command is retrieved.
Hope this helps.

[cc-rock]

I wrote a fix for this issue and sent a pull request:
#780

[axelfontaine]

PR Merged. Thanks Carlo!