I have a dump of a very long table that consistently produces a malformed database image about 50% of the way through.

Tested on

Ubuntu 20.04 sqlite-autoconf-3390400 (also present n earlier versions like 2.6)

To make loading it work at all I had to give myself a heck of a swapfile(I used zram and then about 16GiB on the SSD, but if you have a machine with 32GIB to test on you can probably get away with less I'm only on 16GIB of RAm), and I turn up the cache_size to because I'm not interested in waiting forever. I have tested cache sizes from 250MiB t 16GiB with varying results.

I also had to turn off the systemd-oomd.service, and set sysctl vm.overcommit_memory=2 just to prevent the system from thinking sqlite is a runaway program and killing it. The first lets the kernel give you memory as long as there is swap, the second turns off the over zealous memory pressure based killer that ships with vanilla Ubuntu.

I thought perhaps it was the sqlite CLI that was the problem so then I wrote a python script to try and divide the sql file from one big query into several smaller ones, but it still dies about the same percent through. Basically I get a malformed exception in the python instead. With smaller transactions you don't need to go nuts with the swapfile, but dumping to SQL was the only way I could get this weird table into a usable form.

A zip with both the SQL and my crumby python file trying to cut it into manageable chunks can be found here

https://drive.google.com/file/d/1RIhJoedCU5i6WjO7hWUpdYeG75SRcWoJ/view?usp=sharing

Bad archive. unzip says:

Archive:  /home/drh/Downloads/badsql.zip
  inflating: rebuild.py              
  inflating: hash.sql                 bad CRC b5fe11b0  (should be 73e7ba87)

Trying to use hash.sql results in:

Parse error near line 7: string or blob too big (18)

The resulting database is well-formed and passes "PRAGMA integrity_check".

Thank's for looking into it Richard.

Ok it still broke, and it looks like one particular section of SQL the file is just ultra cursed and in such a way that it can be carried across filesystems.

So it's probably not a sqlite issue, although it was an export from sqlite that caused it.

To be honest I thought we were past this kind of thing in present year, but I'm going to have to figure out how to recover a really borked file on Linux, and then just skip the problem line.

hash.sql.00.tar.gz:	 OK
hash.sql.01.tar.gz:	 OK
hash.sql.02.tar.gz:	 OK
hash.sql.03.tar.gz:	 OK
hash.sql.04.tar.gz:	 OK
hash.sql.05.tar.gz:	 OK
hash.sql.06.tar.gz:	 OK
hash.sql.07.tar.gz:	 OK
hash.sql.08.tar.gz:	
gzip: hash.sql.08.tar.gz: invalid compressed data--crc error
hash.sql.09.tar.gz:	 OK
hash.sql.10.tar.gz:	 OK
hash.sql.11.tar.gz:	 OK
hash.sql.12.tar.gz:	 OK
hash.sql.13.tar.gz:	 OK
hash.sql.14.tar.gz:	 OK
hash.sql.15.tar.gz:	 OK

I guess that sqlite works in utf-8 and TBH I don't know how gzip tries to do text compression and but it seems like passing the erroneous file through iconv ate the illegal sequences. Whatever the garbage string is, basically anything that was expecting text lost its mind.

iconv -f utf-8 -t utf-8 -c hash.sql.08 >hash.sql.08.b

... in Linux on a NTFS volume ... curse Microsoft for existing ...

XD - Joining you in that cursing, only every day!

That said, not sure they deserve this specific verdict - who made NTFS for Linux? Surely not Microsoft?