MySql/MariaDB longtext truncated and max_allowed_packet

517 views Asked by At

I have a problem with MariaDB with a longtext field. It is limited to 32770 chars and there is no way to grow it up.

SELECT CHAR_LENGTH(longtext_column) from table where id = x;

I edited my cnf like this:

[mysqld]
max_allowed_packet     =  1000M

And I confirmed is ok running this query:

enter image description here

Anyway, I run an update query with more than 32770 characters and always truncate the text to 32770.

On the other hand, I red this on the official documentation:

The effective maximum length is less if the value contains multi-byte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory.

Lastly, maybe useful information:

  1. The string I attempt to store is JSON.
  2. I tried on another server with the same result.
  3. I'm running Navicat to run these queries.

What am I missing?

Edit1: I have never got the MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes error.

Edit 2: MySql version 15.1 Distrib 10.3.31-MariaDB

Edit 3: Text for SHOW CREATE TABLE tbl_name command (shortened to the concerning fields):

| table_name | CREATE TABLE `table_name` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `column_in_question` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7498 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

Edit4: I uploaded a MySql script I've using for test de 32KB limitation.

https://pastebin.com/NUw7Q1cE

0

There are 0 answers