Thanks for details testing project, i think that's the first time i see a test example so prepared !

The problem is just about oracle and mariadb connector differ about default fetch size (oracle is the only connector i know that has a default fetch size (of 10 rows)).

Having fetch size has some limitations, JDBC spec permits to go to previous resulset rows, and setting fetch size remove that possibility, and decrease performance for small resultset.

For big resultset, fetch size has to be set, in order to avoid mounting the whole resultset in memory.

This can be done in different ways, like
globally : hibernate.jdbc.fetch_size: 10
per query: query.setHint("org.hibernate.fetchSize", "10")

like in your example :

    @Transactional

    fun resultList(count: Int, action: (Something, Int) -> Unit): Int {

        val query = em.createQuery("select s from Something s", Something::class.java)

        query.setHint("org.hibernate.fetchSize", "10")

        query.maxResults = count

        val resultList = query.resultList

        resultList.forEachIndexed { i, s ->

            action(s, i)

        }

        return count

    }

I'm really surprised that this bug got closed as 'Not a Bug'!
It took me a long time to investigate the issue and I provided code that can easily reproduce the problem (either in JPA or in plain JDBC)

> For big resultset, fetch size has to be set, in order to avoid mounting the whole resultset in memory.
yes I know. That's why I'm setting the fetch size (see line `ps.fetchSize = fetchSize ` in function jdbcResultSet() and also in other JPA functions like getResultStream()) where a value from a function argument is passed (any positive value will work). That is with set fetch size, you can really iterate over any number of rows without getting OOME, but you will get OOM when you also modify them.
Please, try it yourself - create mass data by calling fun create(1000 000), then call fun jdbcResultSet(100 000, 1000, 1, true) -> you will get OOME when the execution reaches line `numUpdated += upds.executeUpdate()` in fun update().

Maybe I shouldn't have included all the possible function like resultList() - I admit I'm not setting the fetch size there (it wouldn't help anyway) because I wanted to test how many records can be iterated using this function before OOME occurs. So, please just focus on fun jdbcResultSet(), which simulates what JPA/Hibernate does under the hood (e.g. when you call fun getResultStrem()).

Please reopen the issue. I'm sure we can find a way how understand each other.

I have also previously read this article from a Hibernate guru, which gives some advice on setting fetch size on mySQL to integer minimum value or setting connection property useCursorFetch - neither works on mariaDB:
https://vladmihalcea.com/whats-new-in-jpa-2-2-stream-the-result-of-a-query-execution/

There is different things that will indicate to connector how to handle memory : fetch size hint and resultset type.
if using ResultSet.TYPE_FORWARD_ONLY resultset type, that indicate that resultset can only read forward, so after using resultset.next(), that previous row can be removed from memory, because previous data won't be read anymore. Using TYPE_SCROLL_INSENSITIVE, that means the opposite, so after reading the 1_000_000st row for example, that application can still go to the first row using resultset.absolute(1) => All the rows must stay in memory, and that can lead to OOM.

Oracle and mariadb protocol are different, when using fetch size. for example for a value of fetch size of X, mariadb will retrieve the whole resultset, putting X rows in memory at a time, while oracle will only retrieve X rows, then when needing more rows, will ask the the next X rows. ( To be complete, mariadb has another exchange protocol that permit to retrieve data the same way, but beeing around 2x less performant that current implementation, it's better this way)

The thing is those 2 parameters - fetch size and resultset type - are how connector handle to know how memory must be handled.
So in case of having to deal with a large number of rows, the only solution are to use TYPE_FORWARD_ONLY resultset type and setting fetch size.

Oracle by default set fetch size to 10, while mariadb load the complete resultset (fetch size of 0). The thing is that loading complete resultset is a bit faster for small resultset. Small resultset are the norm, so i think we have good default.
Of course, if that's always debatable, and if can discuss more about that, about if they are good default or we lack documentation on it.

I'll not talk about MySQL connector setFetchSize(Integer.MIN_VALUE) that correspond in fact to setFetchSize(1), because that doesn't follow JDBC specification (fetch size must be >= 0)

I really appreciate the detailed answer giving insight on some technical details that need to be considered when implementing the driver.
However, there seems to be some misunderstanding - I'm doing everything you suggested (setting fetch size, using only ResultSet.TYPE_FORWARD_ONLY) but I still get OOME when modifying the data in the loop. Can you explain why the line `numUpdated += upds.executeUpdate() ` in the update() function fails with OOME ?

I suspect you have not really run the code. I admit that my testing code is long and has many functions and that may confuse you. Therefore, I've reduced it to bare minimum (ther's a new zip file) and now you only need to

• create a database (by default named `massdata`), optionally modify the url
• set -Xmx100M
• run the program

• it will create 200 000 rows in the `Something` table
• then it will iterate over those records -> this works
• then it will iterate over these records and modify them in the same loop -> this fails with OOME!

Please, try to run it and only then tell me that I'm doing something wrong.

While I do not know any detail about the driver implementation, one thing that I do know is that you can't execute any other query, as long as result set from COM_QUERY/COM_STMT_EXECUTE was not fully read. Protocol does not allow to start a new command before the old one is finished.
So the JDBC driver might just have to read the result set fully, into memory, before update, so it can execute your update.

Now, Roderick, did you succeed with your program using Oracle's MySQL driver, using any tricks that Hibernate guru revealed?

Ah, i've finally understood your problem : basically, when streaming a resultset, you use the same connection to make some update command. In order to run the execution, the connector will then first finish to completly read all the remaining rows of the resultset into memory, and that will result in OOM.

Basically, for your example, changing

                    if (i % flushAfter == 0) {

                        update(toUpdate, connection) // uncomment this line if you want to update the row one by one

                        //batchUpdate(toUpdate, connection) // uncomment this line if you want to update the rows in batches

                        toUpdate.clear()

                    }

to

                    if (i % flushAfter == 0) {

                        val connection2 = dataSource.connection;

                        try {

                            update(toUpdate, connection2) // uncomment this line if you want to update the row one by one

                            //batchUpdate(toUpdate, connection) // uncomment this line if you want to update the rows in batches

                            toUpdate.clear()

                        } finally {

                            connection2.close()

                        }

                    }

To explain a bit more : mariadb connector and database exchanges when using fetch size are :

• connector send SELECT command to database
• database send 200_000 rows.
• connector will read fetch size rows, the remaining still being in socket cache, or still not sent by server.
• when asking connector another command, driver will load all the remaining data in memory, before sending the new command.

Oracle works differently. example with the default fetch size of 10:

• connector send SELECT command to database
• database send 10 rows.
• connector will read 10 rows, then ask for the next 10 rows.
• ...
• when asking driver another command, connector can send it and read the result

Oracle solution is way slower because it requires lots of exchanges between client and server, but does not have the limitation compare mariadb connector have there.

This need at least documentation.