Upgrade or Install 0x87af0001 SQL logic error

[TGSAN]

Brief description of your issue

I get 0x87af0001 SQL logic error message, when use winget upgrade or winget install.

LOG FILE:

2022-03-04 12:30:03.468 [CORE] WinGet, version [1.2.3411-preview], activity [{5B9FE2A1-4319-41E7-A308-48FC7BDD3A52}]
2022-03-04 12:30:03.468 [CORE] OS: Windows.Desktop v10.0.22000.527
2022-03-04 12:30:03.468 [CORE] Command line Args: "C:\Users\tgsan\AppData\Local\Microsoft\WindowsApps\winget.exe" upgrade
2022-03-04 12:30:03.468 [CORE] Package: Microsoft.DesktopAppInstaller v1.17.3411.0
2022-03-04 12:30:03.468 [CORE] IsCOMCall:0; Caller: winget-cli
2022-03-04 12:30:03.475 [CLI ] WinGet invoked with arguments: 'upgrade'
2022-03-04 12:30:03.475 [CLI ] Found subcommand: upgrade
2022-03-04 12:30:03.475 [CLI ] Leaf command to execute: root:upgrade
2022-03-04 12:30:03.480 [CLI ] Executing command: upgrade
2022-03-04 12:30:03.481 [REPO] GetCurrentSourceRefs: Source named 'microsoft.builtin.desktop.frameworks' from origin Default is hidden and is dropped.
2022-03-04 12:30:03.481 [REPO] Default source requested, multiple sources available, adding all to source references.
2022-03-04 12:30:03.481 [REPO] Adding to source references msstore
2022-03-04 12:30:03.481 [REPO] Adding to source references winget
2022-03-04 12:30:03.481 [REPO] Source past auto update time [5 mins]; it has been at least 5 mins
2022-03-04 12:30:03.482 [REPO] Source past auto update time [5 mins]; it has been at least 27439470 mins
2022-03-04 12:30:04.805 [FAIL] D:\a\_work\1\s\external\pkg\src\AppInstallerCommonCore\MsixInfo.cpp(150)\WindowsPackageManager.dll!00007FF9AD67BDA7: (caller: 00007FF9AD6DCD0E) Exception(1) tid(8578) 80070781 系统无法辨识文件名。

2022-03-04 12:30:04.805 [FAIL] D:\a\_work\1\s\external\pkg\src\AppInstallerRepositoryCore\RepositorySource.cpp(53)\WindowsPackageManager.dll!00007FF9AD7D8A41: (caller: 00007FF9AD6D2946) LogHr(1) tid(8578) 80070781 系统无法辨识文件名。
    Msg:[D:\a\_work\1\s\external\pkg\src\AppInstallerCommonCore\MsixInfo.cpp(150)\WindowsPackageManager.dll!00007FF9AD67BDA7: (caller: 00007FF9AD6DCD0E) Exception(1) tid(8578) 80070781 系统无法辨识文件名。
] 

2022-03-04 12:30:04.805 [REPO] Source add/update failed, waiting a bit and retrying: winget
2022-03-04 12:30:07.269 [FAIL] D:\a\_work\1\s\external\pkg\src\AppInstallerCommonCore\MsixInfo.cpp(150)\WindowsPackageManager.dll!00007FF9AD67BDA7: (caller: 00007FF9AD6DCD0E) Exception(2) tid(8578) 80070781 系统无法辨识文件名。

2022-03-04 12:30:07.269 [FAIL] D:\a\_work\1\s\external\pkg\src\AppInstallerRepositoryCore\RepositorySource.cpp(516)\WindowsPackageManager.dll!00007FF9AD7D81B3: (caller: 00007FF9AD5DB9FD) LogHr(2) tid(8578) 80070781 系统无法辨识文件名。
    Msg:[D:\a\_work\1\s\external\pkg\src\AppInstallerCommonCore\MsixInfo.cpp(150)\WindowsPackageManager.dll!00007FF9AD67BDA7: (caller: 00007FF9AD6DCD0E) Exception(2) tid(8578) 80070781 系统无法辨识文件名。
] 

2022-03-04 12:30:07.269 [REPO] Failed to update source: winget
2022-03-04 12:30:07.269 [REPO] Multiple sources available, creating aggregated source.
2022-03-04 12:30:07.269 [REPO] Adding to aggregated source: msstore
2022-03-04 12:30:07.269 [REPO] Sending http GET request to: https://storeedgefd.dsx.mp.microsoft.com/v9.0/information
2022-03-04 12:30:08.121 [REPO] Response status: 200
2022-03-04 12:30:08.121 [REPO] Sending http GET request to: https://storeedgefd.dsx.mp.microsoft.com/v9.0/information
2022-03-04 12:30:08.590 [REPO] Response status: 200
2022-03-04 12:30:08.590 [REPO] Adding to aggregated source: winget
2022-03-04 12:30:08.598 [CORE] Examining extension: PFN = Microsoft.Winget.MSStore.Source_8wekyb3d8bbwe, ID = IndexDB
2022-03-04 12:30:08.599 [CORE] Did not find extension: PFN = Microsoft.Winget.Source_8wekyb3d8bbwe, ID = IndexDB
2022-03-04 12:30:08.599 [REPO] Package not found Microsoft.Winget.Source_8wekyb3d8bbwe
2022-03-04 12:30:08.599 [FAIL] D:\a\_work\1\s\external\pkg\src\AppInstallerRepositoryCore\Microsoft\PreIndexedPackageSourceFactory.cpp(205)\WindowsPackageManager.dll!00007FF9AD6DD681: (caller: 00007FF9AD6D2E6E) Exception(3) tid(8578) 8A15000F 
2022-03-04 12:30:08.599 [FAIL] D:\a\_work\1\s\external\pkg\src\AppInstallerRepositoryCore\RepositorySource.cpp(540)\WindowsPackageManager.dll!00007FF9AD7D83A7: (caller: 00007FF9AD5DB9FD) LogHr(3) tid(8578) 8A15000F     Msg:[D:\a\_work\1\s\external\pkg\src\AppInstallerRepositoryCore\Microsoft\PreIndexedPackageSourceFactory.cpp(205)\WindowsPackageManager.dll!00007FF9AD6DD681: (caller: 00007FF9AD6D2E6E) Exception(3) tid(8578) 8A15000F ] 

2022-03-04 12:30:08.599 [REPO] Failed to open available source: winget
2022-03-04 12:30:08.835 [REPO] Creating PredefinedInstalledSource with filter [None]
2022-03-04 12:30:08.835 [REPO] Creating new SQLite Index [4294967295.4294967295] at ':memory:'
2022-03-04 12:30:08.835 [SQL ] Opening SQLite connection: ':memory:' [6, 0]
2022-03-04 12:30:08.870 [REPO] Examining ARP entries for Machine | X64
2022-03-04 12:30:08.995 [REPO] Examining ARP entries for Machine | X86
2022-03-04 12:30:09.139 [REPO] Examining ARP entries for User | X64
2022-03-04 12:30:09.732 [REPO] Opening SQLite Index for ReadWrite at 'C:\Users\tgsan\AppData\Local\Packages\Microsoft.DesktopAppInstaller_8wekyb3d8bbwe\LocalState\StoreEdgeFD\installed.db'
2022-03-04 12:30:09.732 [SQL ] Opening SQLite connection: 'C:\Users\tgsan\AppData\Local\Packages\Microsoft.DesktopAppInstaller_8wekyb3d8bbwe\LocalState\StoreEdgeFD\installed.db' [2, 0]
2022-03-04 12:30:09.732 [FAIL] D:\a\_work\1\s\external\pkg\src\AppInstallerRepositoryCore\SQLiteWrapper.cpp(173)\WindowsPackageManager.dll!00007FF9AD6F121A: (caller: 00007FF9AD6F21B6) Exception(4) tid(8578) 87AF0001 SQL 错误或缺少数据库
    Msg:[SQL logic error] 

2022-03-04 12:30:09.733 [CLI ] Caught wil::ResultException: 

Steps to reproduce

1. Open any terminal (cmd, pwsh, etc.)
2. Use winget upgrade or winget install packagename

Expected behavior

No error

Actual behavior

Get 0x87af0001 SQL logic error message

Environment

Windows.Desktop v10.0.22000.527  
Microsoft.DesktopAppInstaller v1.17.3411.0

[zbalkan]

I get the same error with winget list command too.

winget list
An unexpected error occurred while executing the command:
SQL logic error
0x87af0001 : SQL error or missing database

winget information:

Windows Package Manager v1.2.10271
Copyright (c) Microsoft Corporation. All rights reserved.

Windows: Windows.Desktop v10.0.19043.1645
Package: Microsoft.DesktopAppInstaller v1.17.10271.0

What can we do to help more information for debugging?

[zbalkan]

UPDATE: Since I could not solve it by resetting it or using troubleshooter, I removed winget at all. I used Appx module of PowerShell. Then, I installed winget using msixbundle downloaded from this very repository.

[sercannaya]

I was having the same problem recently. I deleted the "installed.db" file and solved the problem.

The location of the file "installed.db":

%LOCALAPPDATA%\Packages\Microsoft.DesktopAppInstaller_8wekyb3d8bbwe\LocalState\StoreEdgeFD

[aFooBar]

I was having the same problem recently. I deleted the "installed.db" file and solved the problem. [....]

Weeks of headaches, and solution was that simple Many thanks !

[zbalkan]

It seems like winget needs a background integrity check for SQLite. Then a background repair or ideally rebuild of database would be cleaner for user experience.

[saccofrancesco]

I'm having the same problem. But by deleting the installed.db file, I don't get any changes and the error still occures. Any other solution?

[saccofrancesco]

So, as I see, is this a bug that will be patched with the release of the v1.4 o this software (winget-cli), or it's an error that I and we can resolve trough specific commands or other operations? Thanks

[JohnMcPMS]

If someone could provide the --verbose-logs log file of one of these failures, that would be helpful. It would also be helpful to have installed.db, although it does contain information about the packages installed from that source (should not be personal information, just package).

[saccofrancesco]

I'm open to collaborate to do that. I need to know how I can output a verbose version of the logs and how can I supply the installed.db content. Thanks

[saccofrancesco]

So, I managed to output the --verbose-logs for the command winget upgrade --all --verbose-logs. They are pretty long. WinGet-2022-06-21-19-23-47.195.log. Hope this helps :) P.S. Some informations, are written in Italian. Hope this is not a problem. If so, I can translate them.

[JohnMcPMS]

That helps to show that something strange is happening here... The most SQL basic statement is not compiling. If you (or anyone else) could attach the installed.db from %LOCALAPPDATA%\Packages\Microsoft.DesktopAppInstaller_8wekyb3d8bbwe\LocalState\Microsoft.Winget.Source_8wekyb3d8bbwe\installed.db I would hopefully be able to reproduce the problem locally.

[saccofrancesco]

Ok. I will try to do that

[saccofrancesco]

Ok. I don't know how. This morning I started my Laptop, tried to upgrade --all with winget, and it all works clean. Also listing an installing. The problem seems to be gone by itself. If you want I can provide the installed.db file, but I don't think you can now recreate the error

[BreakfastOnToast]

I've also recently been having this issue. I tried deleting my installed.db file but it still returns with a SQL logic error. I've attached a log using winget list --verbose-logs and also my installed.db file (which I've had to zip up to upload it to GitHub). Hopefully these are of some use to trying to debug or reproduce the issue

WinGet-2022-07-18-03-15-59.185.log installed.zip

[JohnMcPMS]

No, unfortunately those files did not help. SQLite uses hidden/system files to store some data that may not have been merged into the main file. It is possible that you still have something in those files that is affecting things.

I have created a PR (#2352) to improve the logged error message, which might hopefully enable getting to the bottom of the issue.

[g0blue]

Found this issue after running into the same error on Windows Insider build 25217, winget version v1.4.2161-preview.

It seems it's no longer possible to uninstall winget from the insider OS, so I couldn't "refresh" my installation to make it work. The updated error message (from #2352) listed the following:

> winget list
An unexpected error occurred while executing the command:
no such table: metadata
0x871f0001 : SQL error or missing database

I did finally find a solution. If I delete both of the following files, functionality is restored:

%LOCALAPPDATA%\Packages\Microsoft.DesktopAppInstall_8wekyb3d8bbwe\LocalState\StoreEdgeFD\installed.db
%LOCALAPPDATA%\Packages\Microsoft.DesktopAppInstall_8wekyb3d8bbwe\LocalState\Microsoft.Winget.Source_8wekyb3d8bbwe\installed.db