Cannot install FTS extension in DuckDB

TimG · August 9, 2024, 12:09pm

I cannot install the fts extension in DuckDB. The docs say it should auto-install, but it reports that it fails. I have also tried to install manually using

    DBInterface.execute(con, "INSTALL fts FROM 'http://extensions.duckdb.org'";)
    DBInterface.execute(con,"LOAD fts";)

both with and without the From clause. Again, it fails.
This is the error message:

ERROR: LoadError: DuckDB.QueryException("Execute of query \"INSTALL fts FROM 'http://extensions.duckdb.org'\" failed: HTTP Error: Failed to download extension \"fts\" at URL \"http://extensions.duckdb.org/v1.0.0/windows_amd64_mingw/fts.duckdb_extension.gz\"\nExtension \"fts\" is an existing extension.\n")

This issue was raised on Github in July 2023 but it was closed because it went stale after 30 days. Cannot install FTS extension (Julia/Windows) · Issue #8363 · duckdb/duckdb · GitHub

Is anyone aware of a way to install this extension in DuckDB?

TimG · August 9, 2024, 3:16pm

So I created a new issue for DuckDB but just got the following response.

Hi @TimG1964, thanks for opening this issue. Unfortunately, the Julia client is not covered by the official community support. We’ll move it out from the duckdb/duckdb repository to prevent confusion about its state.

This implies that I need to file my issue against DuckDB.jl but I can’t see a way to do that. I can only find DuckDB · JuliaHub but this just links back to the main DuckDB site on github.

How can I raise this issue in the right place?

tbeason · August 9, 2024, 3:34pm

There is no separate repo right now. DuckDB.jl is in the main duckdb repo, that’s what the response is saying. They will I guess be moving it elsewhere. I think this will actually help rather than hurt DuckDB.jl development.

FWIW I also ran into the “same” issue regarding the https extension yesterday. This issue had arisen in the past, been fixed, but seems to have returned. I think it has to do with versioning.

aplavin · August 9, 2024, 9:27pm

Just tried, and

works for me. I guess more details are needed to reproduce – like the package versions or architecture.

TimG · August 10, 2024, 7:18am

So I created testDuckDB.jl:

import Pkg
Pkg.activate(;temp=true)
Pkg.add([
    "DuckDB"
])
using DuckDB
con = DBInterface.connect(DuckDB.DB, "ddb.DuckDB")
DBInterface.execute(con, "INSTALL fts FROM 'http://extensions.duckdb.org'";)
DBInterface.execute(con, "LOAD fts";)

and then, at a command prompt:

julia testDuckDB.jl

Which still gave me the same result:

  Activating new project at `C:\Users\TGebbels\AppData\Local\Temp\jl_uccq3q`
   Resolving package versions...
    Updating `C:\Users\TGebbels\AppData\Local\Temp\jl_uccq3q\Project.toml`
  [d2f5444f] + DuckDB v1.0.0
    Updating `C:\Users\TGebbels\AppData\Local\Temp\jl_uccq3q\Manifest.toml`
  [a10d1c49] + DBInterface v2.6.1
  [9a962f9c] + DataAPI v1.16.0
  [e2d170a0] + DataValueInterfaces v1.0.0
  [d2f5444f] + DuckDB v1.0.0
  [fb4d412d] + FixedPointDecimals v0.5.2
  [842dd82b] + InlineStrings v1.4.2
  [82899510] + IteratorInterfaceExtensions v1.0.0
  [692b3bcd] + JLLWrappers v1.5.0
  [bac558e1] + OrderedCollections v1.6.3
  [69de0a69] + Parsers v2.8.1
  [aea7be01] + PrecompileTools v1.2.1
  [21216c6a] + Preferences v1.4.3
  [3783bdb8] + TableTraits v1.0.1
  [bd369af6] + Tables v1.12.0
  [ea10d353] + WeakRefStrings v1.4.2
  [2cbbab25] + DuckDB_jll v1.0.0+1
  [56f22d72] + Artifacts
  [ade2ca70] + Dates
  [8f399da3] + Libdl
  [de0858da] + Printf
  [9a3f8284] + Random
  [ea8e919c] + SHA v0.7.0
  [fa267f1f] + TOML v1.0.3
  [cf7118a7] + UUIDs
  [4ec0a83e] + Unicode
ERROR: LoadError: Execute of query "INSTALL fts FROM 'http://extensions.duckdb.org'" failed: HTTP Error: Failed to download extension "fts" at URL "http://extensions.duckdb.org/v1.0.0/windows_amd64_mingw/fts.duckdb_extension.gz"
Extension "fts" is an existing extension.

Stacktrace:
 [1] execute(stmt::DuckDB.Stmt, params::@NamedTuple{})
   @ DuckDB C:\Users\TGebbels\.julia\packages\DuckDB\zTfd6\src\result.jl:761
 [2] execute
   @ C:\Users\TGebbels\.julia\packages\DuckDB\zTfd6\src\result.jl:859 [inlined]
 [3] execute
   @ C:\Users\TGebbels\.julia\packages\DBInterface\nQcsk\src\DBInterface.jl:130 [inlined]
 [4] execute(conn::DuckDB.DB, sql::String)
   @ DBInterface C:\Users\TGebbels\.julia\packages\DBInterface\nQcsk\src\DBInterface.jl:152
 [5] top-level scope
   @ C:\Users\TGebbels\OneDrive - The National Lottery Community Fund\Desktop\testDuckDB.jl:8
in expression starting at C:\Users\TGebbels\...\Desktop\testDuckDB.jl:8

Here is my versioninfo()

julia> versioninfo()
Julia Version 1.10.4
Commit 48d4fd4843 (2024-06-04 10:41 UTC)
Build Info:
  Official https://julialang.org/ release
Platform Info:
  OS: Windows (x86_64-w64-mingw32)
  CPU: 8 × 11th Gen Intel(R) Core(TM) i7-1185G7 @ 3.00GHz
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-15.0.7 (ORCJIT, tigerlake)
Threads: 1 default, 0 interactive, 1 GC (on 8 virtual cores)

Taking out the FROM 'http://extensions.duckdb.org' made no difference.

aplavin · August 10, 2024, 7:44am

Your script works for me as-is, maybe some architecture dependence indeed?

julia> versioninfo()
Julia Version 1.10.4
Commit 48d4fd48430 (2024-06-04 10:41 UTC)
Build Info:
  Official https://julialang.org/ release
Platform Info:
  OS: macOS (arm64-apple-darwin22.4.0)
  CPU: 8 × Apple M2
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-15.0.7 (ORCJIT, apple-m1)
Threads: 1 default, 0 interactive, 1 GC (on 4 virtual cores)
Environment:
  JULIA_PKG_USE_CLI_GIT = true

giordano · August 10, 2024, 8:40am

For what is worth, the URL mentioned in the error message doesn’t seem to be valid and returns 403 for me (unless one needs to authenticate somehow):

% curl -LfsI 'https://extensions.duckdb.org/v1.0.0/windows_amd64_mingw/fts.duckdb_extension.gz'
HTTP/2 403
date: Sat, 10 Aug 2024 08:38:47 GMT
content-type: application/xml
x-cache: Error from cloudfront
via: 1.1 1267cfea7779b313ab742be3bae1ae6a.cloudfront.net (CloudFront)
x-amz-cf-pop: MXP53-P2
x-amz-cf-id: XQ9vqgmrS5RjHTDUOzf-pM6SUACwzqCtLUP4pT-VfFd_BUJrctFl8g==
cross-origin-resource-policy: cross-origin
vary: Origin
cf-cache-status: BYPASS
report-to: {"endpoints":[{"url":"https:\/\/a.nel.cloudflare.com\/report\/v4?s=4cyDpAaffm4ODqZmbcLDH4fe8ToAt9WlDayToi9sLkL7Zo3sy0HcpsxtTEddL6xdnOD0BuweBZZyuwlYe2fMMu8JKo21VGTLDYRO7WX%2F%2FbmHD7Z0S%2FYFNd8CeW6Qd1oyL4jsvMN2Eg%3D%3D"}],"group":"cf-nel","max_age":604800}
nel: {"success_fraction":0,"report_to":"cf-nel","max_age":604800}
server: cloudflare
cf-ray: 8b0eabf099630e1d-MXP

TimG · August 10, 2024, 10:59am

Tried again on my home pc in case this error is a result of some corporate lockdown setting, but results were the same.

Home pc is also windows (win10):

julia> versioninfo()
Julia Version 1.10.4
Commit 48d4fd4843 (2024-06-04 10:41 UTC) 
Build Info:
  Official https://julialang.org/ release
Platform Info:
  OS: Windows (x86_64-w64-mingw32)       
  CPU: 8 × Intel(R) Core(TM) i7-2600K CPU @ 3.40GHz
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-15.0.7 (ORCJIT, sandybridge)
Threads: 1 default, 0 interactive, 1 GC (on 8 virtual cores)
Environment:
  JULIA_EDITOR = code
  JULIA_NUM_THREADS = 0

novemberist · August 11, 2024, 11:49pm

It seems to be a Window only issue with the Julia client related to mingw, see this issue on github from some time ago

In fact, I currently can not install any DuckDB extension on Windows with the Julia client (while everything works fine on Linux) - so it’s apparently not just FTS but a more general issue.

There’s a hacky solution in the comments, but I haven’t tried it myself. Given the emphasis on the new support policy for DuckDB, that sadly doesn’t include Julia, I doubt anything will be done about this anytime soon from their side (see the comments in the github issue).

Not sure if anything could be done on Julia’s side to mitigate this issue?

TimG · August 12, 2024, 10:08am

The solution referred to seems to be this:

If I define environmental variable JULIA_DUCKDB_LIBRARY in Windows before I add DuckDB package in Julia and point it to duckdb.dll which I downloaded from the Releases page everything works like a dream.

I’ve not used environment variables before but a quick search indicates that I would need admin privileges to define one as proposed. Maybe this isn’t true but, if it is, this solution isn’t available to me.

abraemer · August 12, 2024, 10:48am

I think you can simply set it in Julia:

ENV["JULIA_DUCKDB_LIBRARY"] = "path/to/library"

TimG · August 12, 2024, 11:25am

I downloaded DuckDB.dll from this file on the releases page: libduckdb-windows-amd64.zip

import Pkg
Pkg.activate(;temp=true)
ENV["JULIA_DUCKDB_LIBRARY"] = raw"C:\Users\TGebbels\...\Documents\Julia\Julia Experimenting\OSdatatesting\duckdb.dll"
Pkg.add(["DuckDB"])
using DuckDB
con = DBInterface.connect(DuckDB.DB, "ddb.DuckDB")
DBInterface.execute(con, "INSTALL fts";)
DBInterface.execute(con, "LOAD fts";)

ERROR: LoadError: DuckDB.QueryException("Execute of query \"INSTALL fts\" failed: HTTP Error: Failed to download extension \"fts\" at URL \"http://extensions.duckdb.org/v1.0.0/windows_amd64_mingw/fts.duckdb_extension.gz\"\nExtension \"fts\" is an existing extension.\n")

novemberist · August 12, 2024, 12:27pm

Seems like it still tries to download (the not existing)

http://extensions.duckdb.org/v1.0.0/windows_amd64_mingw/fts.duckdb_extension.gz

instead of

http://extensions.duckdb.org/v1.0.0/windows_amd64/fts.duckdb_extension.gz

could you try to download and install the extension manually and see if it works?

ufechner7 · August 12, 2024, 12:40pm

Perhaps the space in the path of the folder where you put it is causing problems?

TimG · August 12, 2024, 12:46pm

I have downloaded the file.

I’m unclear how to do this…

TimG · August 12, 2024, 12:49pm

I don’t think the spaces are the problem. I tried using C:\Users\TGebbels\duckdb.dll but it didn’t make any difference.

novemberist · August 12, 2024, 1:03pm

I think a “INSTALL ‘path/to/fts.duckdb_extension’” query shoud work instead of “INSTALL fts”

make sure to decompress the .gz file before

see:
Working with Extensions – DuckDB

TimG · August 12, 2024, 1:27pm

Well, it knows this isn’t a mingw extension…

ERROR: LoadError: DuckDB.QueryException("Execute of query \"INSTALL 'C:\\Users\\TGebbels\\fts.duckdb_extension'\" failed: IO Error: Failed to install 'C:\\Users\\TGebbels\\fts.duckdb_extension'\nThe file was built for the platform 'windows_amd64', but we can only load extensions built for platform 'windows_amd64_mingw'.")

novemberist · August 12, 2024, 1:38pm

Then it seems like setting the environment variable and pointing to the DuckDB library that was not shipped with the Julia package (i.e. not cross-compiled with incompatible mingw) doesn’t work somehow

Have you tried removing and re-adding the DuckDB package from Julia after you set the environment variable?

The only other thing I could think of would be to build the extension yourself for Windows with mingw (on a Linux machine) and try to load it manually

It’s really unfortunate that this is such a hassle, since I’m also locked into a Windows environment at work and would really like to be able to use DuckDB with Julia and extensions on it

TimG · August 12, 2024, 2:24pm

I’ve been trying these options with Pkg.activate(;temp=true) and only adding DuckDB after setting the ENV variable. Is that not enough? Do I need to remove DuckDB universally (and if so, how)?

The only other thing I could think of would be to build the extension yourself for Windows with mingw (on a Linux machine) and try to load it manually

I really don’t think I have the expertise for this and nor do I have ready access to a Linux machine.