[FIXED] Spring boot skipping and duplicating items in pagination

Issue

I have a Users endpoint to get all the users in MySQL sorted by a boolean value using pagination, as the number keeps growing I found out it's skipping some users and duplicating others here's some screenshots in postman where a user with the same id (95) is duplicating in 2 different pages

Also sometimes i can't find some users in all the pages

Page 2

Page 3

ServiceImpl

@Override
    public PagedResponse<?> findAll(UserPrincipal currentUser, int page, int size) {
        AppUtils.validatePageNumberAndSize(page, size);
//return users with 'isBoard' true first
        Pageable pageable = PageRequest.of(page, size, Sort.Direction.DESC, "isBoard");
        if (currentUser.getAuthorities().contains(new SimpleGrantedAuthority(RoleName.ROLE_ADMIN.toString()))
                || currentUser.getAuthorities().contains(new SimpleGrantedAuthority(RoleName.ROLE_SUPER_ADMIN.toString()))) {
            Page<MEUser> contentPage = userRepository.findAll(pageable);
            List<MEUser> list = contentPage.getNumberOfElements() == 0 ? Collections.emptyList() : contentPage.getContent();
            return new PagedResponse<>(list.stream()
                    .map(user -> mapToAllUsersDTO(user, new AllUserDTO()))
                    .collect(Collectors.toList()), contentPage.getNumber(), contentPage.getSize(), contentPage.getTotalElements(), contentPage.getTotalPages(), contentPage.isLast());
        } else {
            Page<MEUser> contentPage = userRepository.findByMemberTrue(pageable);
            List<MEUser> list = contentPage.getNumberOfElements() == 0 ? Collections.emptyList() : contentPage.getContent();
            return new PagedResponse<>(list.stream()
                    .map(user -> mapToAllUsersDTO(user, new AllUserDTO()))
                    .collect(Collectors.toList()), contentPage.getNumber(), contentPage.getSize(), contentPage.getTotalElements(), contentPage.getTotalPages(), contentPage.isLast());
        }
    }

Solution

This is a database problem rather than in your code. The database does not ensure consistent results when there's multiple rows with the same value of an ordered field.

When using pagination with LIMIT and OFFSET you have to supply an order by field with unique values, so in your case you have to sort by isBoard and id to ensure consistent result.

Answered By - b.GHILAS