All the fun that comes with BLOBs
Sometimes we have to live with huge blobs in a database. It may be due to some proprietary systems that you can’t change. On a different occasion, it’s because your developers can’t live without persisting large code objects to the database. You name it…
A Binary Large Object (BLOB) is a data type designed to store binary data in a column. This is different than most other data types used such as integers, and strings that tend to be small and manageable. Since blobs can store binary data, they can be used to store images, multimedia files, anything. When overused BLOBs lead to a massive increase in the database size and lengthy, often complicated backup and restore process.
The database dump is usually the easy part, e.g.
$mysqldump --all-databases --single-transaction --hex-blob --events --max-allowed-packet=500M > backupfile
* Note: the max-allowed-packet should be the same as the server’s max-allowed-packet.
However, upon restoring you may find that the process fails with:
$ mysql --max-allowed-packet=500M < backupfile
ERROR 2006 (HY000) at line 34596: MySQL server has gone away
You will find absolutely nothing in the MySQL log files. The first idea that comes to mind when I see this error is to increase the timeouts. Well, it helps but not necessary when BLOBs are involved. You may spend long hours increasing the timeouts and retrying, with no success.
We know on which line (statement) the restore process fails. I found a few times that the insert statement tries to write a BLOB that is bigger than the max-allowed-packet. This is despite the fact the backup was successfully taken with this exact setting.
You can run the following sed command to the extract the statement from the backup file and check its size:
$sed -n '34596p' backupfile > f_blob
$du -h f_blob
660M f_blob
Set the max-allowed-packet to a value slightly higher than the statement size and the backup will restore just fine:
$ mysql --hex-blob --max-allowed-packet=700M < backupfile