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.

Sign up for GitHub

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

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

Open
philip-stoev opened this issue Aug 28, 2018 · 1 comment
Open

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

philip-stoev opened this issue Aug 28, 2018 · 1 comment

Comments

@philip-stoev
Copy link

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 philip-stoev changed the title UNIQUE indexes created twice Migrator fails when creating UNIQUE indexes because it attempts to create them twice Aug 28, 2018
@philip-stoev
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@philip-stoev