odbc
odbc copied to clipboard
r-dbi
Mutex error only when attempting to read/write
Hi,
I am currently able to connect to a MS SQL Server and read tables with CLI. However, I am still not able to make it work on R due to tds_mutex_unlock. I am debugging for quite some time but I am still not able to figure out what might be the cause.
My setup
I am working on a Linux computer that uses SLURM for resource management and I am using FreeTDS. I have the following files:
.freetds.conf
[global]
tds version = 8
port = 1433
[servername]
host = <serverhost>
realm = <serverrealm>
SPN = <serverspn>
.odbc.ini
[servername]
Description = Research Data Server
Driver = FreeTDS
Servername = <servername>
host = <hostname>
port = 1433
realm = <serverrealm>
tds version = 8
.odbcinst.ini
[FreeTDS]
Description = FreeTDS
Driver = /home/software/sloan/freetds-1.3.18/lib/libtdsodbc.so
Setup = /home/software/sloan/freetds-1.3.18/lib/libtdsodbc.so
fileusage = 1
dontdlclose = 1
Usagecount = 1
Threading = 0
With the files above, I can successfully connect to SQL Server using CLI tsql -S server -U username and perform queries directly from there. The connections seems to be working fine. It also works if I use isql -v servername username password
Going to R
Similarly, my R setup allows me to connect to the server using:
library(DBI)
db <- DBI::dbConnect(
odbc::odbc(),
DSN = <DSN>,
UID = <USER>,
PWD = <PASSWORD>,
database = <DATABASE>
)
After connecting, I can run dbListTables() and see all the tables inside the connection. So it seems that the connection is going through.
However, when I try to read a table I get the following error:
dbReadTable(db, "tablename")
R: ../../include/freetds/thread.h:380: tds_mutex_unlock: Assertion `mtx && mtx->locked' failed.
Interestingly, if I put a non-existent table name, the output tells me that. So it is getting the tables information, but it is not reading them. It is not clear to me why when I try to read/write query it does not work.
Database
> DBI::dbGetInfo(db)
$dbname
[1] "dbname"
$dbms.name
[1] "Microsoft SQL Server"
$db.version
[1] "15.00.4326"
$username
[1] ""
$host
[1] ""
$port
[1] ""
$sourcename
[1] "sourcename"
$servername
[1] "servername"
$drivername
[1] "libtdsodbc.so"
$odbc.version
[1] "03.52"
$driver.version
[1] "01.03.0018"
$odbcdriver.version
[1] "03.50"
$supports.transactions
[1] TRUE
$getdata.extensions.any_column
[1] FALSE
$getdata.extensions.any_order
[1] FALSE
Session Info
> devtools::session_info()
─ Session info ───────────────────────────────────────────────────────────────────────────────────────────────────────────
setting value
version R version 4.2.2 (2022-10-31)
os CentOS Linux 7 (Core)
system x86_64, linux-gnu
ui X11
language (EN)
collate en_US.UTF-8
ctype en_US.UTF-8
tz America/New_York
date 2024-02-13
pandoc NA
─ Packages ───────────────────────────────────────────────────────────────────────────────────────────────────────────────
package * version date (UTC) lib source
cachem 1.0.8 2023-05-01 [1] CRAN (R 4.2.2)
cli 3.6.1 2023-03-23 [1] CRAN (R 4.2.2)
devtools 2.4.5 2022-10-11 [1] CRAN (R 4.2.2)
digest 0.6.34 2024-01-11 [1] CRAN (R 4.2.2)
ellipsis 0.3.2 2021-04-29 [1] CRAN (R 4.2.2)
fastmap 1.1.1 2023-02-24 [1] CRAN (R 4.2.2)
fs 1.6.2 2023-04-25 [1] CRAN (R 4.2.2)
glue 1.6.2 2022-02-24 [1] CRAN (R 4.2.2)
htmltools 0.5.5 2023-03-23 [1] CRAN (R 4.2.2)
htmlwidgets 1.6.2 2023-03-17 [1] CRAN (R 4.2.2)
httpuv 1.6.14 2024-01-26 [1] CRAN (R 4.2.2)
later 1.3.1 2023-05-02 [1] CRAN (R 4.2.2)
lifecycle 1.0.3 2022-10-07 [1] CRAN (R 4.2.2)
magrittr 2.0.3 2022-03-30 [1] CRAN (R 4.2.2)
memoise 2.0.1 2021-11-26 [1] CRAN (R 4.2.2)
mime 0.12 2021-09-28 [1] CRAN (R 4.2.2)
miniUI 0.1.1.1 2018-05-18 [1] CRAN (R 4.2.2)
pkgbuild 1.4.3 2023-12-10 [1] CRAN (R 4.2.2)
pkgload 1.3.4 2024-01-16 [1] CRAN (R 4.2.2)
profvis 0.3.8 2023-05-02 [1] CRAN (R 4.2.2)
promises 1.2.0.1 2021-02-11 [1] CRAN (R 4.2.2)
purrr 1.0.1 2023-01-10 [1] CRAN (R 4.2.2)
R6 2.5.1 2021-08-19 [1] CRAN (R 4.2.2)
Rcpp 1.0.11 2023-07-06 [1] CRAN (R 4.2.2)
remotes 2.4.2.1 2023-07-18 [1] CRAN (R 4.2.2)
rlang 1.1.1 2023-04-28 [1] CRAN (R 4.2.2)
sessioninfo 1.2.2 2021-12-06 [1] CRAN (R 4.2.2)
shiny 1.8.0 2023-11-17 [1] CRAN (R 4.2.2)
stringi 1.7.12 2023-01-11 [1] CRAN (R 4.2.2)
stringr 1.5.1 2023-11-14 [1] CRAN (R 4.2.2)
urlchecker 1.0.1 2021-11-30 [1] CRAN (R 4.2.2)
usethis 2.2.2 2023-07-06 [1] CRAN (R 4.2.2)
vctrs 0.6.3 2023-06-14 [1] CRAN (R 4.2.2)
xtable 1.8-4 2019-04-21 [1] CRAN (R 4.2.2)
Thanks for the thorough issue description, @tomasnobrega.
Could you try installing Microsoft's SQL Server ODBC driver and let us know whether the issue persists with that driver? Especially given the tds_ prefix, this smells like a driver issue to me.
Hi there @tomasnobrega
I am not sure if this is related to your issue, but I would consider leaving the "tds version" as "auto" or failing that, specifying it as "7.4".
As listed here, TDS version 8.0 ( defacto "7.1" / i know this is a bit confusing ) is not supported wtih FreeTDS 1.3+.
Also - i would consider simplifying your setup. For example, I don't think there is a need to modify the stock freetds.conf - you should be able to migrate those settings to your odbc.ini, without needing to reference the servername entry in the freetds config. A list of supported connection attributes for freeTDS can be found here - I see ServerSPN there for example.
I often use freeTDS and I am able to read/write without an issue. I do have an older version installed, so perhaps something is broken in more recent versions - or perhaps it's something specific to your kerberos/authentication scheme. At any rate, I think trying a better TDS version is a good start.
Hi, thank you all for the suggestions. I have tried changing the versions and simplifying the files, still not working. I also thing this might be due to FreeTDS driver. I have asked for the system administrator to install the MS ODBC driver. As soon as this is done I will come here to give another update.
Hi @tomasnobrega
Had a chance to test with the exact FreeTDS version you are using and had no issues reading a table:
┃ > DBI::dbReadTable(conn, "test_xml")
┃ data
┃ 1 myxmldata
┃ > conn@info
┃ $dbname
┃ [1] "master"
┃
┃ $dbms.name
┃ [1] "Microsoft SQL Server"
┃
┃ $db.version
┃ [1] "15.00.4249"
┃
<snip>
┃ $drivername
┃ [1] "libtdsodbc.so"
┃
┃ $odbc.version
┃ [1] "03.52"
┃
┃ $driver.version
┃ [1] "01.03.0018"
So I suspect it's something particular to your setup. I am curious - looks like you are building freeTDS yourself, perhaps. How are you configuring the build?
For what is worth, I use something along the lines of
./configure --with-tdsver=7.4 --enable-msdblib --enable-krb5 --with-openssl