SchemaMigrator/SchemaValidator support for MS SQL Server

[DavideD]

Follows #536

See #903 for an example with PostgreSQL

[gbadner]

@blafond, I was able to get past the SQL_VARIANT columns for the sequence information, so I'll re-assign to myself and continue working on this one.

[gbadner]

@gavinking, I'm seeing stacktrace similar to what was happening in #673 with SQL Server. See below for the stacktrace.

This is the SQL that fails:

select table_name as TABLE_NAME, column_name as COLUMN_NAME, data_type as TYPE_NAME, null as COLUMN_SIZE, 
             null as DECIMAL_DIGITS, is_nullable as IS_NULLABLE, null as DATA_TYPE 
from information_schema.columns 
where 1 = 1 
order by table_catalog, table_schema, table_name, column_name, ordinal_position

One thing that is odd is that adding constraints on the catalog, schema, and table seems to work correctly:

select table_name as TABLE_NAME, column_name as COLUMN_NAME, data_type as TYPE_NAME, null as COLUMN_SIZE, 
             null as DECIMAL_DIGITS, is_nullable as IS_NULLABLE, null as DATA_TYPE 
from information_schema.columns 
where 1 = 1 
     and table_catalog = @P1 and table_schema like @P2 and table_name like @P3 
order by table_catalog, table_schema, table_name, column_name, ordinal_position

So it seems that the results from system tables is causing the overflow.

Stacktrace:

java.util.concurrent.CompletionException: io.netty.handler.codec.DecoderException: java.lang.IndexOutOfBoundsException: index: 8, length: 4088 (expected: range(0, 53))
	at java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:292) ~[?:1.8.0_172-ea]
	at java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:308) ~[?:1.8.0_172-ea]
	at java.util.concurrent.CompletableFuture.uniApply(CompletableFuture.java:593) ~[?:1.8.0_172-ea]
	at java.util.concurrent.CompletableFuture$UniApply.tryFire(CompletableFuture.java:577) ~[?:1.8.0_172-ea]
	at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:474) ~[?:1.8.0_172-ea]
	at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1977) ~[?:1.8.0_172-ea]
	at io.vertx.core.Future.lambda$toCompletionStage$2(Future.java:362) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureImpl$3.onFailure(FutureImpl.java:128) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:79) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:198) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.PromiseImpl.tryFail(PromiseImpl.java:23) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.sqlclient.impl.QueryResultBuilder.tryFail(QueryResultBuilder.java:118) ~[vertx-sql-client-4.1.2.jar:4.1.2]
	at io.vertx.core.Promise.fail(Promise.java:89) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.Promise.handle(Promise.java:53) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.Promise.handle(Promise.java:29) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureImpl$3.onFailure(FutureImpl.java:128) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:79) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:198) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.PromiseImpl.tryFail(PromiseImpl.java:23) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.PromiseImpl.onFailure(PromiseImpl.java:54) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureImpl$ListenerArray.onFailure(FutureImpl.java:236) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureBase.emitFailure(FutureBase.java:79) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.FutureImpl.tryFail(FutureImpl.java:198) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.PromiseImpl.tryFail(PromiseImpl.java:23) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.PromiseImpl.onFailure(PromiseImpl.java:54) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:43) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:23) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.sqlclient.impl.command.CommandResponse.fire(CommandResponse.java:46) ~[vertx-sql-client-4.1.2.jar:4.1.2]
	at io.vertx.sqlclient.impl.SocketConnectionBase.handleMessage(SocketConnectionBase.java:279) ~[vertx-sql-client-4.1.2.jar:4.1.2]
	at io.vertx.sqlclient.impl.SocketConnectionBase.lambda$init$0(SocketConnectionBase.java:98) ~[vertx-sql-client-4.1.2.jar:4.1.2]
	at io.vertx.core.net.impl.NetSocketImpl.lambda$new$1(NetSocketImpl.java:97) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.streams.impl.InboundBuffer.handleEvent(InboundBuffer.java:240) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.streams.impl.InboundBuffer.write(InboundBuffer.java:130) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.net.impl.NetSocketImpl.lambda$handleMessage$9(NetSocketImpl.java:390) ~[vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.EventLoopContext.emit(EventLoopContext.java:49) [vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.ContextImpl.emit(ContextImpl.java:275) [vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.impl.EventLoopContext.emit(EventLoopContext.java:22) [vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.net.impl.NetSocketImpl.handleMessage(NetSocketImpl.java:389) [vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.net.impl.ConnectionBase.read(ConnectionBase.java:155) [vertx-core-4.1.2.jar:4.1.2]
	at io.vertx.core.net.impl.VertxHandler.channelRead(VertxHandler.java:154) [vertx-core-4.1.2.jar:4.1.2]
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.vertx.mssqlclient.impl.codec.TdsMessageCodec.fail(TdsMessageCodec.java:58) [vertx-mssql-client-4.1.2.jar:4.1.2]
	at io.vertx.mssqlclient.impl.codec.TdsMessageCodec.exceptionCaught(TdsMessageCodec.java:48) [vertx-mssql-client-4.1.2.jar:4.1.2]
	at io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:302) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:281) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.fireExceptionCaught(AbstractChannelHandlerContext.java:273) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.ChannelInboundHandlerAdapter.exceptionCaught(ChannelInboundHandlerAdapter.java:143) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeExceptionCaught(AbstractChannelHandlerContext.java:302) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:381) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:719) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:655) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:581) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493) [netty-transport-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989) [netty-common-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) [netty-common-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) [netty-common-4.1.65.Final.jar:4.1.65.Final]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_172-ea]
Caused by: io.netty.handler.codec.DecoderException: java.lang.IndexOutOfBoundsException: index: 8, length: 4088 (expected: range(0, 53))
	at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:477) ~[netty-codec-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:276) ~[netty-codec-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) ~[netty-transport-4.1.65.Final.jar:4.1.65.Final]
	... 15 more
Caused by: java.lang.IndexOutOfBoundsException: index: 8, length: 4088 (expected: range(0, 53))
	at io.netty.buffer.AbstractByteBuf.checkRangeBounds(AbstractByteBuf.java:1390) ~[netty-buffer-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.buffer.AbstractByteBuf.checkIndex0(AbstractByteBuf.java:1397) ~[netty-buffer-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.buffer.PooledSlicedByteBuf.slice(PooledSlicedByteBuf.java:106) ~[netty-buffer-4.1.65.Final.jar:4.1.65.Final]
	at io.vertx.mssqlclient.impl.codec.TdsPacketDecoder.decode(TdsPacketDecoder.java:38) ~[vertx-mssql-client-4.1.2.jar:4.1.2]
	at io.vertx.mssqlclient.impl.codec.TdsPacketDecoder.decode(TdsPacketDecoder.java:21) ~[vertx-mssql-client-4.1.2.jar:4.1.2]
	at io.netty.handler.codec.LengthFieldBasedFrameDecoder.decode(LengthFieldBasedFrameDecoder.java:332) ~[netty-codec-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.handler.codec.ByteToMessageDecoder.decodeRemovalReentryProtection(ByteToMessageDecoder.java:507) ~[netty-codec-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:446) ~[netty-codec-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:276) ~[netty-codec-4.1.65.Final.jar:4.1.65.Final]
	at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) ~[netty-transport-4.1.65.Final.jar:4.1.65.Final]
	... 15 more

[gbadner]

Unfortunately, it requires a change to ORM to reproduce. Without that change, there is a failure because Vert.x does not support SQL Server's SSVARIANT type. The change on ORM works around the SSVARIANT issue.

[gbadner]

@blafond, mentioned a similar problem using DB2.

@gavinking, please let me know if I should create a new Reactive and/or Vert.x issue for this.

[gbadner]

I've created draft PRs to reproduce the stacktrace.

ORM: hibernate/hibernate-orm#4177
Reactive: #945

[gbadner]

FYI, Vert.x 4.1.3 did not fix this.

Changing to following query works, but only intermittently:

select convert( nvarchar(128), table_name ) as TABLE_NAME, convert( nvarchar(128), column_name ) as COLUMN_NAME,
         convert( nvarchar(128), data_type ) as TYPE_NAME, null as COLUMN_SIZE, null as DECIMAL_DIGITS, 
         convert( varchar(3), is_nullable ) as IS_NULLABLE, null as DATA_TYPE 
from information_schema.columns 
where 1 = 1 
order by table_catalog, table_schema, table_name, column_name, ordinal_position

@gavinking , I'm not sure it will be possible to workaround this issue. Should I create a Vert.x issue?

[gavinking]

Yeah, sure, report it to the Vert.x guys.

[gbadner]

eclipse-vertx/vertx-sql-client#1032