Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-1122

OutOfMemoryError when iterating over result set and modify rows in the same connection



    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Not a Bug
    • 3.1.4
    • N/A
    • Other
    • None
    • Windows 10
      MariaDB 10.6.5
      Java 17
      SpringBoot 3.1.1, Hibernate 6.2.5


      Background: I created a test project that demonstrates various ways in Jakarta Persistence API (JPA) how to iterate over arbitrary number of entities read from the database
      and possibly modify them, without running into OutOfMemoryError (OOME). The project is written in Kotlin & SpringBoot and connects to a MariaDB database. I was able to iterate over many (even milions) of entities (rows) from the database without any memory issues even when explicitely lowering heap size to only 200M. But when the iterated entities were also modified in the loop, with higher count (e.g. 100 000), OutOfMemoryError occurred (I guess the complete ResultSet was read into memory). I first suspected that the ORM framework (Hibernate) had some hidden bug so I also tried doing the same in plain JDBC but OutOfMemoryError occurred too! Then I tried connection to an Oracle database and to my surprise, everything worked correctly - either by using JPA or JDBC! So my conclusion is that there is some bug in the MariaDB JDBC driver OR that something else also must be set in the connection properties.

      Attached is the complete project. Just unzip it, import it to some IDE (set JDK 17) and run the Gradle build. Set Set the -Xmx=200M in order to detect possible memory problems soon. Follow README.md.
      Basically, run create 1 000 000 objects first and then run `jdbcresultset` endpoint with count = 100 000 and modifyData=true -> you will get OutOfMemoryError.

      The most important class in MassDataService. It has various functions that show the possible ways how to iterate over objects in JPA (resultList, offsetPaging, resultStream, stream) and in JDBC:

      //pass 100 000, 1000, 1, true -> OutOfMemoryError
          fun jdbcResultSet(count: Int, flushAfter: Int, fetchSize: Int, modifyData: Boolean): Int {
              val connection = dataSource.connection // take connection from the connection pool
              val autoCommit = connection.autoCommit
              connection.autoCommit = false
              try {
                  val limitClause = "limit $count" // MariaDB
                  //val limitClause = "fetch first $count rows only" // Oracle
                  val ps = connection.prepareStatement("select sys_pk, amount, name, description, valid from something $limitClause")
      //                ResultSet.TYPE_FORWARD_ONLY, // default TYPE_FORWARD_ONLY,  TYPE_SCROLL_INSENSITIVE-> OOME
      //                ResultSet.CONCUR_READ_ONLY,  // default CONCUR_READ_ONLY -> PreparedStatement.executeBatch() gets stuck!, CONCUR_UPDATABLE -> OOME
      //                ResultSet.HOLD_CURSORS_OVER_COMMIT)
                  ps.fetchSize = fetchSize // if not set to positive number then OOM occurs!
                  val rs = ps.executeQuery()
                  var i = 0
                  val toUpdate = mutableListOf<Something>()
                  while (rs.next()) {
                      val something = Something(
                          sysPk = rs.getInt("sys_pk"),
                          sysId = null,
                          name = rs.getString("name"),
                          description = rs.getString("description"),
                          amount = rs.getInt("amount"),
                          valid = rs.getBoolean("valid"))
                      if (i % flushAfter == 0) {
                          println("Entity $i: sysPk = ${something.sysPk}, name = ${something.name}")
                      if (modifyData) {
                          something.amount = something.amount + 1
                          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
                      if (i == count) {
                          println("desired count reached..stopping")
              } catch (ex: SQLException) {
                  println("Error: " + ex.message)
              } finally {
                  if (autoCommit) {
                      connection.autoCommit = true
                  connection.close() // return connection to the pool no matter what
              return 0
          private fun update(toUpdate: List<Something>, connection: Connection) {
              var numUpdated = 0
              val upds = connection.prepareStatement("update something set amount = ?, name = ?, description = ?, valid = ? where sys_pk = ?")
              for(something in toUpdate) {
                  upds.setInt(1, something.amount)
                  upds.setString(2, something.name)
                  upds.setString(3, something.description)
                  upds.setBoolean(4, something.valid)
                  upds.setInt(5, something.sysPk!!)
                  numUpdated += upds.executeUpdate() // OOME even on first call !!!
              println("updated $numUpdated record(s)")




            diego dupin Diego Dupin
            Roderick Radek Wikturna
            0 Vote for this issue
            3 Start watching this issue



              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.