Mysql2 error using mysql 5.7.9 and 5.7.10 (Fix: upgrade to 5.7.12 or later)

[slbug]

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 )

[RISCfuture]

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

[tism]

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.

[ghost]

I just ran into this error, as well. Mysql 5.7.10

[gsmetal]

FYI: I have the same error on Mysql 5.7.10 and have no error on Mysql 5.7.12.

[ghost]

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.

[mceachen]

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!

[ghost]

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?

[mceachen]

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.

[mceachen]

Updated the README. Thanks again for your reports.