Hi, I have a problem with Prosody 0.9.2 with mod_storage_sql and MySQL. I have some k users online and i get randomly this error in Prosody: http://wklej.org/id/1261314/ What is this? I have never seen this error before. Tell my how i can fix that. Increase max_prepared_stmt? default is ~16000 max is 1000000.

I increase stmt limit in mysql to extremely value - set global max_prepared_stmt_count=1000000; and i see strange phenomenon. Mysql increased memory usage from 250 mb to 380 mb and suddenly dropped memory to 248 mb. When its going on stmt the beign to close - this is output from mysql when memory usage is 370 and 248 MariaDB [(none)]> show global status like 'com_stmt%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Com_stmt_close | 2025517 | <---- 370 mb | Com_stmt_execute | 2048988 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 2047586 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | +-------------------------+---------+ 7 rows in set (0.00 sec) MariaDB [(none)]> show global status like 'com_stmt%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Com_stmt_close | 2040480 | < ---- 240 | Com_stmt_execute | 2055893 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 2054513 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | +-------------------------+---------+ 7 rows in set (0.00 sec) and it's repeat. Sorry for my english again :)

Apparently we are leaking prepared statements. We will investigate this for 0.9.3, thanks for reporting this.

• tags Milestone-0.9.3 Status-Accepted
• owner Waqas

• tag Milestone-0.9

#664 and #748 are duplicates of this.

And this issue would in turn be part of, or related to #688

Everyone who posted duplicates are now subscribed to this issue. If you have anything to add, do so here. I note that apparently sql2 (sql in 0.10/trunk) is also affected(?)

I can confirm sql in 0.10 (specifically 0.10.r7656+.296543556065+) is affected. In addition to max_prepared_stmt_count statements here is my latest log entry where Prosody crashed 6 hours ago. Oct 28 15:26:52 sql error Error in SQL transaction: /usr/lib/prosody/util/sql.lua:179: Error executing statement parameters: Deadlock found when trying to get lock; try restarting transaction stack traceback: /usr/lib/prosody/util/sql.lua:179: in function 'insert' /usr/lib/prosody/modules/mod_storage_sql.lua:86: in function </usr/lib/prosody/modules/mod_storage_sql.lua:78> (tail call): ? (tail call): ? [C]: in function 'xpcall' /usr/lib/prosody/util/sql.lua:220: in function '_transaction' /usr/lib/prosody/util/sql.lua:234: in function </usr/lib/prosody/util/sql.lua:233> (tail call): ? (tail call): ? ... (tail call): ? (tail call): ? /usr/lib/prosody/modules/mod_iq.lua:54: in function '?' /usr/lib/prosody/util/events.lua:78: in function </usr/lib/prosody/util/events.lua:74> (tail call): ? (tail call): ? /usr/lib/prosody/core/stanza_router.lua:192: in function 'core_post_stanza' /usr/lib/prosody/core/stanza_router.lua:139: in function 'core_process_stanza' /usr/lib/prosody/modules/mod_c2s.lua:257: in function 'func' /usr/lib/prosody/util/async.lua:90: in function </usr/lib/prosody/util/async.lua:88>

That's actually trunk, not 0.10. Arch has weird version numbers. And that looks like a completely different error, so you should file a new issue for that.

Patch to be tested at https://prosody.im/issues/issue/688#comment-5

• tags Priority-High
• owner Waqas MattJ

• tags Status-Started

• tags Patch

• tags Milestone-0.10

Fix pushed in becb593ed86d. Thanks Waqas!

• tags Status-Fixed