Description:
After upgrading from MySQL 5.7 -> MySQL 8.0, our more complex UPDATE statements with JOINs stopped working properly.
Initially we thought they were failing all the time, but they appear to be alternating between working and not working, i.e. no, yes, no, yes, no, yes, no - this pattern is reliably observed.
There are many queries affected in the application in question, but we'll focus on a specific one:
```
UPDATE
matrix m
INNER JOIN(
SELECT
m.handset_id,
m.tariff_id,
m.addon_id,
m.gift_id,
GREATEST(
(
(
(
(
(
IF(
tc.gp_override_base > 0,
tc.gp_override_base,
130.00
) + IF(
ta.gp_adjustment_base IS NULL,
0,
ta.gp_adjustment_base
) - m.ongoing_gp
) * 1.2
) + IF(hc.gp_adjustment IS NULL, 0, hc.gp_adjustment)
) DIV 5
) * 5
) - 0.01,
0
) AS upfront_amount
FROM
matrix m
JOIN tariff_commercials tc ON tc.tariff_id = m.tariff_id
AND tc.site_affiliate_id = 12
AND tc.allow_base = 1
LEFT JOIN tariff_addon ta ON ta.tariff_id = m.tariff_id
AND ta.addon_id = m.addon_id
LEFT JOIN handset_commercials hc ON hc.handset_id = m.handset_id
AND hc.network_id = m.network_id
AND hc.site_affiliate_id = 12
JOIN handset_site_affiliate hsa ON hsa.handset_id = m.handset_id
AND hsa.site_affiliate_id = 12
AND hsa.active = 1
WHERE
m.network_id = 15
AND m.contract_type_id = 2
AND m.tariff_type_id = 13
AND m.gift_id = 0
) AS t ON m.handset_id = t.handset_id
AND m.tariff_id = t.tariff_id
AND m.addon_id = t.addon_id
AND m.gift_id = t.gift_id
SET
m.upfront_amount = t.upfront_amount,
m.base_upfront_gp = m.upfront_gp + ROUND(t.upfront_amount / 1.2, 2),
m.base_ongoing_gp = m.ongoing_gp + ROUND(t.upfront_amount / 1.2, 2),
m.base_partner_gp = m.partner_gp + ROUND(t.upfront_amount / 1.2, 2),
m.valid = 1
WHERE
t.upfront_amount < 399.99
```
Running this returned affected rows 0, even though we knew there were records that needed updating.
To try and debug it, we turned it into a SELECT:
```
SELECT m.*
FROM matrix m
INNER JOIN(
SELECT
m.handset_id,
m.tariff_id,
m.addon_id,
m.gift_id,
GREATEST(
(
(
(
(
(
IF(
tc.gp_override_base > 0,
tc.gp_override_base,
130.00
) + IF(
ta.gp_adjustment_base IS NULL,
0,
ta.gp_adjustment_base
) - m.ongoing_gp
) * 1.2
) + IF(hc.gp_adjustment IS NULL, 0, hc.gp_adjustment)
) DIV 5
) * 5
) - 0.01,
0
) AS upfront_amount
FROM
matrix m
JOIN tariff_commercials tc ON tc.tariff_id = m.tariff_id
AND tc.site_affiliate_id = 12
AND tc.allow_base = 1
LEFT JOIN tariff_addon ta ON ta.tariff_id = m.tariff_id
AND ta.addon_id = m.addon_id
LEFT JOIN handset_commercials hc ON hc.handset_id = m.handset_id
AND hc.network_id = m.network_id
AND hc.site_affiliate_id = 12
JOIN handset_site_affiliate hsa ON hsa.handset_id = m.handset_id
AND hsa.site_affiliate_id = 12
AND hsa.active = 1
WHERE
m.network_id = 15
AND m.contract_type_id = 2
AND m.tariff_type_id = 13
AND m.gift_id = 0
) AS t ON m.handset_id = t.handset_id
AND m.tariff_id = t.tariff_id
AND m.addon_id = t.addon_id
AND m.gift_id = t.gift_id
WHERE
t.upfront_amount < 399.99
```
and this returned just over 19k rows.
Initially we thought this was related to the subquery, so we transformed it into this:
```
CREATE TEMPORARY TABLE tmp_matrix_update_1 (
`id` int unsigned not null primary key auto_increment,
`handset_id` int unsigned NOT NULL,
`tariff_id` int unsigned NOT NULL,
`addon_id` int unsigned NOT NULL DEFAULT '0',
`gift_id` int unsigned NOT NULL,
`upfront_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
key `idx_composite` (`handset_id`, `tariff_id`, `addon_id`, `gift_id`, `upfront_amount`)
);
insert into tmp_matrix_update_1 ( `handset_id`, `tariff_id`, `addon_id`, `gift_id`, `upfront_amount` ) SELECT
m.handset_id,
m.tariff_id,
m.addon_id,
m.gift_id,
GREATEST(
(
(
(
(
(
IF(
tc.gp_override_base > 0,
tc.gp_override_base,
70.00
) + IF(
ta.gp_adjustment_base IS NULL,
0,
ta.gp_adjustment_base
) - m.ongoing_gp
) * 1.2
) + IF(hc.gp_adjustment IS NULL, 0, hc.gp_adjustment)
) DIV 5
) * 5
) - 0.01,
0
) AS upfront_amount
FROM
matrix m
JOIN tariff_commercials tc ON tc.tariff_id = m.tariff_id
AND tc.site_affiliate_id = 12
AND tc.allow_base = 1
LEFT JOIN tariff_addon ta ON ta.tariff_id = m.tariff_id
AND ta.addon_id = m.addon_id
LEFT JOIN handset_commercials hc ON hc.handset_id = m.handset_id
AND hc.network_id = m.network_id
AND hc.site_affiliate_id = 12
JOIN handset_site_affiliate hsa ON hsa.handset_id = m.handset_id
AND hsa.site_affiliate_id = 12
AND hsa.active = 1
WHERE
m.network_id = 13
AND m.contract_type_id = 2
AND m.tariff_type_id = 13
AND m.gift_id = 0;
UPDATE
matrix m
INNER JOIN tmp_matrix_update_1 AS t ON m.handset_id = t.handset_id
AND m.tariff_id = t.tariff_id
AND m.addon_id = t.addon_id
AND m.gift_id = t.gift_id
SET
m.upfront_amount = t.upfront_amount,
m.base_upfront_gp = m.upfront_gp + ROUND(t.upfront_amount / 1.2, 2),
m.base_ongoing_gp = m.ongoing_gp + ROUND(t.upfront_amount / 1.2, 2),
m.base_partner_gp = m.partner_gp + ROUND(t.upfront_amount / 1.2, 2),
m.valid = 1
WHERE
t.upfront_amount < 399.99
```
yet this still returned affected rows 0.
How to repeat:
In the end, we ended up running:
```
UPDATE matrix SET valid = 0 (successfully updated all records, every time)
SELECT count(*)
FROM matrix m
JOIN tmp_matrix_update_1 t
ON m.handset_id = t.handset_id
AND m.tariff_id = t.tariff_id
AND m.addon_id = t.addon_id
AND m.gift_id = t.gift_id (always returns over 19k records)
UPDATE matrix m
JOIN tmp_matrix_update_1 t
ON m.handset_id = t.handset_id
AND m.tariff_id = t.tariff_id
AND m.addon_id = t.addon_id
AND m.gift_id = t.gift_id
SET m.valid = 1 (alternating between either, affected rows 0, or affected rows 19k)
```
As you can see, we ran a query to set all records `valid = 0`, so there would definitely be changes to the records required (to cover affected rows 0 because nothing needed to change).
The SELECT statement proves the join works properly.
And the final UPDATE, on repeated testing, would alternately return `affected rows 0` and when running all those above queries again, would return `affected rows 19k` as expected.
There was no-one else using the DB server, the application was disconnected & we were running these interactively for debugging - I can confirm the behaviour was alternating between updating nothing, or updating as expected, alternately, i.e. no, yes, no, yes, no, yes, no.
Suggested fix:
As a workaround, we had to use a `REPLACE INTO` statement instead:
```
CREATE TEMPORARY TABLE tmp_matrix_update_1 (
`id` int unsigned not null primary key auto_increment,
`handset_id` int unsigned NOT NULL,
`tariff_id` int unsigned NOT NULL,
`addon_id` int unsigned NOT NULL DEFAULT '0',
`gift_id` int unsigned NOT NULL,
`upfront_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
key `idx_composite` (`handset_id`, `tariff_id`, `addon_id`, `gift_id`, `upfront_amount`)
);
insert into tmp_matrix_update_1 ( `handset_id`, `tariff_id`, `addon_id`, `gift_id`, `upfront_amount` ) SELECT
m.handset_id,
m.tariff_id,
m.addon_id,
m.gift_id,
GREATEST(
(
(
(
(
(
IF(
tc.gp_override_base > 0,
tc.gp_override_base,
70.00
) + IF(
ta.gp_adjustment_base IS NULL,
0,
ta.gp_adjustment_base
) - m.ongoing_gp
) * 1.2
) + IF(hc.gp_adjustment IS NULL, 0, hc.gp_adjustment)
) DIV 5
) * 5
) - 0.01,
0
) AS upfront_amount
FROM
matrix m
JOIN tariff_commercials tc ON tc.tariff_id = m.tariff_id
AND tc.site_affiliate_id = 12
AND tc.allow_base = 1
LEFT JOIN tariff_addon ta ON ta.tariff_id = m.tariff_id
AND ta.addon_id = m.addon_id
LEFT JOIN handset_commercials hc ON hc.handset_id = m.handset_id
AND hc.network_id = m.network_id
AND hc.site_affiliate_id = 12
JOIN handset_site_affiliate hsa ON hsa.handset_id = m.handset_id
AND hsa.site_affiliate_id = 12
AND hsa.active = 1
WHERE
m.network_id = 13
AND m.contract_type_id = 2
AND m.tariff_type_id = 13
AND m.gift_id = 0;
replace into matrix
select
m.`id`,
m.`contract_type_id`,
m.`tariff_type_id`,
m.`handset_id`,
m.`tariff_id`,
m.`addon_id`,
m.`gift_id`,
m.`network_id`,
m.`upfront_payment`,
m.`rev_share`,
m.`pre_payment`,
m.`length`,
m.`line_rental`,
m.`family`,
m.`inc_data`,
m.`plan_id`,
m.`sim_cost`,
m.`h_cb_cost`,
m.`h_cb_bonus`,
m.`network_support`,
m.`handset_postage`,
m.`gift_cost`,
m.`gift_postage`,
m.`total_cost`,
m.`commission_upfront`,
m.`commission_ongoing`,
m.`commission_partner`,
m.`upfront_gp`,
m.`ongoing_gp`,
m.`partner_gp`,
m.`tcc`,
t.`upfront_amount`,
m.upfront_gp + ROUND(t.upfront_amount / 1.2, 2), /*m.`base_upfront_gp`,*/
m.ongoing_gp + ROUND(t.upfront_amount / 1.2, 2), /*m.`base_ongoing_gp`,*/
m.partner_gp + ROUND(t.upfront_amount / 1.2, 2), /*m.`base_partner_gp`,*/
m.`cashback_amount`,
m.`cashback_upfront_gp`,
m.`cashback_ongoing_gp`,
m.`redemption_amount`,
m.`redemption_value`,
m.`redemption_upfront_gp`,
m.`redemption_ongoing_gp`,
m.`line_rental_full`,
m.`line_rental_full_value`,
m.`line_rental_full_upfront_gp`,
m.`line_rental_full_ongoing_gp`,
m.`line_rental_half`,
m.`line_rental_half_value`,
m.`line_rental_half_upfront_gp`,
m.`line_rental_half_ongoing_gp`,
1
from matrix m
inner join tmp_matrix_update_1 t ON m.handset_id = t.handset_id
AND m.tariff_id = t.tariff_id
AND m.addon_id = t.addon_id
AND m.gift_id = t.gift_id
where
t.upfront_amount < 399.99;
```
and this was updating the rows as expected.
Description: After upgrading from MySQL 5.7 -> MySQL 8.0, our more complex UPDATE statements with JOINs stopped working properly. Initially we thought they were failing all the time, but they appear to be alternating between working and not working, i.e. no, yes, no, yes, no, yes, no - this pattern is reliably observed. There are many queries affected in the application in question, but we'll focus on a specific one: ``` UPDATE matrix m INNER JOIN( SELECT m.handset_id, m.tariff_id, m.addon_id, m.gift_id, GREATEST( ( ( ( ( ( IF( tc.gp_override_base > 0, tc.gp_override_base, 130.00 ) + IF( ta.gp_adjustment_base IS NULL, 0, ta.gp_adjustment_base ) - m.ongoing_gp ) * 1.2 ) + IF(hc.gp_adjustment IS NULL, 0, hc.gp_adjustment) ) DIV 5 ) * 5 ) - 0.01, 0 ) AS upfront_amount FROM matrix m JOIN tariff_commercials tc ON tc.tariff_id = m.tariff_id AND tc.site_affiliate_id = 12 AND tc.allow_base = 1 LEFT JOIN tariff_addon ta ON ta.tariff_id = m.tariff_id AND ta.addon_id = m.addon_id LEFT JOIN handset_commercials hc ON hc.handset_id = m.handset_id AND hc.network_id = m.network_id AND hc.site_affiliate_id = 12 JOIN handset_site_affiliate hsa ON hsa.handset_id = m.handset_id AND hsa.site_affiliate_id = 12 AND hsa.active = 1 WHERE m.network_id = 15 AND m.contract_type_id = 2 AND m.tariff_type_id = 13 AND m.gift_id = 0 ) AS t ON m.handset_id = t.handset_id AND m.tariff_id = t.tariff_id AND m.addon_id = t.addon_id AND m.gift_id = t.gift_id SET m.upfront_amount = t.upfront_amount, m.base_upfront_gp = m.upfront_gp + ROUND(t.upfront_amount / 1.2, 2), m.base_ongoing_gp = m.ongoing_gp + ROUND(t.upfront_amount / 1.2, 2), m.base_partner_gp = m.partner_gp + ROUND(t.upfront_amount / 1.2, 2), m.valid = 1 WHERE t.upfront_amount < 399.99 ``` Running this returned affected rows 0, even though we knew there were records that needed updating. To try and debug it, we turned it into a SELECT: ``` SELECT m.* FROM matrix m INNER JOIN( SELECT m.handset_id, m.tariff_id, m.addon_id, m.gift_id, GREATEST( ( ( ( ( ( IF( tc.gp_override_base > 0, tc.gp_override_base, 130.00 ) + IF( ta.gp_adjustment_base IS NULL, 0, ta.gp_adjustment_base ) - m.ongoing_gp ) * 1.2 ) + IF(hc.gp_adjustment IS NULL, 0, hc.gp_adjustment) ) DIV 5 ) * 5 ) - 0.01, 0 ) AS upfront_amount FROM matrix m JOIN tariff_commercials tc ON tc.tariff_id = m.tariff_id AND tc.site_affiliate_id = 12 AND tc.allow_base = 1 LEFT JOIN tariff_addon ta ON ta.tariff_id = m.tariff_id AND ta.addon_id = m.addon_id LEFT JOIN handset_commercials hc ON hc.handset_id = m.handset_id AND hc.network_id = m.network_id AND hc.site_affiliate_id = 12 JOIN handset_site_affiliate hsa ON hsa.handset_id = m.handset_id AND hsa.site_affiliate_id = 12 AND hsa.active = 1 WHERE m.network_id = 15 AND m.contract_type_id = 2 AND m.tariff_type_id = 13 AND m.gift_id = 0 ) AS t ON m.handset_id = t.handset_id AND m.tariff_id = t.tariff_id AND m.addon_id = t.addon_id AND m.gift_id = t.gift_id WHERE t.upfront_amount < 399.99 ``` and this returned just over 19k rows. Initially we thought this was related to the subquery, so we transformed it into this: ``` CREATE TEMPORARY TABLE tmp_matrix_update_1 ( `id` int unsigned not null primary key auto_increment, `handset_id` int unsigned NOT NULL, `tariff_id` int unsigned NOT NULL, `addon_id` int unsigned NOT NULL DEFAULT '0', `gift_id` int unsigned NOT NULL, `upfront_amount` decimal(10,2) NOT NULL DEFAULT '0.00', key `idx_composite` (`handset_id`, `tariff_id`, `addon_id`, `gift_id`, `upfront_amount`) ); insert into tmp_matrix_update_1 ( `handset_id`, `tariff_id`, `addon_id`, `gift_id`, `upfront_amount` ) SELECT m.handset_id, m.tariff_id, m.addon_id, m.gift_id, GREATEST( ( ( ( ( ( IF( tc.gp_override_base > 0, tc.gp_override_base, 70.00 ) + IF( ta.gp_adjustment_base IS NULL, 0, ta.gp_adjustment_base ) - m.ongoing_gp ) * 1.2 ) + IF(hc.gp_adjustment IS NULL, 0, hc.gp_adjustment) ) DIV 5 ) * 5 ) - 0.01, 0 ) AS upfront_amount FROM matrix m JOIN tariff_commercials tc ON tc.tariff_id = m.tariff_id AND tc.site_affiliate_id = 12 AND tc.allow_base = 1 LEFT JOIN tariff_addon ta ON ta.tariff_id = m.tariff_id AND ta.addon_id = m.addon_id LEFT JOIN handset_commercials hc ON hc.handset_id = m.handset_id AND hc.network_id = m.network_id AND hc.site_affiliate_id = 12 JOIN handset_site_affiliate hsa ON hsa.handset_id = m.handset_id AND hsa.site_affiliate_id = 12 AND hsa.active = 1 WHERE m.network_id = 13 AND m.contract_type_id = 2 AND m.tariff_type_id = 13 AND m.gift_id = 0; UPDATE matrix m INNER JOIN tmp_matrix_update_1 AS t ON m.handset_id = t.handset_id AND m.tariff_id = t.tariff_id AND m.addon_id = t.addon_id AND m.gift_id = t.gift_id SET m.upfront_amount = t.upfront_amount, m.base_upfront_gp = m.upfront_gp + ROUND(t.upfront_amount / 1.2, 2), m.base_ongoing_gp = m.ongoing_gp + ROUND(t.upfront_amount / 1.2, 2), m.base_partner_gp = m.partner_gp + ROUND(t.upfront_amount / 1.2, 2), m.valid = 1 WHERE t.upfront_amount < 399.99 ``` yet this still returned affected rows 0. How to repeat: In the end, we ended up running: ``` UPDATE matrix SET valid = 0 (successfully updated all records, every time) SELECT count(*) FROM matrix m JOIN tmp_matrix_update_1 t ON m.handset_id = t.handset_id AND m.tariff_id = t.tariff_id AND m.addon_id = t.addon_id AND m.gift_id = t.gift_id (always returns over 19k records) UPDATE matrix m JOIN tmp_matrix_update_1 t ON m.handset_id = t.handset_id AND m.tariff_id = t.tariff_id AND m.addon_id = t.addon_id AND m.gift_id = t.gift_id SET m.valid = 1 (alternating between either, affected rows 0, or affected rows 19k) ``` As you can see, we ran a query to set all records `valid = 0`, so there would definitely be changes to the records required (to cover affected rows 0 because nothing needed to change). The SELECT statement proves the join works properly. And the final UPDATE, on repeated testing, would alternately return `affected rows 0` and when running all those above queries again, would return `affected rows 19k` as expected. There was no-one else using the DB server, the application was disconnected & we were running these interactively for debugging - I can confirm the behaviour was alternating between updating nothing, or updating as expected, alternately, i.e. no, yes, no, yes, no, yes, no. Suggested fix: As a workaround, we had to use a `REPLACE INTO` statement instead: ``` CREATE TEMPORARY TABLE tmp_matrix_update_1 ( `id` int unsigned not null primary key auto_increment, `handset_id` int unsigned NOT NULL, `tariff_id` int unsigned NOT NULL, `addon_id` int unsigned NOT NULL DEFAULT '0', `gift_id` int unsigned NOT NULL, `upfront_amount` decimal(10,2) NOT NULL DEFAULT '0.00', key `idx_composite` (`handset_id`, `tariff_id`, `addon_id`, `gift_id`, `upfront_amount`) ); insert into tmp_matrix_update_1 ( `handset_id`, `tariff_id`, `addon_id`, `gift_id`, `upfront_amount` ) SELECT m.handset_id, m.tariff_id, m.addon_id, m.gift_id, GREATEST( ( ( ( ( ( IF( tc.gp_override_base > 0, tc.gp_override_base, 70.00 ) + IF( ta.gp_adjustment_base IS NULL, 0, ta.gp_adjustment_base ) - m.ongoing_gp ) * 1.2 ) + IF(hc.gp_adjustment IS NULL, 0, hc.gp_adjustment) ) DIV 5 ) * 5 ) - 0.01, 0 ) AS upfront_amount FROM matrix m JOIN tariff_commercials tc ON tc.tariff_id = m.tariff_id AND tc.site_affiliate_id = 12 AND tc.allow_base = 1 LEFT JOIN tariff_addon ta ON ta.tariff_id = m.tariff_id AND ta.addon_id = m.addon_id LEFT JOIN handset_commercials hc ON hc.handset_id = m.handset_id AND hc.network_id = m.network_id AND hc.site_affiliate_id = 12 JOIN handset_site_affiliate hsa ON hsa.handset_id = m.handset_id AND hsa.site_affiliate_id = 12 AND hsa.active = 1 WHERE m.network_id = 13 AND m.contract_type_id = 2 AND m.tariff_type_id = 13 AND m.gift_id = 0; replace into matrix select m.`id`, m.`contract_type_id`, m.`tariff_type_id`, m.`handset_id`, m.`tariff_id`, m.`addon_id`, m.`gift_id`, m.`network_id`, m.`upfront_payment`, m.`rev_share`, m.`pre_payment`, m.`length`, m.`line_rental`, m.`family`, m.`inc_data`, m.`plan_id`, m.`sim_cost`, m.`h_cb_cost`, m.`h_cb_bonus`, m.`network_support`, m.`handset_postage`, m.`gift_cost`, m.`gift_postage`, m.`total_cost`, m.`commission_upfront`, m.`commission_ongoing`, m.`commission_partner`, m.`upfront_gp`, m.`ongoing_gp`, m.`partner_gp`, m.`tcc`, t.`upfront_amount`, m.upfront_gp + ROUND(t.upfront_amount / 1.2, 2), /*m.`base_upfront_gp`,*/ m.ongoing_gp + ROUND(t.upfront_amount / 1.2, 2), /*m.`base_ongoing_gp`,*/ m.partner_gp + ROUND(t.upfront_amount / 1.2, 2), /*m.`base_partner_gp`,*/ m.`cashback_amount`, m.`cashback_upfront_gp`, m.`cashback_ongoing_gp`, m.`redemption_amount`, m.`redemption_value`, m.`redemption_upfront_gp`, m.`redemption_ongoing_gp`, m.`line_rental_full`, m.`line_rental_full_value`, m.`line_rental_full_upfront_gp`, m.`line_rental_full_ongoing_gp`, m.`line_rental_half`, m.`line_rental_half_value`, m.`line_rental_half_upfront_gp`, m.`line_rental_half_ongoing_gp`, 1 from matrix m inner join tmp_matrix_update_1 t ON m.handset_id = t.handset_id AND m.tariff_id = t.tariff_id AND m.addon_id = t.addon_id AND m.gift_id = t.gift_id where t.upfront_amount < 399.99; ``` and this was updating the rows as expected.