I get this below error when I am running the below code to read my MySQL server table.
my_data <- dbReadTable(mydb, "ar_data")
Warning message:
row.names not set (duplicate elements in field)
Is there any way through which I don't ask R to read the row names. My table is fine and I don't want to make any changes to my MySQL table.
Here are a few options:
library(RMySQL)
library(DBI)
drv <- dbDriver("MySQL")
con <- dbConnect (drv, dbname="mydb", user="username")
data <- mtcars; rownames(data) <- NULL; data$row_names <- rownames(mtcars)[1]
dbWriteTable(con, "mtcars", data, overwrite = T, row.names = F)
head( dbReadTable(con, "mtcars"), 3 )
# mpg cyl disp hp drat wt qsec vs am gear carb row_names
# 1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 Mazda RX4
# 2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 Mazda RX4
# 3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 Mazda RX4
# Warning message:
# row.names not set (duplicate elements in field)
# suppress warnings
head( suppressWarnings(dbReadTable(con, "mtcars")), 3 )
# mpg cyl disp hp drat wt qsec vs am gear carb row_names
# 1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4 Mazda RX4
# 2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4 Mazda RX4
# 3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1 Mazda RX4
# rename column row_names to rn
dbSendQuery(con, "ALTER TABLE mtcars CHANGE COLUMN row_names rn TEXT")
head( dbReadTable(con, "mtcars"), 3 )
# rn mpg cyl disp hp drat wt qsec vs am gear carb
# 1 Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
# 2 Mazda RX4 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
# 3 Mazda RX4 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
dbSendQuery(con, 'DROP TABLE mtcars')
dbDisconnect(con)