What steps will reproduce the problem? 1. Install prosody 0.11.8 with mod_pep via docker DIGEST:sha256:602140d31c591390da7b57900c4bf460a9430a68b496fafff5e37e0666931d69 2. Configure with sql backend with mysql (version >= 8.0.22) 3. Connect a client with PEP support (e.g. Conversations). What is the expected output? No error. What do you see instead? These error messages show up multiple times if a client connects. sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute domain.tld:pep error Unable to get item: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute What version of the product are you using? On what operating system? - prosody 0.11.8 with mod_pep - mysql (version >= 8.0.22) Please provide any additional information below. AFAIK, prepared statements have been changed starting with version 8.0.22 of mysql to avoid programming mistakes. Same issue seems to be discussed here: https://github.com/sidorares/node-mysql2/issues/1239

Hi, thanks for the report. Could you try to reproduce this with debug logging and mod_debug_sql enabled. This would show what the full query was.

• tags Status-NeedInfo Component-Persistence MySQL

Great resonse time. :-) There seem to be multiple queries affected. All of them related to 'prosodyarchive'. I have cut them out of the debug log... domain.tld:bookmarks debug No existing Private XML bookmarks for foobar@domain.tld, migration already done: <nil> sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'pep_storage:bookmarks' ORDER BY "sort_id" DESC LIMIT 1; sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:814: in function 'get' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:861: in function 'head' /usr/lib/prosody/util/pubsub.lua:662: in function 'get_last_item' /usr/lib/prosody/modules/mod_bookmarks/mod_bookmarks.lua:103: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/sessionmanager.lua:209: in function 'sm_bind_resource' /usr/lib/prosody/modules/mod_saslauth.lua:313: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:142: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute domain.tld:pep error Unable to get item: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute -- domain.tld:pep debug get_pep_service("foobar") sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'pep_eu.siacs.conversations.axolotl.bundles:1610564730' ORDER BY "sort_id" DESC LIMIT 1; sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:795: in function 'items' /usr/lib/prosody/util/pubsub.lua:641: in function 'get_items' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:311: in function 'handler' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:291: in function </usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:278> (...tail calls...) /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/modules/mod_iq.lua:38: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:180: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute domain.tld:pep error Unable to get items: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute -- domain.tld:pep debug get_pep_service("foobar") sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'pep_urn:xmpp:avatar:metadata' ORDER BY "sort_id" DESC LIMIT 1; sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:814: in function 'get' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:861: in function 'head' /usr/lib/prosody/util/pubsub.lua:662: in function 'get_last_item' /usr/lib/prosody/modules/mod_vcard_legacy.lua:312: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:176: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute domain.tld:pep error Unable to get item: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute -- domain.tld:mam debug Archive query, id gdmejm08uf with anyone from the dawn of time until now sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'archive' AND "sort_id" > COALESCE( ( SELECT "sort_id" FROM "prosodyarchive" WHERE "key" = '33f6b268-d415-4659-aa51-c4a586a2ebd1' AND "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'archive' LIMIT 1 ), 0) ORDER BY "sort_id" ASC LIMIT 51; sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_mam/mod_mam.lua:133: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:181: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute -- domain.tld:pep debug get_pep_service("foobar") sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'pep_eu.siacs.conversations.axolotl.devicelist' ORDER BY "sort_id" DESC LIMIT 1; sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:814: in function 'get' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:861: in function 'head' /usr/lib/prosody/util/pubsub.lua:662: in function 'get_last_item' /usr/lib/prosody/modules/mod_pep.lua:268: in function 'resend_last_item' /usr/lib/prosody/modules/mod_pep.lua:295: in function 'update_subscriptions' /usr/lib/prosody/modules/mod_pep.lua:390: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:180: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute domain.tld:pep error Unable to get item: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute -- sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'pep_storage:bookmarks' ORDER BY "sort_id" DESC LIMIT 1; sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:814: in function 'get' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:861: in function 'head' /usr/lib/prosody/util/pubsub.lua:662: in function 'get_last_item' /usr/lib/prosody/modules/mod_pep.lua:268: in function 'resend_last_item' /usr/lib/prosody/modules/mod_pep.lua:295: in function 'update_subscriptions' /usr/lib/prosody/modules/mod_pep.lua:390: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:180: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute domain.tld:pep error Unable to get item: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute -- sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'pep_urn:xmpp:avatar:metadata' ORDER BY "sort_id" DESC LIMIT 1; sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:814: in function 'get' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:861: in function 'head' /usr/lib/prosody/util/pubsub.lua:662: in function 'get_last_item' /usr/lib/prosody/modules/mod_pep.lua:268: in function 'resend_last_item' /usr/lib/prosody/modules/mod_pep.lua:295: in function 'update_subscriptions' /usr/lib/prosody/modules/mod_pep.lua:390: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:180: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute domain.tld:pep error Unable to get item: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute -- domain.tld:pep debug get_pep_service("foobar") sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug [select] SELECT "key", "type", "value", "when", "with" FROM "prosodyarchive" WHERE "host" = 'domain.tld' AND "user" = 'foobar' AND "store" = 'pep_urn:xmpp:avatar:metadata' ORDER BY "sort_id" DESC LIMIT 1; sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:814: in function 'get' /usr/lib/prosody/modules/mod_pubsub/pubsub.lib.lua:861: in function 'head' /usr/lib/prosody/util/pubsub.lua:662: in function 'get_last_item' /usr/lib/prosody/modules/mod_vcard_legacy.lua:312: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:176: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:276: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute domain.tld:pep error Unable to get item: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute

Found https://bugs.mysql.com/bug.php?id=102642 which makes me think MySQL changed their API in some incompatible way. I think this means it needs to be fixed in LuaDBI. If you are lucky it might simply need a recompile.

Could reproduce with: ``` lua db=assert(require"DBI".Connect("MySQL", "prosody", "prosody", "secret", "127.0.0.1", 3306)) s=assert(db:prepare("SELECT 1 LIMIT ? OFFSET ?")) assert(s:execute(1, 1)) ``` I wonder if it's because numbers in Lua are floats (prior to 5.3), and it's expecting integers there? Filed this issue: https://github.com/mwild1/luadbi/issues/64 I guess we could work around it by inserting that number with printf instead.

• tags Status-Accepted

still no movement on this issue which seems to also break MAM on my server.

• tags Milestone-0.12

If you're encountering this issue on Lua 5.3, please just use Lua 5.2, which is the recommended version of Lua for that version of Prosody. Lua 5.3 is not officially supported with this version of Prosody. Prosody 0.12.x will officially target Lua 5.4 and Lua 5.3. If you're encountering this issue on Lua 5.2/5.1, please shout, because it probably means we wrongly diagnosed the issue.

I'm using 5.1 Prosody 0.11.9 # Prosody directories Data directory: /var/lib/prosody Config directory: /etc/prosody Source directory: /usr/lib/prosody Plugin directories: /usr/lib/prosody/modules/ # Lua environment Lua version: Lua 5.2 Lua module search paths: /usr/lib/prosody/?.lua /usr/local/share/lua/5.2/?.lua /usr/local/share/lua/5.2/?/init.lua /usr/local/lib/lua/5.2/?.lua /usr/local/lib/lua/5.2/?/init.lua /usr/share/lua/5.2/?.lua /usr/share/lua/5.2/?/init.lua Lua C module search paths: /usr/lib/prosody/?.so /usr/local/lib/lua/5.2/?.so /usr/lib/x86_64-linux-gnu/lua/5.2/?.so /usr/lib/lua/5.2/?.so /usr/local/lib/lua/5.2/loadall.so LuaRocks: Not installed # Network Backend: select # Lua module versions DBI: 0.7 lfs: LuaFileSystem 1.6.3 libevent: 2.1.8-stable luaevent: 0.4.6 lxp: LuaExpat 1.3.0 socket: LuaSocket 3.0-rc1 ssl: 0.7 this is the error log I'm seeing: mod_websocket debug Websocket received frame: opcode=1, 473 bytes c2s55bf6d58e010 debug Received[c2s]: <iq id='0bf5aa0e-8569-489b-87a2-1cbc0a39b5fc:sendIQ' to='fzgxgibw44uol2jl3dvgbmr1cqgqcs1pyl@157.230.71.9' type='set'> sql debug SQL transaction begin [function(mod_storage_sql.lua:62)] sql debug SQL transaction success [function(mod_storage_sql.lua:62)] 157.230.71.9:mam debug Archive query, id nil with x68yw9zalex243jjx5dkxqcwjuzb087gyhcbl564@157.230.71.9 from the dawn of time until now sql debug SQL transaction begin [function(mod_storage_sql.lua:328)] sql debug Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute stack traceback: /usr/lib/prosody/util/sql.lua:151: in function </usr/lib/prosody/util/sql.lua:148> (...tail calls...) [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:206: in function '_transaction' /usr/lib/prosody/util/sql.lua:221: in function 'transaction' /usr/lib/prosody/modules/mod_storage_sql.lua:328: in function 'find' /usr/lib/prosody/modules/mod_mam/mod_mam.lua:133: in function '?' /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/util/events.lua:79: in function </usr/lib/prosody/util/events.lua:75> (...tail calls...) /usr/lib/prosody/core/stanza_router.lua:181: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:127: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:284: in function 'func' /usr/lib/prosody/util/async.lua:127: in function </usr/lib/prosody/util/async.lua:125> sql debug SQL transaction failure [function(mod_storage_sql.lua:328)]: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute sql debug SQL connection is up, so not retrying sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:151: Error executing statement parameters: Incorrect arguments to mysqld_stmt_execute I'm running prosody in a docker container, mysql database is in a separate docker container on the same server, seems to give this error whenever I try to access the message archive

sorry I meant 5.2 not 5.1...

it does seem like the consensus for the root cause of the issue is MySQL 8.0.22 and above. My MySQL server is 8.0.25 so that adds up. The github issue linked in the original post has some workarounds but I don't know if something needs to be updated in Prosody or in LuaDBI https://github.com/sidorares/node-mysql2/issues/1239

solved this by downgrading my MySQL container to 8.0.17 (the version I'm running on my production server that hasn't been updated in a while). It should work with any version 8.0.21 and below, as 8.0.22 introduced a change that is causing this issue. Not sure how the fix for this is being planned for 0.12 but it doesn't seem like the targeted Lua version matters, and it doesn't look like LuaDBI is going to be updated.

Would someone kindly explain why this, which appears to be an incompatible API change introduced a minor version of MySQL, should be fixed in Prosody?

It seems like the way prepared statements are generated needs to be patched in LuaDBI to address this properly, I don't have the skills to do that unfortunately. Barring that I guess it doesn't Have to be fixed in Prosody but at least a note should be added to the documentation since a lot of modules are supposed to be compatible with MySQL. If a fix could be made by just inserting the values differently and that fix is backwards compatible with older versions of MySQL then why not do it? What's the point of maintaining this library otherwise?

Because of a screw-up of my ISP I needed to reinstall my entire server and hence ran into this issue as well. I got annoyed enough to do the following dirty fix in the "mod_storage_sql.lua" "function archive_store:find": ```lua -- if query.limit then -- args[#args+1] = query.limit; -- end -- sql_query = sql_query:format(t_concat(where, " AND "), query.reverse -- and "DESC" or "ASC", query.limit and " LIMIT ?" or ""); sql_query = sql_query:format(t_concat(where, " AND "), query.reverse and "DESC" or "ASC", query.limit and " LIMIT " .. query.limit or ""); ``` so far it seems to work and I hope it might help somebody else in urgent need. Best, NUF

was this fixed in 0.12?

This is still an issue in prosody-0.12 1nightly184-1~jammy with MySQL 8.0.30 (percona).

Percona might not work anyway due to #1665

#1665 is if you're using Galera replication only

This should patch LIMIT and OFFSET for MySQL 8: diff -rNu prosody.orig/modules/mod_storage_sql.lua prosody/modules/mod_storage_sql.lua --- prosody.orig/modules/mod_storage_sql.lua 2022-12-29 21:40:20.000000000 +0000 +++ prosody/modules/mod_storage_sql.lua 2023-01-01 01:41:11.448767542 +0000 @@ -469,12 +469,9 @@ local ok, err = archive_where_id_range(query, args, where); if not ok then return ok, err; end - if query.limit then - args[#args+1] = query.limit; - end sql_query = sql_query:format(t_concat(where, " AND "), query.reverse - and "DESC" or "ASC", query.limit and " LIMIT ?" or ""); + and "DESC" or "ASC", query.limit and " LIMIT " .. query.limit or ""); return engine:select(sql_query, unpack(args)); end); if not ok then return ok, result; end @@ -592,6 +589,17 @@ if not ok then return ok, err; end if query.truncate == nil then sql_query = sql_query:format(t_concat(where, " AND ")); + 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 18446744073709551615 OFFSET %s + ) AS limiter on result.sort_id = limiter.sort_id;]]; + + sql_query = string.format(sql_query, t_concat(where, " AND "), + query.reverse and "ASC" or "DESC", query.truncate); else args[#args+1] = query.truncate; local unlimited = "ALL"; @@ -613,15 +621,6 @@ ]]; end 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"; end sql_query = string.format(sql_query, t_concat(where, " AND "), query.reverse and "ASC" or "DESC", unlimited);

Thanks for the patch. I ran it against our storage tests and it passes. Any chance it could be submitted as a commit for attribution (and ? I note that it introduces additional string concatenation into building SQL statements which is somewhat scary.

• tags Patch

Merged patch as https://hg.prosody.im/trunk/rev/fba795dd99c8 Thanks It'll be in 0.12.3 when that's released. Please do direct attention to LuaDBI where the real fix should eventually go: https://github.com/mwild1/luadbi/issues/64

• tags