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

OracleDB failed to migrate while schema name contains character "-" #1333

Closed
eugenpp opened this issue Jun 5, 2016 · 5 comments
Closed

OracleDB failed to migrate while schema name contains character "-" #1333

eugenpp opened this issue Jun 5, 2016 · 5 comments

Comments

@eugenpp
Copy link

@eugenpp eugenpp commented Jun 5, 2016

What version of Flyway are you using?

4.0.1

What database are you using (type & version)?

Oracle 11.2

What operating system are you using?

Windows, Linux

What did you do?
  • run command line "flyway migrate" or "flyway repair" or whatever
  • run migration within java application
What did you expect to see?

Migrated database.

What did you see instead?

Database unchanged.

Database: jdbc:oracle:thin:@**** (Oracle 11.2)
....
ERROR: Error restoring current schema to its original setting

with -X switch enabled....

C:\tmp\flyway-4.0.1>flyway -X repair
Flyway 4.0.1 by Boxfuse

DEBUG: Java 1.6.0_45 (Sun Microsystems Inc.)
DEBUG: Windows 8 6.2 amd64

...
DEBUG: flyway.jarDirs -> C:\tmp\flyway-4.0.1\jars
DEBUG: flyway.password -> *********
DEBUG: flyway.url -> jdbc:oracle:*****************
DEBUG: flyway.user -> MY-SCHEMA
DEBUG: flyway.locations -> classpath:db.migration
...
Database: jdbc:oracle:thin:********************* (Oracle 11.2)
DEBUG: DDL Transactions Supported: false
DEBUG: Schema: "MY-SCHEMA"
DEBUG: Spring Jdbc available: false
DEBUG: Scanning for SQL callbacks ...
DEBUG: Scanning for classpath resources at 'classpath:db/migration' (Prefix: '', Suffix: '.sql')
DEBUG: Determining location urls for classpath:db/migration using ClassLoader sun.misc.Launcher$AppClassLoader@40affc70 ...
...
ERROR: Unexpected error
org.flywaydb.core.api.FlywayException: Error restoring current schema to its original setting
        at org.flywaydb.core.internal.dbsupport.DbSupport.restoreCurrentSchema(DbSupport.java:132)
        at org.flywaydb.core.internal.command.DbRepair.repair(DbRepair.java:144)
        at org.flywaydb.core.Flyway$6.execute(Flyway.java:1100)
        at org.flywaydb.core.Flyway$6.execute(Flyway.java:1098)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1365)
        at org.flywaydb.core.Flyway.repair(Flyway.java:1098)
        at org.flywaydb.commandline.Main.executeOperation(Main.java:145)
        at org.flywaydb.commandline.Main.main(Main.java:102)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00922: chřbaj˙ca alebo neplatnß vożba

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
        at org.flywaydb.core.internal.dbsupport.JdbcTemplate.execute(JdbcTemplate.java:219)
        at org.flywaydb.core.internal.dbsupport.oracle.OracleDbSupport.doChangeCurrentSchemaTo(OracleDbSupport.java:55)
        at org.flywaydb.core.internal.dbsupport.DbSupport.restoreCurrentSchema(DbSupport.java:130)
        ... 7 more
@eugenpp
Copy link
Author

@eugenpp eugenpp commented Jun 5, 2016

Error is caused by SQL command

public class OracleDbSupport extends DbSupport {
...  
    @Override
    protected void doChangeCurrentSchemaTo(String schema) throws SQLException {
        jdbcTemplate.execute("ALTER SESSION SET CURRENT_SCHEMA=" + schema);
    }

For schema names with special characters (like "-" in our MY-SCHEMA) schema name should be quoted.

eugenpp pushed a commit to eugenpp/flyway that referenced this issue Jun 5, 2016
@axelfontaine axelfontaine added this to the Flyway 4.0.2 milestone Jun 5, 2016
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Jun 8, 2016
axelfontaine added a commit that referenced this issue Jun 9, 2016
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Jun 9, 2016
@axelfontaine axelfontaine removed this from the Flyway 4.0.2 milestone Jun 9, 2016
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 9, 2016

Schema name is already quoted. Reverting previous erroneous fix. That is not the issue.

Please reopen with exact steps to reproduce if it occurs again.

@eugenpp
Copy link
Author

@eugenpp eugenpp commented Jun 9, 2016

Schema is not quoted. I build version 4.0.1 from sources to add logging info to the source code.

/**
 * Oracle-specific support.
 */
public class OracleDbSupport extends DbSupport {
    @Override
    protected void doChangeCurrentSchemaTo(String schema) throws SQLException {
        String command = "ALTER SESSION SET CURRENT_SCHEMA=" + schema;
        // ADDED LOG MESSAGE
        System.out.println("****"+command+"****");
        jdbcTemplate.execute(command);
    }
}

flyway migrate....

Database: jdbc:oracle:thin:@localhost:1525/MYORA (Oracle 11.2)
Repair of failed migration in metadata table "MY-SCHEMA"."schema_version" not necessary. No failed migration detected.
Successfully repaired metadata table "MY-SCHEMA"."schema_version" (execution time 00:00.055s).
Manual cleanup of the remaining effects the failed migration may still be required.
****ALTER SESSION SET CURRENT_SCHEMA=MY-SCHEMA****
ERROR: Error restoring current schema to its original setting

Note line _ALTER SESSION SET CURRENT_SCHEMA=MY-SCHEMA_.
If you got quoted schema in ALTER SESSION command then I have no idea what is the reason. Maybe some database or environment settings????

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Jun 9, 2016

Thanks for investigating. Then the problem lies in one of the calling methods.

@axelfontaine axelfontaine added this to the Flyway 4.1 milestone Jun 9, 2016
@eugenpp
Copy link
Author

@eugenpp eugenpp commented Jun 9, 2016

SELECT USER FROM dual returns unqoted string regardless schema name is created as case sensitive or not.

case insensitive

create user TEST identified by passwd;
SELECT USER FROM dual # returns TEST without quotes
ALTER SESSION SET CURRENT_SCHEMA=TEST # succeed
ALTER SESSION SET CURRENT_SCHEMA="TEST" # succeed!!!
create user TesT identified by passwd;
SELECT USER FROM dual # returns uppercased! TEST without quotes
ALTER SESSION SET CURRENT_SCHEMA=TEST # succeed
ALTER SESSION SET CURRENT_SCHEMA="TEST" # succeed!!!

case sensitive

create user MY-TEST identified by passwd; # FAILED
# to create user name with hyphen in it you should use quoted name. 
create user "MY-TEST" identified by passwd;
SELECT USER FROM dual # returns MY-TEST without quotes!!!
ALTER SESSION SET CURRENT_SCHEMA=MY-TEST # FAILED
ALTER SESSION SET CURRENT_SCHEMA="MY-TEST" # succeed
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Jan 31, 2017
pradheeps pushed a commit to pradheeps/flyway that referenced this issue Mar 7, 2017
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