rmariadb

How to increase the max number of columns in RMariaDB?

I am trying to write a table with more than 1000 columns to a MariaDB table:

conn <- DBI::dbConnect(
    drv = RMariaDB::MariaDB(),
    mysql=F, 
...
  )

tab <- as.data.frame(matrix(1:1600,ncol=1600,nrow=1) )
names(tab)=paste0("X",1:1600)
dbWriteTable(conn,"test",tab,overwrite=T)

This gives me: Error: Too many columns [1117].

Is there any way to increase the column limit?

Solution

  • If I'm understanding the doc correctly, dbWriteTable used like this will create the table, and that create table will, I'm guessing, use your default storage engine, InnoDB. InnoDB has a 1000 column limit, but other storage engines have higher limits; mariadb may have an overall limit of 4096.

    If you call dbWriteTable with append TRUE, it reuses an existing table if it can; try explicitly creating a table with the myisam or aria storage engine (and delete the table contents before calling dbWriteTable if appropriate).