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

Jump to bottom

Flyway upgrade from 3.2.1 to 4.0 fails with Oracle error ORA-00955: name is already used by an existing object #1235

Closed
kevin-wimmer opened this issue Mar 10, 2016 · 6 comments
Closed

Flyway upgrade from 3.2.1 to 4.0 fails with Oracle error ORA-00955: name is already used by an existing object #1235

kevin-wimmer opened this issue Mar 10, 2016 · 6 comments
Labels
d: Oracle t: bug
Milestone
Flyway 4.0.1

Comments

@kevin-wimmer
Copy link

What version of Flyway are you using?

3.2.1

What database are you using (type & version)?

Oracle 11g (11.2.0.1.0)

What operating system are you using?

Windows 7 SP1

What did you do?

After replacing flyway-core-3.2.1.jar with flyway-core-4.0.jar and redeploying my application, Flyway attempts to upgrade the "schema_version" table, but fails with the following Oracle error:

ORA-00955: name is already used by an existing object

At issue is the fact that the upgrade script removes the old PK constraint using the following statement:

ALTER TABLE "schema_version" DROP CONSTRAINT "schema_version_pk";

While this does remove the PK constraint, it does not always remove the implicit index created with the same name. As a result, the upgrade script fails when attempting to create a new constraint with the name "schema_version_pk". Replacing the above statement with the one below in the Oracle upgrade script resolves this issue:

ALTER TABLE "schema_version" DROP PRIMARY KEY DROP INDEX;
What did you expect to see?

I expected to see the flyway "schema_version" table successfully upgraded.

What did you see instead?
org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: 
Script failed
-------------
SQL State  : 42000
Error Code : 955
Message    : ORA-00955: name is already used by an existing object
Line       : 22
Statement  : ALTER TABLE "EASE"."schema_version" ADD CONSTRAINT "schema_version_pk" PRIMARY KEY ("installed_rank")

    at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.upgradeIfNecessary(MetaDataTableImpl.java:79)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1356)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.jboss.reflect.plugins.introspection.ReflectionUtils.invoke(ReflectionUtils.java:59)
    at org.jboss.reflect.plugins.introspection.ReflectMethodInfoImpl.invoke(ReflectMethodInfoImpl.java:150)
    at org.jboss.joinpoint.plugins.BasicMethodJoinPoint.dispatch(BasicMethodJoinPoint.java:66)
    at org.jboss.kernel.plugins.dependency.KernelControllerContextAction$JoinpointDispatchWrapper.execute(KernelControllerContextAction.java:241)
    at org.jboss.kernel.plugins.dependency.ExecutionWrapper.execute(ExecutionWrapper.java:47)
    at org.jboss.kernel.plugins.dependency.KernelControllerContextAction.dispatchExecutionWrapper(KernelControllerContextAction.java:109)
    at org.jboss.kernel.plugins.dependency.KernelControllerContextAction.dispatchJoinPoint(KernelControllerContextAction.java:70)
    at org.jboss.kernel.plugins.dependency.LifecycleAction.installActionInternal(LifecycleAction.java:221)
    at org.jboss.kernel.plugins.dependency.InstallsAwareAction.installAction(InstallsAwareAction.java:54)
    at org.jboss.kernel.plugins.dependency.InstallsAwareAction.installAction(InstallsAwareAction.java:42)
    at org.jboss.dependency.plugins.action.SimpleControllerContextAction.simpleInstallAction(SimpleControllerContextAction.java:62)
    at org.jboss.dependency.plugins.action.AccessControllerContextAction.install(AccessControllerContextAction.java:71)
    at org.jboss.dependency.plugins.AbstractControllerContextActions.install(AbstractControllerContextActions.java:51)
    at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
    at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1631)
    at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:934)
    at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1082)
    at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:984)
    at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:774)
    at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:540)
    at org.jboss.deployers.vfs.deployer.kernel.BeanMetaDataDeployer.deploy(BeanMetaDataDeployer.java:121)
    at org.jboss.deployers.vfs.deployer.kernel.BeanMetaDataDeployer.deploy(BeanMetaDataDeployer.java:51)
    at org.jboss.deployers.spi.deployer.helpers.AbstractSimpleRealDeployer.internalDeploy(AbstractSimpleRealDeployer.java:62)
    at org.jboss.deployers.spi.deployer.helpers.AbstractRealDeployer.deploy(AbstractRealDeployer.java:50)
    at org.jboss.deployers.plugins.deployers.DeployerWrapper.deploy(DeployerWrapper.java:171)
    at org.jboss.deployers.plugins.deployers.DeployersImpl.doDeploy(DeployersImpl.java:1439)
    at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1157)
    at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1178)
    at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1210)
    at org.jboss.deployers.plugins.deployers.DeployersImpl.install(DeployersImpl.java:1098)
    at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
    at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1631)
    at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:934)
    at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1082)
    at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:984)
    at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:822)
    at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:553)
    at org.jboss.deployers.plugins.deployers.DeployersImpl.process(DeployersImpl.java:781)
    at org.jboss.deployers.plugins.main.MainDeployerImpl.process(MainDeployerImpl.java:702)
    at org.jboss.system.server.profileservice.repository.MainDeployerAdapter.process(MainDeployerAdapter.java:117)
    at org.jboss.system.server.profileservice.repository.ProfileDeployAction.install(ProfileDeployAction.java:70)
    at org.jboss.system.server.profileservice.repository.AbstractProfileAction.install(AbstractProfileAction.java:53)
    at org.jboss.system.server.profileservice.repository.AbstractProfileService.install(AbstractProfileService.java:361)
    at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
    at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1631)
    at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:934)
    at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1082)
    at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:984)
    at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:822)
    at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:553)
    at org.jboss.system.server.profileservice.repository.AbstractProfileService.activateProfile(AbstractProfileService.java:306)
    at org.jboss.system.server.profileservice.ProfileServiceBootstrap.start(ProfileServiceBootstrap.java:271)
    at org.jboss.bootstrap.AbstractServerImpl.start(AbstractServerImpl.java:461)
    at org.jboss.Main.boot(Main.java:221)
    at org.jboss.Main$1.run(Main.java:556)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1792)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1745)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:334)
    at org.jboss.resource.adapter.jdbc.WrappedStatement.execute(WrappedStatement.java:91)
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238)
    at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114)
    ... 63 more
@PeeZu
Copy link

PeeZu commented Mar 10, 2016

Same issue. Cannot be upgraded safely.

@axelfontaine axelfontaine added this to the Flyway 4.0.1 milestone Mar 10, 2016
@marbot
Copy link

marbot commented Apr 5, 2016

Same here. We're trying to upgrade from 3.2.1 to 4.0 on Oracle 11.2.0.4.

@axelfontaine
Copy link
Contributor

Fixed

axelfontaine pushed a commit to flyway/flywaydb.org that referenced this issue Apr 19, 2016
Added notes for flyway/flyway#1235
3c37a15
@tsbx
Copy link
Contributor

tsbx commented Apr 19, 2016 •
edited

Hello Axel,

I don't see any code commit referencing this issue.
Is it this diff : d18f67f#diff-9f5e725829945ed3f57ccca26d54cb1f ?

@tsbx tsbx mentioned this issue Apr 23, 2016
Merged
@tsbx
Copy link
Contributor

tsbx commented Apr 23, 2016

The change d18f67f#diff-9f5e725829945ed3f57ccca26d54cb1f does not solve the issue.

I created PR #1290 with the modifications @kevin-wimmer suggested.

@tsbx
Copy link
Contributor

tsbx commented Apr 29, 2016

@axelfontaine Could you review #1290 when you have a chance ?

axelfontaine pushed a commit that referenced this issue May 5, 2016
@tsbx
Fix SQL statement for flyway 4.0 upgrade on Oracle [#1235] (#1290)
31d502d 
This change contains two modifications:
* delete the name of the PK to be dropped, because it is not valid SQL
  (see https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2104014)
* implement the only working solution for #1235, which was initially suggested by @kevin-wimmer -
  using the DROP INDEX clause
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
d: Oracle t: bug
Projects
None yet
Milestone
Flyway 4.0.1
Development

No branches or pull requests
5 participants
@axelfontaine @marbot @PeeZu @tsbx @kevin-wimmer