Topics

Issue migrating from MySQL 5.7 to 8.0.20: Index/candidate part #0 for xyz already set


Page bloom
 
Edited

Has anyone else seen this issue before?
 
Everything has been working fine for all MySQL 5.x versions but after exporting the database as a SQL dump from 5.7 and importing the SQL dump into a new MySQL 8.0.20 installation (no separate mysql upgrade process is required if using 8.0.16 or later - does it automatically according to MySQL website) when we attempt to start up the app and it performs schema validation/update we see:

[DEBUG] 09:30:35.121 Schema - An error occurred while auto-creating schema elements - rolling back
[DEBUG] 09:30:35.121 Schema - Schema Transaction ROLLING BACK with connection "com.sun.proxy.$Proxy30@2075f909"
[DEBUG] 09:30:35.121 Schema - Schema Transaction CLOSED with connection "com.sun.proxy.$Proxy30@2075f909"
[DEBUG] 09:30:35.122 Connection - ManagedConnection COMMITTING : "org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl@6092a087 [conn=com.sun.proxy.$Proxy30@2075f909, commitOnRelease=true, closeOnRelease=true, closeOnTxnEnd=true]"
[DEBUG] 09:30:35.122 Connection - ManagedConnection CLOSED : "org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl@6092a087 [conn=com.sun.proxy.$Proxy30@2075f909, commitOnRelease=true, closeOnRelease=true, closeOnTxnEnd=true]"
[WARN ] 09:30:35.123 Persistence - Unknown Error during auto starter execution. : Index/candidate part #0 for sa_wkf_orgroledef already set
Index/candidate part #0 for sa_wkf_orgroledef already set
org.datanucleus.exceptions.NucleusException: Index/candidate part #0 for sa_wkf_orgroledef already set
at org.datanucleus.store.rdbms.key.ColumnOrderedKey.setColumn(ColumnOrderedKey.java:106)
at org.datanucleus.store.rdbms.table.TableImpl.getExistingIndices(TableImpl.java:1150)
at org.datanucleus.store.rdbms.table.TableImpl.validateIndices(TableImpl.java:560)
at org.datanucleus.store.rdbms.table.TableImpl.validateConstraints(TableImpl.java:386)
at org.datanucleus.store.rdbms.table.ClassTable.validateConstraints(ClassTable.java:3596)
at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3502)
at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2938)
at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:118)
at org.datanucleus.store.rdbms.RDBMSStoreManager.manageClasses(RDBMSStoreManager.java:1641)
at org.datanucleus.PersistenceNucleusContextImpl.initialiseAutoStart(PersistenceNucleusContextImpl.java:802)
at org.datanucleus.PersistenceNucleusContextImpl.initialise(PersistenceNucleusContextImpl.java:498)
at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:859)
at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.createPersistenceManagerFactory(JDOPersistenceManagerFactory.java:346)
at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.getPersistenceManagerFactory(JDOPersistenceManagerFactory.java:225)
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 javax.jdo.JDOHelper$16.run(JDOHelper.java:1975)
at java.security.AccessController.doPrivileged(Native Method)
at javax.jdo.JDOHelper.invoke(JDOHelper.java:1970)
at javax.jdo.JDOHelper.invokeGetPersistenceManagerFactoryOnImplementation(JDOHelper.java:1177)
at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:814)
at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:702)
at com.sas.framework.expojo.jdo.JdoExpojoContextFactory.init(JdoExpojoContextFactory.java:213)

The method in which the exception is thrown is ColumnOrderedKey:

    public void setColumn(int seq, Column col) {
        this.assertSameDatastoreObject(col);
        setMinSize(this.columns, seq + 1);
        setMinSize(this.columnOrdering, seq + 1);
        if (this.columns.get(seq) != null) {
            throw (new NucleusException("Index/candidate part #" + seq + " for " + this.table + " already set")).setFatal();
        } else {
            this.columns.set(seq, col);
            this.columnOrdering.set(seq, (Object)null);
        }
    }

I'm trying to debug why this would be occurring and need some background info on what this method is trying to do.

From the source comments ColumnOrderedKey is a composite key where the ordering of the columns is important.

It looks like it's trying to set a particular column in the columns list but that element of the list has already been set beforehand - any suggestions as to how that could occur or an approach to debugging that scenario?


Andy
 

Sets the relative position of a column that is part of an index. So if you have a composite index formed of two columns, the index behaves differently dependent on the ordering / position of columns.

CREATE INDEX i1 ON t1 (col1 ASC, col2 DESC);

Debug what MySQL JDBC driver is returning from the getIndexes call (i.e what info TableImpl.getExistingIndices has available to it), how many columns in the index, their positions, their ordering etc.


Page bloom
 

Thanks for those details. I'll try to debug this.

I assume that ColumnOrderedKey is used regardless of the number of columns in the index. i.e. it is used for single key indexes as well as composite key indexes.

I base this assumption on the table involved in the above exception having no composite key indexes in either the meta data nor the existing table: just three single key indexes: primary key, an index on a foreign key and an index on the classid column (discriminator column).

If the index name in the existing database was different to what is expected by DN would that cause this type of issue? I wonder if the algorithm used for forming the index name could have different behaviour under the new MySQL 8.x JDBC driver (Connector/J) or MySQL 8.x database.


Page bloom
 
Edited

This paragraph from the "Changes in MySQL 8.0.16" page (https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-16.html) looks interesting:

Incompatible Change: In MySQL 5.7, specifying a FOREIGN KEY definition for an InnoDB table without a CONSTRAINT symbol clause, or specifying the CONSTRAINT keyword without a symbol, causes InnoDB to use a generated constraint name. That behavior changed in MySQL 8.0, with InnoDB using the FOREIGN KEY index_name value instead of a generated name. Because constraint names must be unique per schema (database), the change caused errors due to foreign key index names that were not unique per schema. To avoid such errors, the new constraint naming behavior has been reverted, and InnoDB once again uses a generated constraint name.

For consistency with InnoDB, the NDB storage engine now uses a generated constraint name if the CONSTRAINT symbol clause is not specified, or the CONSTRAINT keyword is specified without a symbol. In NDB releases based on MySQL 5.7 and earlier MySQL 8.0 releases, NDB used the FOREIGN KEY index_name value.

The changes described above may introduce incompatibilities for applications that depend on the previous foreign key constraint naming behavior. (Bug #29173134)


Although, as we're using InnoDB and not NDB then it appears as though they've reverted the new 8.0 behaviour back to 5.7 behaviour as of 8.0.16 - confused :~]