Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Sql Server schema clean throws exception for indices on virtual columns for json attributes #2096

Closed
hdconradi opened this issue Aug 1, 2018 · 1 comment

Comments

@hdconradi
Copy link

@hdconradi hdconradi commented Aug 1, 2018

We are using Sql Server (2017/2016) with flyway 5.1.4 in a spring boot environment and the clean method is called from the java API.

In one of our table we store json documents in a column.
It is possible to create an index on attributes of the json document by first defining a virtual column.
However, those indices are not deleted which then leads to an exception when dropping the schema.

Consider the following examples:

CREATE TABLE ${schema}.test1 (
  resource      NVARCHAR(MAX) NOT NULL,
  v_id as JSON_VALUE(resource, '$.id')
  CONSTRAINT idx_test1 UNIQUE
)

CREATE TABLE ${schema}.test2 (
  resource     NVARCHAR(MAX) NOT NULL,

  v_id1 AS JSON_VALUE(resource, '$.id1'),
  v_id2 AS JSON_VALUE(resource, '$.id2')
)

CREATE UNIQUE INDEX idx_test2 ON ${schema}.test2 (v_id1, v_id2)
GO

Trying to clean that schema results in the following exception:

Unable to clean schema [test_schema]
----------------------------------
SQL State  : S0001
Error Code : 5074
Message    : The index 'idx_test1' is dependent on column 'v_id1'.

        at org.flywaydb.core.internal.database.Schema.clean(Schema.java:151) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.internal.command.DbClean$2.call(DbClean.java:149) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.internal.command.DbClean$2.call(DbClean.java:146) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.internal.command.DbClean.dropSchema(DbClean.java:146) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:111) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.Flyway.doClean(Flyway.java:1043) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.Flyway.access$300(Flyway.java:78) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.Flyway$3.execute(Flyway.java:1061) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.Flyway$3.execute(Flyway.java:1054) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.Flyway.execute(Flyway.java:1238) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.Flyway.clean(Flyway.java:1054) ~[flyway-core-5.1.4.jar!/:?]
        at de.icw.inf.spring.boot.flyway.clean.CleanMigrateFlywayStrategy.migrate(CleanMigrateFlywayStrategy.java:30) ~[icw-spring-boot-flyway-clean-extension-2.0-SNAPSHOT.jar!/:2.0-SNAPSHOT]
        at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:63) ~[spring-boot-autoconfigure-2.0.3.RELEASE.jar!/:2.0.3.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1767) ~[spring-beans-5.0.7.RELEASE.jar!/:5.0.7.RELEASE]
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1704) ~[spring-beans-5.0.7.RELEASE.jar!/:5.0.7.RELEASE]
        ... 21 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 'idx_test1' is dependent on column 'v_id1'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:548) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:479) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:463) ~[mssql-jdbc-6.4.0.jre8.jar!/:?]
        at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.execute(JdbcTemplate.java:225) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.internal.database.sqlserver.SQLServerSchema.doClean(SQLServerSchema.java:235) ~[flyway-core-5.1.4.jar!/:?]
        at org.flywaydb.core.internal.database.Schema.clean(Schema.java:149) ~[flyway-core-5.1.4.jar!/:?]
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Aug 2, 2018

Thank you for the very clear report and the concise example. This was a great help to reproduce the issue. Fixed.

axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Aug 2, 2018
dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants