$ /opt/mailman/mm/bin/mailman version GNU Mailman 3.3.2 (Tom Sawyer)

So, I'm running > 3.2. Any other advice on debugging the truncated data?

Bryan Loniewski Rutgers University OIT:EAS:Messaging and Collaboration Services System Administrator

From: Mark Sapiro <mark@msapiro.net> Sent: Monday, August 2, 2021 10:06 PM To: mailman-users@mailman3.org Subject: [MM3-users] Re: UnpicklingError on mailman lists command

On 8/2/21 4:15 PM, Bryan Loniewski wrote:

Hi all --

$ /opt/mailman/mm/bin/mailman lists Traceback (most recent call last): ... File "/opt/mailman/venv/lib/python3.6/site-packages/mailman/commands/cli_lists.py", line 78, in lists for mlist in list_manager.mailing_lists: File "/opt/mailman/venv/lib/python3.6/site-packages/mailman/model/listmanager.py", line 100, in mailing_lists MailingList._list_id).all() File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3246, in all return list(self) File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 101, in instances cursor.close() File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__ exc_value, with_traceback=exc_tb, File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_ raise exception File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 81, in instances rows = [proc(row) for row in fetch] File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 81, in <listcomp> rows = [proc(row) for row in fetch] File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 589, in _instance populators, File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 726, in _populate_full dict_[key] = getter(row) File "/opt/mailman/venv/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py", line 1708, in process return loads(value) _pickle.UnpicklingError: pickle data was truncated

This is probably https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgitlab.com... which was fixed in Mailman 3.2.0 by https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgitlab.com....

What version are you running? If your version is 3.2.0 or greater, it could be one of the other issues with MySQL/MariaDB truncating data.

-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan

Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.mail...

Okay, that helped identify possible culprits. Three of our lists are hitting/over the max BLOB length (65,535 bytes) for accept_these_nonmembers. I executed this SQL command for each of the BLOB cols you referenced and only accept_these_nonmembers is borked:

MySQL [mailman]> SELECT list_name, OCTET_LENGTH(accept_these_nonmembers) as howManyBytes from mailinglist where OCTET_LENGTH(accept_these_nonmembers) > 64000; +---------------+--------------+ | list_name | howManyBytes | +---------------+--------------+ | <snip> | 65535 | | <snip> | 65535 | | <snip> | 65535 | +---------------+--------------+ 3 rows in set (0.01 sec)

So, putting aside *why* these lists are so large wrt that column, can or should I alter the col type(s) to MEDIUMBLOB? If yes, would that remain on suite upgrades? If no, is there a better alternative? Or some recommendation in between?

Bryan Loniewski Rutgers University OIT:EAS:Messaging and Collaboration Services System Administrator

From: Mark Sapiro <mark@msapiro.net> Sent: Tuesday, August 3, 2021 12:15 AM To: mailman-users@mailman3.org Subject: [MM3-users] Re: UnpicklingError on mailman lists command

On 8/2/21 7:35 PM, Bryan Loniewski wrote:

$ /opt/mailman/mm/bin/mailman version GNU Mailman 3.3.2 (Tom Sawyer)

So, I'm running > 3.2. Any other advice on debugging the truncated data?

The traceback indicates sqlalchemy is unpickling data whose type is PickleType. The only mailinglist columns which are PickleType are the legacy *these_nonmembers and topics.

You could try invoking mysql on Mailman's database and doing

SELECT list_name, accept_these_nonmembers, hold_these_nonmembers, reject_these_nonmembers, discard_these_nonmembers, topics FROM mailinglist;

and look for the longest values to possibly identify the problem list and attribute.

-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan

Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.mail...

Okay, so the issue has been fixed by updating the data to an empty list equivalent (after finding, confirming and copying another lists empty value):

MySQL [mailman]> SELECT accept_these_nonmembers INTO @newatn FROM mailinglist WHERE list_name='foo_is_a_good_list_with_emptyval';

MySQL [mailman]> UPDATE mailinglist SET accept_these_nonmembers=@newatn WHERE list_name='the_borked_list'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0

Thanks for the help on troubleshooting that!

Based on your import21 comments, I'm realizing the issue may be caused by multiple runs of import21. I have been (although I've stopped it for some time now) running import21 nightly to sync up my Mailman3 list data with Mailman2. I've been doing the same for archiving as well.

I've noticed now that some lists have duplicate acceptable alias entries (looks to be like <number of aliases> x <number of import21 runs>) and the same goes for *_these_nonmembers entries.

Is this a symptom of running the old(er) import21 command pre-3.3.2? Will the 3.3.2 import21 work as expected w/out the duplications if I was to clear out the appropriate lists col data (i.e., is it okay to run import21 multiple times)? I tried to figure out if there were any other duplicative data, but did not come across any. Is there more that I'm missing?

Bryan Loniewski Rutgers University OIT:EAS:Messaging and Collaboration Services System Administrator

From: Mark Sapiro <mark@msapiro.net> Sent: Tuesday, August 3, 2021 12:28 PM To: mailman-users@mailman3.org Subject: [MM3-users] Re: UnpicklingError on mailman lists command

On 8/3/21 7:35 AM, Bryan Loniewski wrote:

Okay, that helped identify possible culprits. Three of our lists are hitting/over the max BLOB length (65,535 bytes) for accept_these_nonmembers. I executed this SQL command for each of the BLOB cols you referenced and only accept_these_nonmembers is borked:

MySQL [mailman]> SELECT list_name, OCTET_LENGTH(accept_these_nonmembers) as howManyBytes from mailinglist where OCTET_LENGTH(accept_these_nonmembers) > 64000; +---------------+--------------+ | list_name | howManyBytes | +---------------+--------------+ | <snip> | 65535 | | <snip> | 65535 | | <snip> | 65535 | +---------------+--------------+ 3 rows in set (0.01 sec)

So, putting aside *why* these lists are so large wrt that column, can or should I alter the col type(s) to MEDIUMBLOB? If yes, would that remain on suite upgrades? If no, is there a better alternative? Or some recommendation in between?

Modifying the column type at this point will not help because the data has already been truncated and the missing piece is gone. It may help avoid future issues, but I suspect it's not necessary. It would probably 'stick' on an upgrade as long as there were no new migration affecting those columns, but if there were such a migration, it could fail due to the existing type not being as expected.

I suspect these lists were imported from Mailman 2.1 with the mailman import21 command. The goal of that is to import the addresses as non-members with the appropriate action and put only the regexps in the MM 3 *_these_nonmembers. Prior to Mailman 3.3.2, import21 would add the entire 2.1 *_these_nonmembers and then delete the non-regexps. Possibly something went wrong in that process because of the truncation and left the new *_these_nonmembers in a broken state. Or possibly you just have a huge number of regexps in these list's accept_these_nonmembers.

I suggest you use mysql to update this column's data for these three lists. to a value which unpickles as an empty list. This value is the python bytes b'\x80\x04\x5d\x94\x2e' (i.e. 5 bytes with hex values 80, 04, 5d, 94 and 2e). I'm not sure how that is stored in a MySQL BLOB, but I'm sure you can determine that. This will allow you to actually access those lists in Mailman.

Also note that beginning with Postorius 1.3.3, the *_these_nonmembers settings are exposed on the List Settings -> Message Acceptance form.

If you still have the original MM 2.1 config.pck files, you can examine them with Mailman 2.1's bin/dumpdb command to see what was in accept_these_nonmembers, and then see if the appropriate MM 3 nonmembers were created and if the remaining regexps are not too long, add them. If you need help with this, I will help.

-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan

Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.mail...