From 21e358c847273538b00b4d3c4f237a0459e92051 Mon Sep 17 00:00:00 2001 From: Matthew Wild Date: Thu, 11 Oct 2018 18:20:09 +0100 Subject: mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200 --- plugins/mod_storage_sql.lua | 12 +++++++++--- 1 file changed, 9 insertions(+), 3 deletions(-) diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua index 018d0d29..395e937d 100644 --- a/plugins/mod_storage_sql.lua +++ b/plugins/mod_storage_sql.lua @@ -398,6 +398,15 @@ function archive_store:delete(username, query) LIMIT %s OFFSET ?; ]]; unlimited = "-1"; + elseif engine.params.driver == "MySQL" then + sql_query = [[ + DELETE result FROM prosodyarchive AS result JOIN ( + SELECT sort_id FROM prosodyarchive + WHERE %s + ORDER BY "sort_id" %s + LIMIT %s OFFSET ? + ) AS limiter on result.sort_id = limiter.sort_id;]]; + unlimited = "18446744073709551615"; else sql_query = [[ DELETE FROM "prosodyarchive" @@ -407,9 +416,6 @@ function archive_store:delete(username, query) ORDER BY "sort_id" %s LIMIT %s OFFSET ? );]]; - if engine.params.driver == "MySQL" then - unlimited = "18446744073709551615"; - end end sql_query = string.format(sql_query, t_concat(where, " AND "), query.reverse and "ASC" or "DESC", unlimited); -- cgit v1.2.3