Um, slightly off-topic here, but when you said:

... this usually didn't matter because MySQL was able to handle quite a lot of inserts in a single transaction. That is, until it couldn't, and blew up with a buffer size error in the middle of an 8-hour job.

I had to wonder: are you sure you want to be doing that many inserts via DBI? If, instead, you could save the insertion data to a file, and when the file is complete and closed, you execute a single "LOAD DATA INFILE" statement, not only would you vastly reduce the unpleasant likelihood of bad things happening in the middle of database modifications, but also a DBI process that currently takes 8 hours might end up taking noticeably less time.

(Of course, I could envision situations where other database things need to be done that depend on, and must be interleaved with, a sequence of inserts. But in a case like that, I'd still hope to find a way to refactor the task so that inserts can be done in bulk with the db-server's native data-import tools. And then there might be the problem of permissions, if the server is running remotely and being managed by others, in which case some diplomacy might be worth trying...)