Solution
My original post is preserved below, where the problem was poorly formed and assumed to be within the tidyverse.
After further investigation it became clear that the RMySQL::MySQL() driver garbled characters with diacritical marks in my data, switching to the RMariaDB::MariaDB() driver solved my issues entirely. As noted, the RMariaDB package is designed as a replacement for RMySQL.
As community does not allow me to delete my question, I hope that this addendum is useful to anyone else that faces issues with character encodings using RMySQL.
The Problem
I'm using the pool library to access a database and display the data using DT. When my app runs locally the app works correctly, but remotely on shinyapps.io the diacritics are not showing correctly. Here's my deployed application: https://trainingidn.shinyapps.io/dt_special-characters/
Notice that row 3 in all the tables generated by running code locally display Čavlena but the tables generated remotely display ?avlena.
I'm kind of stumped where the problem lies, as inspecting the columns using Encoding and stringi::stri_enc_detect() everything seems to be identical!
A Gist
You might not trust me to click download buttons in a Shiny app! That's understandable. I uploaded everything to a Gist here - https://gist.github.com/martinjhnhadley/ce54566d880b8f30245e486c55ebd42a
The Code
The code below will work for you on shinyapps.io but unfortunately your local IP is very unlikely to be in the range that will let you access the data locally.
ui.R
library("DT")
fluidPage(
h1("readr_local_exported_display_main_table_DT"),
DT::dataTableOutput("readr_local_exported_display_main_table_DT"),
downloadButton("download_readr_local_exported_display_main_data", "Download Table"),
h1("readr_shinyapps_exported_display_main_table_DT"),
DT::dataTableOutput("readr_shinyapps_exported_display_main_table_DT"),
downloadButton("download_readr_shinyapps_exported_display_main_data", "Download Table"),
h1("utils_shinyapps_exported_display_main_table_DT"),
DT::dataTableOutput("utils_shinyapps_exported_display_main_table_DT"),
downloadButton("download_utils_shinyapps_exported_display_main_data", "Download Table"),
h1("utils_local_exported_display_main_table_DT"),
DT::dataTableOutput("utils_local_exported_display_main_table_DT"),
downloadButton("download_utils_local_exported_display_main_data", "Download Table")
)
server.R
library("tidyverse")
library("shiny")
library("DT")
library("pool")
library("dbplyr")
library("RMySQL")
function(input, output, session) {
observe({
if (session$clientData$url_hostname == "127.0.0.1") {
oxrep_db <- dbPool(
drv = RMySQL::MySQL(),
dbname = "oxrep",
port = 3306,
host = "163.1.169.203",
user = "ouit0409"
)
display_main_data <- oxrep_db %>%
tbl("Shipwrecks") %>%
collect() %>%
slice(285:300) %>%
mutate(encoding = Encoding(wreckName)) %>%
select(encoding, wreckSite)
display_main_data %>%
write_csv("data/readr_local_exported_display_main_data.csv")
display_main_data %>%
write.csv("data/utils_local_exported_display_main_data.csv")
} else {
oxrep_db <- dbPool(
drv = RMySQL::MySQL(),
dbname = "oxrep",
port = 3306,
host = "163.1.169.203",
user = "shiney"
)
display_main_data <- oxrep_db %>%
tbl("Shipwrecks") %>%
collect() %>%
slice(285:300) %>%
mutate(encoding = Encoding(wreckName)) %>%
select(encoding, wreckSite)
display_main_data %>%
write_csv("data/readr_shinyapps_exported_display_main_data.csv")
display_main_data %>%
write.csv("data/utils_shinyapps_exported_display_main_data.csv")
}
})
output$readr_local_exported_display_main_table_DT <-
DT::renderDataTable({
# input$update
if (file.exists("data/readr_local_exported_display_main_data.csv")) {
read_csv("data/readr_local_exported_display_main_data.csv") %>%
datatable()
} else {
tibble(note = "You must run this app locally first!") %>%
datatable()
}
})
output$download_readr_local_exported_display_main_data <- downloadHandler(
filename = function() {
paste("readr_local_exported_display_main_data", ".csv", sep = "")
},
content = function(file) {
read_csv("data/readr_local_exported_display_main_data.csv") %>%
write_csv(file)
}
)
output$readr_shinyapps_exported_display_main_table_DT <-
DT::renderDataTable({
# input$update
if (file.exists("data/readr_shinyapps_exported_display_main_data.csv")) {
read_csv("data/readr_shinyapps_exported_display_main_data.csv") %>%
datatable()
} else {
tibble(note = "You must run this app remotely as well!") %>%
datatable()
}
})
output$download_readr_shinyapps_exported_display_main_data <- downloadHandler(
filename = function() {
paste("readr_shinyapps_exported_display_main_data", ".csv", sep = "")
},
content = function(file) {
read_csv("data/readr_shinyapps_exported_display_main_data.csv") %>%
write_csv(file)
}
)
output$utils_local_exported_display_main_table_DT <-
DT::renderDataTable({
if (file.exists("data/utils_local_exported_display_main_data.csv")) {
read_csv("data/utils_local_exported_display_main_data.csv") %>%
datatable()
} else {
tibble(note = "You must run this app locally first!") %>%
datatable()
}
})
output$download_utils_local_exported_display_main_data <- downloadHandler(
filename = function() {
paste("utils_local_exported_display_main_data", ".csv", sep = "")
},
content = function(file) {
read_csv("data/utils_local_exported_display_main_data.csv") %>%
write_csv(file)
}
)
output$utils_shinyapps_exported_display_main_table_DT <-
DT::renderDataTable({
if (file.exists("data/utils_shinyapps_exported_display_main_data.csv")) {
read_csv("data/utils_shinyapps_exported_display_main_data.csv") %>%
datatable()
} else {
tibble(note = "You must run this app remotely as well!") %>%
datatable()
}
})
output$download_utils_shinyapps_exported_display_main_data <- downloadHandler(
filename = function() {
paste("utils_shinyapps_exported_display_main_data", ".csv", sep = "")
},
content = function(file) {
read_csv("data/utils_shinyapps_exported_display_main_data.csv") %>%
write_csv(file)
}
)
}