How can MySQL's data types with 4GB limits be fully used since max_allowed_packet is hard limited to 1GB?

Question

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.

Answer 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.