error when writing character fields containing NAs to mysql table

[eduardgrebe]

I get the following error when trying to write a table to a mysql connection with copy_to():

> sanbs_db <- src_mysql("sanbs", host = "localhost", user = "root")
> results_diagnostic.db <- copy_to(sanbs_db, results_diagnostic, temporary = FALSE, indexes = list(c("result_id","donation_id")))
Error in if (n <= 65535) { : missing value where TRUE/FALSE needed

I believe the error occurs because I have true NAs in character fields, and the following check in src-mysql.r produces n=NA instead of the expected maximum length:

db_data_type.MySQLConnection <- function(con, fields, ...) {
  char_type <- function(x) {
    n <- max(nchar(as.character(x), "bytes"))
    if (n <= 65535) {
      paste0("varchar(", n, ")")
    } else {
      "mediumtext"
    }
}

the problem can be demonstrated with:

> stringvec <- c("a",NA,"bb")
> max(nchar(stringvec))
[1] NA
> max(na.omit(nchar(stringvec)))
[1] 2

I'm not sure what this means for writing NULL values into the db table.

[krlmlr]

Thanks, confirmed. Would you like to contribute a test case and perhaps a fix?

[eduardgrebe]

Submitted a pull request with a proposed fix and test case.