mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200
--- a/plugins/mod_storage_sql.lua Thu Oct 11 18:56:11 2018 +0200
+++ b/plugins/mod_storage_sql.lua Thu Oct 11 18:20:09 2018 +0100
@@ -398,6 +398,15 @@
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 @@
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);