# Database recreation not really starting? **URL:** \ **Category:** Support\ **Created:** [October 30, 2023, 10:00am UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948 "2023-10-30T10:00:32Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [November 1, 2023, 11:27am UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/21 "2023-11-01T11:27:40Z")
> [@titilesoleil](#): > > Do you want me to upload it somehwere ? Please do, and post a link. > [@titilesoleil](#): > > I also installed an SQL client so I can open the tables. This is nice. The bug report privacy protection removes paths, which might be relevant to this issue. Logs can get removed too (maybe based on having paths in them), so I might need some help later. The info so far will take a little time to look at, but on the surface you have the same fewer-files-than-expected that was seen before. Cause never got fully tracked down, but more data will possibly help. > [@titilesoleil](#): > > Unexpected number of deleted filesets 5 vs 6 We have some history with these too, but again no full understanding of how they come about. Sorry. IIRC it says something Duplicati decided to change had already been changed. Database confusion should become more visible when we can see the database bug report and get some context for this. ---
**Author:** ![titilesoleil](https://forum.duplicati.com/user_avatar/forum.duplicati.com/titilesoleil/32/8592_2.png) [@titilesoleil](https://forum.duplicati.com/u/titilesoleil)\ **Post date:** [November 1, 2023, 12:34pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/22 "2023-11-01T12:34:45Z")
Here is the result. I hope I did it correctly. I assumed that “Unexpected difference in fileset version 8: 29.09.2023 13:00:00 (database id: 5)” meant that I should put 5 as fileidnumber. Translation from french : Execution without error. Result : 0 records bring back in 14529s at line 1 L’exécution s’est terminée sans erreur. Résultat : 0 enregistrements ramenés en 14529ms À la ligne 1 : SELECT DISTINCT PATH, FilesetID, FileID, Typ FROM ( SELECT A.Path AS Path, D.FilesetID, D.FileID, “regfile” as Typ FROM File A LEFT JOIN Blockset B ON A.BlocksetID = B.ID LEFT JOIN Metadataset C ON A.MetadataID = C.ID LEFT JOIN FilesetEntry D ON A.ID = D.FileID LEFT JOIN Blockset E ON E.ID = C.BlocksetID LEFT JOIN BlocksetEntry G ON B.ID = G.BlocksetID LEFT JOIN BlocksetEntry I ON E.ID = I.BlocksetID WHERE A.BlocksetId \>= 0 AND D.FilesetID = (5) AND (I.“Index” = 0 OR I.“Index” IS NULL) AND (G.“Index” = 0 OR G.“Index” IS NULL) UNION SELECT B.Path, A.FilesetID, A.FileID, “dirlink” as Typ FROM FilesetEntry A, File B, Metadataset C, Blockset D, BlocksetEntry E, Block F WHERE A.FileID = B.ID AND B.MetadataID = C.ID AND C.BlocksetID = D.ID AND E.BlocksetID = C.BlocksetID AND E.BlockID = F.ID AND E.“Index” = 0 AND (B.BlocksetID = -100 OR B.BlocksetID = -200) AND A.FilesetID = (5) ) R WHERE NOT EXISTS ---
**Author:** ![titilesoleil](https://forum.duplicati.com/user_avatar/forum.duplicati.com/titilesoleil/32/8592_2.png) [@titilesoleil](https://forum.duplicati.com/u/titilesoleil)\ **Post date:** [November 1, 2023, 12:37pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/23 "2023-11-01T12:37:22Z")
I started the upload on my google drive, it should be available in about 15 minutes on this link [https://drive.google.com/drive/folders/1\_6nv\_GX4GGxZZ7WaupwA9\_e5xmVJOc7z?usp=sharing](https://drive.google.com/drive/folders/1_6nv_GX4GGxZZ7WaupwA9_e5xmVJOc7z?usp=sharing) ---
**Author:** ![gpatel-fr](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/g/7993a0/32.png) [@gpatel-fr](https://forum.duplicati.com/u/gpatel-fr)\ **Post date:** [November 1, 2023, 1:14pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/24 "2023-11-01T13:14:43Z")
> [@titilesoleil](#): > > Here is the result. I hope I did it correctly Duh, you did but my query was not powerful enough to catch this problem. Here is one better I think (beware that this time you have to put 5 in 3 locations - there is no need to put () around the number, by the way) ```plaintext SELECT file.path, filesetid, fileid FROM FilesetEntry JOIN file ON (file.id = FilesetEntry.fileid) WHERE Filesetentry.FilesetID=(fileidnumber) AND FilesetEntry.FileID = File.ID AND NOT EXISTS ( SELECT DISTINCT PATH FROM ( SELECT A.Path FROM File A LEFT JOIN Blockset B ON A.BlocksetID = B.ID LEFT JOIN Metadataset C ON A.MetadataID = C.ID LEFT JOIN FilesetEntry D ON A.ID = D.FileID LEFT JOIN Blockset E ON E.ID = C.BlocksetID LEFT JOIN BlocksetEntry G ON B.ID = G.BlocksetID LEFT JOIN BlocksetEntry I ON E.ID = I.BlocksetID WHERE A.BlocksetId >= 0 AND D.FilesetID = (fileidnumber) AND (I."Index" = 0 OR I."Index" IS NULL) AND (G."Index" = 0 OR G."Index" IS NULL) UNION SELECT B.Path FROM FilesetEntry A, File B, Metadataset C, Blockset D, BlocksetEntry E, Block F WHERE A.FileID = B.ID AND B.MetadataID = C.ID AND C.BlocksetID = D.ID AND E.BlocksetID = C.BlocksetID AND E.BlockID = F.ID AND E."Index" = 0 AND (B.BlocksetID = -100 OR B.BlocksetID = -200) AND A.FilesetID = (fileidnumber) ) R WHERE R.Path = File.Path ) ``` ---
**Author:** ![gpatel-fr](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/g/7993a0/32.png) [@gpatel-fr](https://forum.duplicati.com/u/gpatel-fr)\ **Post date:** [November 1, 2023, 2:06pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/25 "2023-11-01T14:06:12Z")
I added a ‘fake’ problem that triggered my query and it shows that when there is a structural problem for a given file in the database, it impacts all filesets including this file. That’s why it’s not really useful to start to delete filesets. A better workaround would be to delete the file(s). ---
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [November 1, 2023, 2:20pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/26 "2023-11-01T14:20:20Z")
Looks a lot like the other-topic situation where the [fileset referenced non-existent files](https://forum.duplicati.com/t/unexpected-difference-in-fileset-version-s/16895/7). Repeating here: > `SELECT * FROM "FilesetEntry" LEFT JOIN "FixedFile" ON "FilesetEntry"."FileID" = "FixedFile"."ID" WHERE "FilesetEntry"."FilesetID" = 5 AND "FixedFile"."ID" IS NULL` If using an original database, refer to `File` view instead of `FixedFile` table in the database bug report. Result table has 65 rows, probably corresponding to 65 reduction of calculated files versus fileset’s list: > [@titilesoleil](#): > > Unexpected difference in fileset version 8: 29.09.2023 13:00:00 (database id: 5), found 317826 entries, but expected 317891 is 65 short from the complex query that looks for data bits compared to the simple expected fileset list. Above query (if I’m doing this right) means that again some records of old files got removed somehow. > [@gpatel-fr](#): > > Could you please run the following query looks like it might be trying to look in the other direction (if fileset had extras). EXISTS is also suspect, although all this comment is tentative. Regardless the query did not return any rows when I had run it. ---
**Author:** ![gpatel-fr](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/g/7993a0/32.png) [@gpatel-fr](https://forum.duplicati.com/u/gpatel-fr)\ **Post date:** [November 1, 2023, 2:24pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/27 "2023-11-01T14:24:26Z")
> [@ts678](#): > > Regardless the query did not return any rows when I had run it. If (like I think) you are thinking to the first version, it was clearly wrong. The next one is better though. ---
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [November 1, 2023, 2:31pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/28 "2023-11-01T14:31:03Z")
> [@gpatel-fr](#): > > The next one is better though. Sorry. I quoted to the wrong one. I’m talking about this one: > [@gpatel-fr](#): > > Here is one better I think (beware that this time you have to put 5 in 3 locations - there is no need to put () around the number, by the way) I adjusted it to `FixedFile` (as I’m looking at the database bug report), replaced placeholder with 5, and played with `NOT EXISTS` versus `EXISTS` (true if a row is returned, so a NOT EXISTS is likely to be false unless all that querying (is this still the union of files and folders/symlinks) is not returning any row at all. EDIT: Ran it again. Original run returns 0 rows. If I change NOT EXISTS to EXISTS, I get 317826 rows which matches the calculated count reported by the user, so wonder if it’s trying to run the concept of NOT IN. ---
**Author:** ![gpatel-fr](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/g/7993a0/32.png) [@gpatel-fr](https://forum.duplicati.com/u/gpatel-fr)\ **Post date:** [November 1, 2023, 2:52pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/29 "2023-11-01T14:52:46Z")
> [@ts678](#): > > If I change NOT EXISTS to EXISTS, I get 317826 rows and what gives `SELECT count(*) FROM "FilesetEntry" LEFT JOIN "FixedFile" ON "FilesetEntry"."FileID" = "FixedFile"."ID" WHERE "FilesetEntry"."FilesetID" = 5` ---
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [November 1, 2023, 3:01pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/30 "2023-11-01T15:01:24Z")
> [@gpatel-fr](#): > > what gives 317891 which user reports as the expected value from the short (compared to huge one) SQL query. EDIT: Run of Duplicati short query also gives 317891 from this about-as-short-as-possible query `SELECT COUNT(*) FROM "FilesetEntry" WHERE "FilesetEntry"."FilesetID" = 5` ---
**Author:** ![gpatel-fr](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/g/7993a0/32.png) [@gpatel-fr](https://forum.duplicati.com/u/gpatel-fr)\ **Post date:** [November 1, 2023, 3:07pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/31 "2023-11-01T15:07:01Z")
This is baffling, there is no filesetentry not referring an existing file then. I don’t understand why the not exists don’t return the missing entries. ---
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [November 1, 2023, 3:15pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/32 "2023-11-01T15:15:34Z")
> [@gpatel-fr](#): > > I don’t understand why the not exists don’t return the missing entries. Probably because EXISTS is true therefore NOT EXISTS is false and so filters out all rows in WHERE. EDIT 1: ```plaintext WHERE Filesetentry.FilesetID = (5) AND FilesetEntry.FileID = FixedFILE.ID AND NOT EXISTS ( ``` EDIT 2: ![image](https://forum.duplicati.com/uploads/default/original/2X/c/cc92c4642fcce83a9a8e4e0fb9bfc3f6fcb09451.png) ![image](https://forum.duplicati.com/uploads/default/original/2X/f/f9e84b01677ded418face7c2d7472e0d306fd79a.png) ![image](https://forum.duplicati.com/uploads/default/original/2X/9/922b7f98a8d93d34b8161530dc17835e3daba0e3.png) Notice the lack of 228778 above. One possibility is some deletion decided to remove it, however these things are supposed to be coordinated. Did something (maybe network error?) block the coordination? ---
**Author:** ![gpatel-fr](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/g/7993a0/32.png) [@gpatel-fr](https://forum.duplicati.com/u/gpatel-fr)\ **Post date:** [November 1, 2023, 3:50pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/33 "2023-11-01T15:50:26Z")
> [@ts678](#): > > 317891 which user reports as the expected value Ah yes, I see now. It’s the left join. These fileset entries are disconnected from any file name. So they can’t be restored anyway. This is impacting several filesets, so it is either a recurring problem, or generated by a misbehaving compact (more likely I think) While I was waiting for the bug report to download, I have made a [Pull-request](https://github.com/duplicati/duplicati/pull/5052) to more accurately report this situation. ---
**Author:** ![titilesoleil](https://forum.duplicati.com/user_avatar/forum.duplicati.com/titilesoleil/32/8592_2.png) [@titilesoleil](https://forum.duplicati.com/u/titilesoleil)\ **Post date:** [November 1, 2023, 4:28pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/34 "2023-11-01T16:28:32Z")
Could this specific point be related to the issue trigger ? The data I was recovering from my ancient USB keys dated a time when I was an Apple user. They had files with non standard characters in the name files. Moreover, the corruped data files, as Grsync could extract them, had very strange characters in the file names. The size of the files were goofy too (how can a 100Gb file come from a 32Gb usb key ?) Maybe when Duplicati tried to analyse these files before I could purge them and add them to the database, it messed something up ? ---
**Author:** ![gpatel-fr](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/g/7993a0/32.png) [@gpatel-fr](https://forum.duplicati.com/u/gpatel-fr)\ **Post date:** [November 1, 2023, 6:10pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/35 "2023-11-01T18:10:08Z")
> [@titilesoleil](#): > > Maybe when Duplicati tried to analyse these files before I could purge them and add them to the database, it messed something up ? I think that it’s more likely than crashes have corrupted the database. I’d say that a possible way forward could be first to do a deep test of the backend in the Command line mode of the Web UI, using ‘all’ as parameter and setting full-block-verification to true. Then if this goes well, exit Duplicati, rename the job database (find its name before closing Duplicati), then try to recreate it using the Duplicati.Commandline.exe utility: Duplicati.CommandLine.exe repair “your connection string here” use export as command line to find the connection string, add --console-log-level and set it to verbose. save the output even if it succeeds. ---
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [November 1, 2023, 8:06pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/36 "2023-11-01T20:06:04Z")
> [@titilesoleil](#): > > Moreover, the corruped data files, as Grsync could extract them, had very strange characters in the file names. Possible OS difference in how non-US-ASCII were represented long ago on Apple, versus current? Non-English system for awhile used lots of [8 bit character code pages](https://en.wikipedia.org/wiki/Code_page). Currently [Unicode](https://en.wikipedia.org/wiki/Unicode) is typical. [Not backing up filenames with ANSI encoding #4753](https://github.com/duplicati/duplicati/issues/4753) was a case like that. It didn’t make a big mess. > [@titilesoleil](#): > > (how can a 100Gb file come from a 32Gb usb key ?) Maybe [sparse file](https://en.wikipedia.org/wiki/Sparse_file) if the USB file system supports them. Regardless, Duplicati should handle its data. ---
**Author:** ![ts678](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/t/8491ac/32.png) [@ts678](https://forum.duplicati.com/u/ts678)\ **Post date:** [November 1, 2023, 10:08pm UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/37 "2023-11-01T22:08:25Z")
> [@titilesoleil](#): > > What I really don’t want to do, is having to delete my remote backup and re-upload it again. It makes me anxious to be without a distant backup. It takes ages, also… I wonder how much of that time is upload speed limited, versus Duplicati processing time for big backup. [blocksize](https://duplicati.readthedocs.io/en/latest/06-advanced-options/#blocksize) is at default 100 KB which is slow past 100 GB. You have over 600 GB at destination, and the proposed deep test will download it all. The recreate might too (normally not, but damage may cause it). Keeping an eye on the Verbose output will give warning (if dblock files get downloaded, it may get long). Regardless, it’s one way to proceed, and might let us save those 65 files, which interestingly are coming up in later backups as missing. This is different than the other database bug report, where losses varied. Here, with broken backup, it looks like the same names. Tested it first by manually trying each fileset ID. Out of 10, the oldest and newest are OK (by this test), and the other 8 have 65 losses, looking the same: > `SELECT "FileID","Lastmodified" FROM "FilesetEntry" LEFT JOIN "FixedFile" ON "FilesetEntry"."FileID" = "FixedFile"."ID" WHERE "FixedFile"."ID" IS NULL` gives 520 rows (65 \* 8). Add DISTINCT after SELECT, and row count drops to 65, so no variation I think. ![image](https://forum.duplicati.com/uploads/default/original/2X/7/72ebf6e7c8f76511b0d9d17149fc05afb26c45c7.png) is interesting because Backup at Operation 30 seemingly finished backup 29 before (shown as partial), probably using a synthetic filelist as mentioned in the other case. RemoteOperation table shows 29 did lots of `put` operations then kind of stopped before uploading its `dlist` on 21 Oct. Backup 30 uploaded: ![image](https://forum.duplicati.com/uploads/default/original/2X/c/c10f5e237a77053193c3b585e36b9c5fb54afb16.png) All logs in the database bug report got erased for privacy, but original log from then may be interesting. ---
**Author:** ![titilesoleil](https://forum.duplicati.com/user_avatar/forum.duplicati.com/titilesoleil/32/8592_2.png) [@titilesoleil](https://forum.duplicati.com/u/titilesoleil)\ **Post date:** [November 2, 2023, 10:16am UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/38 "2023-11-02T10:16:25Z")
I read both your post several times, and from what I understand (which is not everything, sorry), possible solutions from now on will mean downloading everything that is on the backup server. I don’t really have the space though, I would have to buy a new external hard drive just for this. Maybe at this point it would be better for me to connect to the swift server with Cyberduck and delete everything, and start fresh with a new configuration file and database. If I understand correctly, some 65 files on the backup server are goofy and causes the error. What I don’t understand is why I can’t just tell Duplicati “hey, backing up my current files is more important. Discard whatever old versions of the goofy things you have and just backup the current ones !” It’s not like my computer had crashed, I have everything right here on my computer… If this is about 65 files that he does not find anymore on my computer, they are probably one of many corrupted unsalvageable usb key garbage stuff that I voluntarily decided to delete… But, well, if I have to start fresh then I will do so. Except if you have any idea that I can try without having to purchase a new hard drive to download the backup server ? ---
**Author:** ![gpatel-fr](https://forum.duplicati.com/letter_avatar_proxy/v4/letter/g/7993a0/32.png) [@gpatel-fr](https://forum.duplicati.com/u/gpatel-fr)\ **Post date:** [November 2, 2023, 10:57am UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/39 "2023-11-02T10:57:06Z")
> [@titilesoleil](#): > > possible solutions from now on will mean downloading everything that is on the backup server. I’m not sure of what you are talking about here, if it’s the ‘test’ command, it downloads everything yes, but not for storing it, it’s checked one block at a time. The downside of ‘downloading everything’ from the cloud is real, but it’s a matter of bandwidth, especially if you are paying for it. Nonetheless, I was suggesting a deep backend test because you had (from your history) quite a few crashes so the backend state is not known. > [@titilesoleil](#): > > Maybe at this point it would be better for me to connect to the swift server with Cyberduck and delete everything In this case you will have to upload everything again. It may be annoying if your bandwidth is limited, and also if you are paying for bandwidth. > [@titilesoleil](#): > > What I don’t understand is why I can’t just tell Duplicati “hey, backing up my current files is more important. Discard whatever old versions of the goofy things you have and just backup the current ones It may be that it is possible, but just now I don’t know since it’s a problem I have not yet encountered and it could be the case that hidden somewhere in the mass of options there is one that handles it. The only thing that I can be sure of at this moment is that if the backend is all right, rebuilding the database will get rid of this stuff and build a new sane database (minus the history). The state of your database is strange and suggests either a bug or that it has been interrupted in an updating phase. One thing I know too is that it’s possible to get rid of this extraneous data using Sql, but I’m not sure of the outcome. ---
**Author:** ![titilesoleil](https://forum.duplicati.com/user_avatar/forum.duplicati.com/titilesoleil/32/8592_2.png) [@titilesoleil](https://forum.duplicati.com/u/titilesoleil)\ **Post date:** [November 2, 2023, 11:38am UTC](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948/40 "2023-11-02T11:38:02Z")
Thank you again for your answers. Fortunately, my bandwidth is not limited. I pay a fixed amount per month no matter how much I upload or download. I just don’t like deleting the distant files. Since I have backups of the database, I think it might be interesting to attempt a new database re-creation with the live log on verbose to see what happens. I don’t know why the last one didn’t proceed but know I have learn a buch of ways to watch what Duplicati is doing that I didn’t know before [Previous page](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948.md?page=1) [Next page](https://forum.duplicati.com/t/database-recreation-not-really-starting/16948.md?page=3)