Git corrupts SQLite database

Question

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:

1. Open the database in SQLite3 by linux command line
2. Make a mistake in the data
3. Ask Git to restore the released version of the file (this is reaching out to GitHub for the copy)
4. 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:

1. 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
2. Is there something I can do that is easier to restore the file other than get the copy from the live website

• Katie: 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.
• CL.: Is the database file OK in a fresh checkout elsewhere? (In theory, the files should be the same.)
• Katie: @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!

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.

• Rusi: Moral 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 forgotten