Parser Not Available for message type '1' - Mysql

[naveedh27]

I tried to call a procedure and return a resultset. But i get this error sometimes. Can someone help me out?

MySQLConnection: Transport failure
com.github.jasync.sql.db.exceptions.ParserNotAvailableException: There is no parser available for message type '1' (1)
at com.github.jasync.sql.db.mysql.codec.MySQLFrameDecoder.handleCommonFlow(MySQLFrameDecoder.kt:152) ~[jasync-mysql-0.9.51.jar:?]
at com.github.jasync.sql.db.mysql.codec.MySQLFrameDecoder.decode(MySQLFrameDecoder.kt:94) ~[jasync-mysql-0.9.51.jar:?]
at io.netty.handler.codec.ByteToMessageDecoder.decodeRemovalReentryProtection(ByteToMessageDecoder.java:502) ~[netty-codec-4.1.30.Final.jar:4.1.30.Final]
at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:441) ~[netty-codec-4.1.30.Final.jar:4.1.30.Final]
at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:278) ~[netty-codec-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:340) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1434) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:348) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:965) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:163) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:644) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:579) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:496) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:458) ~[netty-transport-4.1.30.Final.jar:4.1.30.Final]
at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:897) ~[netty-common-4.1.30.Final.jar:4.1.30.Final]
at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) ~[netty-common-4.1.30.Final.jar:4.1.30.Final]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]

[oshai]

We have tests for stored procedure: https://github.com/jasync-sql/jasync-sql/blob/master/mysql-async/src/test/java/com/github/jasync/sql/db/mysql/StoredProceduresSpec.kt

If you can produce something that is similar to your use case it might help us to reproduce.
What I suspect is that maybe there are multiple result sets which are not supported.

[krrsachi]

Hi Oshai - Thanks for looking at this issue. We are able to recreate this issue with following sample program. Only one resultset is returned from the mysql procedure.

Maven project attached.
jasyncproctest.zip

Can you please help fixing this issue.

JasyncProcTest.java
import io.vertx.core.Vertx;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.asyncsql.AsyncSQLClient;
import io.vertx.ext.asyncsql.MySQLClient;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

public class JasyncProcTest {

private static final Logger log = LogManager.getLogger(JasyncProcTest.class);

public static void main(String[] args) throws Exception {
    Vertx vertx = Vertx.vertx();
    JsonObject options = new JsonObject()
            .put("host", "127.0.0.1")
            .put("port", 3306)
            .put("username", "root")
            .put("password", "root")
            .put("database", "gdae2_market");
    AsyncSQLClient mysql = MySQLClient.createShared(vertx, options);
    mysql.update("DROP PROCEDURE IF EXISTS `new_procedure`", drop -> {
                mysql.update("CREATE  PROCEDURE `new_procedure`()\n" +
                                "BEGIN\n" +
                                "SELECT '1' column1, '2' column2;\n" +
                                "END", create -> {
                            for (int i = 0; i < 100; i++) {
                                mysql.queryWithParams("CALL new_procedure()", new JsonArray(), r -> {
                                    if(r.failed()){
                                        log.error("Error :: ", r.cause());
                                    } else {
                                        log.info("Result :: " + r.result().getRows());
                                    }
                                });
                            }

                        }
                );
            }
    );
}

}

Exceptions
2019-05-16 12:48:18,341 ERROR [vert.x-eventloop-thread-10] MySQLConnection: Transport failure
java.lang.IndexOutOfBoundsException: readerIndex(2) + length(8) exceeds writerIndex(2): UnpooledSlicedByteBuf(ridx: 2, widx: 2, cap: 2/2, unwrapped: UnpooledByteBufAllocator$InstrumentedUnpooledUnsafeNoCleanerDirectByteBuf(ridx: 51, widx: 170, cap: 448))
at io.netty.buffer.AbstractByteBuf.checkReadableBytes0(AbstractByteBuf.java:1433) ~[netty-buffer-4.1.30.Final.jar:4.1.30.Final]
at io.netty.buffer.AbstractByteBuf.readLong(AbstractByteBuf.java:831) ~[netty-buffer-4.1.30.Final.jar:4.1.30.Final]
at io.netty.buffer.SwappedByteBuf.readLong(SwappedByteBuf.java:601) ~[netty-buffer-4.1.30.Final.jar:4.1.30.Final]
at com.github.jasync.sql.db.mysql.binary.decoder.LongDecoder.decode(LongDecoder.kt:6) ~[jasync-mysql-0.9.51.jar:?]
at com.github.jasync.sql.db.mysql.binary.BinaryRowDecoder.decode(BinaryRowDecoder.kt:40) ~[jasync-mysql-0.9.51.jar:?]

2019-05-16 12:48:18,342 ERROR [vert.x-eventloop-thread-0] MySQLConnection: Transport failure
com.github.jasync.sql.db.exceptions.ParserNotAvailableException: There is no parser available for message type '1' (1)
at com.github.jasync.sql.db.mysql.codec.MySQLFrameDecoder.handleCommonFlow(MySQLFrameDecoder.kt:152) ~[jasync-mysql-0.9.51.jar:?]
at com.github.jasync.sql.db.mysql.codec.MySQLFrameDecoder.decode(MySQLFrameDecoder.kt:94) ~[jasync-mysql-0.9.51.jar:?]

[oshai]

I was able to reproduce that. It seems that in stored procedure call the server is sending another OK packet after EOF packet, unlike regular queries. In most cases, the packet is just thrown away, but in case the next query already started, the state is being corrupted. I am trying to figure out from the protocol docs where is that mentioned and how to support it.
In the meanwhile as a workaround, you can try to add a short pause (delay or sleep) after executing a stored procedure.

[oshai]

I see here the following:

The trailing OK_Packet is the response to the CALL statement and contains the affected-rows count of the last statement. In our case we inserted 2 rows, but only the affected_rows of the last INSERT statement is returned as part of the OK_Packet. If the last statement is a SELECT, the affected-rows count is 0.

So there is an additional OK packet for stored procedure call like I suspected. Still, have to think about how to support that.

[oshai]

fixed in 0.9.53

[naveedh27]

Thanks a lot oshai. I will test and let you know.