Results are different with MySQL 5.7.18 versus 5.7.16

Question

We are in the process of upgrading our version of MySQL from 5.7.16 to 5.7.18. We have an existing process that does not seem to be compatible with the newer version of MySQL.

It is a paging process that updates up to 100,000 rows by doing 500 at a time. It has worked fine for years on 5.7.16 and all previous versions of MySQL. Since we upgraded the process will only execute the 500 rows a few times and then continue to the subsequent steps. For example it will update 500 rows 3 times and then on the fourth time it will update only 1 row. Sometimes it will update 500 rows 5 times and then on the sixth time it will update only 1 row again. It seems suspicious that process iterates a few times and then always only 1 row on the final time.

Is there anything wrong with our old implementation that is not compatible with the newer version of MySQL with regards to LIMIT or ORDER BY? Those are the only two things that we think could be a cause for failure. It was easy for us to notice the failure in this area but we need to know why one implementation works but not the other so that we can find any other areas in our code base where a failure is less obvious.

Old implementation that does not work with MySQL 5.7.18:

public void changeItemsType(TypeEnum inProgress, TypeEnum complete) {
    int howMany = 500;
    int selectSize = howMany;
    Long lastId = 0L;
    while (selectSize == howMany) {
        Query q = null;
        q = buildChangeQuery(inProgress, howMany, lastId);
        List<BigInteger> rows = GenericsUtil.cast(q.getResultList());
        selectSize = rows.size();
        if (rows != null && !rows.isEmpty()) {
            lastId = buildIdUpdateList(complete, lastId, rows, inProgress);
        }
    }
}

private Query buildChangeQuery(TypeEnum inProgress, int howMany, Long lastId) {
    StringBuffer sb = new StringBuffer();
    sb.append("select id from Item \n");
    sb.append("where processType = :inprogress \n");
    sb.append("and id > :lastId \n");
    sb.append("order by id \n");
    Query q = this.em.createNativeQuery(sb.toString());
    q.setParameter("inprogress", inProgress.name());
    q.setParameter("lastId", lastId);
    q.setMaxResults(howMany);
    return q;
}

private Long buildIdUpdateList(TypeEnum complete, Long lastId, List<BigInteger> rows, TypeEnum inProgress) {
    List<Long> ids = new ArrayList<Long>();
    Long itemId = 0L;
    for (BigInteger row : rows) {
        itemId = row.longValue();
        ids.add(itemId);
    }
    lastId = itemId;
    updateItems(complete, ids, inProgress);
    return lastId;
}

private void updateItems(TypeEnum complete, List<Long> ids, TypeEnum inProgress) {
    Collections.sort(ids);
    StringBuffer sb = new StringBuffer();
    sb.append("update Item \n");
    sb.append("set type = :complete, \n");
    sb.append("updateDate = :now, \n");
    sb.append("version = version + 1, \n");
    sb.append("updateAction = :inProgress \n");
    sb.append("where id in (:ids) \n");
    Query q = this.em.createNativeQuery(sb.toString());
    q.setParameter("complete", complete.name());
    q.setParameter("ids", ids);
    q.setParameter("now", new Date());
    q.setParameter("inProgress", inProgress.name().replaceAll("IN_PROGRESS_", ""));
    q.executeUpdate();
}

New implementation of the same process, which does work with MySQL 5.7.18:

public void changeItemsType(TypeEnum inProgress, TypeEnum complete, int howMany) {
    int updateCount = -1;
    while (updateCount != 0) {
        updateCount = 0;
        updateCount = updateItems(inProgress, complete, howMany);
    }
}

private int updateItems(TypeEnum inProgress, TypeEnum complete, int howMany) {
    StringBuffer sb = new StringBuffer();
    sb.append("update Item \n");
    sb.append("set processType = :complete, \n");
    sb.append("updateDate = :now, \n");
    sb.append("version = version + 1, \n");
    sb.append("updateAction = :inProgress \n");
    sb.append("where type = :inProgressTemp \n");

    Query q = this.em.createNativeQuery(sb.toString());
    q.setParameter("inProgressTemp", inProgress.name());
    q.setParameter("complete", complete.name());
    q.setParameter("now", new Date());
    q.setParameter("inProgress", inProgress.name().replaceAll("IN_PROGRESS_", ""));
    q.setMaxResults(howMany);

    int updateSize = 0;
    updateSize = q.executeUpdate();
    return updateSize;
}

Did you try debugging to see what's going on? E.g. printing/logging `lastId` for each block of 500, to see if it somehow skips a lot of IDs. — Andreas, Sep 20 '17 at 16:42
We did add logging to keep track of the `lastId` each time through the iteration. It was the value that we expected as the 500th, 1000th, etc. one. Then, it would only get the 1501st one and that was it. It's a very odd scenario. — Craig Morrison, Sep 20 '17 at 17:22
In my original explanation I didn't include the fact that we are using Spring and Hibernate in case some of the notation doesn't look standard. — Craig Morrison, Sep 20 '17 at 17:24
You should edit the question and add that clarification there, e.g. by adding tags. You should also consider splitting that first block of text into multiple paragraphs, to make it more readable. — Andreas, Sep 20 '17 at 18:12

Results are different with MySQL 5.7.18 versus 5.7.16

0 Answers0