MYSQL – Use longblob with 4GB despite max_allowed_packet of 1GB

Picl asked:

Recently I was given an old application using MySQL 5.6 which has a problem with big files.

I found many threads stating that max_allowed_packet cannot be set to more than 1GB as this is the defined limit for MySQL. However there is this datatype LONGBLOB which is defined as supporting sizes up to 4GB.

As I can’t imagine, that they defined a datatype, that you can’t use – how is it possible to save Blobs this size despite max_allowed_packet being 1GB?

My answer:

The only way to do this is to split the blob being uploaded into chunks which are equal or smaller to the max_allowed_packet size. They are uploaded via multiple calls to the MySQL C API function mysql_stmt_send_long_data() (q.v.). The MySQL command line client should handle this for you. In your own code you may have to check whether your preferred language’s API handles this or whether you have to do so yourself.

View the full question and any other answers on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.