Hi,
Part of the issue is that the old tables were latin1. I found the command to check this:
SELECT T.table_name, CCSA.character_set_name FROM
information_schema.TABLES
T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE
CCSA.collation_name = T.table_collation
AND T.table_schema = "mailman3";
It will display something like this:
+--------------------+--------------------+ | table_name | character_set_name | +--------------------+--------------------+ | pendedkeyvalue | latin1 | | alembic_version | utf8mb4 | | acceptablealias | utf8mb4 | | address | utf8mb4 | | autoresponserecord | utf8mb4 | | ban | utf8mb4 | | bounceevent | utf8mb4 | | domain | utf8mb4 | | file_cache | utf8mb4 | | template | utf8mb4 | | mailinglist | utf8mb4 | | user | utf8mb4 | | contentfilter | utf8mb4 | | domain_owner | utf8mb4 | | uid | utf8mb4 | | member | utf8mb4 | | _request | utf8mb4 | | headermatch | utf8mb4 | | preferences | utf8mb4 | | workflowstate | utf8mb4 | | listarchiver | utf8mb4 | | message | utf8mb4 | | onelastdigest | utf8mb4 | | pended | utf8mb4 | +--------------------+--------------------+
And the command to convert the table is this:
ALTER TABLE pendedkeyvalue CONVERT TO CHARACTER SET utf8mb4;
And it is better to be done with mailman3 down or you will get DB access issues and not complete the change.
Problem is... all the tables changed except pendedkeyvalue.
MariaDB [mailman3]> ALTER TABLE pendedkeyvalue CONVERT TO CHARACTER SET utf8mb4; ERROR 1074 (42000): Column length too big for column 'value' (max = 16383); use BLOB or TEXT instead
mysql/mariadb allows only a certain amount of characters per entry in the table, moving to utf8mb4 with 4 bytes is taking more than what is permitted.
So the question is whether it is possible to change the types in this table.
MariaDB [mailman3]> desc pendedkeyvalue; +-----------+----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | key | varchar(255) | YES | MUL | NULL | | | value | varchar(20000) | YES | MUL | NULL | | | pended_id | int(11) | YES | MUL | NULL | | +-----------+----------------+------+-----+---------+----------------+ 4 rows in set (0.001 sec)
key and value are varchar. Can any of them be changed to text instead? Perhaps even both?
Best Regards, Helio Loureiro https://helio.loureiro.eng.br https://github.com/helioloureiro https://mastodon.social/@helioloureiro