Hi there, I get the following error using latest Trunk (1nightly948-1~stretch) on Debian stretch and latest community-modules (just pulled changeset 3217:063abaab666f): Aug 08 10:48:34 sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:162: Error preparing statement handle: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' Found following stacktrace in a debug log: https://pastebin.com/qPz9qNEn These are the modules used: | adhoc | admin_adhoc | announce | auth_internal_hashed | auto_answer_disco_info | bidi | blocklist | c2s | cache_c2s_caps | carbons | cloud_notify | csi | csi_battery_saver | dialback | disco | groups | http | iq | lastlog | limit_auth | log_auth | mam | mam_adhoc | message | offline | pep | pep_vcard_png_avatar | ping | presence | private | profile | register | register_web | roster | s2s | saslauth | smacks | smacks_offline | storage_sql | time | tls | uptime | user_account_management | vcard | version | watchregistrations I presume that this may be related to the recent changes in mod_pep (formerly mod_pep_plus), but have no clue how to track down the core of the issue. Any help is greatly appreciated!

Hi, thanks for the report! I wonder if this is at all related to #1073 (but that was MySQL, not MariaDB). Could you enable mod_debug_sql and capture the query from debug logs that Prosody is sending?

Thanks for your quick response! I enabled mod_sql_debug and collected several stacktraces: https://pastebin.com/i2y3RF3W From what I understand, its always a "DELETE" query for a pep node in the "prosodyarchive" database. The affected pep nodes seem to be related to avatars and OMEMO pre-keys. According to the error message, the LIMIT in the subquery is problematic. Looking at the statements, I wonder why there is a "LIMIT 18446744073709551615 OFFSET 1" in there? BTW: I saw the same error using MySQL 5.5 on Debian Wheezy. On Debian Stretch MySQL has been replaced with MariaDB (AFAIK they should be compatible except for the underlying file formats). Currently I am running mysql Ver 15.1 Distrib 10.1.26-MariaDB Cheers and thanks for your help!

Interesting. I assumed the LIMIT was a bug, but it seems it's intentional: https://hg.prosody.im/trunk/file/tip/plugins/mod_storage_sql.lua#l410 and indeed, this is the suggested method from the MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/select.html So I guess it's simply as the statement says, and it's just a limitation of MySQL/MariaDB supporting LIMIT in subqueries. Given that in this case the LIMIT is "unlimited", I guess it can be removed. Though I'm sure there are cases where a limited query may be performed, and this would require refactoring the query entirely, possibly splitting it up into multiple queries (and hurting performance :( ). I'll put together a test case, and see what we can do about it. SQL databases really are such a nightmare to work with, they've taken up so much dev time on this project. You'd think storing data was a solved problem by now.

• tags Milestone-0.11 Status-Accepted

By the way, as a workaround the old PEP module is still available, switch from "pep" to "pep_simple" in your config to use it.

Just checked and for some reason I get the same error when using "pep_simple". However, I do not see any obvious problems using the server instance. Maybe the only side effect is that the database can become very large due to the failing DELETE statements. I did some research and, as explained at https://dba.stackexchange.com/questions/20218/mysql-delete-all-but-last-n-records , it should be possible to transform the DELETE statement using a JOIN, thus getting rid of the subquery. This allows to keep the original subquery as is. As an example, instead of using -- DELETE FROM "prosodyarchive" WHERE "sort_id" IN ( SELECT "sort_id" FROM "prosodyarchive" WHERE "host" = 'example.com' AND "user" = 'username' AND "store" = 'pep_urn:xmpp:avatar:metadata' ORDER BY "sort_id" DESC LIMIT 18446744073709551615 OFFSET 1 ); -- we could use: -- DELETE result FROM prosodyarchive AS result JOIN (SELECT sort_id FROM prosodyarchive WHERE host = 'example.com' AND user = 'username' ORDER BY sort_id DESC LIMIT 18446744073709551615 OFFSET 1) AS limiter on result.sort_id = limiter.sort_id; -- Tested the above query on my instance and can confirm that it works as intended in this specific case. However, somebody with more SQL experience should double check whether this solution would work for all possible DELETE queries and if it comes with noteworthy performance penalties.

Thanks for the research! I turned your proposed query into a patch. However it's not at all tested, and it involves a DELETE, so I don't recommend running it on your server without backups unless you're feeling lucky... I'll set up a test environment as soon as I can, and try to get this resolved so we can move forward with the release. diff -r e13a1a0b0107 plugins/mod_storage_sql.lua --- a/plugins/mod_storage_sql.lua Wed Aug 08 23:20:07 2018 +0100 +++ b/plugins/mod_storage_sql.lua Thu Aug 09 00:25:16 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);

• owner MattJ
• tags Status-Started

So with the above patch applied, it resolves the issue but then I ran into what I believe is #1073. I'm working on fixing and testing both of them and should be done soon.

Updating title for clarity.

• title Error using mod_storage_sql & pep MySQL: Error using mod_storage_sql & pep

Committed a test and fix for this today: https://hg.prosody.im/trunk/rev/3a683b2476e2 Thanks for the patience, and help with forming a suitable replacement query! We now have tests to cover most storage operations, to catch issues like this and prevent them happening in the future. Note that there is still an open issue #1073 which affects the use of MySQL with Prosody trunk, which is my next focus.

• tags Status-Fixed