I am having trouble with Duckdb (through R) since I have changed computer and reinstalled all of my software.
I have a local duckdb connection through which I am writing datasets (with DBI). For example:
path_db <- "~/data/duckdb/pmeasyr.duckdb"
connection_db <- DBI::dbConnect(duckdb::duckdb(), path_db)
DBI::dbWriteTable(connection_db, "mco_22_ium", struct )
which then does appear among my list of tables
> dbListTables(connection_db)
[1] "mco_22_ium"
I am then accessing them in R through dplyr.
dplyr::tbl(conn_serveur, "mco_22_ium")
I used to easily access the tables I had written to my connection. Now, the tables keep « disappearing » every time I restart my R session. I do see that the size of my local duckdb connection is constant or increasing when writing new tables (over 16 000 000 Ko at the moment). But with each new session, the commands:
path_db <- "~/data/duckdb/pmeasyr.duckdb"
connection_db <- DBI::dbConnect(duckdb::duckdb(), path_db)
dbListTables(connection_db)
Keep returning :
character 0
And trying to reach the tables directly with
dplyr ::tbl(connection_db, "table")
I always get the error message.
« Error: rapi_prepare: Failed to prepare query SELECT *
FROM table AS "q01"
WHERE (0 = 1)
Error: Catalog Error: Table with name table does not exist! »
Every time I reload the tables into my duckdb connection, I am able to access them, collect them, etc., until I close the R session. Once I reopen it, the tables are lost again.
I haven’t find any similar question reported online...
So far, I have tried :
- uninstalling DBI, duckdb, dplyr,
- deleting the .duckdb file to start one from scratch
- changing the path to the duckdb .duckdb file
- updating R,
- uninstalling and reinstalling R
My sessionInfo :
R version 4.2.1 (2022-06-23 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)
Matrix products: default
locale:
[1] LC_COLLATE=French_France.utf8 LC_CTYPE=French_France.utf8 LC_MONETARY=French_France.utf8
[4] LC_NUMERIC=C LC_TIME=French_France.utf8
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] magrittr_2.0.3 openxlsx_4.2.5 referime_0.1.0 rAphpQueries_0.0.0.9000
[5] testthat_3.1.4 ggplot2_3.3.6 dplyr_1.0.9 pmeasyr_0.2.9
[9] duckdb_0.4.0 DBI_1.1.3 RPostgres_1.4.4
loaded via a namespace (and not attached):
[1] pkgload_1.3.0 tidyr_1.2.0 vroom_1.5.7 bit64_4.0.5 jsonlite_1.8.0 gsubfn_0.7
[7] brio_1.1.3 assertthat_0.2.1 blob_1.2.3 cellranger_1.1.0 yaml_2.3.5 remotes_2.4.2
[13] sessioninfo_1.2.2 pillar_1.8.0 RSQLite_2.2.15 lattice_0.20-45 glue_1.6.2 chron_2.3-57
[19] colorspace_2.0-3 pkgconfig_2.0.3 devtools_2.4.3 haven_2.5.0 purrr_0.3.4 scales_1.2.0
[25] processx_3.7.0 tzdb_0.3.0 tibble_3.1.7 generics_0.1.3 tictoc_1.0.1 sqldf_0.4-11
[31] sjlabelled_1.2.0 usethis_2.1.6 ellipsis_0.3.2 cachem_1.0.6 withr_2.5.0 RPostgreSQL_0.7-3
[37] cli_3.3.0 proto_1.0.0 crayon_1.5.1 readxl_1.4.0 memoise_2.0.1 ps_1.7.1
[43] fs_1.5.2 fansi_1.0.3 forcats_0.5.1 pkgbuild_1.3.1 tools_4.2.1 prettyunits_1.1.1
[49] hms_1.1.1 lifecycle_1.0.1 stringr_1.4.0 munsell_0.5.0 zip_2.2.0 callr_3.7.2
[55] RODBC_1.3-19 compiler_4.2.1 rlang_1.0.4 grid_4.2.1 rstudioapi_0.13 gtable_0.3.0
[61] curl_4.3.2 R6_2.5.1 zoo_1.8-10 lubridate_1.8.0 fastmap_1.1.0 bit_4.0.4
[67] utf8_1.2.2 rprojroot_2.0.3 insight_0.18.0 readr_2.1.2 desc_1.4.1 stringi_1.7.8
[73] parallel_4.2.1 Rcpp_1.0.9 vctrs_0.4.1 dbplyr_2.2.1 tidyselect_1.1.2
Thank you in advance if you have advice on the matter !
Edit : I had switched to an older version of duckdb during my tests to solve the problem. Following Mause's advice (thank you !) I switched back to duckdb's latest version (along with DBI).
Trying to connect, I got :
Error: rapi_startup: Failed to open database: IO Error: Trying to read a database file with version number 33, but we can only read version 38.
The database file was created with an older version of DuckDB.
The storage of DuckDB is not yet stable; newer versions of DuckDB cannot read old database files and vice versa.
The storage will be stabilized when version 1.0 releases.
For now, we recommend that you load the database file in a supported version of DuckDB, and use the EXPORT DATABASE command followed by IMPORT DATABASE on the current version of DuckDB.
I created a .duckdb file from scratch with the package latest version. It seems like my tables are kept now. But it has already happened in the last weeks that they « disappeared » after a few days so I will wait a few days and let you know whether the problem is well resolved !
