This is mostly an explainer for a specific error some databases encounter, 'database image is malformed'. The cloning and repair steps only relate to this specific problem, but as this problem is caused by hard drive damage, users who have other hard drive problems are pointed here as background reading.
Please take a breath and read carefully. Search online if there is anything you don't understand, and plan out what you are going to do. Whatever the problem, do not try to rush it while you are stressed out, since that often causes mistakes.
Corrupted databases happen because of hardware failure, usually the hard drive writing bad sectors due to old age or a power cut at a bad time, but sometimes it can be due to a bad or unseated cable, or even overheating.
Malformed errors are a very serious problem. Software cannot cause it. If you do not know this was a once-off event like a powercut, you should assume your hard drive or some other component of your machine is damaged.
For a small database, this whole repair process may take an hour. For a larger, expect it to take much longer. You may want to do it in several sessions. I am always contactable if you need help.
If you are on Windows, open task manager (Ctrl+Shift+Esc) and wait for hydrus_client.exe to disappear from the list. If it takes more than a couple of minutes, forcibly close it with task manager.
If it finds many problems, then your drive has been compromised in some way and you should view it as unreliable. If it is an old drive, you should think about hurrying its replacement. The exception to 'buy a new drive' is if the existing one is new, otherwise works well, and you can trace the error to a specific event, such as an unprotected power surge during a storm that violently reset your computer. The other exception is if you cannot afford it. :/
Another good tool is CrystalDiskInfo, which checks hard drive health at the physical level. If your drive is having trouble reading or seeking or it is piling up uncorrectable sectors, it is time to abandon ship!
If you have a recent backup of your client, it is probably a good idea just to restore from that. Many hard drive errors cannot be recovered from, so if your backup is good, this may be the only way to recover the lost information.
Note that some 'malformed' errors are in deeper parts of the database that are only accessed rarely. If the problem is small and occured months ago, it is entirely possible your backups also have it. It is worth doing an integrity_check as below on your backups, just in case.
First of all, make a _new_ backup of the corrupted db, just in case something goes wrong with the recovery and we need to try again. You can just copy the client*.db files.
To see if a file is not malformed, you need to run an integrity check. To do this, go to the db directory and look for the 'sqlite3' executable. Run it, and it'll bring up a terminal. Type or copy/paste this:
It can be slow. A few MB a second is typical on an HDD (SSDs obviously faster), so expect a 10GB file to take a while. A 60GB mappings.db may take two hours. If it takes way way too long, and your Task Manager suggests only 50KB/s read, consider again if your hard drive is healthy or not.
Please note that newer versions of SQLite support a second check command:
PRAGMA quick_check;
You can swap this in place of any 'integrity_check'. It does most of the work of the full check and runs significantly faster, so you may like to run it first just to see if your databases have any critical damage. If your SQLite supports this command, it will say 'ok' (or error information) after a delay; if it does not support it, it will immediately return with a new prompt line, no response.
The integrity check doesn't correct anything, but it lets you know the magnitude of the problem: if only a couple of issues are found, you may be in luck. There are several .db files in the database, and client.db is likely not the one broken. client.mappings.db is usually the largest and busiest file in most people's databases, so it is a common victim. If you do not know which file is already broken, try opening the other files in new shells to figure out the extent of the damage. This is the same as with client.db, like so:
(this one can take ages simply because of the size of the file, so only do it if you really need to check; if you know it is broke, just move on to cloning now, no need to waste time confirming it)
The four files are separate, so if one is broken but the other three are fine, you only need to work on the one that is broken. There is no point cloning or recovering a database that passes an integrity check. This is sometimes confusing, and it can be a pain to type everything out exact, so I will copy all recovery routines for all filenames, so you can copy/paste quickly.
There are three known repair operations. Try one and see if you can pass an integrity_check on the broken file. Clone works well for almost all simple problems, but the others have better success in different situations.
This instructs the database to copy itself to a new file. When it comes across damaged data, it will attempt to cleanly correct or nullify the broken links. The new file should be clean, but missing some data. It typically loses anywhere from 15-75%, which you can see by filesize. Losing 1-5% is normal because a clone includes an implicit vacuum.
And wait a bit. It'll report its progress as it tries to copy your db. It will be slow. Remember to go '.exit' once it is done to close the shell neatly.
If the clone says some errors involving 'fts' or 'fts4', like 'subtags_fts4_content already exists', but it keeps on working, don't worry about it! That probably isn't a real error, and even if it is, 'fts' stuff will be automatically fixed on the next boot. Same if you get any errors about 'sqlite_stat1' table.
Once it is done, the cloned file may be significantly smaller than the original. 50% reduction in size is typical. This means some data has been lost. If this is in client.caches.db, client.master.db or client.mappings.db, it is probably fine (particularly if you sync to a tag repository like the PTR), as we can regenerate that data with some work once you are working again.
Do not delete your original files for now. Just rename them to 'old' and move them somewhere safe. Also be careful not to lose track of which is which (filesize and creation date can help here)! Make sure your new cloned files are all named right and then try running the client!
You do not have to do an integrity_check after you clone. If you know your current hard drive is good, you can assume a clone works to create a 'clean' database.
This command tries to fix a database file in place. I do not recommend it as it works very very slowly. While it may be able to recover some rows a clones would lose, it cannot fix everything and may leave you with a database that is still malformed, so you'll want to run integrity_check again and do a clone if needed.
If the recovery works, do the integrity_check. If that is also good, you are good to try to boot.
** manual export and import **
This dumps the db to a (potentially huge) sql file and then reads it back into a new file. This seems to use slightly different access routines to clone and recover, but is extremely slow (think 25-100KB/s), so only try it in desperation. It comes thanks to a user, whose notes I have edited.
The example is for client.mappings.db, but obviously swap in a different name if a different file is broken for you.
1. Create an SQL dump file:
Run sqlite3, then enter this:
.open client.mappings.db
.mode insert
.output client.mappings.db.sql
.dump
.exit
2. Edit the SQL dump file:
In the .sql file:
Remove line 2 (it should say "BEGIN TRANSACTION;")
Remove last line of text (it should say "ROLLBACK; -- due to errors")
Save and close
Note: I recommend using commands like grep or sed to remove these on large files
That new file should be good, so swap it in like you would a clone, and try booting.
*** help, nothing worked! ***
If none of the operations can make a functional database, you may simply be fugged. I am sorry. Feel free to contact me and we'll see what the best way forward is.
Also, if you recover ok but still get problems, please contact me. You'll likely see a bad tag count here or there.
Check install_dir/help/contact.html for ways to get me.
Since client.mappings.db is the file most often hit by disk problems, and since recovery operations often lose some original data, recovering lost tags is often the next job after cleanup. A clone will also compact your database, so just losing a few percent probably isn't a big deal. If your file is now half as big after a clone, you probably lost 40% of your tags. If it is 97% of the original size, you probably lost nothing.
In client.caches.db, there is a cache of your tags for the files currently in the client. We can use this cache to repopulate your mappings tables with _database->check and repair->repopulate truncated mappings tables_. It won't always get everything, but if client.caches.db was undamaged, it should work for most of your files.
If you sync with the PTR or a private repository, tell your client to reprocess them. Turn on _help->advanced mode_, then go to _services->review services_ and under 'reset processing', tell it to fill in definition and content gaps. It'll take time, but it will fill in the holes again.
When your recovery is done, your tag counts or siblings may be incorrect. If so you probably want to run one or more of the tasks under _database->regenerate_. Be careful though, as these operations can take a very very long time to finish.
If client.caches.db was hit, and after cloning it seems to be significantly smaller, this is the best scenario, since everything in there is just a mirror of some other data and can be reprocessed. Run the main jobs under _database->regenerate_ in turn. Read the pre-run descriptions to make sure you aren't doing the same thing twice, since some of the mapping cache regen jobs there are big and do the work of other jobs too. That'll likely be enough for almost all cases, but if you still get screwy behaviour with the duplicate system or something, let me know and I'll see what I can do.
An last-ditch alternate answer, if your client.caches.db is really truncated/broken, is just to delete the file and boot without it. The client's repair code is now good enough to try to regenerate the entire file on boot. It may give you more post-boot regen instructions.
If you are absolutely certain you restored your database files to the point where they passed "PRAGMA integrity_check;", and within a few days or weeks you get 'malformed' again out of the blue, you very likely have an ongoing problem. First off, pause all repository processing, or whatever you were doing that raised the error. Then do your CrystalDiskInfo job again, and absolutely make sure all your cables are plugged in solid and you have no power or cooling problems. You can try doing another clone/repair, but if your computer is quickly turning clean database files into corrupt ones, there is likely a hardware fault, whether that is a problem in the drive, cable, motherboard, power supply, drivers, cool air supply, or it could be something esoteric in software like a duff driver, or a cloud backup system or anti-virus forcing access into the database files while they are in use.
If you are in this situation, I am happy to help you figure things out, but you know your system's hardware better than I do. As far as I know, I cannot create a 'malformed' error with my database access routines even if wanted to--it can only come from an external fault.
Some users encountered 'malformed' or 'disk I/O error' problems alongside Windows OS errors about 'Delayed Write Failed' on the hydrus directory, usually after sleeping and then waking the computer, but also after the database has finished a lot of work.
This seems to be something to do with Write Caching failing for certain very large WAL journal files, and particularly affects some NVMe drive(r)s. I have attempted to relieve the large WAL files, but 'disk I/O error' is still happening for some users. The two things to try seem to be:
1) Launch the program with "--db_journal_mode TRUNCATE" argument, turning off WAL journaling. Check the 'launch arguments' article in the help for more info. This generally fixes the problem completely.
2) Disable Write Caching on the drive. This sucks, since write caching is normally really useful, but it seems to help/fix it completely, so it may be ok for a hydrus-exclusive drive.
I say this to everyone who goes through this: losing data sucks, I know. I lost a drive when I was younger and tens of thousands of cool files with it. It feels shit, but there is an excellent lesson to learn: almost all the pain would have been avoided if you had a backup. This pain will be your motivation to correct the situation.
If you do not have a backup routine, this is the time to sort it out. If you cannot afford a USB drive, find a friend or family member who can sell you a USB stick for a few bucks. At worst, simply make a copy on the same drive. At the very least, you just need the spare space for the client*.db files. If you have enough money for an ongoing computer budget, and enough IQ to figure out and stick to a schedule, just fold it into your calendar. Rotating in a new WD Passport every few years is not an outrageous expense, and you can protect every document, childhood photo, ancient-and-now-impossible-to-find 32-bit utility, and your hydrus db, and you can sling it in your backpack and no longer have to worry about losing it all in a fire. If everything goes black one day, you still get a heart attack, but the maximum damage is money and stress and a week of work lost, rather than losing everything you have ever done. A backup is an insurance policy.
