Not able to fetch and process huge records from MySQL to generate CSV file

[Grey Seal]

I am trying to fetch 10k records, for now, from MySQL database using queryStream from vertx-mysql-postgresql-client but I am getting the below-mentioned error every time. The idea is to fetch half a million records from MySQL and then send the resultset as CSV file to the user but it's not even working for 10k records.

java.lang.StackOverflowError

at io.vertx.core.json.JsonArray.add(JsonArray.java:444)

at io.vertx.ext.asyncsql.impl.ScalaUtils.convertValue(ScalaUtils.java:138)

at io.vertx.ext.asyncsql.impl.ScalaUtils.access$000(ScalaUtils.java:41)

at io.vertx.ext.asyncsql.impl.ScalaUtils$4.apply(ScalaUtils.java:105)

at io.vertx.ext.asyncsql.impl.ScalaUtils$4.apply(ScalaUtils.java:102)

at scala.collection.Iterator.foreach(Iterator.scala:929)

at scala.collection.Iterator.foreach$(Iterator.scala:929)

at scala.collection.AbstractIterator.foreach(Iterator.scala:1417)

at scala.collection.IterableLike.foreach(IterableLike.scala:71)

at scala.collection.IterableLike.foreach$(IterableLike.scala:70)

at com.github.mauricio.async.db.general.ArrayRowData.foreach(ArrayRowData.scala:21)

at io.vertx.ext.asyncsql.impl.ScalaUtils.rowToJsonArray(ScalaUtils.java:102)

at io.vertx.ext.asyncsql.impl.AsyncSQLRowStream.nextRow(AsyncSQLRowStream.java:98)

at io.vertx.ext.asyncsql.impl.AsyncSQLRowStream.nextRow(AsyncSQLRowStream.java:99)

at io.vertx.ext.asyncsql.impl.AsyncSQLRowStream.nextRow(AsyncSQLRowStream.java:99) 

Reproducer at https://github.com/greyseal/vertx-csv-download

Note: I am trying to compare this with the Spring Boot. Spring Boot version is working fine https://github.com/greyseal/spring-boot-csv-download

[Grey Seal]

I am still looking for answer/s.

[Julien Viet]

HI,

can you provide a longer stack trace ? 

it says StackOverflowError so it must be quite large

that will help to understand what is going on.

Julien

ps: you need also to understand that during Christmas holidays, most people are just on PTO and not even reading this list, replies on this list are done on a best effort by the developers but also often by community members

-- 
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
Visit this group at https://groups.google.com/group/vertx.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/06cf6144-5521-4906-88ed-ee737ced3bbe%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Grey Seal]

Thanks for the reply, Julien. Here is the full stack trace.

at io.vertx.ext.asyncsql.impl.AsyncSQLRowStream.nextRow(AsyncSQLRowStream.java:99)

Apologies for being impatient. Anyways, Happy new year.

[Julien Viet]

I think the implementation of nextRow() should not be recursive (for many reasons :-) ) and we can consider this as a bug.

I've opened an issue for this : https://github.com/vert-x3/vertx-mysql-postgresql-client/issues/98

it should be fixed in 3.5.1, in the meantime perhaps you can try to make your own bug fix and contribute it ?

Julien

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/94bd3c95-ce7f-4754-8ce5-024b14d439e3%40googlegroups.com.

[Grey Seal]

Hi Julien

I tried the same example with JDBC client and this time I am getting thread blocked which looks fine as it took longer to fetch records from the database and then convert to CSV. Should we consider this as a bug or any other way to download huge CSV. I tried with spring boot https://github.com/greyseal/spring-boot-csv-download and there it's working very fine.

Jan 31, 2018 6:26:49 PM io.vertx.core.impl.BlockedThreadChecker

WARNING: Thread Thread[vert.x-eventloop-thread-1,5,main] has been blocked for 6490 ms, time limit is 2000

io.vertx.core.VertxException: Thread blocked

at java...@9.0.1/java.lang.StringUTF16.compress(StringUTF16.java:157)

at java...@9.0.1/java.lang.String.<init>(String.java:3015)

at java...@9.0.1/java.lang.String.<init>(String.java:657)

at java...@9.0.1/java.lang.System$2.newStringUnsafe(System.java:2107)

at java...@9.0.1/java.util.StringJoiner.compactElts(StringJoiner.java:255)

at java...@9.0.1/java.util.StringJoiner.toString(StringJoiner.java:178)

at java...@9.0.1/java.lang.String.join(String.java:2402)

at app//com.vertx.test.RequestHandler.toString(RequestHandler.java:43)

at app//com.vertx.test.RequestHandler.lambda$0(RequestHandler.java:29)

at app//com.vertx.test.RequestHandler$$Lambda$165/1268685181.handle(Unknown Source)

at app//io.vertx.core.eventbus.impl.EventBusImpl.lambda$convertHandler$1(EventBusImpl.java:354)

at app//io.vertx.core.eventbus.impl.EventBusImpl$$Lambda$166/1585037726.handle(Unknown Source)

at app//io.vertx.core.eventbus.impl.HandlerRegistration.deliver(HandlerRegistration.java:212)

at app//io.vertx.core.eventbus.impl.HandlerRegistration.handle(HandlerRegistration.java:189)

at app//io.vertx.core.eventbus.impl.EventBusImpl.lambda$deliverToHandler$3(EventBusImpl.java:538)

at app//io.vertx.core.eventbus.impl.EventBusImpl$$Lambda$169/2106653639.handle(Unknown Source)

at app//io.vertx.core.impl.ContextImpl.lambda$wrapTask$2(ContextImpl.java:344)

at app//io.vertx.core.impl.ContextImpl$$Lambda$87/1623009085.run(Unknown Source)

at app//io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:163)

at app//io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:403)

at app//io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:463)

at app//io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:858)

at java...@9.0.1/java.lang.Thread.run(Thread.java:844)

...

[Julien Viet]

can you show your request handler class ?

what is the size of the resulting string ?

I see also you are using Java 9 that compress the string that seems to take CPU, what if you use Java 8 instead ?

On 31 Jan 2018, at 14:09, Grey Seal <saubk...@gmail.com> wrote:

at app//com.vertx.test.RequestHandler.toString(RequestHandler.java:43)

[Grey Seal]

you can see the class at https://github.com/greyseal/vertx-csv-download/blob/master/src/main/java/com/vertx/test/RequestHandler.java. In the meantime will try with Java 8. I also tried with executeBlocking but that also was not of much help, same thread blocked error.

For the size, I will try to get it but looks like the for loop in the RequestHandler is causing this.

[Julien Viet]

what the actual data size you have ?

how long is the string after ?

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
Visit this group at https://groups.google.com/group/vertx.

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/5fd8de43-1c5f-4015-92d3-b2922a0774b4%40googlegroups.com.

[Grey Seal]

There are about half a million records having 7 columns. You can find it at https://github.com/greyseal/vertx-csv-download/blob/master/src/main/resources/Sample-SQL-File-500000-Rows.sql. I am not able to get the string size as IDE becomes unresponsive after some time.

[Julien Viet]

how can I run the reproducer ?

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/cca62d4b-e22f-4981-9702-12fbeaadbbee%40googlegroups.com.

[Grey Seal]

I am using eclipse IDE. You can add run configurations as Main Class: io.vertx.core.Launcher and Program Arguments as: run com.vertx.test.MainVerticle

[Grey Seal]

Hi Julien

Did you get a chance to repro? I am still stuck. When I removed for loop from the RequestHandler, file got downloaded pretty easily. However, I need records in a CSV with each JSON array as a row.

[Grey Seal]

I did the same test in Java 8 and still, thread block exists.

Feb 02, 2018 5:25:54 PM io.vertx.core.impl.BlockedThreadChecker WARNING: Thread Thread[vert.x-eventloop-thread-1,5,main] has been blocked for 5291 ms, time limit is 2000 io.vertx.core.VertxException: Thread blocked at com.vertx.test.RequestHandler.toString(RequestHandler.java:43) at com.vertx.test.RequestHandler.lambda$handle$0(RequestHandler.java:29) at com.vertx.test.RequestHandler$$Lambda$78/1519358202.handle(Unknown Source) at io.vertx.core.eventbus.impl.EventBusImpl.lambda$convertHandler$1(EventBusImpl.java:354) at io.vertx.core.eventbus.impl.EventBusImpl$$Lambda$79/1636941829.handle(Unknown Source) at io.vertx.core.eventbus.impl.HandlerRegistration.deliver(HandlerRegistration.java:212) at io.vertx.core.eventbus.impl.HandlerRegistration.handle(HandlerRegistration.java:189) at io.vertx.core.eventbus.impl.EventBusImpl.lambda$deliverToHandler$3(EventBusImpl.java:538) at io.vertx.core.eventbus.impl.EventBusImpl$$Lambda$82/934572424.handle(Unknown Source) at io.vertx.core.impl.ContextImpl.lambda$wrapTask$2(ContextImpl.java:344) at io.vertx.core.impl.ContextImpl$$Lambda$28/1429880200.run(Unknown Source) at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:163) at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:403) at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:463) at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:858) at java.lang.Thread.run(Thread.java:745)

Feb 02, 2018 5:25:55 PM io.vertx.core.impl.BlockedThreadChecker WARNING: Thread Thread[vert.x-eventloop-thread-1,5,main] has been blocked for 6295 ms, time limit is 2000 io.vertx.core.VertxException: Thread blocked at java.util.Arrays.copyOf(Arrays.java:3332) at java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124) at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448) at java.lang.StringBuilder.append(StringBuilder.java:136) at java.util.StringJoiner.prepareBuilder(StringJoiner.java:223) at java.util.StringJoiner.add(StringJoiner.java:185) at java.lang.String.join(String.java:2456) at com.vertx.test.RequestHandler.toString(RequestHandler.java:43) at com.vertx.test.RequestHandler.lambda$handle$0(RequestHandler.java:29) at com.vertx.test.RequestHandler$$Lambda$78/1519358202.handle(Unknown Source) at io.vertx.core.eventbus.impl.EventBusImpl.lambda$convertHandler$1(EventBusImpl.java:354) at io.vertx.core.eventbus.impl.EventBusImpl$$Lambda$79/1636941829.handle(Unknown Source) at io.vertx.core.eventbus.impl.HandlerRegistration.deliver(HandlerRegistration.java:212) at io.vertx.core.eventbus.impl.HandlerRegistration.handle(HandlerRegistration.java:189) at io.vertx.core.eventbus.impl.EventBusImpl.lambda$deliverToHandler$3(EventBusImpl.java:538) at io.vertx.core.eventbus.impl.EventBusImpl$$Lambda$82/934572424.handle(Unknown Source) at io.vertx.core.impl.ContextImpl.lambda$wrapTask$2(ContextImpl.java:344) at io.vertx.core.impl.ContextImpl$$Lambda$28/1429880200.run(Unknown Source) at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:163) at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:403) at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:463) at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:858) at java.lang.Thread.run(Thread.java:745)

[Julien Viet]

I've tried to run it using docker for mysql with the following command:

docker run --rm --name vertx-mysql -v /Users/julien/java/reproducers/vertx-csv-download/src/main/resources/:/docker-entrypoint-initdb.d  -e MYSQL_USER=root -e MYSQL_PASSWORD=root@1010 -e MYSQL_DATABASE=greymetrics -p 3306:3306 mysql/mysql-server:5.6

but I get:

13:27:51.009 [vert.x-worker-thread-3] DEBUG com.mchange.v2.sql.SqlUtils - Attempted to convert SQLException to SQLException. Leaving it alone. [SQLState: 42S22; errorCode: 1054]

java.sql.SQLSyntaxErrorException: Unknown column 'id' in 'field list'

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)

at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)

at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826)

at com.mysql.cj.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1923)

on 

curl http://localhost:8181/api/csv

Julien

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/5098428c-91e9-4739-9f41-f8cfa3472368%40googlegroups.com.

[Grey Seal]

Oops, my bad... please update 

DatabaseHandler.SELECT_USER_DETAILS  to "SELECT `user_id`, `username`, `first_name`, `last_name`, `gender`, `password`, `status` FROM `user_details` ".

In my local, I have updated `user_id` to `id`.

[Julien Viet]

Hi,

I've been able to get the issue.

The code currently process 500,000 strings in a non efficient manner.

In such situation you should consider that the string processing as a stream instead of a value.

Considering the result as a value, means that:

1/ the computation of this value is considered as a blocking task (i.e processing everything at once is not cooperative)

2/ the entire result will have to fit in a buffer which consumes a considerable amount of heap (what if your client is slow and cannot consume all data ?)

Instead you should use a streaming approach because it is much more the reactive way of doing such things.

Instead of using a request/response (which force to buffer) paradigm between the http server and the database verticle, you could try the following:

1/ when you get the http request : setup an event bus handler on a unique address (UUID string)

2/ this bus handler will receive the chunks and write them on the fly to the http server response (using chunked encoding)

3/ send a message to the DB verticle with the handler address as part of the message

4/ the database handler will then stream the chunks to the event bus address, the last chunk can set a message header indicating it is finished

that will solve one part of the problem which is the 1/ part

to solve the the 2/ you will need to setup a Pump between the HttpServerResponse and the MessageConsumer as well as a Pump between the MessageProducer and the SQLRowStream.

this way you will have a reactive end to end streaming between your database and your rest API.

cheers

Julien

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/5343435d-6590-4e2f-93dc-f332d2b23d02%40googlegroups.com.

[Grey Seal]

Thanks, Julien.

If I get it correct, in the RequestHandler.handle, I have to do something like this

Handler<Response> busHandler = new Handler<Response>() {

    public void handle(Response response) {

        //handle message here response.body

    }

};

this.vertx.eventBus().registerHandler("4f127476-41c8-4d55-97fa-6e0c98f27e74", busHandler);

For 2/, this busHandler will receive the chunks from where? I didn't get this point and so on. Could you please help me understand this.

[Jez P]

The DatabaseHandler as it streams the chunks in, should send them on to the address being listened to by the busHandler.

Right now you're just adding all those chunks to an array. You should be sending them on as they arrive, and letting the response write them as they are sent through, rather than accumulating them and then sending them on.

[Grey Seal]

Got it. Will try the same. Thanks.

[Grey Seal]

I did the aforementioned changes, and able to download the file successfully. Thanks for the inputs.

However, if I call the API second time. it's throwing "Response has already been written" error.

Feb 05, 2018 3:52:29 PM io.vertx.core.eventbus.impl.HandlerRegistration

SEVERE: Failed to handleMessage. address: 4f127476-41c8-4d55-97fa-6e0c98f27e74

java.lang.IllegalStateException: Response has already been written

at io.vertx.core.http.impl.HttpServerResponseImpl.checkWritten(HttpServerResponseImpl.java:572)

at io.vertx.core.http.impl.HttpServerResponseImpl.write(HttpServerResponseImpl.java:598)

at io.vertx.core.http.impl.HttpServerResponseImpl.write(HttpServerResponseImpl.java:293)

at io.vertx.core.http.impl.HttpServerResponseImpl.write(HttpServerResponseImpl.java:58)

at io.vertx.core.streams.impl.PumpImpl.lambda$new$1(PumpImpl.java:64)

at io.vertx.core.eventbus.impl.BodyReadStream.lambda$handler$0(BodyReadStream.java:46)

at io.vertx.core.eventbus.impl.HandlerRegistration.deliver(HandlerRegistration.java:212)

at io.vertx.core.eventbus.impl.HandlerRegistration.handle(HandlerRegistration.java:189)

at io.vertx.core.eventbus.impl.EventBusImpl.lambda$deliverToHandler$3(EventBusImpl.java:538)

at io.vertx.core.impl.ContextImpl.lambda$wrapTask$2(ContextImpl.java:344)

at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:163)

at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:403)

at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:463)

at io.netty.util.concurrent.SingleThreadEventExecutor$5.run(SingleThreadEventExecutor.java:858)

at java.base/java.lang.Thread.run(Thread.java:844)

You can review the code at : https://github.com/greyseal/vertx-csv-download/blob/master/src/main/java/com/vertx/test/RequestHandler.java

[Grey Seal]

Any help will be helpful here.

[Julien Viet]

it looks like you are reusing the same HttpServerResponse instance

you should check it's not used once you have called end() on it

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/a5228cc3-7af3-4701-9895-a160ecdd99c9%40googlegroups.com.