# HG changeset patch
# User Matthew Wild <mwild1@gmail.com>
# Date 1539278409 -3600
# Node ID 3a683b2476e2b7a4aa4e3f1da7c2d44ffa10ed0a
# Parent  bd178ed0459b5f981d8fea64c21c85c0d57dce92
mod_storage_sql: Workaround MySQL not supporting LIMIT subquery in archive truncate operations, fixes #1200

diff -r bd178ed0459b -r 3a683b2476e2 plugins/mod_storage_sql.lua
--- 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);