Using unique constraint and index on field with MongoDB


#1

Hi,
I have a JPA data loader application.

One Entity that gets loaded has the following Table annotation. With it a UniqueConstraint and Index.

  @Table(name = "I_POLICYHOLDER", uniqueConstraints = @UniqueConstraint(columnNames = "email"),
        indexes = { @Index(columnList = "email", name="I_POLICYHOLDER_EMAIL") })

Using relational databases (PostgreSQL etc) this Entity is created. Using MongoDB I am seeing this error

[2018-06-01 10:12:28] INFO: HHH000476: Executing import script 'org.hibernate.tool.schema.internal.exec.ScriptSourceInputNonExistentImpl@3a26ec8d'
[2018-06-01 10:12:28] INFO: OGM000076: No explicit or implicit defined JTAPlatform. Using NoJtaPlatform
[2018-06-01 10:12:28] WARN: OGM001218: Cannot use primary key column name 'sequence_name' for id generator, going to use '_id' instead
[2018-06-01 10:12:28] INFO: OGM001202: Closing connection to MongoDB
[2018-06-01 10:12:28] INFO: Closed connection [connectionId{localValue:7, serverValue:7}] to localhost:27017 because the pool has been closed.
[2018-06-01 10:12:28] SEVERE: usage: java StandaloneJPANoddyappLoader <numVehicles<numAddresses<numPolicyHolders>
javax.persistence.PersistenceException: [PersistenceUnit: noddyapp] Unable to build Hibernate SessionFactory
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:970)
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:895)
        at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:58)
        at org.hibernate.ogm.jpa.HibernateOgmPersistence.createEntityManagerFactory(HibernateOgmPersistence.java:57)
        at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:79)
        at org.acme.enterprise.noddyapp.loader.jpa.LoadJPANoddyappHelper.getEntityManagerFactory(LoadJPANoddyappHelper.java:59)
        at org.acme.enterprise.noddyapp.loader.jpa.LoadJPANoddyappHelper.getEntityManagerFactory(LoadJPANoddyappHelper.java:37)
        at org.acme.enterprise.noddyapp.loader.jpa.LoadNoddyappJPA.loadDomain(LoadNoddyappJPA.java:65)
        at org.acme.enterprise.noddyapp.loader.jpa.StandaloneJPANoddyappLoadTracker.startSections(StandaloneJPANoddyappLoadTracker.java:44)
        at org.acme.enterprise.noddyapp.loader.jpa.StandaloneJPANoddyappLoadTracker.start(StandaloneJPANoddyappLoadTracker.java:37)
        at org.acme.enterprise.noddyapp.loader.jpa.StandaloneJPANoddyappLoader.main(StandaloneJPANoddyappLoader.java:44)
Caused by: org.hibernate.HibernateException: OGM001231: Unable to create index I_POLICYHOLDER_EMAIL on collection I_POLICYHOLDER
        at org.hibernate.ogm.datastore.mongodb.impl.MongoDBSchemaDefiner.createIndex(MongoDBSchemaDefiner.java:291)
        at org.hibernate.ogm.datastore.mongodb.impl.MongoDBSchemaDefiner.initializeSchema(MongoDBSchemaDefiner.java:88)
        at org.hibernate.ogm.service.impl.SchemaDefiningObserver.sessionFactoryCreated(SchemaDefiningObserver.java:42)
        at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35)
        at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:374)
        at org.hibernate.ogm.boot.impl.OgmSessionFactoryBuilderImpl.build(OgmSessionFactoryBuilderImpl.java:55)
        at org.hibernate.ogm.boot.impl.OgmSessionFactoryBuilderImpl.build(OgmSessionFactoryBuilderImpl.java:23)
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892)
        ... 9 more
Caused by: com.mongodb.MongoCommandException: Command failed with error 85: 'Index: { v: 1, key: { email: 1 }, name: "I_POLICYHOLDER_EMAIL", ns: "vehicleQQQQ.I_POLICYHOLDER" } already exists with different options: { v: 1, unique: true, key: { email: 1 }, name: "UK5rtet5bte4eueo0edcomw1i3x", ns: "vehicleQQQQ.I_POLICYHOLDER", sparse: true }' on server localhost:27017. The full response is { "ok" : 0.0, "errmsg" : "Index: { v: 1, key: { email: 1 }, name: \"I_POLICYHOLDER_EMAIL\", ns: \"vehicleQQQQ.I_POLICYHOLDER\" } already exists with different options: { v: 1, unique: true, key: { email: 1 }, name: \"UK5rtet5bte4eueo0edcomw1i3x\", ns: \"vehicleQQQQ.I_POLICYHOLDER\", sparse: true }", "code" : 85, "codeName" : "IndexOptionsConflict" }
        at com.mongodb.connection.ProtocolHelper.getCommandFailureException(ProtocolHelper.java:164)
        at com.mongodb.connection.InternalStreamConnection.receiveCommandMessageResponse(InternalStreamConnection.java:295)
        at com.mongodb.connection.InternalStreamConnection.sendAndReceive(InternalStreamConnection.java:255)
        at com.mongodb.connection.UsageTrackingInternalConnection.sendAndReceive(UsageTrackingInternalConnection.java:98)
        at com.mongodb.connection.DefaultConnectionPool$PooledConnection.sendAndReceive(DefaultConnectionPool.java:441)
        at com.mongodb.connection.CommandProtocolImpl.execute(CommandProtocolImpl.java:80)
        at com.mongodb.connection.DefaultServer$DefaultServerProtocolExecutor.execute(DefaultServer.java:189)
        at com.mongodb.connection.DefaultServerConnection.executeProtocol(DefaultServerConnection.java:264)
        at com.mongodb.connection.DefaultServerConnection.command(DefaultServerConnection.java:126)
        at com.mongodb.connection.DefaultServerConnection.command(DefaultServerConnection.java:118)
        at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:226)
        at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:217)
        at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:154)
        at com.mongodb.operation.CommandOperationHelper.executeWrappedCommandProtocol(CommandOperationHelper.java:147)
        at com.mongodb.operation.CreateIndexesOperation$1.call(CreateIndexesOperation.java:173)
        at com.mongodb.operation.CreateIndexesOperation$1.call(CreateIndexesOperation.java:168)
        at com.mongodb.operation.OperationHelper.withConnectionSource(OperationHelper.java:462)
        at com.mongodb.operation.OperationHelper.withConnection(OperationHelper.java:424)
        at com.mongodb.operation.CreateIndexesOperation.execute(CreateIndexesOperation.java:168)
        at com.mongodb.operation.CreateIndexesOperation.execute(CreateIndexesOperation.java:69)
        at com.mongodb.Mongo$3.execute(Mongo.java:837)
        at com.mongodb.MongoCollectionImpl.executeCreateIndexes(MongoCollectionImpl.java:867)
        at com.mongodb.MongoCollectionImpl.createIndexes(MongoCollectionImpl.java:820)
        at com.mongodb.MongoCollectionImpl.createIndexes(MongoCollectionImpl.java:815)
        at com.mongodb.MongoCollectionImpl.createIndex(MongoCollectionImpl.java:800)
        at org.hibernate.ogm.datastore.mongodb.impl.MongoDBSchemaDefiner.createIndex(MongoDBSchemaDefiner.java:279)
        ... 16 more

Is this more of a MongoDB issue because I combined a unique constraint and an index on the same field ?

Regards,
Jeremy


#2

Hi @whitingjr,

You have this issue because unique constraints are implemented as unique indexes for MongoDB. So you end up creating a unique index for the unique constraint and then an additional index on the same field for your index, which MongoDB does not support.

You don’t need to create an index on the email field as your unique constraint already does so.

That being said, I think we should probably log a nice warning instead of throwing the MongoDB exception.


#3

Yes I thought removing a redundant index is the way to go. Which is fine with me.

I agree a useful WARNING message is a better experience. Do you want me to raise a JIRA ?


#4

Yes, please do.

Thanks!