Slow DELETE query in Mail 3.7.2

small1 commented 1 month ago

Steps to reproduce

Delete lots of mail from another client
Go tp nextcloud mail
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 commented 1 month ago

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

ChristophWurst commented 1 month ago

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 https://github.com/nextcloud/mail/pull/9761

ChristophWurst commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

That db query takes forever!

ChristophWurst commented 4 weeks ago

This should be fine with https://github.com/nextcloud/mail/pull/9813

nextcloud / mail