I use a column called sequence on tables where I have to re-sort blocks of rows into an arbitrarily order.

CREATE TABLE `tkmemberstage` (
  `ID` bigint(22) unsigned NOT NULL AUTO_INCREMENT,
  `tkmembershipID` bigint(22) unsigned NOT NULL DEFAULT '0',
  `sequence` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `tkmembershipID` (`tkmembershipID`)
)

insert into tkmemberstage set tkmembershipID = 1, sequence = 10;
insert into tkmemberstage set tkmembershipID = 1, sequence = 20;
insert into tkmemberstage set tkmembershipID = 1, sequence = 30;
insert into tkmemberstage set tkmembershipID = 1, sequence = 40;

A few years back I researched the following as a means of resequencing a block of rows in a single UPDATE

This works by creating an interim virtual table and then updating the entire block of rows in one transaction instead of having to write each row out programmatically.

The UPDATE statement goes like this:

update 
tkmemberstage 
join 
(select tkmemberstage.ID,
(@newSequence := @newSequence+10) as newSequence 
from 
tkmemberstage 
cross join 
(select @newSequence := 0) 
constructedTable 
where 
tkmemberstage.tkmembershipID = 1  
order by tkmemberstage.sequence) 
constructedTableReordered 
on tkmemberstage.ID=constructedTableReordered.ID 
set tkmemberstage.sequence=constructedTableReordered.newSequence;

I have copied it verbatim from a development server in order not to add any translation problems to the mix.

I currently have this running on a mix of RDS servers in the AWS cloud and on anything other than a 5.7 server it works as expected.

In troubleshooting this I tried to deconstruct the statement and try it in partial chunks.

Here is the dataset I'm working on:

mysql> select ID, sequence from tkmemberstage;
+----+----------+
| ID | sequence |
+----+----------+
|  1 |       10 |
|  2 |       20 |
|  3 |       30 |
|  4 |       40 |
+----+----------+
4 rows in set (0.00 sec)

I then perform an update to sequence on a single row:

mysql> update tkmemberstage set sequence=sequence - 11 where tkmemberstage.ID = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select ID, sequence from tkmemberstage;
+----+----------+
| ID | sequence |
+----+----------+
|  1 |       10 |
|  2 |       20 |
|  3 |       30 |
|  4 |       29 |
+----+----------+
4 rows in set (0.00 sec)

Now if I run the SELECT part of the UPDATE statement I get exactly what we expect:

mysql> select tkmemberstage.ID,(@newSequence := @newSequence+10) as newSequence from tkmemberstage cross join (select @newSequence := 0) constructedTable where tkmemberstage.tkmembershipID = 1  order by tkmemberstage.sequence;
+----+-------------+
| ID | newSequence |
+----+-------------+
|  1 |          10 |
|  2 |          20 |
|  4 |          30 |
|  3 |          40 |
+----+-------------+
4 rows in set (0.01 sec)

And yet when I run the final UPDATE statement itself here is the result:

mysql> update tkmemberstage join (select tkmemberstage.ID,(@newSequence := @newSequence+10) as newSequence from tkmemberstage cross join (select @newSequence := 0) constructedTable where tkmemberstage.tkmembershipID = 1  order by tkmemberstage.sequence) constructedTableReordered on tkmemberstage.ID=constructedTableReordered.ID set tkmemberstage.sequence=constructedTableReordered.newSequence;
Query OK, 1 row affected (0.00 sec)
Rows matched: 4  Changed: 1  Warnings: 0

mysql> select ID, sequence from tkmemberstage;
+----+----------+
| ID | sequence |
+----+----------+
|  1 |       10 |
|  2 |       20 |
|  3 |       30 |
|  4 |       40 |
+----+----------+
4 rows in set (0.00 sec)

So the first obvious question is what changed in 5.7 that could/would cause this kind of behaviour.

(It's been consistent for well over 6 months now. I just patched the 5.7 server today and retested and still the same results. I also installed 5.7 on a AWS AMI (Cloud9) instance where I did these tests so this behaviour is now confirmed on numerous instances of 5.7.)

The second question is of course what exactly do we do about it?

The third, a nice to know, is how would we go deeper in troubleshooting this?

For what it is worth here is the EXPLAIN of the UPDATE on 5.7:

mysql> explain update tkmemberstage join (select tkmemberstage.ID,(@newSequence := @newSequence+10) as newSequence from tkmemberstage cross join (select @newSequence := 0) constructedTable where tkmemberstage.tkmembershipID = 1  order by tkmemberstage.sequence) constructedTableReordered on tkmemberstage.ID=constructedTableReordered.ID set tkmemberstage.sequence=constructedTableReordered.newSequence;                                                                                                                                                                                                          
+----+-------------+---------------+------------+------+----------------+-------------+---------+-------------------------------+------+----------+----------------------------------------------+
| id | select_type | table         | partitions | type | possible_keys  | key         | key_len | ref                           | rows | filtered | Extra                                        |
+----+-------------+---------------+------------+------+----------------+-------------+---------+-------------------------------+------+----------+----------------------------------------------+
|  1 | UPDATE      | tkmemberstage | NULL       | ALL  | PRIMARY        | NULL        | NULL    | NULL                          |    4 |   100.00 | NULL                                         |
|  1 | PRIMARY     | <derived2>    | NULL       | ref  | <auto_key0>    | <auto_key0> | 8       | well20180515.tkmemberstage.ID |    2 |   100.00 | NULL                                         |
|  2 | DERIVED     | tkmemberstage | NULL       | ALL  | tkmembershipID | NULL        | NULL    | NULL                          |    4 |   100.00 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | <derived3>    | NULL       | ALL  | NULL           | NULL        | NULL    | NULL                          |    1 |   100.00 | Using join buffer (Block Nested Loop)        |
|  3 | DERIVED     | NULL          | NULL       | NULL | NULL           | NULL        | NULL    | NULL                          | NULL |     NULL | No tables used                               |
+----+-------------+---------------+------------+------+----------------+-------------+---------+-------------------------------+------+----------+----------------------------------------------+
5 rows in set (0.00 sec)

Whereas here is the EXPLAIN on a non 5.7 version:

mysql> explain update tkmemberstage join (select tkmemberstage.ID,(@newSequence := @newSequence+10) as newSequence from tkmemberstage cross join (select @newSequence := 0) constructedTable where tkmemberstage.tkmembershipID = 1  order by tkmemberstage.sequence) constructedTableReordered on tkmemberstage.ID=constructedTableReordered.ID set tkmemberstage.sequence=constructedTableReordered.newSequence;  
+----+-------------+---------------+--------+----------------+----------------+---------+------------------------------+------+----------------+
| id | select_type | table         | type   | possible_keys  | key            | key_len | ref                          | rows | Extra          |
+----+-------------+---------------+--------+----------------+----------------+---------+------------------------------+------+----------------+
|  1 | PRIMARY     | <derived2>    | ALL    | NULL           | NULL           | NULL    | NULL                         |    2 | NULL           |
|  1 | PRIMARY     | tkmemberstage | eq_ref | PRIMARY        | PRIMARY        | 8       | constructedTableReordered.ID |    1 | NULL           |
|  2 | DERIVED     | <derived3>    | system | NULL           | NULL           | NULL    | NULL                         |    1 | Using filesort |
|  2 | DERIVED     | tkmemberstage | ref    | tkmembershipID | tkmembershipID | 8       | const                        |    1 | Using where    |
|  3 | DERIVED     | NULL          | NULL   | NULL           | NULL           | NULL    | NULL                         | NULL | No tables used |
+----+-------------+---------------+--------+----------------+----------------+---------+------------------------------+------+----------------+
5 rows in set (0.03 sec)
  • 1
    I haven't checked, but while it certanly can be related to a versiln change, your update doesn't seem determunistic- could you add an order by to the update to discard that? Sometimes optimizer optimizations (or regressions) reveals bugs on the logic of a query- and yours look like one that would depend on the output order- which is not guarantee without an order by. – jynus Oct 23 at 20:23
  • I'm not sure I understand. I do have order by tkmemberstage.sequence -- where else should an order by be inserted? – Techmag Oct 23 at 20:31
  • 1
    Discussion moved to chat. – ypercubeᵀᴹ Oct 23 at 21:35
up vote 4 down vote accepted

The problem is likely caused by one of these - or both:

  • Lack of matching index for the query - on (tkmembershipID, sequence). Without this index (and any index), the optimizer will likely choose a table scan.

  • Optimization improvements on version 5.7. I've seen similar issues in other flavours (call me Maria) where an optimization improvement caused a query to use a different plan. Specifically, (after either version 5.3 or 5.5) MariaDB's optimizer knew that ORDER BY in subqueries is redundant, so it could be "optimized away".

    If a similar thing happens here, and the plan used gets rid of the ORDER BY, that explains the results.

    (And that may be considered a bug - as it changes your expected outcome when using variables. You can file a bug report to MySQL, with your situation and they may be able to fix it - or suggest a different solution/workaround).

In the mean time, I suggest you add the index mentioned above and check the plans again.


By the way, it seems like your query can be simplified to:

set @newSequence = 0 ; 

update tkmemberstage 
set sequence = (@newSequence := @newSequence + 10) 
where tkmembershipID = 1 
order by sequence ;
  • @Techmag To expand my initial suggestion- See that ORDER BY in the last update (it may not be sql-standard)?, that is the one I was mentioning it. An index may create an implicit order, but without the order by, it is still non deterministic. You should do all updates/deletes that affect multiple columns depending on the order with an explicit ORDER BY (e.g. another typical example would be DELETE FROM ... LIMIT 10). I personally would consider this not a server regression itself, but I can see it as annoying. The order by on subqueries should not affect the outer update-theoretically. – jynus Oct 24 at 9:57
  • @jynus No, that is not standard. They could not add an ORDER BY in their UPDATE, MySQL does not allow it when the UPDATE has a join. It's only allowed for single table updates. – ypercubeᵀᴹ Oct 24 at 11:00
  • As I use a connection poo l is there any way not to do the "fix" (which works beautifully btw so thank you) as a single transaction? I use Scala (which uses most of Java's underlying mechanisms) and can wrap the transaction in a synchronoous block which would help but I've even seen those break under heavy load conditions. What will happen is one thread/connection will get the first statement and another thread/connection will get the second and this would of course break as @newSequence is not initialized. (Hmmm I should test that supposition first howedver). – Techmag Oct 24 at 13:46

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.