search

nuodb / migration-tools

Migration tools for NuoDB
http://nuodb.github.com/migration-tools/
BSD 3-Clause "New" or "Revised" License
27 stars 10 forks source link

Migrator fails when creating UNIQUE indexes because it attempts to create them twice #43

Open philip-stoev opened 6 years ago

philip-stoev commented 6 years ago

The backup.cat contains the following:

          <column name="CORPID">
            <type code="4" name="integer" size="4" precision="9" scale="0"/>
          </column>
          <column name="CORPNAME">
            <type code="12" name="varchar" size="25" precision="25" scale="0"/>
          </column>
          <column name="CORPDESC" nullable="true">
            <type code="2005" name="clob" size="8" precision="100" scale="0"/>
          </column>
          <column name="CORPFBITS" nullable="true">
            <type code="-5" name="bigint" size="8" precision="19" scale="0"/>
          </column>
          <column name="CORPADDFS" nullable="true">
            <type code="2004" name="binary varying" size="50" precision="50" scale="0"/>
          </column>
          <column name="CREATIONTIME" nullable="true">
            <type code="93" name="timestamp" size="12" precision="16" scale="6"/>
          </column>
          <column name="LASTMODTIME" nullable="true">
            <type code="-5" name="bigint" size="8" precision="19" scale="0"/>
          </column>
          <primary-key name="CORPINFO..PRIMARY_KEY">
            <column name="CORPID"/>
          </primary-key>
          <index name="CORPINFO_CORPNAME" unique="true">
            <column name="CORPNAME"/>
          </index>
          <index name="CORPINFO_CORPFBITS" unique="false">
            <column name="CORPFBITS"/>
          </index>
          <index name="CORPINFO_LASTMODTIME" unique="false">
            <column name="LASTMODTIME"/>
          </index>
        </table>

This causes migrator to generate the following DDL:

CREATE TABLE "CORPINFO" ("CORPID" INTEGER NOT NULL, "CORPNAME" VARCHAR(25) NOT NULL UNIQUE, "CORPDESC" CLOB, "CORPFBITS" BIGINT, "CORPADDFS" VARBINARY(50), "CREATIONTIME" TIMESTAMP(6), "LASTMODTIME" BIGINT);
CREATE UNIQUE INDEX "IDX_Corpinfo_corpinfo_corpname" ON "dg"."CORPINFO" ("CORPNAME");
...

Which fails because we do not allow multiple identical indexes on the same table. MIgrator should have only created a single index, so either use the UNIQUE keyword in CREATE TABLE or use CREATE UNIQUE INDEX, but not both.

The entire dump directory is available from bidata:///space/builds/nuo_datasets/SubDBdump.tar.gz

philip-stoev commented 6 years ago

Stack trace:

0 != 1 : 02-06-2018 11:25:10 (ERROR) (com.nuodb.migrator.jdbc.metadata.generator.ScriptExporterBase.exportScript(ScriptExporterBase.java:55)) - Failed exporting script CREATE UNIQUE INDEX "IDX_Corpinfo_corpinfo_corpname" ON "DG"."CORPINFO" ("CORPNAME"), CREATE INDEX "IDX_Corpinfo_corpinfo_corpfbits" ON "DG"."CORPINFO" ("CORPFBITS"), CREATE INDEX "IDX_Corpinfo_corpinfo_lastmodtime" ON "DG"."CORPINFO" ("LASTMODTIME")
02-06-2018 11:25:10 (ERROR) (com.nuodb.migrator.bootstrap.Bootstrap.main(Bootstrap.java:122)) - Execution failed
com.nuodb.migrator.jdbc.session.WorkException: java.sql.SQLSyntaxErrorException: a unique index on these columns already exists: CORPINFO_UNIQUE_0
    at com.nuodb.migrator.jdbc.session.SimpleWorkManager.close(SimpleWorkManager.java:181)
    at com.nuodb.migrator.backup.loader.SimpleBackupLoaderManager.close(SimpleBackupLoaderManager.java:175)
    at com.nuodb.migrator.backup.loader.BackupLoader.load(BackupLoader.java:373)
    at com.nuodb.migrator.backup.loader.BackupLoader.load(BackupLoader.java:171)
    at com.nuodb.migrator.backup.loader.BackupLoader.load(BackupLoader.java:161)
    at com.nuodb.migrator.backup.loader.BackupLoader.load(BackupLoader.java:157)
    at com.nuodb.migrator.load.LoadJob.execute(LoadJob.java:112)
    at com.nuodb.migrator.job.SimpleJobExecutor.execute(SimpleJobExecutor.java:114)
    at com.nuodb.migrator.Migrator.execute(Migrator.java:79)
    at com.nuodb.migrator.Migrator.execute(Migrator.java:69)
    at com.nuodb.migrator.cli.run.CliLoadJob.execute(CliLoadJob.java:89)
    at com.nuodb.migrator.cli.run.CliRunAdapter.execute(CliRunAdapter.java:65)
    at com.nuodb.migrator.cli.CliHandler.handleCommand(CliHandler.java:223)
    at com.nuodb.migrator.cli.CliHandler.handleOptionSet(CliHandler.java:169)
    at com.nuodb.migrator.cli.CliHandler.parse(CliHandler.java:85)
    at com.nuodb.migrator.cli.CliHandler.boot(CliHandler.java:78)
    at com.nuodb.migrator.bootstrap.Bootstrap.boot(Bootstrap.java:84)
    at com.nuodb.migrator.bootstrap.Bootstrap.main(Bootstrap.java:119)
Caused by: java.sql.SQLSyntaxErrorException: a unique index on these columns already exists: CORPINFO_UNIQUE_0
    at com.nuodb.jdbc.SQLStateException$9.create(SQLStateException.java:64)
    at com.nuodb.jdbc.SQLStateException.raise(SQLStateException.java:139)
    at com.nuodb.jdbc.RemConnection.sendAndReceive(RemConnection.java:1077)
    at com.nuodb.jdbc.RemConnection.sendAndReceive(RemConnection.java:1049)
    at com.nuodb.jdbc.RemStatement.executeUpdate(RemStatement.java:294)
    at com.nuodb.jdbc.RemStatement.executeUpdate(RemStatement.java:286)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.nuodb.migrator.utils.ReflectionUtils.invokeMethodNoWrap(ReflectionUtils.java:160)
    at com.nuodb.migrator.utils.aop.ReflectionProxy$1.proceed(ReflectionProxy.java:76)
    at com.nuodb.migrator.utils.aop.MethodInterceptors$4.invoke(MethodInterceptors.java:149)
    at com.nuodb.migrator.utils.aop.ReflectionProxy$1.proceed(ReflectionProxy.java:78)
    at com.nuodb.migrator.jdbc.connection.LoggingConnectionProvider$8.invoke(LoggingConnectionProvider.java:177)
    at com.nuodb.migrator.utils.aop.ReflectionProxy$1.proceed(ReflectionProxy.java:78)
    at com.nuodb.migrator.utils.aop.ReflectionProxy.invoke(ReflectionProxy.java:81)
    at com.sun.proxy.$Proxy2.executeUpdate(Unknown Source)
    at com.nuodb.migrator.jdbc.metadata.generator.ConnectionScriptExporter.doExportScript(ConnectionScriptExporter.java:63)
    at com.nuodb.migrator.jdbc.metadata.generator.ScriptExporterBase.exportScript(ScriptExporterBase.java:52)
    at com.nuodb.migrator.jdbc.metadata.generator.CompositeScriptExporter.exportScript(CompositeScriptExporter.java:63)
    at com.nuodb.migrator.jdbc.metadata.generator.CompositeScriptExporter.exportScripts(CompositeScriptExporter.java:70)
    at com.nuodb.migrator.backup.loader.LoadConstraintWork.execute(LoadConstraintWork.java:124)
    at com.nuodb.migrator.jdbc.session.SimpleWorkManager.execute(SimpleWorkManager.java:125)
    at com.nuodb.migrator.jdbc.session.SimpleWorkManager.execute(SimpleWorkManager.java:88)
    at com.nuodb.migrator.jdbc.session.SimpleWorkManager.execute(SimpleWorkManager.java:105)
    at com.nuodb.migrator.backup.loader.BackupLoader$2.run(BackupLoader.java:620)
    at com.nuodb.migrator.utils.concurrent.ForkJoinTask$AdaptedRunnable.exec(ForkJoinTask.java:1103)
    at com.nuodb.migrator.utils.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:279)
    at com.nuodb.migrator.utils.concurrent.ForkJoinWorkerThread.execTask(ForkJoinWorkerThread.java:573)
    at com.nuodb.migrator.utils.concurrent.ForkJoinPool.scan(ForkJoinPool.java:703)
    at com.nuodb.migrator.utils.concurrent.ForkJoinPool.work(ForkJoinPool.java:560)
    at com.nuodb.migrator.utils.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:370)

Related internal JIRA DB-22857