How to prevent RMySQL warnings "Unsigned INTEGER in col imported as numeric"?

Question

When loading data from a MySQL table there are often warnings such as :

unrecognized MySQL field type 7 in column 26 imported as character

Unsigned INTEGER in col 3 imported as numeric

How can I correctly load the database table into a dataframe so that these warnings are not shown?

I see the function RMySQL::dbDataType() can "Determine the SQL Data Type of an S object". Is there a way to tell it which MySQL data type matches which vector type in a data frame?

Seguidilla

The CRAN page describes RMySQL as a "Legacy 'DBI' interface to 'MySQL' / 'MariaDB' [...] A modern 'MySQL' client based on 'Rcpp' is available from the 'RMariaDB' package." – Seguidilla

Answer 1

You're not doing anything incorrectly--RMySQL "helpfully" gives you all those warnings just in case that wasn't what you want (it almost always is). @hadley recommends installing the development version (0.11.x) in which this annoyance is reportedly solved. Otherwise, you can use suppressWarnings(...), where ... is the statement for which you want to hide warnings. Or just live with it till version 0.11 hits CRAN.

Altair

Since I develop a package that is used by others to manipulate data on a server, I prefer to stick with the CRAN version so that the server is easier to maintain. For the moment I have chosen to live with these RMySQL warnings. – Seguidilla

I have to keep Java at an old version on my machine for a mission critical desktop client, so I've made the same choice. It hurts me because I've basically taught myself to ignore all warnings--it's a bad habit, but it's what I'm stuck with. – Altair

Answer 2

Switching from RMySQL to RMariaDB also removes the warnings, see https://github.com/r-dbi/RMySQL/issues/37.

Desiccate

Answer 3

The best option may be RMariaDB but if you're stuck with RMySQL, you can ignore specific warnings with the function below (adapted from this answer)

#' Suppress warnings matching regex
#'
#' @param expr Expression to evaluate
#' @param regex Regex pattern passed to grepl
#' @param ... Additional arguments passed to grepl
#'
#' @export
suppress_warnings_matching <- function(expr, pattern, ...) {
  eval.parent(substitute(
    withCallingHandlers(expr, warning = function(w) {
      msg <- conditionMessage(w)
      if (grepl(pattern, msg, ...)) {
        invokeRestart("muffleWarning")
      }
    })
  ))
}

I mostly use dbplyr to interact with my DBs which means the queries actually get executed when I either collect or print interactively in the console. If you use pool, there is also a warning when calling tbl because there is a field lookup in tbl.Pool for some reason.

The following should work but isn't perfect.

# Override dplyr's collect with a more silent version
collect <- function(...) {
  annoying_mysql_warning <- "Unsigned INTEGER in col [0-9]+ imported as numeric"
  suppress_warnings_matching(dplyr::collect(...), annoying_mysql_warning)
}

# Override base print with a more silent version
print <- function(...) {
  annoying_mysql_warning <- "Unsigned INTEGER in col [0-9]+ imported as numeric"
  suppress_warnings_matching(base::print(...), annoying_mysql_warning)
}

It doesn't work when printing in the Rstudio console and I don't especially like overriding generic functions anyway.

A more efficient way to suppress the warning on collection, interactive use / print and sometimes on assignment (when using pool) is the following, which works in all my use cases:

# Suppress the warning on collect
.S3method("collect", "tbl_sql", function(...) {
  annoying_mysql_warning <- "Unsigned INTEGER in col [0-9]+ imported as numeric"
  suppress_warnings_matching(dbplyr:::collect.tbl_sql(...), annoying_mysql_warning)
})

# Suppress the warning on print (e.g. interactive use)
.S3method("print", "tbl_sql", function(...) {
  annoying_mysql_warning <- "Unsigned INTEGER in col [0-9]+ imported as numeric"
  suppress_warnings_matching(dbplyr:::print.tbl_sql(...), annoying_mysql_warning)
})

# Suppress the warning on `tbl`
.S3method("tbl", "DBIConnection", function(...) {
  annoying_mysql_warning <- "Unsigned INTEGER in col [0-9]+ imported as numeric"
  suppress_warnings_matching(dplyr:::tbl.DBIConnection(...), annoying_mysql_warning)
})

# Suppress the warning on `tbl` for pools
.S3method("tbl", "Pool", function(...) {
  annoying_mysql_warning <- "Unsigned INTEGER in col [0-9]+ imported as numeric"
  suppress_warnings_matching(pool:::tbl.Pool(...), annoying_mysql_warning)
})
})

This works great! It's less than ideal because we have to use internal package functions (using the triple colon :::), but it's worth it for me!

Avoid using this in prod but, if you do, make sure your dbplyr version is frozen to avoid bad surprises.

Tavy