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.