After databases/sqlite3 was updated in binary packages to 3.41 and DQS was defaulted to off (Bug #269798), the net/vnstat binary package no longer works with an existing database: > # vnstat > Database updated: 2023-03-04 19:15:00 > > em2 since 2022-04-02 > > rx: 39.51 GB tx: 37.82 GB total: 77.33 GB > > monthly > rx | tx | total | avg. rate > ------------------------+-------------+-------------+--------------- > Error: Failed to get interface id from database (1): no such column: em2 > Error: Failed to fetch month data. I've looked around, but not knowing sqlite3 terribly well (at least from a C standpoint), I do not see how I can fix vnstat to re-enable DQS itself. vnstat does not make any calls to sqlite3_db_config(), and I don't know enough about the rest of the sqlite3 API to find another function to pass the SQLITE_DBCONFIG_DQS_DDL & SQLITE_DBCONFIG_DQS_DML flags to (see Bug #269889, comment 6). I've looked at manually building databases/sqlite3 from ports (per UPDATING), but despite deselecting TCL support, it keeps trying to drag TCL in as a build dependency. As far as I know, if TCL is disabled, it shouldn't be needed as a dependency. I feel this is a possible bug in the port's Makefile. I know there's a TCL "flavor", but if I am building the default flavor, TCL should be disabled, right?
Created attachment 240587 [details] Attempt to fix vnstat to enable DQS Okay, I gave a shot at adding a call to sqlite3_db_config to re-enable DQS. The code compiles and vnstat starts up, but it did not address the issue, so I assume it's going to be a bit more complicated of a fix. I am attaching the patch so others can see if there's something I missed out on. For now, I am going to try and force a downgrade to sqlite3 and lock it at 3.40 until a fix is found.
(In reply to Joshua Kinard from comment #0) > I've looked at manually building databases/sqlite3 from ports (per UPDATING), > but despite deselecting TCL support, it keeps trying to drag TCL in as a build > dependency. As far as I know, if TCL is disabled, it shouldn't be needed as a > dependency. I feel this is a possible bug in the port's Makefile. I know > there's a TCL "flavor", but if I am building the default flavor, TCL should be > disabled, right? And that's a nope. Not easy to find, but apparently, sqlite3's build system requires TCL for some "amalgamation" purpose. So ignore this bit.
Trying to use a ports-built copy of databases/sqlite3 with a ports-built copy of net/vnstat has an issue....somewhere. Regardless of the options I've selected in databases/sqlite3, after building it as a package and then installing it, the ports-built copy of net/vnstat refuses to use it. Instead, pkg will attempt to pull in sqlite3-tcl from official repos when I ask it to install the local copy I built. It looks like a known bug(s) with pkg's solver: https://github.com/freebsd/pkg/issues/2054 https://github.com/freebsd/pkg/issues/2075 I think the way I am triggering this is because I am using ports to build local packages, and although I do not have any additional repositories defined besides the official/default one, using pkg to install a *.pkg file from local path counts as another repo, so I'm triggering those above issues. Can someone maybe look at re-enabling DQS in sqlite until pkg's solver bug(s) are fully worked out? For now, I am going to try and find a way to downgrade sqlite back to 3.40 and then lock it in place so I can get a working vnstat program back on all of my systems.
(In reply to Joshua Kinard from comment #3) Hi. After a pkg update I have the same issues with vnstat. Do I understand correctly that it would be best if vnstat would change this in their code? For now though I need vnstat to work so did you find out how to downgrade sqlite3 with pkg (instead of ports)?
(In reply to ~Luna from comment #4) > Hi. After a pkg update I have the same issues with vnstat. Do I understand > correctly that it would be best if vnstat would change this in their code? > > For now though I need vnstat to work so did you find out how to downgrade > sqlite3 with pkg (instead of ports)? I kinda found a way to downgrade sqlite3 via pkg. There's a site (which I don't have the URL on hand atm) that gave the mirror link to the older 3.40.1 copy. Ideally, you would install that, then lock it from being upgraded. But the fix I finally went with was to build sqlite3 from ports via 'make package', making sure to enable DQS, then, and this is the critical part, I used 'pkg add' instead of 'pkg install' to install the locally-built port, which dodges around the possible bug in pkg's solver component that gets confused. Then I locked both vnstat and sqlite3 so that pkg won't try to automatically upgrade them. It requires some manual work to watch for updates in ports and rebuild as appropriate, but I have such a small number of locally-built ports for my FreeBSD systems and jails, that it's fairly manageable. I looked through some of vnstat's code and I don't really see where they're relying on the DQS feature. Most of their core database code is in src/dbsql.c, and I am wondering if it's an issue of whether the database was initialized while DQS was enabled. I have a Gentoo Linux machine that also runs vnstat, but the ebuild there does not (and hasn't ever, I think) enabled DQS, so my vnstat database there had no issues when sqlite was updated to 3.41. Thus it may be a matter of re-initializing the vnstat.db database with DQS disabled to get a proper fix. Then one would have to dump the old DB and import it into the new DB. Totally untested, but it's a thought I had.
Created attachment 240646 [details] enable DQS at run time Reading the docs at https://sqlite.org/quirks.html#dblquote the third argument should be 1 when the DQS quirks are disabled by default. I've attached a refinement of Joshua Kinard's patch that enables both SQLITE_DBCONFIG_DQS_DDL and SQLITE_DBCONFIG_DQS_DML. This appears to work around the problem till it is reported and fixed upstream.
Assign to maintainer
Created attachment 240750 [details] Apply upstream fix This applies the upstream fix at https://github.com/vergoh/vnstat/commit/2e66151541ffafd7de8778c14a66262a901716dc
(In reply to Thomas Hurst from comment #8) Hi, I'm still getting this error; Error: Failed to get interface id from database (1): no such column: vtnet0 Error: Failed to fetch month data. after patching, compiling then reinstalling This is on main-n261613-2b4b3789f877 amd64 and fresh ports tree Sun Mar 19 23:48:25 GMT
(In reply to void from comment #9) Sounds like it wasn't patched properly. Can you confirm your net/vnstat/files directory contains: patch-src_dbsql.c patch-src_vnstat__func.c patch-src_vnstati.c pkg-message.in patch-src_vnstat.c patch-src_vnstatd.c patch-tests_dbsql__tests.c vnstat.in An easy mistake would be to apply the patch from net/vnstat instead of the root of the ports tree — that would dump the patch-* files in net/vnstat instead, and you'd literally just get the Makefile PORTREVISION bump and nothing else.
(In reply to Thomas Hurst from comment #10) Hi, thanks for the quick reply. Yeah, I applied it in the wrong place. Using -p0 in /usr/ports fixed it. Sorry for the noise
vnstat has been updated to 2.11 in ports 1a9007ddc6e5b1cf2fcc7f97ade6be89182e8b56. This version contains the upstream fix (https://github.com/vergoh/vnstat/commit/2e66151541ffafd7de8778c14a66262a901716dc).