Mysql2 error using mysql 5.7.9 and 5.7.10 (Fix: upgrade to 5.7.12 or later)
slbug opened this issue · 9 comments
EXCEPTION: Mysql2::Error: You can't specify target table 'page_hierarchies' for update in FROM clause: DELETE FROM `page_hierarchies` WHERE descendant_id IN ( SELECT DISTINCT descendant_id FROM (SELECT descendant_id FROM `page_hierarchies` WHERE ancestor_id = 5 OR descendant_id = 5 ) AS x )
Seems that MySQL 5.7 has gotten more strict about subselects on mutating tables. I've written a workaround that splits the query into two steps. Note that this is not as concurrent-safe and should probably not be used in high-concurrency environments.
module ClosureTree
module HierarchyMaintenance
def delete_hierarchy_references
_ct.with_advisory_lock do
results = _ct.connection.execute(<<-SQL.strip_heredoc)
SELECT DISTINCT descendant_id
FROM #{_ct.quoted_hierarchy_table_name}
WHERE ancestor_id = #{_ct.quote(id)}
OR descendant_id = #{_ct.quote(id)}
SQL
ids = Array.new
results.each { |(descendant_id)| ids << descendant_id }
_ct.connection.execute <<-SQL.strip_heredoc
DELETE FROM #{_ct.quoted_hierarchy_table_name}
WHERE descendant_id IN (#{ids.join(',')})
SQL
end
end
end
end
You can do this in one pass, the DISTINCT currently in the query is one level too high.
module ClosureTree
module HierarchyMaintenance
def delete_hierarchy_references
_ct.with_advisory_lock do
_ct.connection.execute <<-SQL.strip_heredoc
DELETE FROM #{_ct.quoted_hierarchy_table_name}
WHERE descendant_id IN (
SELECT descendant_id
FROM (SELECT DISTINCT descendant_id
FROM #{_ct.quoted_hierarchy_table_name}
WHERE ancestor_id = #{_ct.quote(id)}
OR descendant_id = #{_ct.quote(id)}
) AS x )
SQL
end
end
end
end
I'll have a look at a PR, still need to confirm this doesn't now break things on <5.7.
I just ran into this error, as well. Mysql 5.7.10
FYI: I have the same error on Mysql 5.7.10 and have no error on Mysql 5.7.12.
Follow up: I haven't seen the issue and am currently using mysql version 5.7.16 , I wonder what changed and then unchanged between mysql patch versions.
How do people feel we should handle this? A note on the README, or a warning message in the code?
Thanks, @pskrz and @gsmetal for the updates!
Both, the README is easy enough, it'd be nice to add a why and what changed to cause the failure and what changed that the failure no longer happens.
I don't know where you would put the warning message though, when adding the gem and bundle installing?
I'm assuming the MySQL query parser was changed--and it wouldn't be the first time a patch release to MySQL caused heartache. Their versioning has been maddening since I started using it in pre-5.x times.
Updated the README. Thanks again for your reports.