Closed juliasilge closed 4 years ago
I have recently experienced the same problem with Oracle, upon upgrading to 3.6.0. The disturbing twist for me is that I'm seeing it only on one of the three Oracle db's I use. And I can confirm that downgrading to 3.5.3 also fixed it.
Another odd tidbit is that while character columns came over garbled, when I queried system info tables in Oracle, like all_tab_columns
the character data came through just fine.
With R 3.6 and 3.6.1 I on macOS 10.12 and 10.14 using the FreeTDS and MS 017 driver connecting to MS SQL server 17 I experienced something similar calling the table list. Downgrade to 3.5.3 resolved as well. System info is correct, the listed tables are gibberish pure.
I too have had this issue with MSSQL 2014 and R 3.6.x on macOS. My solution was, as everyone else has, rolling my installation back to R 3.5.3, but that won't be sustainable long-term. For me, the scrambled character columns were limited to unicode columns of type NVARCHAR
, columns of type VARCHAR
seemed unaffected in my (brief) testing.
Is it an R Internals Bug or due to odbc library itself?
How could such issue be debugged?
I just switched to R3.6.1 and I'm using MSSQLServer2014. It works good on my computer so far.
Two things are included below:
The issue, for me at least, is with any column of type NVARCHAR. I should also mention that this bug completely breaks the connections tab in RStudio 1.2.5001 once I run the initial DBI::dbConnect
CREATE DATABASE RProblems;
GO
CREATE TABLE RProblems.dbo.ColumnTypeTesting
(
nvarchar_text NVARCHAR(20) NOT NULL,
varchar_text VARCHAR(20) NOT NULL
)
GO
INSERT INTO RProblems.dbo.ColumnTypeTesting (nvarchar_text, varchar_text)
VALUES ('Philadelphia', 'Philadelphia'),
('Boston', 'Boston'),
('New York', 'New York'),
('Buffalo', 'Buffalo'),
('Vancouver', 'Vancouver'),
('Toronto', 'Toronto'),
('Calgary', 'Calgary'),
('Seattle', 'Seattle')
library(DBI)
DATABASE = DBI::dbConnect(
odbc::odbc(),
driver = "ODBC Driver 17 for SQL Server",
port = 1433,
server = 'XXXXXXX',
database = 'RProblems',
uid = 'XXXXXXX',
pwd = 'XXXXXXX'
)
dbGetQuery(DATABASE, 'SELECT nvarchar_text, varchar_text FROM RProblems.dbo.ColumnTypeTesting')
#> nvarchar_text varchar_text
#> 1 Piaepi Philadelphia
#> 2 Bso Boston
#> 3 NwYr New York
#> 4 Bfa Buffalo
#> 5 Vnov Vancouver
#> 6 Trn Toronto
#> 7 Cla Calgary
#> 8 Sat Seattle
Created on 2019-10-22 by the reprex package (v0.3.0)
Sorry, I still can't reproduce your results on my computer. Below is what I get.
conn <- DBI::dbConnect(
odbc::odbc(), server = "XXXX", database = "XXX", uid = "XXX", pwd = "XXX",
encoding = "GB2312", timezone = "Asia/Shanghai", driver = "SQL Server", port = 1433
)
DBI::dbExecute(conn, "CREATE TABLE ColumnTypeTesting
(
nvarchar_text NVARCHAR(20) NOT NULL,
varchar_text VARCHAR(20) NOT NULL
)")
#> [1] 0
DBI::dbExecute(conn, "INSERT INTO ColumnTypeTesting (nvarchar_text, varchar_text)
VALUES ('Philadelphia', 'Philadelphia'),
('Boston', 'Boston'),
('New York', 'New York'),
('Buffalo', 'Buffalo'),
('Vancouver', 'Vancouver'),
('Toronto', 'Toronto'),
('Calgary', 'Calgary'),
('Seattle', 'Seattle')")
#> [1] 8
DBI::dbGetQuery(conn, "select nvarchar_text, varchar_text FROM ColumnTypeTesting")
#> nvarchar_text varchar_text
#> 1 Philadelphia Philadelphia
#> 2 Boston Boston
#> 3 New York New York
#> 4 Buffalo Buffalo
#> 5 Vancouver Vancouver
#> 6 Toronto Toronto
#> 7 Calgary Calgary
#> 8 Seattle Seattle
DBI::dbGetQuery(conn, "select @@VERSION")
#>
#> 1 Microsoft SQL Server 2014 - 12.0.2000.8 (X64) \n\tFeb 20 2014 20:04:26 \n\tCopyright (c) Microsoft Corporation\n\tEnterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )\n
DBI::dbExecute(conn, "drop table ColumnTypeTesting")
#> [1] 0
DBI::dbDisconnect(conn)
Created on 2019-10-23 by the reprex package (v0.3.0)
Here's my idea:
@JakeHurlbut Thanks to your reprex I think we are closer to the root causes...
Yes, I can reproduce this on macOS, if I use the CRAN's binary version of the odbc package. More specifically, install odbc
by executing command install.packages('odbc')
.
And no, it works fine regarding the code above, if I build the odbc package from source on my Mac. More specifically, both methods give the same correct output :
install.packages("odbc", type = 'source')
.remotes::install_github('r-lib/odbc')
.(Note, in order to successfully build compiled codes on macOS you may need to execute xcode-select --install
in the terminal first)
So, I believe the cause of this error is somewhere in the toolchain that CRAN uses to build the binary version for macOS. Unfortunately, I can't help further because I don't know how CRAN manages to build the macOS' binary. @jimhester @krlmlr Any ideas?
In conclusion, the workaround to this issue is building the odbc package from the source. (Would be great if you can confirm this workaround works for you)
For the future investigators, I want to note that it's possible (with little effort) to have an MS SQL Server database inside of a docker container, making the reproduce of this issue and further investigation easily.
@shrektan your theory about the CRAN binary might be correct, I just installed odbc from source using install.packages("odbc", type = 'source')
and that fixed the issue for me on macOS.
I was directed here on the basis of this Stack question.
@JakeHurlbut's solution worked for me with the caveat that I needed to reinstall xcode before I could install from source (xcode-select --install
).
NOTE: The xcode issue appears to be unrelated to the odbc
issue. Rather, I believe this is connected to my recent upgrade to Catalina.
@JakeHurlbut thanks for the reproducible example and @shrektan thanks for your hypothesis of the cause, if this is an issue only with the mac binary this may also be the reason I could not reproduce the behavior when I tried on linux.
I will try to reproduce this myself today and verify it is due to the CRAN toolchain.
I can reproduce the issue on macOS with the CRAN binaries, and re-compiling the odbc package from source fixes the issue. However I cannot re-create the broken binary even if I use the CRAN toolchain on my machine.
Hopefully the problem will resolve itself in the next odbc release, if not in the meantime please re-compile odbc yourself with
install.packages("odbc", type = "source")
Multiple users and I have confirmed this is still an issue with the 1.2.0 CRAN binary. However I have an alternative hypothesis of the issue which should be fixed by c96be77.
I will release an updated version of odbc soon which should hopefully fix this. In the meantime you can workaround the issue by recompiling the package yourself.
FYI: I've encountered this issue again using R 4.3.1 on an intel Mac here in Sept. 2023. Installing odbc from source did not help, but rather crashed R everytime I used the package. However, downgrading R to 4.2.3 and using the cran version of odbc solved the issue.
@RaSieb I've been having the same issue!
Issue Description and Expected Result
Since updating to R 3.6.0, columns of type character come through to R garbled.
I updated to R 3.6.0 last night and discovered that columns that are text/character no longer are brought into memory in R correctly. This looks like the same thing reported in #279 with dropping even-indexed characters.
I have not found any problems with other data types. I reinstalled all packages and drivers and still experience this problem.
Database
SQL Server 2017
Reproducible Example
Calling this reproducible is stretching it as this is me calling our real databases.
New version with 3.6.0:
Created on 2019-06-13 by the reprex package (v0.3.0)
Old version when I install back to 3.5.3:
Created on 2019-06-13 by the reprex package (v0.3.0)
Here is the session info for the R 3.6.0 version that has the problem.
Session Info
```r devtools::session_info() ─ Session info ───────────────────────────────────────────────────────────────────────────────────────────────────────── setting value version R version 3.6.0 (2019-04-26) os macOS High Sierra 10.13.6 system x86_64, darwin15.6.0 ui RStudio language (EN) collate en_US.UTF-8 ctype en_US.UTF-8 tz America/Denver date 2019-06-13 ─ Packages ───────────────────────────────────────────────────────────────────────────────────────────────────────────── package * version date lib source assertthat 0.2.1 2019-03-21 [1] CRAN (R 3.6.0) backports 1.1.4 2019-04-10 [1] CRAN (R 3.6.0) bit 1.1-14 2018-05-29 [1] CRAN (R 3.6.0) bit64 0.9-7 2017-05-08 [1] CRAN (R 3.6.0) blob 1.1.1 2018-03-25 [1] CRAN (R 3.6.0) callr 3.2.0 2019-03-15 [1] CRAN (R 3.6.0) cli 1.1.0 2019-03-19 [1] CRAN (R 3.6.0) clipr 0.6.0 2019-04-15 [1] CRAN (R 3.6.0) crayon 1.3.4 2017-09-16 [1] CRAN (R 3.6.0) curl 3.3 2019-01-10 [1] CRAN (R 3.6.0) DBI * 1.0.0 2018-05-02 [1] CRAN (R 3.6.0) dbplyr * 1.4.1 2019-06-05 [1] CRAN (R 3.6.0) desc 1.2.0 2018-05-01 [1] CRAN (R 3.6.0) devtools 2.0.2 2019-04-08 [1] CRAN (R 3.6.0) digest 0.6.19 2019-05-20 [1] CRAN (R 3.6.0) dplyr * 0.8.1 2019-05-14 [1] CRAN (R 3.6.0) evaluate 0.14 2019-05-28 [1] CRAN (R 3.6.0) fansi 0.4.0 2018-10-05 [1] CRAN (R 3.6.0) fs 1.3.1 2019-05-06 [1] CRAN (R 3.6.0) glue 1.3.1 2019-03-12 [1] CRAN (R 3.6.0) goodshirt 0.2.2 2019-06-13 [1] Github (adam-gruer/goodshirt@7253105) hms 0.4.2 2018-03-10 [1] CRAN (R 3.6.0) htmltools 0.3.6 2017-04-28 [1] CRAN (R 3.6.0) httr 1.4.0 2018-12-11 [1] CRAN (R 3.6.0) jsonlite 1.6 2018-12-07 [1] CRAN (R 3.6.0) keyring 1.1.0 2018-07-16 [1] CRAN (R 3.6.0) knitr 1.23 2019-05-18 [1] CRAN (R 3.6.0) magrittr 1.5 2014-11-22 [1] CRAN (R 3.6.0) memoise 1.1.0 2017-04-21 [1] CRAN (R 3.6.0) nycflights13 * 1.0.0 2018-06-26 [1] CRAN (R 3.6.0) odbc 1.1.6 2018-06-09 [1] CRAN (R 3.6.0) pillar 1.4.1 2019-05-28 [1] CRAN (R 3.6.0) pkgbuild 1.0.3 2019-03-20 [1] CRAN (R 3.6.0) pkgconfig 2.0.2 2018-08-16 [1] CRAN (R 3.6.0) pkgload 1.0.2 2018-10-29 [1] CRAN (R 3.6.0) prettyunits 1.0.2 2015-07-13 [1] CRAN (R 3.6.0) processx 3.3.1 2019-05-08 [1] CRAN (R 3.6.0) ps 1.3.0 2018-12-21 [1] CRAN (R 3.6.0) purrr 0.3.2 2019-03-15 [1] CRAN (R 3.6.0) R6 2.4.0 2019-02-14 [1] CRAN (R 3.6.0) Rcpp 1.0.1 2019-03-17 [1] CRAN (R 3.6.0) remotes 2.0.4 2019-04-10 [1] CRAN (R 3.6.0) reprex * 0.3.0 2019-05-16 [1] CRAN (R 3.6.0) rlang 0.3.4 2019-04-07 [1] CRAN (R 3.6.0) rmarkdown 1.13 2019-05-22 [1] CRAN (R 3.6.0) rprojroot 1.3-2 2018-01-03 [1] CRAN (R 3.6.0) rstudioapi 0.10 2019-03-19 [1] CRAN (R 3.6.0) sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 3.6.0) sqlstackr * 0.6.2 2019-06-13 [1] Github (RPackages/sqlstackr@791c79d) stackutils 0.1.0 2019-06-13 [1] local testthat 2.1.1 2019-04-23 [1] CRAN (R 3.6.0) tibble 2.1.3 2019-06-06 [1] CRAN (R 3.6.0) tidyselect 0.2.5 2018-10-11 [1] CRAN (R 3.6.0) usethis 1.5.0 2019-04-07 [1] CRAN (R 3.6.0) utf8 1.1.4 2018-05-24 [1] CRAN (R 3.6.0) vctrs 0.1.0 2018-11-29 [1] CRAN (R 3.6.0) whisker 0.3-2 2013-04-28 [1] CRAN (R 3.6.0) withr 2.1.2 2018-03-15 [1] CRAN (R 3.6.0) xfun 0.7 2019-05-14 [1] CRAN (R 3.6.0) zeallot 0.1.0 2018-01-28 [1] CRAN (R 3.6.0) [1] /Library/Frameworks/R.framework/Versions/3.6/Resources/library ```