Actually, a significantly simpler reproduction is possible.

#!/bin/sh

set -x

db_loc="${HOME}/tmp"
db1_name="db_test1.sqlite3"
db2_name="db_test2.sqlite3"
db1="${db_loc}/${db1_name}"
db2="${db_loc}/${db2_name}"
dbc="sqlite3 ${db1}"
dump="${db_loc}/dump"
mkdir -p "${db_loc}"
rm -f "${db1}"
rm -f "${db2}"
rm -f "${dump}"

$dbc 'CREATE TABLE t_test1 (id INTEGER PRIMARY KEY AUTOINCREMENT, c_ VARCHAR(255));'
$dbc 'DROP TABLE t_test1;'
$dbc .dump > "${dump}"
sqlite3 "${db2}" < "${dump}"

no such table: sqlite_sequence. ... Is this a bug?

IIRC, that was fixed sometime in the past year, whereas your version is close to two years old. Please try with the latest release or the current trunk (which will soon be version 3.47).

I've just built the latest commit of the github repo, as I am not familiar with Fossil.

The version string the sqlite3 binary gives me is 3.47.0 2024-10-12 19:33:47 43787b8ec5348207ae84e6f16acf2605c1ca024fc02b022ce2f36b8495e8alt1 (64-bit).

I do not see a change of behavior.

I do not see a change of behavior.

Indeed. After checking the help for .dump, it looks like you need to add the --nosys flag to it and fix the line which misuses a double-quoted string. The change to your repro script is in these two lines:

$dbc "INSERT INTO t_test1 (c_) VALUES ('foo');" # this is an unrelated problem

$dbc '.dump --nosys' > "${dump}"

i'd have sworn there was a more transparent fix for the sequence bit that but am unable to find any reference to one.

Regarding the "foo" part, please see (all of) section 8 of www:/quirks.html (the last line has info relevant for your case). On my build (from the current trunk) your script, without that change, reports:

Error: in prepare, no such column: "foo" - should this be a string literal in single-quotes?
  INSERT INTO t_test1 (c_) VALUES ("foo");
                     error here ---^

Yes, I've corrected the quoting locally and didn't mention it.

So, what are the criteria to use (or not use) '--nosys'?

It feels odd to me that sqlite attempts to DELETE rows from a table that isn't there. Is this considered correct behavior?

So, what are the criteria to use (or not use) '--nosys'?

Great question! Next question?

(No idea!)

It feels odd to me that sqlite attempts to DELETE rows from a table that isn't there. Is this considered correct behavior?

i know this has come up before and the answer was to use --nosys. Whether it's considered correct for this case... we'll need to wait on feedback from either Dan or Richard.

Should be fixed by check-in 8d7fe903d09a2a79.

You are the first person to notice this problem in the ".dump" command since the problem was first introduced into the code in 2005.

I can confirm that the latest commit let's my test script run through.

Thanks much for the quick fix, much appreciated!