How to convert a column to BLOB in R before exporting it into MySQL

Question

I am exporting a 2GB data frame from R to MySQL. My each column is having so many values. While exporting Data from R to MySQL i am getting following error:

 could not run statement: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

command i used:

dbWriteTable(conn = con, name = 'tablename', value = as.data.frame(cc))

I am using RMySQL package to export data to MySQL database. I didn't already create table in MySQL database. I am directly creating table from R. Can I change the column's class type to BLOB in R? How to deal with it?

More details needed. "exporting Data from R to MySQL" - how? using a particular package in R? what package? what command? — mathematical.coffee, Jul 21 '15 at 06:24

score 1 · Answer 1 · edited May 23 '17 at 12:14

Without a reproducible example it is a little hard to tell, but I believe you can pass the field.types variable into dbWriteTable. See ?mysqlBuildTableDefinition for a description of field.types - it should be a named list with the data types. This question may also help you.

e.g. if your column is called bigblob you would do

dbWriteTable(conn=con, name='tablename', value=as.data.frame(cc),
             field.types=list(bigblob='BLOB',
                              # types for all your other columns too
             ))

I believe if you specify the type for one column, you must specify the types for all the others too.

How to convert a column to BLOB in R before exporting it into MySQL

1 Answers1