build error: "sqlite error while executing INSERT"

[gondox]

I keep on trying to build an image for my raspberry pi cm4 and this time I ran into an sqlite error while running "make arm DEVICE=RPI" both on a x86 machine as well as on a raspberry pi:

Code Select Expand


Nothing to do.
Installing pkg-1.17.5_1...
the most recent version of pkg-1.17.5_1 is already installed
Installing iperf3-3.11...
Extracting iperf3-3.11: 100%
Installing ruby-2.7.6_2,1...
`-- Installing libyaml-0.2.5...
`-- Extracting libyaml-0.2.5: 100%
`-- Installing libunwind-20211201_1...
`-- Extracting libunwind-20211201_1: 100%
`-- Installing libedit-3.1.20210910,1...
`-- Extracting libedit-3.1.20210910,1: 100%
Extracting ruby-2.7.6_2,1: 100%
=====
Message from ruby-2.7.6_2,1:

--
Some of the standard commands are provided as separate ports for ease
of upgrading:

        devel/ruby-gems:                gem - RubyGems package manager
        devel/rubygem-irb:              irb - Interactive Ruby
        devel/rubygem-rake:             rake - Ruby Make
        devel/rubygem-rdoc:             rdoc - Ruby Documentation System
        sysutils/rubygem-bundler:       bundler - Tool that manages gem dependencies for ruby applications

And some of the standard libraries are provided as separate ports
since they require extra dependencies:

        databases/rubygem-dbm:  DBM module
        databases/rubygem-gdbm: GDBM module

Install them as occasion demands.
--
===>   NOTICE:

This port is deprecated; you may wish to reconsider installing it:

Please migrate to newer versions. Ruby 2.7 will reach its EoL on April 12, 2023.

It is scheduled to be removed on or after 2023-04-12.
pkg: warning: database version 36 is newer than libpkg(3) version 35, but still compatible
pkg: sqlite error while executing INSERT OR IGNORE INTO mtree(content) VALUES(?1) in file pkgdb.c:115: no such table: mtree
Updating FreeBSD repository catalogue...
Fetching meta.conf: 100%    163 B   0.2kB/s    00:01   
Fetching packagesite.pkg: 100%    6 MiB   3.2MB/s    00:02   
Processing entries: 100%
FreeBSD repository update completed. 30551 packages processed.
All repositories are up to date.
pkg: warning: database version 36 is newer than libpkg(3) version 35, but still compatible
pkg: sqlite error while executing INSERT OR IGNORE INTO mtree(content) VALUES(?1) in file pkgdb.c:115: no such table: mtree
*** Error code 1

Stop.
make[1]: stopped in /usr/plugins/benchmarks/iperf
*** Error code 1

Stop.
make: stopped in /usr/tools
root@generic:/usr/tools #


Any idea how this can happen or be fixed?

EDIT: I tried to find all sqlite db files in / and tried the .selftest command on all of them as suggested here: https://forum.opnsense.org/index.php?topic=15868.msg72658#msg72658 but it found no error in any of them.

[franco]

Looks like breakage in pkg 1.18 vs 1.17? I'm NOT going to update pkg again just because they keep adding breaking changes...

[gondox]

I think the make script uses pkg 1.17.5 at least it says so in line 2:

Code Select Expand


...
Installing pkg-1.17.5_1...
...

I couldn't find a reference to a version 1.18.
Can I somehow switch to an older version that did not have these problems?

[franco]

Check pkg version on the host..


Cheers,
Franco

[gondox]

Ah Ok,

now I can see what you mean:

Code Select Expand


freebsd@generic:~ % uname -a
FreeBSD generic 13.0-RELEASE FreeBSD 13.0-RELEASE #0 releng/13.0-n244733-ea31abc261f: Fri Apr  9 06:06:55 UTC 2021     root@releng1.nyi.freebsd.org:/usr/obj/usr/src/arm64.aarch64/sys/GENERIC  arm64
freebsd@generic:~ % pkg --version
1.18.3


If this causes the problem, shouldn't everyone get this sqlite error message from now on?

[astronaut]

I am in the same place, same error on an up-to-date FreeBSD 13.1 system. Any suggestions how to switch to pkg-1.17.5_1 on my build system? I couldn't find a pkg file for manual install. (I am new to FreeBSD, so please be lenient... :-)

EDIT: Or any other suggestion how to succesfully build OPNsense, of course. I might be on the wrong track...

[gondox]

@astronaut: I had used 13.1 in my first attempts as well and ran into pkg issues, then i switched to 13.0 and this "worked" for some time (although my builds failed for other reasons) and by now I seem to have pkg issues with 13.0 as well.

I assume that this needs to be fixed for OPNsense 22.7, because this is based on FreeBSD 13.1 and would then have the new pkg version automatically?

So maybe building OPNsense 22.7 on FreeBSD 13.1 might work?

[franco]

Guys, this is becoming a bit ridiculous. I get that you are enthusiastic about OPNsense building but I'm asking you to read older tickets and issues and learn what the hell is going on in your build systems.

The first rule of build club is: don't mess up your build system.

The second rule of build club is: if your build system is messed up fix it.

FreeBSD package manager continues to break interoperability between versions. Their idea is that the new version can do the same crap the old version did, but the old versions have very little "forward" thinking. New features are added that don't benefit the functionality of pkg as a package manager tool itself. We could probably continue to use a version from 2-3 years ago without issue even today.

So if you want to avoid pkg messing up your build just make sure that you build pkg from OUR ports tree for YOUR build system. On a basic build system that means:

# cd /usr/ports/ports-mgmt/pkg
# make package
# make deinstall
# make install

Done...


Cheers,
Franco

[astronaut]

@franco: That helped a lot, thank you!

For reference: After building as franco describes,

Code Select Expand

# make reinstall failed. However, I manually installed the pkg file:

Code Select Expand

# cd work/pkg
# pkg install --force pkg-1.17.5_1.pkg

Then (in the correct directory, of course)

Code Select Expand

# make DVD completed flawlessly.

Disclaimer: Being a bloody beginner, I am not sure if this is the right way, so be aware.  ;)

[franco]

Ah, thanks, I changed the reinstall to deinstall/install... that one should work. :)

I will look into making the build tools complain on mismatching pkg version. Previous quick attempts didn't work for various reasons.


Cheers,
Franco

[gondox]

funnily enough, I now get the same error, when I try to install pkg version 1.17.5 from the opnsense ports  ;D

Code Select Expand


root@generic:/usr/ports/ports-mgmt/pkg # make package
...
====> Compressing man pages (compress-man)
===>   Installing ldconfig configuration file
===>  Building package for pkg-1.17.5_1
root@generic:/usr/ports/ports-mgmt/pkg # make deinstall
===>  Deinstalling for pkg
pkg-static: warning: database version 36 is newer than libpkg(3) version 35, but still compatible
pkg-static: sqlite error while executing INSERT OR IGNORE INTO mtree(content) VALUES(?1) in file pkgdb.c:115: no such table: mtree
===>   pkg not installed, skipping
root@generic:/usr/ports/ports-mgmt/pkg # make install
===>  Installing for pkg-1.17.5_1
===>  Checking if pkg is already installed
pkg-static: warning: database version 36 is newer than libpkg(3) version 35, but still compatible
pkg-static: sqlite error while executing INSERT OR IGNORE INTO mtree(content) VALUES(?1) in file pkgdb.c:115: no such table: mtree
*** Error code 1

Stop.
make[2]: stopped in /usr/ports/ports-mgmt/pkg
*** Error code 1

Stop.
make[1]: stopped in /usr/ports/ports-mgmt/pkg
*** Error code 1

Stop.
make: stopped in /usr/ports/ports-mgmt/pkg
root@generic:/usr/ports/ports-mgmt/pkg #


Are there any other packages I might want to build from the OPNsense ports manually instead of using the ones that come with Freebsd 13.0?

[franco]

No, just pkg doing this weird thing.

And, yep, it doesn't like going back as I said...

If you delete all packages and the database /var/db/pkg it should be back to normal after install.


Cheers,
Franco

[gondox]

I opted to set it all up anew, just to make sure.

While doing "make update" to get the ports directory in /usr/ports/ (so that I can reinstall pkg), I found out, that 22.7 is now the current stable branch?

Code Select Expand


>>> Fetching /usr/core:
>>> Updating branch stable/22.7 of /usr/core:
branch 'stable/22.7' set up to track 'origin/stable/22.7'.
Switched to a new branch 'stable/22.7'
Already up to date.


Does this mean I should switch to Freebsd 13.1 as host system?

[franco]

I've successfully built 22.7 on 13.0 as well with OS=13.0 setting, but I recommend

a) upgrading to FreeBSD 13.1 to build 22.7, or
b) checking out 22.1.10 tags to build 22.1 on there for the time being.


Cheers,
Franco

[gondox]

I set it all up from scratch, this time with freebsd 13.1
and the first thing I did was to install git, because I need it to download the tools:
"pkg install git" (just as in the README)
And I guess this messes up the sqlite database, because although I do the steps described by astronaut as well as franco (I tested both)

I will get something like this:

Code Select Expand


root@generic:/usr/ports/ports-mgmt/pkg # make package
...
===>  Building package for pkg-1.17.5_1
root@generic:/usr/ports/ports-mgmt/pkg # cd work/pkg
root@generic:/usr/ports/ports-mgmt/pkg/work/pkg # pkg install --force pkg-1.17.5_1.pkg
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
Updating database digests format: 100%
Checking integrity... done (0 conflicting)
The following 1 package(s) will be affected (of 0 checked):

Installed packages to be DOWNGRADED:
pkg: 1.18.3 -> 1.17.5_1

Number of packages to be downgraded: 1

The operation will free 2 MiB.

Proceed with this action? [y/N]: y
[1/1] Downgrading pkg from 1.18.3 to 1.17.5_1...
Extracting pkg-1.17.5_1: 100%
root@generic:/usr/ports/ports-mgmt/pkg/work/pkg #
root@generic:/usr/tools # make arm DEVICE=RPI
...
WITHOUT_MODULES=cloudabi32
>>> Running build step: base
>>> Passing arguments: (none)
pkg: warning: database version 36 is newer than libpkg(3) version 35, but still compatible
pkg: sqlite error while executing INSERT OR IGNORE INTO mtree(content) VALUES(?1) in file pkgdb.c:115: no such table: mtree
>>> Required package 'u-boot-rpi4' is not installed.
*** Error code 1

Stop.
make: stopped in /usr/tools
root@generic:/usr/tools #


So I am in a bit of a catch 22: I need the tools to install pkg 17.5, and to do that I need git, but as soon as I install git: I mess up the sqlite db