Is there any way to insert a large value in a mysql DB without changing max_allowed_packet ?

Question

Hello,

I know that in order to insert values larger than max_allowed_packet bytes into a MySQL database, the default solution would be to assure that both client and server side max_allowed_packet variables are bigger than the chunk of data that a query inserts into the DB.

However, is there any way to do so without changing the server side variable mentioned above? This would be useful when I have to insert data into a database that is hosted in an ISP that doesn't allow me to change the max_allowed_packet limit.

Another related question: MySql longblob limit is 4GB, but max_allowed_packet limit is 1GB. So, is it possible to insert values larger than 1GB in a longblob table column?

Answer 1

I don't think that there is a way. Maybe splitting the blob will do the trick.

But I think that reading this article http://blogs.msdn.com/b/oldnewthing/archive/2007/03/01/1775759.aspx from Reymond Chen is the best answer to your question.

Relational databases are not designed for that kind of use. If you need to store a gigabyte+ blob in a database, well it is better usually to store it on the NFS and just have a short file location string in your DB. Will save you much troubles down the road.