An UPDATE runs unexpectedly on MySQL 5.7 whereas it runs as expected on prior versions

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
3
down vote

favorite












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)









share|improve this question









New contributor




Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.















  • 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
    4 hours ago










  • I'm not sure I understand. I do have order by tkmemberstage.sequence -- where else should an order by be inserted?
    – Techmag
    4 hours ago










  • Added CREATE and INSERT as per request. tkmembershipID is a foreign key for working with a "block" of records at one time.
    – Techmag
    4 hours ago










  • You mean: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 99 order by sequence;
    – Techmag
    3 hours ago










  • Sorry -- am flipping between multiple databases/datasets -- It looks like the following works on 5.7: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 1 order by sequence; - Next step is to update the code and run application level checks. Unsure why I would have chosen a more complex form however I suspect there was a reason at the time :) I might re-find that as I update the code...
    – Techmag
    3 hours ago
















up vote
3
down vote

favorite












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)









share|improve this question









New contributor




Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.















  • 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
    4 hours ago










  • I'm not sure I understand. I do have order by tkmemberstage.sequence -- where else should an order by be inserted?
    – Techmag
    4 hours ago










  • Added CREATE and INSERT as per request. tkmembershipID is a foreign key for working with a "block" of records at one time.
    – Techmag
    4 hours ago










  • You mean: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 99 order by sequence;
    – Techmag
    3 hours ago










  • Sorry -- am flipping between multiple databases/datasets -- It looks like the following works on 5.7: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 1 order by sequence; - Next step is to update the code and run application level checks. Unsure why I would have chosen a more complex form however I suspect there was a reason at the time :) I might re-find that as I update the code...
    – Techmag
    3 hours ago












up vote
3
down vote

favorite









up vote
3
down vote

favorite











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)









share|improve this question









New contributor




Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











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)






mysql update query






share|improve this question









New contributor




Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 4 hours ago





















New contributor




Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 5 hours ago









Techmag

1185




1185




New contributor




Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






Techmag is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.







  • 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
    4 hours ago










  • I'm not sure I understand. I do have order by tkmemberstage.sequence -- where else should an order by be inserted?
    – Techmag
    4 hours ago










  • Added CREATE and INSERT as per request. tkmembershipID is a foreign key for working with a "block" of records at one time.
    – Techmag
    4 hours ago










  • You mean: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 99 order by sequence;
    – Techmag
    3 hours ago










  • Sorry -- am flipping between multiple databases/datasets -- It looks like the following works on 5.7: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 1 order by sequence; - Next step is to update the code and run application level checks. Unsure why I would have chosen a more complex form however I suspect there was a reason at the time :) I might re-find that as I update the code...
    – Techmag
    3 hours ago












  • 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
    4 hours ago










  • I'm not sure I understand. I do have order by tkmemberstage.sequence -- where else should an order by be inserted?
    – Techmag
    4 hours ago










  • Added CREATE and INSERT as per request. tkmembershipID is a foreign key for working with a "block" of records at one time.
    – Techmag
    4 hours ago










  • You mean: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 99 order by sequence;
    – Techmag
    3 hours ago










  • Sorry -- am flipping between multiple databases/datasets -- It looks like the following works on 5.7: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 1 order by sequence; - Next step is to update the code and run application level checks. Unsure why I would have chosen a more complex form however I suspect there was a reason at the time :) I might re-find that as I update the code...
    – Techmag
    3 hours ago







1




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
4 hours ago




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
4 hours ago












I'm not sure I understand. I do have order by tkmemberstage.sequence -- where else should an order by be inserted?
– Techmag
4 hours ago




I'm not sure I understand. I do have order by tkmemberstage.sequence -- where else should an order by be inserted?
– Techmag
4 hours ago












Added CREATE and INSERT as per request. tkmembershipID is a foreign key for working with a "block" of records at one time.
– Techmag
4 hours ago




Added CREATE and INSERT as per request. tkmembershipID is a foreign key for working with a "block" of records at one time.
– Techmag
4 hours ago












You mean: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 99 order by sequence;
– Techmag
3 hours ago




You mean: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 99 order by sequence;
– Techmag
3 hours ago












Sorry -- am flipping between multiple databases/datasets -- It looks like the following works on 5.7: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 1 order by sequence; - Next step is to update the code and run application level checks. Unsure why I would have chosen a more complex form however I suspect there was a reason at the time :) I might re-find that as I update the code...
– Techmag
3 hours ago




Sorry -- am flipping between multiple databases/datasets -- It looks like the following works on 5.7: set @newSequence = 0 ; update tkmemberstage set sequence = (@newSequence := @newSequence + 10) where tkmembershipID = 1 order by sequence; - Next step is to update the code and run application level checks. Unsure why I would have chosen a more complex form however I suspect there was a reason at the time :) I might re-find that as I update the code...
– Techmag
3 hours ago










1 Answer
1






active

oldest

votes

















up vote
2
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 ;





share|improve this answer






















    Your Answer







    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: false,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );






    Techmag is a new contributor. Be nice, and check out our Code of Conduct.









     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220841%2fan-update-runs-unexpectedly-on-mysql-5-7-whereas-it-runs-as-expected-on-prior-ve%23new-answer', 'question_page');

    );

    Post as a guest






























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    2
    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 ;





    share|improve this answer


























      up vote
      2
      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 ;





      share|improve this answer
























        up vote
        2
        down vote



        accepted







        up vote
        2
        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 ;





        share|improve this answer














        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 ;






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited 3 hours ago

























        answered 3 hours ago









        ypercubeᵀᴹ

        72.5k11120195




        72.5k11120195




















            Techmag is a new contributor. Be nice, and check out our Code of Conduct.









             

            draft saved


            draft discarded


















            Techmag is a new contributor. Be nice, and check out our Code of Conduct.












            Techmag is a new contributor. Be nice, and check out our Code of Conduct.











            Techmag is a new contributor. Be nice, and check out our Code of Conduct.













             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220841%2fan-update-runs-unexpectedly-on-mysql-5-7-whereas-it-runs-as-expected-on-prior-ve%23new-answer', 'question_page');

            );

            Post as a guest













































































            Popular posts from this blog

            How to check contact read email or not when send email to Individual?

            Displaying single band from multi-band raster using QGIS

            How many registers does an x86_64 CPU actually have?