0

I am getting this error com.mysql.jdbc.PacketTooBigException and tracing through the code, it appears that it occurs on the following scenario (but only on tests)

public interface Students extends JpaRepository<Student, Integer> {

    @EntityGraph("Student.withProgramAndSchedule")
    @Query("from Student s")
    Iterable<Student> findAllWithProgramAndSchedule();
}

@Entity
@NamedEntityGraph(
    name = "Student.withProgramAndSchedule",
    attributeNodes = {
        @NamedAttributeNode("programEnrollments"),
        @NamedAttributeNode("schedules")
    }
)
public class Student implements Serializable {
...
}

Only during DataJpaTest it appears it does not clean up the Entity Manager context even when I had cleared the entity manager

@After
public void clearEntityManager() {
    entityManager.clear();
}

Since this is with anonymized real data, it has thousands of records and what happens is it creates a query that looks like

select ... from ... where studentSchedule_0.id in (?, ?, ... thousands of ? later, ... ?)

Then it blows the 1MB packet limit.

So my question is (because I can't find it in the references) is it possible to limit the query size when it does the fetch with the IDs?

Archimedes Trajano
  • 35,625
  • 19
  • 175
  • 265
  • Clearing the entity manager isn't the same as removing something from the database. – M. Deinum Aug 26 '20 at 17:31
  • I don't want it removed from the database, the clearing was supposed to detach the objects from the entity manager. – Archimedes Trajano Aug 26 '20 at 17:55
  • That obviously won't work in reducing the amount of selects. You are using a fetch graph which needs to fetch additional information. So the query will be issued anyway. – M. Deinum Aug 27 '20 at 05:30

2 Answers2

1

I am not in a place that let's me code up to confirm what I think will work.

Using findFirst as below:

findFirstWithProgramAndSchedule() // gets one

Or to limit to the first 10

findFirst10WithProgramAndSchedule()

I am trying to be helpful, even though I can't code it up today. Thus, I waited to see if others had the answer, but I believe this will work per the references.

Reference:

Technical differences between Spring Data JPA's findFirst and findTop

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.limit-query-result

K. hervey
  • 119
  • 2
  • 13
1

You can use jpa pagination to limit query result :

Iterable<Student> findAllWithProgramAndSchedule(Pageable pageable);

and for test:

Pageable page= PageRequest.of(1, 5);
Tohid Makari
  • 1,700
  • 3
  • 15
  • 29
  • 1
    again, I am not trying to limit the result set. I am trying to limit the size of the query that is sent to get the associated entity graph. – Archimedes Trajano Aug 28 '20 at 15:30