I use SQLite for websites I create and manage for my clients (I am a web developer). They are very small and straightforward databases.
This is now happened twice, so it is pretty clear that it wasn't a fluke. This last one was a clear set of steps that then corrupted the database:
- Open the database in SQLite3 by linux command line
- Make a mistake in the data
- Ask Git to restore the released version of the file (this is reaching out to GitHub for the copy)
- Open the restored version, and file is corrupt
I then struggle to get it back in order, nothing I do in git fixes it. I can luckily grab it from the live website (no updates there) and all is well.
My two questions:
- Is there something I can do differently while getting the file from the git repository to revert, it is not clear to me why git would corrupt the file
- Is there something I can do that is easier to restore the file other than get the copy from the live website
-
I opened it on the Bash command line and you know, it is possible that I hadn't closed it via .exit, but I'm not sure. I haven't done anything with WAL mode, so it is the default configuration. Did a quick test and I cannot ask git to revert unless it is closed, so I must have exited, plus was probably waiting for git to detect a change had occurred with the file.Katie– Katie2020年04月08日 15:49:54 +00:00Commented Apr 8, 2020 at 15:49
-
Is the database file OK in a fresh checkout elsewhere? (In theory, the files should be the same.)CL.– CL.2020年04月09日 06:42:23 +00:00Commented Apr 9, 2020 at 6:42
-
@CL. Thank you so much for the suggesting of cloning elsewhere, this revealed that it was actually corrupt in GitHub, which really threw me. But since I then knew it was git that was corrupting it, I was able to find the problem which was in my .gitattributes. Will post answer for posterity. Much appreciate your help!Katie– Katie2020年04月10日 13:39:35 +00:00Commented Apr 10, 2020 at 13:39
1 Answer 1
This was what I finally figured out, in case someone else comes along and hits the same problem. It was very confusing because the corruption was coming from git, so when I did something like rollback, it got corrupted then, not with my own activities.
I am running PHPStorm on Windows, with a shared directory on a virtual linux machine. I need line endings to be LF, but since PHPStorm is running in Windows, the CRLF and LF battles ensue.
I had long ago fixed it with .gitattributes. But when I switched from MySQL to SQLite, I had not added .db as a file not to be messed with when it came to line endings.
Git was replacing a CRLF with an LF in the .db binary file
In .gitattributes, you need:
# Set the default behavior to always be linefeed for linux
* text=auto
# Definitively text files
*.txt text eol=lf
*.php text eol=lf
*.html text eol=lf
...
# Ensure those won't be changed
*.jpg -text
*.png -text
*.db -text
I added the last line and all was well, git is no longer trying to be helpful with the binary file and corrupting it with LFs.
One last thing I learned, you can set the gitattributes file, then go to your root directory and run the command:
git add --renormalize .
git status
You will then see any file that will be converted from CRLF to LF, a good way to fix an entire directory as well as making sure it is not doing to the SQLite database.
-
1Moral of the story is
* text=auto
is treacherous! Overall its better for you to decide which files are text and which binary. Using* text=auto
seems easy when you write it but comes back to bite you typically at a much later point when youve forgottenRusi– Rusi2021年08月03日 12:48:05 +00:00Commented Aug 3, 2021 at 12:48