Some queries that I had written for MySQL 5.5 no longer work for MySQL 5.7. I'm not sure if there is a configuration setting...
The following query is no longer valid:
Update Tablex set Column1 = 3 where Tablex.ID = 20 and not exists (
Select * From ( Select * from Tablex a where a.StartTime > :Start and a.EndTime <:end ) as DerivedTable1
//You can't specify target table Tablex for update in FROM clause
This worked perfectly in MySQL 5.5, basically the idea is that MySQL would create a temp table...but now I guess not. Anyone know how to update this for MySQL 5.7? Alternatively I can use a transaction and pre-query the exists condition and then proceed based on the results.
That's indeed a change in MySQL 5.7, described in the docs:
The optimizer now handles derived tables and views in the
FROMclause in consistent fashion to better avoid unnecessary materialization and to enable use of pushed-down conditions that produce more efficient execution plans. However, for statements such as
UPDATEthat modify tables, using the merge strategy for a derived table that previously was materialized can result in an
The error occurs when merging a derived table into the outer query block results in a statement that both selects from and modifies a table. (Materialization does not cause the problem because, in effect, it converts the derived table to a separate table.) To avoid this error, disable the
derived_mergeflag of the
optimizer_switchsystem variable before executing the statement:
mysql> SET optimizer_switch = 'derived_merge=off';
Another possible workaround is mentioned in this thread:
- Force the derived table to be materialized, e.g by adding
SELECT, or adding a
This approach has been chosen by Rails developers to fix the similar bug.
The workaround referenced in the answer from raina77ow is correct. However, not being able to execute this particular kind of query
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...); is a bug that was introduced in 5.7.6 and fixed in 5.7.11.
Bug report: https://bugs.mysql.com/bug.php?id=79333
5.7.11 release notes (search for 79333).
You can do this all in one query, using a join:
Update Tablex x left join Tablex x2 on x2.id = x.id and x2.StartTime > :Start and x2.EndTime < :end set x.Column1 = 3 where x.ID = 20 and x2.id is null;
This should work in both MySQL 5.5 and 5.7.
@raina77ow is right.
you also can add a row at
ps: I use rails, so I can't change the default behavior of rails.
I solved that by upgrading from 5.7.9 to the latest version now as I speak is 5.7.12 and the subquery workaround started working again.