MS SQL: failure when inserting row if a column has a foreign key constraint

[tsegismont]

There are many tests in the HR suite using parent/child data. For example: EagerTest.

With MS SQL, these tests fail with:

javax.persistence.PersistenceException: org.hibernate.HibernateException: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.

I suspect this is due to the column definition:

[Hibernate] create table Node (id int not null, string varchar(255), version int, parent_id int, primary key (id))

Perhaps it should be explicitly declared as NULL

[gavinking]

Perhaps it should be explicitly declared as NULL

I don't think so. I think there must be something else going on.

[gavinking]

(Perhaps a table definition isn't being dropped correctly between tests?)

[tsegismont]

If I run only org.hibernate.reactive.EagerTest#testEagerCollectionFetch on a fresh DB, I get:

INFO Version [vert.x-eventloop-thread-0] HHH000412: Hibernate ORM core version 5.4.29.Final
Using database type: MSSQLSERVER
INFO Version [vert.x-worker-thread-0] HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
INFO Dialect [vert.x-worker-thread-0] HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect
INFO ReactiveIntegrator [vert.x-worker-thread-0] HRX000001: Hibernate Reactive Preview
INFO DefaultSqlClientPool [vert.x-worker-thread-0] HRX000011: SQL Client URL [jdbc:sqlserver://localhost:1433;user=SA;password=HReact@~~]
INFO DefaultSqlClientPool [vert.x-worker-thread-0] HRX000012: Connection pool size: 5
INFO DefaultSqlClientPool [vert.x-eventloop-thread-0] HRX000013: Detected driver [io.vertx.pgclient.spi.PgDriver]
INFO DefaultSqlClientPool [vert.x-eventloop-thread-0] HRX000013: Detected driver [io.vertx.mysqlclient.spi.MySQLDriver]
INFO DefaultSqlClientPool [vert.x-eventloop-thread-0] HRX000013: Detected driver [io.vertx.db2client.spi.DB2Driver]
INFO DefaultSqlClientPool [vert.x-eventloop-thread-0] HRX000013: Detected driver [io.vertx.mssqlclient.spi.MSSQLDriver]
[Hibernate] alter table Element drop constraint FKcwlxr32pujpq4d7va12w278fw
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Statement(s) could not be prepared.]
[Hibernate] alter table Node drop constraint FKnuolrnqa3oux6ln0vkqeuhsda
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Statement(s) could not be prepared.]
[Hibernate] drop table Element
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Cannot drop the table 'Element', because it does not exist or you do not have permission.]
[Hibernate] drop table Node
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Cannot drop the table 'Node', because it does not exist or you do not have permission.]
[Hibernate] drop sequence hibernate_sequence
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Cannot drop the sequence 'hibernate_sequence', because it does not exist or you do not have permission.]
[Hibernate] alter table Element drop constraint FKcwlxr32pujpq4d7va12w278fw
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Statement(s) could not be prepared.]
[Hibernate] alter table Node drop constraint FKnuolrnqa3oux6ln0vkqeuhsda
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Statement(s) could not be prepared.]
[Hibernate] drop table Element
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Cannot drop the table 'Element', because it does not exist or you do not have permission.]
[Hibernate] drop table Node
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Cannot drop the table 'Node', because it does not exist or you do not have permission.]
[Hibernate] drop sequence hibernate_sequence
WARN GenerationTargetToDatabase [vert.x-eventloop-thread-0] HRX000021: DDL command failed [io.vertx.mssqlclient.MSSQLException: Cannot drop the sequence 'hibernate_sequence', because it does not exist or you do not have permission.]
[Hibernate] create sequence hibernate_sequence start with 1 increment by 1
[Hibernate] create table Element (id int not null, node_id int, primary key (id))
[Hibernate] create table Node (id int not null, string varchar(255), version int, parent_id int, primary key (id))
[Hibernate] alter table Element add constraint FKcwlxr32pujpq4d7va12w278fw foreign key (node_id) references Node
[Hibernate] alter table Node add constraint FKnuolrnqa3oux6ln0vkqeuhsda foreign key (parent_id) references Node
[Hibernate] select next value for hibernate_sequence
[Hibernate] select next value for hibernate_sequence
[Hibernate] select next value for hibernate_sequence
[Hibernate] select next value for hibernate_sequence
[Hibernate] select next value for hibernate_sequence
[Hibernate] insert into Node (parent_id, string, version, id) values (@P1, @P2, @P3, @P4)

javax.persistence.PersistenceException: org.hibernate.HibernateException: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
java.util.concurrent.CompletionException: javax.persistence.PersistenceException: org.hibernate.HibernateException: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
	at java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:273)
	at java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:280)
	at java.util.concurrent.CompletableFuture.uniHandle(CompletableFuture.java:838)
	at java.util.concurrent.CompletableFuture$UniHandle.tryFire(CompletableFuture.java:811)
	at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:488)
	at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1990)
	at com.ibm.asyncutil.iteration.AsyncTrampoline$TrampolineInternal.lambda$unroll$0(AsyncTrampoline.java:121)
	at java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:774)
	at java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:750)
	at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:488)
	at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1990)
	at com.ibm.asyncutil.iteration.AsyncTrampoline$TrampolineInternal.lambda$unroll$0(AsyncTrampoline.java:121)
	at java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:774)
	at java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:750)
	at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:488)
	at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1990)
	at org.hibernate.reactive.pool.impl.Handlers.lambda$toCompletionStage$0(Handlers.java:29)
	at io.vertx.core.impl.future.FutureImpl$3.onFailure(FutureImpl.java:128)
	at io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:79)
	at io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:198)
	at io.vertx.core.impl.future.PromiseImpl.tryFail(PromiseImpl.java:23)
	at io.vertx.sqlclient.impl.QueryResultBuilder.tryFail(QueryResultBuilder.java:118)
	at io.vertx.core.Promise.fail(Promise.java:89)
	at io.vertx.core.Promise.handle(Promise.java:53)
	at io.vertx.core.Promise.handle(Promise.java:29)
	at io.vertx.core.impl.future.FutureImpl$3.onFailure(FutureImpl.java:128)
	at io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:79)
	at io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:198)
	at io.vertx.core.impl.future.PromiseImpl.tryFail(PromiseImpl.java:23)
	at io.vertx.core.impl.future.PromiseImpl.onFailure(PromiseImpl.java:54)
	at io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:43)
	at io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:23)
	at io.vertx.sqlclient.impl.command.CommandResponse.fire(CommandResponse.java:46)
	at io.vertx.sqlclient.impl.SocketConnectionBase.handleMessage(SocketConnectionBase.java:261)
	at io.vertx.sqlclient.impl.SocketConnectionBase.lambda$init$0(SocketConnectionBase.java:97)
	at io.vertx.core.net.impl.NetSocketImpl.lambda$new$1(NetSocketImpl.java:97)
	at io.vertx.core.streams.impl.InboundBuffer.handleEvent(InboundBuffer.java:240)
	at io.vertx.core.streams.impl.InboundBuffer.write(InboundBuffer.java:130)
	at io.vertx.core.net.impl.NetSocketImpl.lambda$handleMessage$9(NetSocketImpl.java:390)
	at io.vertx.core.impl.EventLoopContext.emit(EventLoopContext.java:49)
	at io.vertx.core.impl.ContextImpl.emit(ContextImpl.java:275)
	at io.vertx.core.impl.EventLoopContext.emit(EventLoopContext.java:22)
	at io.vertx.core.net.impl.NetSocketImpl.handleMessage(NetSocketImpl.java:389)
	at io.vertx.core.net.impl.ConnectionBase.read(ConnectionBase.java:153)
	at io.vertx.core.net.impl.VertxHandler.channelRead(VertxHandler.java:154)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
	at io.vertx.mssqlclient.impl.codec.TdsMessageEncoder.lambda$write$0(TdsMessageEncoder.java:52)
	at io.vertx.mssqlclient.impl.codec.MSSQLCommandCodec.complete(MSSQLCommandCodec.java:70)
	at io.vertx.mssqlclient.impl.codec.ExtendedQueryCommandCodec.decodeMessage(ExtendedQueryCommandCodec.java:121)
	at io.vertx.mssqlclient.impl.codec.TdsMessageDecoder.decodeMessage(TdsMessageDecoder.java:70)
	at io.vertx.mssqlclient.impl.codec.TdsMessageDecoder.decode(TdsMessageDecoder.java:49)
	at io.vertx.mssqlclient.impl.codec.TdsMessageDecoder.decode(TdsMessageDecoder.java:27)
	at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
	at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)
	at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
	at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
	at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
	at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166)
	at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:719)
	at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:655)
	at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:581)
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493)
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.lang.Thread.run(Thread.java:748)
Caused by: javax.persistence.PersistenceException: org.hibernate.HibernateException: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
	at org.hibernate.reactive.session.impl.ReactiveExceptionConverter.convert(ReactiveExceptionConverter.java:31)
	at org.hibernate.reactive.session.impl.ReactiveSessionImpl.lambda$doFlush$34(ReactiveSessionImpl.java:879)
	at java.util.concurrent.CompletableFuture.uniHandle(CompletableFuture.java:836)
	... 73 more
Caused by: org.hibernate.HibernateException: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
	... 76 more
Caused by: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
	at io.vertx.mssqlclient.impl.codec.MSSQLCommandCodec.handleErrorToken(MSSQLCommandCodec.java:60)
	at io.vertx.mssqlclient.impl.codec.ExtendedQueryCommandCodec.decodeMessage(ExtendedQueryCommandCodec.java:84)
	... 25 more


Gradle Test Executor 34 > org.hibernate.reactive.EagerTest > testEagerCollectionFetch FAILED
    java.util.concurrent.CompletionException: javax.persistence.PersistenceException: org.hibernate.HibernateException: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
        at java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:273)
        at java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:280)
        at java.util.concurrent.CompletableFuture.uniHandle(CompletableFuture.java:838)
        at java.util.concurrent.CompletableFuture$UniHandle.tryFire(CompletableFuture.java:811)
        at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:488)
        at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1990)
        at com.ibm.asyncutil.iteration.AsyncTrampoline$TrampolineInternal.lambda$unroll$0(AsyncTrampoline.java:121)
        at java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:774)
        at java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:750)
        at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:488)
        at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1990)
        at com.ibm.asyncutil.iteration.AsyncTrampoline$TrampolineInternal.lambda$unroll$0(AsyncTrampoline.java:121)
        at java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:774)
        at java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:750)
        at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:488)
        at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1990)
        at org.hibernate.reactive.pool.impl.Handlers.lambda$toCompletionStage$0(Handlers.java:29)
        at io.vertx.core.impl.future.FutureImpl$3.onFailure(FutureImpl.java:128)
        at io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:79)
        at io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:198)
        at io.vertx.core.impl.future.PromiseImpl.tryFail(PromiseImpl.java:23)
        at io.vertx.sqlclient.impl.QueryResultBuilder.tryFail(QueryResultBuilder.java:118)
        at io.vertx.core.Promise.fail(Promise.java:89)
        at io.vertx.core.Promise.handle(Promise.java:53)
        at io.vertx.core.Promise.handle(Promise.java:29)
        at io.vertx.core.impl.future.FutureImpl$3.onFailure(FutureImpl.java:128)
        at io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:79)
        at io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:198)
        at io.vertx.core.impl.future.PromiseImpl.tryFail(PromiseImpl.java:23)
        at io.vertx.core.impl.future.PromiseImpl.onFailure(PromiseImpl.java:54)
        at io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:43)
        at io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:23)
        at io.vertx.sqlclient.impl.command.CommandResponse.fire(CommandResponse.java:46)
        at io.vertx.sqlclient.impl.SocketConnectionBase.handleMessage(SocketConnectionBase.java:261)
        at io.vertx.sqlclient.impl.SocketConnectionBase.lambda$init$0(SocketConnectionBase.java:97)
        at io.vertx.core.net.impl.NetSocketImpl.lambda$new$1(NetSocketImpl.java:97)
        at io.vertx.core.streams.impl.InboundBuffer.handleEvent(InboundBuffer.java:240)
        at io.vertx.core.streams.impl.InboundBuffer.write(InboundBuffer.java:130)
        at io.vertx.core.net.impl.NetSocketImpl.lambda$handleMessage$9(NetSocketImpl.java:390)
        at io.vertx.core.impl.EventLoopContext.emit(EventLoopContext.java:49)
        at io.vertx.core.impl.ContextImpl.emit(ContextImpl.java:275)
        at io.vertx.core.impl.EventLoopContext.emit(EventLoopContext.java:22)
        at io.vertx.core.net.impl.NetSocketImpl.handleMessage(NetSocketImpl.java:389)
        at io.vertx.core.net.impl.ConnectionBase.read(ConnectionBase.java:153)
        at io.vertx.core.net.impl.VertxHandler.channelRead(VertxHandler.java:154)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.vertx.mssqlclient.impl.codec.TdsMessageEncoder.lambda$write$0(TdsMessageEncoder.java:52)
        at io.vertx.mssqlclient.impl.codec.MSSQLCommandCodec.complete(MSSQLCommandCodec.java:70)
        at io.vertx.mssqlclient.impl.codec.ExtendedQueryCommandCodec.decodeMessage(ExtendedQueryCommandCodec.java:121)
        at io.vertx.mssqlclient.impl.codec.TdsMessageDecoder.decodeMessage(TdsMessageDecoder.java:70)
        at io.vertx.mssqlclient.impl.codec.TdsMessageDecoder.decode(TdsMessageDecoder.java:49)
        at io.vertx.mssqlclient.impl.codec.TdsMessageDecoder.decode(TdsMessageDecoder.java:27)
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:88)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
        at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
        at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166)
        at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:719)
        at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:655)
        at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:581)
        at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493)
        at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
        at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.lang.Thread.run(Thread.java:748)
        Caused by:
        javax.persistence.PersistenceException: org.hibernate.HibernateException: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
            at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
            at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
            at org.hibernate.reactive.session.impl.ReactiveExceptionConverter.convert(ReactiveExceptionConverter.java:31)
            at org.hibernate.reactive.session.impl.ReactiveSessionImpl.lambda$doFlush$34(ReactiveSessionImpl.java:879)
            at java.util.concurrent.CompletableFuture.uniHandle(CompletableFuture.java:836)
            ... 73 more
            Caused by:
            org.hibernate.HibernateException: io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
                at org.hibernate.reactive.session.impl.ReactiveExceptionConverter.convert(ReactiveExceptionConverter.java:31)
                ... 75 more
                Caused by:
                io.vertx.mssqlclient.MSSQLException: Cannot insert the value NULL into column 'parent_id', table 'master.dbo.Node'; column does not allow nulls. INSERT fails.
                    at io.vertx.mssqlclient.impl.codec.MSSQLCommandCodec.handleErrorToken(MSSQLCommandCodec.java:60)
                    at io.vertx.mssqlclient.impl.codec.ExtendedQueryCommandCodec.decodeMessage(ExtendedQueryCommandCodec.java:84)
                    ... 25 more
----------------------------------------------------------------
mssql results: FAILURE (1 tests, 0 passed, 1 failed, 0 skipped)
----------------------------------------------------------------

The first warnings are expected I think, given the DB is empty. Then the test setup looks OK:

[Hibernate] create sequence hibernate_sequence start with 1 increment by 1
[Hibernate] create table Element (id int not null, node_id int, primary key (id))
[Hibernate] create table Node (id int not null, string varchar(255), version int, parent_id int, primary key (id))
[Hibernate] alter table Element add constraint FKcwlxr32pujpq4d7va12w278fw foreign key (node_id) references Node
[Hibernate] alter table Node add constraint FKnuolrnqa3oux6ln0vkqeuhsda foreign key (parent_id) references Node

[gavinking]

MSSQLException: Cannot drop the table 'Node', because it does not exist or you do not have permission.]

This is surely the real reason.

[gavinking]

This is surely the real reason.

Oh, sorry, my bad, no, it's not the reason.

[gavinking]

I really have no clue. I'm sure it's not the case that SQL Server foreign keys are not null by default, so there must be something else going on.

Have you tried executing the same SQL from the GUI client to check that what I'm saying is true?

[tsegismont]

No. I will try that

…

[tsegismont]

It works when I ran this from sqlcmd:

1> create sequence hibernate_sequence start with 1 increment by 1
2> go
1> create table Element (id int not null, node_id int, primary key (id))
2> go
1> create table Node (id int not null, string varchar(255), version int, parent_id int, primary key (id))
2> go
1> alter table Element add constraint FKcwlxr32pujpq4d7va12w278fw foreign key (node_id) references Node
2> go
1> alter table Node add constraint FKnuolrnqa3oux6ln0vkqeuhsda foreign key (parent_id) references Node
2> go
1> insert into Node (parent_id, string, version, id) values (null, 'parent',0, 1)
2> go

(1 rows affected)
1> select * from Node
2> go
id          string                                                                                                                                                                                                                                                          version     parent_id  
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
          1 parent                                                                                                                                                                                                                                                                    0        NULL

(1 rows affected)
1> exit

So you're right, it's not related to constraint definition.

[gbadner]

@tsegismont, when debugging EagerTest with a breakpoint on the MSSQLException constructor, I'm seeing a MSSQLException with message "Incorrect syntax near '?'." getting created.

That exception seems to get eaten, and instead a MSSQLException with message "Statement(s) could not be prepared" gets thrown.

I'm also seeing the same sort of issue when debugging some other tests as well using Vert.x 4.1.0-SNAPSHOT.

[tsegismont]

@gbadner you need the changes I shared in DavideD#13 (in particular, the Parameters class implementation for SQL Server)

[DavideD]

I'm going to create a branch upstream to keep track of the progress with sql server, so that I can rebase it to the latest main and we can have a reference branch with all changes.

[gavinking]

@tsegismont what is the status of this?

(Whatever the problem is, I don't think the issue description captures it.)