CODEWITHSUNDEEP
mysql
John Smith
John Smith

Reputation: 6197

Inserting big data, more than "max_allowed_packet" can I dodge it somehow?

Im inserting blob data to MySQL table, but now the size exceed "max_allowed_packet" settings. Its the limit of server, I cant increase that. Can I somehow insert bigger data more than that? I already gz_compress-ed the data

Upvotes: 0

Views: 338

Answers (1)

Jim
Jim

Reputation: 22646

If you definitely can't change the max_allowed_packet size (though I recommend you try - see MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes ). Then you may be able to upload the data in parts. I haven't tried this so you'll likely want to test:

INSERT INTO foo (id, dat) VALUES (1, startOfBlob);
UPDATE foo SET dat = CONCAT(dat,secondBitOfBlob) WHERE id = 1;
UPDATE foo SET dat = CONCAT(dat,thirdBitOfBlob) WHERE id = 1;

This can fragment the blob so you'll likely want to run OPTIMIZE TABLE after this.

If the blobs you are storing are very large you also may want to consider storing them differently - i.e. split over rows or stored in a file somewhere.

Upvotes: 1

Related Questions