Mutex error only when attempting to read/write

[tomasnobrega]

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

```r > 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

```r > 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) ```

[simonpcouch]

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.

[detule]

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.

[tomasnobrega]

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.

[detule]

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

[detule]

@tomasnobrega

Have you had a chance to re-visit this ( in particular double checking your build flags )?

[detule]

Closing due to lack of activity.