Empty the trash bin with 49000 files in it

ℹ️ Support
1

Hi, I am trying to empty my trash bin via the web GUI. There are 49000 files in it and the easiest option would be an “Empty Trash” button, but there is no such button in the GUI.

I tried selecting all the files at once and deleting them but the selection is so enormous, it simply crashes my server (MySQL crashes and corrupts the DB so I must restore from a backup).

I tried calling a DELETE method via WebDAV as described in the documentation (Trashbin — Nextcloud latest Developer Manual latest documentation) but the MySQL crashes also in this case.

How can I work around this and get rid of all those files in the Trash bin? Maybe by manually editing the database?

Thanks in advance for any suggestions.

Version Information: nextcloud 23.0.0, PHP 8.0 on FreeBSD 13.0-RELEASE-p6

2

There is an occ command for that: Using the occ command — Nextcloud latest Administration Manual latest documentation

3

Can you elaborate? That’s not normal behavior, even with a lot of files. Maybe something else is going on.

Also, to clarify, these were recently deleted files, correct? Because the trash bin is managed in the background by default otherwise: Deleted Items (trash bin) — Nextcloud latest Administration Manual latest documentation

4

Thank you, I have been searching for this command for quite a while now!
However, when I execute it, the MySQL data gets corrupted and the DB server crashes, see log posted below.

5

I am testing again with the command occ trashbin:cleanup and it works up to a point (it was able to completely clear the table oc_files_trash).
However, when I try to load Nextcloud’s GUI it results in an Internal Server Error.
The MySQL server has crashed and the following info is logged (it looks like a MariaDB bug):

> 2023-11-29 13:04:55 4 [ERROR] InnoDB: Unable to find a record to delete-mark
> InnoDB: tuple DATA TUPLE: 3 fields;
>  0: len 8; hex 8000000000000003; asc         ;;
>  1: len 32; hex 3839353439343563383165303537323631353837653136303234653231316239; asc 8954945c81e057261587e16024e211b9;;
>  2: len 8; hex 80000000000241e3; asc       A ;;
> 
> InnoDB: record PHYSICAL RECORD: n_fields 3; compact format; info bits 0
>  0: len 8; hex 8000000000000003; asc         ;;
>  1: len 30; hex 383935343934356338316530353732363135383765313630323465323131; asc 8954945c81e057261587e16024e211; (total 32 bytes);
>  2: len 8; hex 800000000002411b; asc       A ;;
> 2023-11-29 13:04:55 4 [ERROR] InnoDB: page [page id: space=31, page number=5409] (234 records, index id 713).
> 2023-11-29 13:04:55 4 [ERROR] InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
> 2023-11-29 13:05:23 0x814305000  InnoDB: Assertion failure in file /wrkdirs/usr/ports/databases/mariadb105-server/work/mariadb-10.5.13/storage/innobase/rem/rem0rec.cc line 877
> InnoDB: We intentionally generate a memory trap.
> InnoDB: Submit a detailed bug report to https://jira.mariadb.org/
> InnoDB: If you get repeated assertion failures or crashes, even
> InnoDB: immediately after the mysqld startup, there may be
> InnoDB: corruption in the InnoDB tablespace. Please refer to
> InnoDB: https://mariadb.com/kb/en/library/innodb-recovery-modes/
> InnoDB: about forcing recovery.
> 231129 13:05:23 [ERROR] mysqld got signal 6 ;
> This could be because you hit a bug. It is also possible that this binary
> or one of the libraries it was linked against is corrupt, improperly built,
> or misconfigured. This error can also be caused by malfunctioning hardware.
> 
> To report this bug, see https://mariadb.com/kb/en/reporting-bugs
> 
> We will try our best to scrape up some info that will hopefully help
> diagnose the problem, but since we have already crashed, 
> something is definitely wrong and this may fail.
> 
> Server version: 10.5.13-MariaDB
> key_buffer_size=134217728
> read_buffer_size=131072
> max_used_connections=1
> max_threads=153
> thread_count=1
> It is possible that mysqld could use up to 
> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467794 K  bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
> 
> Thread pointer: 0x81fc06458
> Attempting backtrace. You can use the following information to find out
> where mysqld died. If you see no messages after this, something went
> terribly wrong...
> stack_bottom = 0x7fffdc739f38 thread_stack 0x49000
> 0x1329b5c <my_print_stacktrace+0x3c> at /usr/local/libexec/mariadbd
> 0xc87890 <handle_fatal_signal+0x290> at /usr/local/libexec/mariadbd
> 0x801924e00 <_pthread_sigmask+0x530> at /lib/libthr.so.3
> 
> Trying to get some variables.
> Some pointers may be invalid and cause the dump to abort.
> Query (0x81fc4fa30): DELETE FROM `oc_filecache` WHERE `parent` IN (178, 179, 181, 180, 175560, 250343, 128469, 162607, 170054, 175347, 200959, 232446, 273737, 275047, 278286, 251, 284874, 149358, 153735, 314, 148197, 203594, 160288, 3208, 125073, 8393, 3555, 175558, 123538, 6536, 242817, 177346, 3760, 123733, 13534, 153736, 13535, 13537, 123735, 123734, 3761, 3762, 150608, 150611, 3763, 3556, 247150, 2723, 123311, 229114, 272210, 167360, 230809, 272256, 167801, 279399, 275430, 1484, 2550, 1485, 2549, 27120, 29437, 122916, 70921, 122920, 121889, 132058, 132970, 149413, 149377, 151398, 162590, 170040, 175329, 163206, 170121, 199764, 199839, 20335, 214507, 231807, 231825, 241258, 252197, 259930, 259871, 267295, 267339, 272764, 272801, 274852, 278283, 278317, 281985, 285997, 122896, 244, 152442, 2105, 154934, 123, 163763, 149357, 149120, 150898, 163762, 238, 2106, 2104, 116, 232657, 116930, 203592, 122898, 2108, 177766, 199161, 210973, 122875, 2111, 122897, 122876, 2503, 2504, 180034, 122899, 123287, 2505, 123288, 282539, 177378, 122880, 2507, 122877, 2508, 122900, 2548, 2704, 122879, 2705, 2706, 2724, 2725, 2726, 123310, 2736, 2737, 1746, 285084, 122901, 147864, 149815, 122902, 122906, 123309, 2750, 2751, 123314, 2752, 2753, 123320, 2754, 242761, 177135, 916, 2755, 151186, 248667, 123290, 242575, 248734, 250165, 252283, 242872, 252857, 270180, 276165, 1439, 123396, 3105, 3106, 122774, 181815, 1729, 122904, 15510, 77883, 122903, 285103, 78046, 285128, 8396, 285092, 122905, 70933, 122907, 122909, 122908, 122910, 123312, 160017, 21233, 285111, 7137, 152491, 123301, 122911, 67034, 2703, 160216, 154916, 150897, 90514, 153730, 243, 122776, 122779, 122775, 122778, 122780, 122777, 122822, 122823, 122824, 122825, 122826, 122827, 122832, 122831, 122828, 122829, 122830, 122834, 122833, 122858, 122869, 122857, 122862, 122861, 122864, 122863, 122867, 122868, 122871, 122836, 122835, 122837, 122838, 122839, 122840, 122854, 122853, 122860, 122865, 122859, 122866, 122870, 122846, 122851, 122845, 122852, 122844, 122850, 122843, 122849, 122856, 122842, 122847, 122855, 122841, 122848, 122804, 122801, 122803, 122812, 122815, 122813, 122814, 122816, 122817, 122818, 122819, 122821, 122820, 122799, 122802, 122805, 122806, 122809, 122810, 122811, 122807, 122808, 122781, 122782, 122787, 122786, 122788, 122789, 122790, 122791, 122792, 122793, 122795, 122794, 122796, 122797, 122798, 122800, 3111, 3112, 3114, 3115, 123400, 123403, 2975, 2977, 2978, 279988, 3034, 3102, 3107, 3113, 3049, 3052, 3053, 3051, 3054, 3056, 3057, 3058, 3059, 3060, 3061, 3062, 3063, 3064, 3065, 3066, 3067, 3068, 3069, 3070, 3071, 3072, 3074, 3075, 3077, 3073, 2979, 2980, 2981, 2983, 3002, 3005, 3031, 3033, 3041, 3045, 3037, 3039, 3044, 3023, 3026, 3032, 3004, 3008, 3009, 3010, 3017, 3018, 3020, 3013, 3011, 2996, 3000, 3003, 2993, 2997, 2985, 2989, 2984, 2988, 917, 2795, 2796, 1752, 1758, 2732, 2734, 2735, 2741, 2740, 2739, 2733, 2718, 2720, 273725, 279517, 2710, 2713, 2711, 2551, 2684, 2686, 2691, 2692, 2695, 2696, 2699, 2698, 122914, 123291, 123293, 123294, 123295, 123296, 123297, 123298, 123299, 123300, 2542, 177010, 2509, 2510, 2511, 2513, 2512, 2514, 2515, 2518, 2519, 2521, 2522, 78044, 2524, 2526, 2529, 2528, 2530, 2532, 2533, 2536, 2538, 2539, 2541, 247086, 2112, 2113, 2123, 2455, 2485, 2423, 2421, 2424, 2450, 2451, 2452, 2454, 2474, 2472, 2457, 2456, 2436, 2435, 2437, 2438, 2440, 2426, 2425, 2428, 3386, 2118, 2119, 2120, 2121, 2122, 2284, 2281, 2126, 2125, 2124, 150899, 3196, 284338)
> 
> Connection ID (thread ID): 4
> Status: NOT_KILLED
> 
> Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
> 
> The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
> information that should help you find out what is causing the crash.
> Core pattern: %N.core
> 2023-11-29 13:05:24 0 [Note] InnoDB: Uses event mutexes
> 2023-11-29 13:05:24 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
> 2023-11-29 13:05:24 0 [Note] InnoDB: Number of pools: 1
> 2023-11-29 13:05:24 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
> 2023-11-29 13:05:24 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
> 2023-11-29 13:05:24 0 [Note] InnoDB: Completed initialization of buffer pool
> 2023-11-29 13:05:24 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=424793675381,424818776113
> 2023-11-29 13:05:24 0 [Note] InnoDB: Starting a batch to recover 6464 pages from redo log.
> 2023-11-29 13:05:30 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 87764 row operations to undo
> 2023-11-29 13:05:30 0 [Note] InnoDB: Trx id counter is 250123518
> 2023-11-29 13:05:30 0 [Note] InnoDB: Starting final batch to recover 3282 pages from redo log.
> 2023-11-29 13:05:40 0 [Note] InnoDB: 128 rollback segments are active.
> 2023-11-29 13:05:40 0 [Note] InnoDB: Starting in background the rollback of recovered transactions
> 2023-11-29 13:05:40 0 [Note] InnoDB: To roll back: 1 transactions, 87764 rows
> 2023-11-29 13:05:40 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
> 2023-11-29 13:05:40 0 [Note] InnoDB: Creating shared tablespace for temporary tables
> 2023-11-29 13:05:40 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
> 2023-11-29 13:05:40 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
> 2023-11-29 13:05:40 0 [Note] InnoDB: 10.5.13 started; log sequence number 424826134622; transaction id 250123519
> 2023-11-29 13:05:40 0 [Note] Plugin 'FEEDBACK' is disabled.
> 2023-11-29 13:05:40 0 [Note] InnoDB: Loading buffer pool(s) from /var/db/mysql/ib_buffer_pool
> 2023-11-29 13:05:40 0 [Note] Server socket created on IP: '127.0.0.1'.
> 2023-11-29 13:05:40 0 [Note] Reading of all Master_info entries succeeded
> 2023-11-29 13:05:40 0 [Note] Added new Master_info '' to hash table
> 2023-11-29 13:05:40 0 [Note] /usr/local/libexec/mariadbd: ready for connections.
> Version: '10.5.13-MariaDB'  socket: '/var/run/mysql/mysql.sock'  port: 3306  FreeBSD Ports
> 2023-11-29 13:05:40 0 [Note] InnoDB: Buffer pool(s) load completed at 231129 13:05:40
> 2023-11-29 13:05:40 0 [ERROR] InnoDB: Insert buffer insert fails; page free 21, dtuple size 43
> InnoDB: Cannot insert index record DATA TUPLE: 3 fields;
>  0: len 8; hex 8000000000000c81; asc         ;;
>  1: len 20; hex 70726566732e6a732e7631373030383037343336; asc prefs.js.v1700807436;;
>  2: len 8; hex 8000000000046627; asc       f';;
> 
> InnoDB: The table where this index record belongs
> InnoDB: is now probably corrupt. Please run CHECK TABLE on
> InnoDB: that table.
> 2023-11-29 13:05:40 0 [ERROR] InnoDB: page [page id: space=31, page number=9916], size 8192, bitmap bits 0
> 2023-11-29 13:05:40 0 [ERROR] InnoDB: Submit a detailed bug report to https://jira.mariadb.org/

I cannot assess if this is due to a memory limitation or not.
Should I file a bug report to MariaDB?

I run on FreeBSD 13.0 with mariadb105-server-10.5.13.

6

I succeeded in executing occ trashbin:cleanup at last.
Apparently, the MariaDB memory default of 128M was insufficient, which lead to somehow corrupting the database.
I increased the memory to 512M and executed the command in the console again, this time all files were removed from the Trash Bin and Nextcloud is still working properly.

In summary, the resolution was:

  1. Increase the MariaDB memory to 512 MB in /usr/local/etc/mysql/conf.d/server.cnf:
# Memory tweaks, as suggested in https://mariadb.com/kb/en/mariadb-memory-allocation/
innodb_buffer_pool_size = 536870912
key_buffer_size = 10M
  1. Restarted MariaDB server
  2. Executed the command: php occ trashbin:cleanup myusername

Thanks for your help, I will accept the suggested solution.

1 Like
7

innodb_buffer_pool_size is the most important buffer size and even 512 MB is really on the low side. I use 4GB.
about key_buffer_size read this: Configuring MariaDB for Optimal Performance - MariaDB Knowledge Base

  • If you don’t use MyISAM tables explicitly (true for most MariaDB 10.4+ users), you can set key_buffer_size to a very low value, like 64K.

I would suggest, to use a tuning tool like → MySQLTuner-perl ← to tweek your database to optimum instead of trying and stumbling around without real knowledge :wink:

Much luck,
ernolf

1 Like
8

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.