3

Had a Rscript for inserting a dataframe into MariaDB working ok. Nobody changed anything but suddenly started to fail.

insertconnection <-
  dbConnect(RMariaDB::MariaDB(),
            user = 'user',
            password = 'pwd',
            dbname = 'db',
            host = 'myhost.com'
  )

dbWriteTable(
  insertconnection,
  name = "summary",
  df,
  append = TRUE,
  overwrite = FALSE,
  row.names = FALSE
)
Error in result_create(conn@ptr, statement, is_statement) :
  Unknown column '$1' in 'field list' [1054]
Calls: dbWriteTable ... new -> initialize -> initialize -> result_create -> .Call

There is no such $1 field

   > names(df)
     [1] "hit"                     "id"                     "reference"          "province"               
     [5] "town"                    "type"               "level"                     "assess"

Here a summary of the dataframe. 

> str(df)
'data.frame':   314322 obs. of  8 variables:
 $ hit                    : num  3568 3617 3624 3625 3627 ...
 $ id                    : chr  "184340" "11440" "38440" "58440" ...
 $ reference         : chr  "18440" "18440" "18440" "18440" ...
 $ province               : chr  "Alberta" "Alberta" "Alberta" "Alberta" ...
 $ town                   : chr  "unknown" "unknown" "unknown" "unknown" ...
 $ type              : chr  "dat" "res" "dat" "res" ...
 $ level                    : chr  "AAO001005" "BBO001005" "CCO001005" "CRO001005" ...
 $ assess: chr  "[returned] IKJ" "Bk LOW" "Bk UP" "Bk LOW" ...

Here the create table command. 

CREATE TABLE `summary` (
    `hit` INT(11) NULL DEFAULT NULL,
    `id` INT(11) NULL DEFAULT NULL,
    `reference` TEXT NULL,
    `province` TEXT NULL,
    `town` TEXT NULL,
    `type` TEXT NULL,
    `level` TEXT NULL,
    `assess` TEXT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Same error is on different machines using Rstudio server

Forge
  • 1,587
  • 1
  • 15
  • 36
  • updated question adding STR and CREATE TABLE. Data is confidential. sorry – Forge Oct 26 '18 at 09:31
  • The error is consistent with one or more of the columns names in your data frame not matching the column names in the `summary` table. Did you just run that `CREATE TABLE` command, or is that old data? Check the MySQL table and data frame, both immediately before you run your R script. – Tim Biegeleisen Oct 26 '18 at 09:44
  • That is actual data and actual CREATE. identical names(df) and names(summary) – Forge Oct 26 '18 at 10:05

0 Answers0