mpio-be / sdb Goto Github PK

Hi,

I had the problem of appended ~12M records to a MariaDB database and hit the "Got a packet bigger than 'max_allowed_packet' bytes" problem. Even increasing max_allowed_packet to maximum of 1073741824 didn't help.

I was going to upload the data in chunks, but your dbSafeWriteTable() looked like a simple solution. However, I found a few bugs that I fixed to get it to work in my case.

The first was "Can't subset columns that don't exist."

sdb::dbSafeWriteTable(con, "my_table", my_df, chunksize = 100000, append = T, overwrite = F)

r: Can't subset columns that don't exist.
x Locations 12, 13, 14, 15, 16, etc. don't exist.
ℹ There are only 11 columns.
Run `rlang::last_error()` to see where the error occurred.

The problem seems to be the sub-setting in the dbSafeWriteTable() function:

z = x[ (ii[[i]]) ]

It is missing the ','.

After I fixed this, I got a generic error:

Error in connection_sql_tables(conn@ptr, table_name = name) : 
  nanodbc/nanodbc.cpp:1347: 

Debugging showed it occurred at:

o[i] = RMariaDB::dbWriteTable( conn = con, name = name, value = z, append = TRUE, row.names = FALSE, ...)

Changing this line to the one below fixed the problem.

o[i] = DBI::dbWriteTable(conn = con, name = name, value = z, append = TRUE, row.names = FALSE, ...)

Something to do with scoping?

Here is the complete function that I used:

dbSafeWriteTableFixed <- function(con, name, x, append = TRUE, chunkSize = 1000, verbose = TRUE, ...) {
  
  n = nrow(x)
  i.to.n = 1:n
  ii = split(i.to.n, ceiling(seq_along(i.to.n)/chunkSize) )
  
  o = vector(length = length(ii))
  
  if(verbose) pb = txtProgressBar(max = length(ii), style = 3)
  for(i in 1:length(ii) )   {
    
    # z = x[ (ii[[i]]) ]
    z = x[ (ii[[i]]), ]
    
    # o[i] = RMariaDB::dbWriteTable(conn = con, name = name, value = z, append = TRUE, row.names = FALSE, ...)
    o[i] = DBI::dbWriteTable(conn = con, name = name, value = z, append = TRUE, row.names = FALSE, ...)
    if(verbose) setTxtProgressBar(pb, i)
    
  }
  
  all(o)
  
}

I hope this helps.