6

The MySQL types LONGTEXT and LONGBLOB are documented with data limits of L + 4 bytes, where L < 2^32 (~4GB). But max_allowed_packet is only configurable up to 1GB.

The largest possible packet that can be transmitted to or from a MySQL 8.0 server or client is 1GB.

Wouldn't that mean the limit of LONGTEXT and LONGBLOG is actually ~1GB? The JSON type is limited by max_allowed_packet but the text/blob types do not mention the same limitation. Are there ways to work with those types, maybe in fragments, to work within the packet limit?

Note: I understand storing data larger than 1MB in a DB column is discouraged for performance and design reasons. I'm just wondering how I'd ever store or retrieve more than 1GB in a TEXT/BLOB if it's ever required.

4
  • Not network packet size, but how MySQL defines a packet: "a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave."
    – jimp
    Mar 26, 2020 at 14:19
  • Misunderstood, sorry about that. Interesting question then indeed.
    – Dave
    Mar 26, 2020 at 14:21
  • Give it a try, as far as i know, it doesn't matter as long as you connection holds it woukd send data and mysql can handle ot, but i never tried such big data. only smaler files. Set the max packet size tio 1 MB and send 4 . It will work. but as i said give it a try
    – nbk
    Mar 26, 2020 at 17:28
  • There are many restrictions and practical limitations. I would not try to get anywhere near 1GB.
    – Rick James
    Apr 4, 2020 at 15:26

1 Answer 1

1

In this MySQL bug report a member of the verification team responds and says:

this problem is easily solved by using chunks with prepared statements. Some of our client programs do support this feature.

So as a naive chunking solution we could try something like:

INSERT INTO table1 (bigcol) VALUES (@chunk1); 
SET @id := last_insert_id();
UPDATE table1 SET bigcol = concat(bigcol, @chunk2) WHERE id = @id;
UPDATE table1 SET bigcol = concat(bigcol, @chunk3) WHERE id = @id;
UPDATE table1 SET bigcol = concat(bigcol, @chunk4) WHERE id = @id;

I've tested this with a smaller value, 1024, for max_allowed_packet and the same size for each chunk. It doesn't work, at least not if you use the mariadb command-line client, so presumably the same with the mysql client. Each UPDATE fails with:

ERROR 1301 (HY000): Result of concat() was larger than max_allowed_packet (1024) - truncated

It makes no difference if we use prepared statements.

However, if using the C API, there is a method mysql_stmt_send_long_data(), documented by both MySQL and MariaDB, which can supposedly chunk the data for you and avoid the limit.

So, there you have it - there is a way to do this, but it's not supported by the command-line clients.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.