Slow DELETE query in Mail 3.7.2

[small1]

Steps to reproduce

1. Delete lots of mail from another client
2. Go tp nextcloud mail
3. Infinite loading of mail and error logs says 409 mailbox is locked.

Expected behavior

Updated list of emails

Actual behavior

Get the updated list of emails

Mail app version

3.7.2

Mailserver or service

dovecot imap

Operating system

Debian 12

PHP engine version

PHP 8.2

Web server

Apache (supported)

Database

MariaDB

Additional info

MessageService.js:48
GET https:// .... /apps/mail/api/messages?mailboxId=64&limit=20 409 (Conflict)

App.vue:86 [ERROR] mail: Background sync failed: undefined
{app: 'mail', uid: 'jobe', level: '0', error: MailboxLockedError
at m (https://... /apps/mail/js/mail.js?v=62d0ca7e-77:2:72882)
…}

[small1]

131463 | nextcloud | localhost | nextcloud | Query | 3 | Updating | DELETE FROM oc_mail_recipients WHERE message_id IN (SELECT id FROM oc_mail_messages WHERE (`

This might be related. Mariadb is on 100% cpu and has a few thread with that.

[ChristophWurst]

131463 | nextcloud | localhost | nextcloud | Query | 3 | Updating | DELETE FROM oc_mail_recipients WHERE message_id IN (SELECT id FROM oc_mail_messages WHERE (`

Addressed with #9761

[ChristophWurst]

This might be related.

Yes, probably. The sync has to delete those messages and it will be slow due to the full table scan. Until the query returns the mailbox will remain locked.

[small1]

*************************** 1. row ***************************
Id: 131463
User: nextcloud
Host: localhost
db: nextcloud
Command: Query
Time: 1
State: Updating
Info: DELETE FROM oc_mail_recipients WHERE message_id IN (SELECT id FROM oc_mail_messages WHERE (mailbox_id = '64') AND (uid IN (476563, 476564, 476565, 476566, 476567, 476568, 476569, 476570, 476571, 476573, 476574, 476575, 476576, 476577, 476578, 476579, 476580, 476581, 476582, 476583, 476584, 476585, 476586, 476587, 476588, 476589, 476590, 476591, 476592, 476593, 476594, 476595, 476596, 476597, 476598, 476599, 476600, 476601, 476602, 476603, 476604, 476605, 476606, 476607, 476608, 476609, 476610, 476611, 476612, 476613, 476614, 476615, 476616, 476617, 476618, 476619, 476620, 476621, 476622, 476623, 476624, 476625, 476626, 476627, 476628, 476629, 476630, 476631, 476632, 476633, 476634, 476635, 476636, 476637, 476638, 476639, 476640, 476642, 476643, 476644, 476645, 476648, 476649, 476650, 476651, 476653, 476654, 476655, 476656, 476657, 476658, 476659, 476660, 476661, 476662, 476663, 476664, 476665, 476666, 476667, 476668, 476669, 476670, 476671, 476672, 476673, 476674, 476675, 476676, 476677, 476678, 476679, 476680, 476681, 476682, 476683, 476684, 476685, 476686, 476687, 476688, 476689, 476690, 476691, 476692, 476693, 476694, 476695, 476696, 476697, 476699, 476700, 476701, 476702, 476703, 476704, 476705, 476706, 476707, 476708, 476709, 476712, 476713, 476714, 476715, 476716, 476717, 476718, 476719, 476720, 476721, 476722, 476723, 476724, 476725, 476726, 476727, 476728, 476729, 476730, 476731, 476732, 476733, 476734, 476735, 476736, 476737, 476738, 476739, 476740, 476741, 476742, 476743, 476744, 476745, 476746, 476747, 476748, 476749, 476750, 476751, 476752, 476753, 476754, 476755, 476756, 476757, 476758, 476759, 476760, 476761, 476762, 476763, 476764, 476765, 476766, 476767, 476768, 476769, 476770, 476771, 476772, 476773, 476774, 476775, 476776, 476777, 476778, 476779, 476780, 476781, 476782, 476783, 476784, 476785, 476786, 476787, 476788, 476789, 476790, 476791, 476792, 476793, 476794, 476795, 476796, 476797, 476798, 476799, 476800, 476801, 476802, 476803, 476804, 476805, 476806, 476807, 476808, 476809, 476812, 476813, 476814, 476815, 476816, 476817, 476818, 476819, 476820, 476821, 476822, 476823, 476824, 476825, 476826, 476827, 476828, 476829, 476830, 476831, 476832, 476833, 476834, 476835, 476836, 476837, 476838, 476839, 476840, 476841, 476842, 476843, 476844, 476845, 476846, 476847, 476848, 476849, 476850, 476851, 476852, 476853, 476854, 476855, 476856, 476857, 476858, 476859, 476860, 476861, 476862, 476863, 476864, 476865, 476866, 476867, 476868, 476869, 476870, 476871, 476872, 476873, 476874, 476875, 476876, 476877, 476878, 476879, 476880, 476881, 476882, 476883, 476884, 476885, 476886, 476887, 476888, 476889, 476890, 476891, 476892, 476893, 476894, 476895, 476896, 476897, 476898, 476899, 476900, 476901, 476902, 476903, 476904, 476905, 476907, 476908, 476909, 476910, 476911, 476912, 476913, 476914, 476915, 476916, 476917, 476918, 476919, 476920, 476921, 476922, 476923, 476924, 476925, 476926, 476927, 476928, 476929, 476930, 476931, 476932, 476933, 476934, 476935, 476936, 476937, 476938, 476939, 476940, 476941, 476942, 476943, 476944, 476945, 476946, 476948, 476949, 476950, 476951, 476952, 476953, 476954, 476955, 476956, 476957, 476958, 476959, 476962, 476963, 476964, 476965, 476966, 476967, 476968, 476969, 476970, 476971, 476972, 476973, 476974, 476975, 476976, 476977, 476978, 476979, 476980, 476981, 476982, 476983, 476984, 476985, 476986, 476987, 476988, 476989, 476990, 476991, 476992, 476993, 476994, 476995, 476996, 476997, 476998, 476999, 477000, 477001, 477002, 477003, 477004, 477005, 477006, 477007, 477008, 477009, 477010, 477011, 477012, 477013, 477014, 477015, 477016, 477017, 477018, 477019, 477020, 477021, 477022, 477023, 477024, 477025, 477026, 477027, 477028, 477029, 477030, 477031, 477032, 477033, 477034, 477035, 477036, 477037, 477038, 477039, 477040, 477041, 477042, 477043, 477044, 477045, 477046, 477047, 477048, 477049, 477050, 477051, 477052, 477053, 477054, 477055, 477056, 477057, 477058, 477059, 477060, 477061, 477062, 477063, 477064, 477065, 477066, 477067, 477068, 477069, 477070, 477072, 477074, 477075, 477076, 477077, 477078)))
Progress: 0.000

[small1]

That db query takes forever!

[ChristophWurst]

This should be fine with #9813