# pkg autoremove gave me a sqlite error



## BobSlacker (Tuesday at 12:50 PM)

Here is the screenshot:





Do you guys have any idea about whats is causing this behavior?


----------



## Erichans (Tuesday at 1:40 PM)

No idea, but it seems others have experienced similar behaviour after `pkg autoremove`: pkg fails to report 'Disk full' condition: 'sudo pkg autoremove' fails with 'pkg: sqlite error while executing VACUUM; in file pkgdb.c:2333: cannot VACUUM - SQL statements in progress' #2098


----------



## BobSlacker (Tuesday at 1:44 PM)

Erichans said:


> No idea, but it seems others have experienced similar behaviour after `pkg autoremove`: pkg fails to report 'Disk full' condition: 'sudo pkg autoremove' fails with 'pkg: sqlite error while executing VACUUM; in file pkgdb.c:2333: cannot VACUUM - SQL statements in progress' #2098


I read that, but I don't have a disk full condition (almost 3T of free disk space). And I don't have a github account to respond there.

Edit.: More info bellow:


----------



## covacat (Tuesday at 3:38 PM)

try to backup / dump / recreate the database


----------



## SirDice (Tuesday at 3:45 PM)

Did you perhaps still have a `pkg install` running in another terminal? That VACUUM is a special query that tries to clean up the database, but it can't do that if there's still a query running. Or maybe a pkg-install(8)/pkg-delete(8) that got broken off? Although the command may have terminated, the SQL query might still be running on the database. Or maybe some query is stuck in a never ending loop. Or it's transaction was never finished. 



> A VACUUM will fail if there is an open transaction on the database connection that is attempting to run the VACUUM. Unfinalized SQL statements typically hold a read transaction open, so the VACUUM might fail if there are unfinalized SQL statements on the same connection. VACUUM (but not VACUUM INTO) is a write operation and so if another database connection is holding a lock that prevents writes, then the VACUUM will fail.





			VACUUM


----------



## alexseitsinger (Tuesday at 3:59 PM)

BobSlacker said:


> Here is the screenshot:
> View attachment 15357
> 
> Do you guys have any idea about whats is causing this behavior?


Does this error occur when you use something besides (t)csh? (/bin/sh).


----------



## BobSlacker (Tuesday at 4:32 PM)

covacat said:


> try to backup / dump / recreate the database


I don't know how to do that, Can you give me some documentation about it, please?



SirDice said:


> Did you perhaps still have a `pkg install` running in another terminal? That VACUUM is a special query that tries to clean up the database, but it can't do that if there's still a query running. Or maybe a pkg-install(8)/pkg-delete(8) that got broken off? Although the command may have terminated, the SQL query might still be running on the database. Or maybe some query is stuck in a never ending loop. Or it's transaction was never finished.
> 
> 
> 
> ...


I don't think this is it. The error is persistent after reboot.



alexseitsinger said:


> Does this error occur when you use something besides (t)csh? (/bin/sh).


I use zsh, I just tried with tcsh and got the same error:


```
{beastie} @ FreeBaSeD-T430 > /home/beastie
 → tcsh                                                                                                                                                                                                      0 17:35:00 23-01-10
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg delete freecad
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 1 packages (of 0 packages in the universe):

Installed packages to be REMOVED:
    FreeCAD: 0.20.2_1

Number of packages to be removed: 1

The operation will free 220 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/1] Deinstalling FreeCAD-0.20.2_1...
[1/1] Deleting files for FreeCAD-0.20.2_1: 100%
Building the Shared MIME-Info database cache
Building cache database of MIME types
Generating GTK icon cache for /usr/local/share/icons/hicolor
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg autoremove
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 6 packages:

Installed packages to be REMOVED:
    fr-med: 4.0.0_2
    libarea: 20191031_1
    opencascade: 7.7.0
    py39-matplotlib: 3.4.3_5
    py39-pivy: 0.6.6
    py39-pyside2: 5.15.2_2

Number of packages to be removed: 6

The operation will free 372 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/6] Deinstalling libarea-20191031_1...
[1/6] Deleting files for libarea-20191031_1: 100%
[2/6] Deinstalling py39-matplotlib-3.4.3_5...
[2/6] Deleting files for py39-matplotlib-3.4.3_5: 100%
[3/6] Deinstalling py39-pyside2-5.15.2_2...
[3/6] Deleting files for py39-pyside2-5.15.2_2: 100%
[4/6] Deinstalling opencascade-7.7.0...
[4/6] Deleting files for opencascade-7.7.0: 100%
[5/6] Deinstalling fr-med-4.0.0_2...
[5/6] Deleting files for fr-med-4.0.0_2: 100%
[6/6] Deinstalling py39-pivy-0.6.6...
[6/6] Deleting files for py39-pivy-0.6.6: 100%
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg autoremove
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 28 packages:

Installed packages to be REMOVED:
    SoQt: 1.6.0_1,1
    draco-3d-compression: 1.5.5
    freeimage: 3.18.0_4
    ftgl: 2.4.0_1,1
    hdf5-110: 1.10.9
    py39-boost-libs: 1.80.0
    py39-cycler: 0.11.0
    py39-dateutil: 2.8.2
    py39-kiwisolver: 1.4.4_1,1
    py39-pillow: 9.3.0
    py39-pluggy: 1.0.0
    py39-pytz: 2022.7,1
    py39-shiboken2: 5.15.2_4
    py39-tornado: 6.2
    qt5-3d: 5.15.7p6
    qt5-assistant: 5.15.7p1
    qt5-charts: 5.15.7p0
    qt5-datavis3d: 5.15.7p0
    qt5-gamepad: 5.15.7p0
    qt5-remoteobjects: 5.15.7p0
    qt5-scripttools: 5.15.11p0
    qt5-scxml: 5.15.7p0
    qt5-sensors: 5.15.7p0
    qt5-speech: 5.15.7p1
    qt5-uitools: 5.15.7p1
    qt5-webengine: 5.15.2_7
    vtk9: 9.2.2_1
    xvid: 1.3.7,1

Number of packages to be removed: 28

The operation will free 440 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/28] Deinstalling qt5-datavis3d-5.15.7p0...
[1/28] Deleting files for qt5-datavis3d-5.15.7p0: 100%
[2/28] Deinstalling qt5-charts-5.15.7p0...
[2/28] Deleting files for qt5-charts-5.15.7p0: 100%
[3/28] Deinstalling py39-pluggy-1.0.0...
[3/28] Deleting files for py39-pluggy-1.0.0: 100%
[4/28] Deinstalling py39-cycler-0.11.0...
[4/28] Deleting files for py39-cycler-0.11.0: 100%
[5/28] Deinstalling qt5-assistant-5.15.7p1...
[5/28] Deleting files for qt5-assistant-5.15.7p1: 100%
[6/28] Deinstalling py39-tornado-6.2...
[6/28] Deleting files for py39-tornado-6.2: 100%
[7/28] Deinstalling qt5-gamepad-5.15.7p0...
[7/28] Deleting files for qt5-gamepad-5.15.7p0: 100%
[8/28] Deinstalling py39-pytz-2022.7,1...
[8/28] Deleting files for py39-pytz-2022.7,1: 100%
[9/28] Deinstalling qt5-scxml-5.15.7p0...
[9/28] Deleting files for qt5-scxml-5.15.7p0: 100%
[10/28] Deinstalling py39-kiwisolver-1.4.4_1,1...
[10/28] Deleting files for py39-kiwisolver-1.4.4_1,1: 100%
[11/28] Deinstalling qt5-3d-5.15.7p6...
[11/28] Deleting files for qt5-3d-5.15.7p6: 100%
[12/28] Deinstalling qt5-uitools-5.15.7p1...
[12/28] Deleting files for qt5-uitools-5.15.7p1: 100%
[13/28] Deinstalling py39-pillow-9.3.0...
[13/28] Deleting files for py39-pillow-9.3.0: 100%
[14/28] Deinstalling py39-shiboken2-5.15.2_4...
[14/28] Deleting files for py39-shiboken2-5.15.2_4: 100%
[15/28] Deinstalling hdf5-110-1.10.9...
[15/28] Deleting files for hdf5-110-1.10.9: 100%
[16/28] Deinstalling xvid-1.3.7,1...
[16/28] Deleting files for xvid-1.3.7,1: 100%
[17/28] Deinstalling vtk9-9.2.2_1...
[17/28] Deleting files for vtk9-9.2.2_1: 100%
[18/28] Deinstalling qt5-webengine-5.15.2_7...
[18/28] Deleting files for qt5-webengine-5.15.2_7: 100%
[19/28] Deinstalling SoQt-1.6.0_1,1...
[19/28] Deleting files for SoQt-1.6.0_1,1: 100%
[20/28] Deinstalling qt5-remoteobjects-5.15.7p0...
[20/28] Deleting files for qt5-remoteobjects-5.15.7p0: 100%
[21/28] Deinstalling draco-3d-compression-1.5.5...
[21/28] Deleting files for draco-3d-compression-1.5.5: 100%
[22/28] Deinstalling py39-dateutil-2.8.2...
[22/28] Deleting files for py39-dateutil-2.8.2: 100%
[23/28] Deinstalling ftgl-2.4.0_1,1...
[23/28] Deleting files for ftgl-2.4.0_1,1: 100%
[24/28] Deinstalling py39-boost-libs-1.80.0...
[24/28] Deleting files for py39-boost-libs-1.80.0: 100%
[25/28] Deinstalling qt5-sensors-5.15.7p0...
[25/28] Deleting files for qt5-sensors-5.15.7p0: 100%
[26/28] Deinstalling qt5-speech-5.15.7p1...
[26/28] Deleting files for qt5-speech-5.15.7p1: 100%
[27/28] Deinstalling freeimage-3.18.0_4...
[27/28] Deleting files for freeimage-3.18.0_4: 100%
[28/28] Deinstalling qt5-scripttools-5.15.11p0...
[28/28] Deleting files for qt5-scripttools-5.15.11p0: 100%
Building cache database of MIME types
pkg: sqlite error while executing VACUUM; in file pkgdb.c:2333: cannot VACUUM - SQL statements in progress
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg autoremove
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 16 packages:

Installed packages to be REMOVED:
    Coin: 4.0.0_3
    alsa-plugins: 1.2.2_8
    assimp: 5.2.5
    libimagequant: 4.0.4_2
    py39-mpi4py: 3.1.4
    py39-olefile: 0.46
    py39-tkinter: 3.9.16_6
    qt5-buildtools: 5.15.7p177
    qt5-designer: 5.15.7p1
    qt5-help: 5.15.7p1
    qt5-location: 5.15.7p3
    qt5-qmake: 5.15.7p177
    qt5-script: 5.15.11p0
    qt5-testlib: 5.15.7p177
    qt5-webchannel: 5.15.7p3
    qt5-xmlpatterns: 5.15.7p0

Number of packages to be removed: 16

The operation will free 101 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/16] Deinstalling py39-olefile-0.46...
[1/16] Deleting files for py39-olefile-0.46: 100%
[2/16] Deinstalling qt5-script-5.15.11p0...
[2/16] Deleting files for qt5-script-5.15.11p0: 100%
[3/16] Deinstalling qt5-testlib-5.15.7p177...
[3/16] Deleting files for qt5-testlib-5.15.7p177: 100%
[4/16] Deinstalling qt5-webchannel-5.15.7p3...
[4/16] Deleting files for qt5-webchannel-5.15.7p3: 100%
[5/16] Deinstalling Coin-4.0.0_3...
[5/16] Deleting files for Coin-4.0.0_3: 100%
[6/16] Deinstalling qt5-help-5.15.7p1...
[6/16] Deleting files for qt5-help-5.15.7p1: 100%
[7/16] Deinstalling alsa-plugins-1.2.2_8...
[7/16] Deleting files for alsa-plugins-1.2.2_8: 100%
[8/16] Deinstalling assimp-5.2.5...
[8/16] Deleting files for assimp-5.2.5: 100%
[9/16] Deinstalling qt5-buildtools-5.15.7p177...
[9/16] Deleting files for qt5-buildtools-5.15.7p177: 100%
[10/16] Deinstalling qt5-location-5.15.7p3...
[10/16] Deleting files for qt5-location-5.15.7p3: 100%
[11/16] Deinstalling libimagequant-4.0.4_2...
[11/16] Deleting files for libimagequant-4.0.4_2: 100%
[12/16] Deinstalling qt5-qmake-5.15.7p177...
[12/16] Deleting files for qt5-qmake-5.15.7p177: 100%
[13/16] Deinstalling py39-tkinter-3.9.16_6...
[13/16] Deleting files for py39-tkinter-3.9.16_6: 100%
[14/16] Deinstalling qt5-xmlpatterns-5.15.7p0...
[14/16] Deleting files for qt5-xmlpatterns-5.15.7p0: 100%
[15/16] Deinstalling py39-mpi4py-3.1.4...
[15/16] Deleting files for py39-mpi4py-3.1.4: 100%
[16/16] Deinstalling qt5-designer-5.15.7p1...
[16/16] Deleting files for qt5-designer-5.15.7p1: 100%
Building cache database of MIME types
pkg: sqlite error while executing VACUUM; in file pkgdb.c:2333: cannot VACUUM - SQL statements in progress
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg autoremove
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 6 packages:

Installed packages to be REMOVED:
    openmpi: 4.1.4
    qt5-serialport: 5.15.7p0
    qt5-sqldrivers-sqlite3: 5.15.7p177
    qt5-uiplugin: 5.15.7p1
    simage: 1.7.0_9
    tk86: 8.6.13

Number of packages to be removed: 6

The operation will free 16 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/6] Deinstalling simage-1.7.0_9...
[1/6] Deleting files for simage-1.7.0_9: 100%
[2/6] Deinstalling qt5-sqldrivers-sqlite3-5.15.7p177...
[2/6] Deleting files for qt5-sqldrivers-sqlite3-5.15.7p177: 100%
[3/6] Deinstalling qt5-uiplugin-5.15.7p1...
[3/6] Deleting files for qt5-uiplugin-5.15.7p1: 100%
[4/6] Deinstalling qt5-serialport-5.15.7p0...
[4/6] Deleting files for qt5-serialport-5.15.7p0: 100%
[5/6] Deinstalling tk86-8.6.13...
[5/6] Deleting files for tk86-8.6.13: 100%
[6/6] Deinstalling openmpi-4.1.4...
[6/6] Deleting files for openmpi-4.1.4: 100%
pkg: sqlite error while executing VACUUM; in file pkgdb.c:2333: cannot VACUUM - SQL statements in progress
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg autoremove
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 2 packages:

Installed packages to be REMOVED:
    slurm-wlm: 20.02.7_2
    tcl86: 8.6.13

Number of packages to be removed: 2

The operation will free 36 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/2] Deinstalling slurm-wlm-20.02.7_2...
[1/2] Deleting files for slurm-wlm-20.02.7_2: 100%
==> You should manually remove the "slurm" user.
==> You should manually remove the "slurm" group
[2/2] Deinstalling tcl86-8.6.13...
[2/2] Deleting files for tcl86-8.6.13: 100%
pkg: sqlite error while executing VACUUM; in file pkgdb.c:2333: cannot VACUUM - SQL statements in progress
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg autoremove
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 3 packages:

Installed packages to be REMOVED:
    hwloc2: 2.8.0
    lua54: 5.4.4
    munge: 0.5.14_1

Number of packages to be removed: 3

The operation will free 10 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/3] Deinstalling lua54-5.4.4...
[1/3] Deleting files for lua54-5.4.4: 100%
[2/3] Deinstalling munge-0.5.14_1...
[2/3] Deleting files for munge-0.5.14_1: 100%
[3/3] Deinstalling hwloc2-2.8.0...
[3/3] Deleting files for hwloc2-2.8.0: 100%
pkg: sqlite error while executing VACUUM; in file pkgdb.c:2333: cannot VACUUM - SQL statements in progress
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg autoremove
Checking integrity... done (0 conflicting)
Deinstallation has been requested for the following 1 packages:

Installed packages to be REMOVED:
    level-zero: 1.8.12

Number of packages to be removed: 1

The operation will free 2 MiB.

Proceed with deinstalling packages? [y/N]: y
[1/1] Deinstalling level-zero-1.8.12...
[1/1] Deleting files for level-zero-1.8.12: 100%
pkg: sqlite error while executing VACUUM; in file pkgdb.c:2333: cannot VACUUM - SQL statements in progress
beastie@FreeBaSeD-T430: /home/beastie$ doas pkg autoremove
Checking integrity... done (0 conflicting)
Nothing to do.
beastie@FreeBaSeD-T430: /home/beastie$
```

IDK if this helps, but I use this script to update my OS:

```
#!/usr/bin/env bash

if [[ "${UID}" != "0" ]] ; then echo "UID != 0" ; exit ; fi

printexec() {

    printf "\e[91;1m * ${*}\e[0m\n"
    "${@}"

}

printexec freebsd-update fetch install
printexec pkg update
printexec pkg upgrade
printexec pkg autoremove
printexec pkg audit -F -r
printexec pkg clean
printexec portsnap fetch update
printexec portmaster --clean-distfiles
printexec pkg stats

printf "\e[91;1m * Done\e[0m\n"

exit 0
```


----------



## alexseitsinger (Tuesday at 4:52 PM)

SirDice said:


> Did you perhaps still have a `pkg install` running in another terminal? That VACUUM is a special query that tries to clean up the database, but it can't do that if there's still a query running. Or maybe a pkg-install(8)/pkg-delete(8) that got broken off? Although the command may have terminated, the SQL query might still be running on the database. Or maybe some query is stuck in a never ending loop. Or it's transaction was never finished.
> 
> 
> 
> ...


As he suggested, I would confirm that there isn't a stray process(es) running somewhere in the background.
`/bin/pgrep pkg | /sbin/ps -o args`

If you see the pkg delete or pkg install commands idle or something that would mean he is correct.
As I've never run into this before, I don't have any quick answers for you friend. I would encourage you to determine how this is happening and why so that you can appreciate the answer that you eventually find--eventually you will be able to troubleshoot all of the tings on your own, making you a bona-fide FreeBSD sysadmin.


----------



## covacat (Tuesday at 5:08 PM)

to backup copy /var/db/pkg/local.sqlite somewhere else

sqlite3  /var/db/pkg/local.sqlite .dump |sqlite3 newdb.sqlite3
mv newdb.sqlite3 /var/db/pkg/local.sqlite


----------



## BobSlacker (Tuesday at 5:41 PM)

alexseitsinger said:


> As he suggested, I would confirm that there isn't a stray process(es) running somewhere in the background.
> `/bin/pgrep pkg | /sbin/ps -o args`
> 
> If you see the pkg delete or pkg install commands idle or something that would mean he is correct.
> As I've never run into this before, I don't have any quick answers for you friend. I would encourage you to determine how this is happening and why so that you can appreciate the answer that you eventually find--eventually you will be able to troubleshoot all of the tings on your own, making you a bona-fide FreeBSD sysadmin.


Nothing.

```
{beastie} @ FreeBaSeD-T430 > /home/beastie
 → /bin/pgrep pkg | /bin/ps -o args                                                                                                                                                                          1 13:52:51 23-01-10
COMMAND
/usr/local/bin/zsh
/usr/local/bin/zsh
[pgrep]
/bin/ps -o args
/usr/local/bin/zsh
tcsh
/usr/local/bin/zsh
{beastie} @ FreeBaSeD-T430 > /home/beastie
 → pgrep pkg                                                                                                                                                                                                 0 18:38:07 23-01-10
{beastie} @ FreeBaSeD-T430 > /home/beastie
 → doas pgrep pkg                                                                                                                                                                                            1 18:38:30 23-01-10
{beastie} @ FreeBaSeD-T430 > /home/beastie
 → doas /bin/pgrep pkg | /bin/ps -o args                                                                                                                                                                     1 18:40:56 23-01-10
COMMAND
/usr/local/bin/zsh
/bin/ps -o args
/usr/local/bin/zsh
/usr/local/bin/zsh
tcsh
/usr/local/bin/zsh
```


----------



## SirDice (Tuesday at 5:44 PM)

Please don't post pictures of text. Just copy/paste the text in a [code]...[/code] block, pictures are impossible to quote from.


----------



## BobSlacker (Tuesday at 5:47 PM)

SirDice said:


> Please don't post pictures of text. Just copy/paste the text in a [code]...[/code] block, pictures are impossible to quote from.


Edited.


----------

