search

modoboa / modoboa-amavis

The amavis frontend of Modoboa
https://modoboa.org
MIT License
23 stars 14 forks source link

Quarantine deletion not working with MariaDb 5.5/10.0 and modoboa 1.15.0 #135

Closed dymitar closed 2 years ago

dymitar commented 3 years ago

The manage.py qcleanup command is not deleting quarantined messages. The problem is in the database<->model configuration for table msgs. The table configuration is: | mail_id | varbinary(16) | NO | PRI | NULL | | The model is: mail_id = models.CharField(max_length=12, primary_key=True)

The query returns the data in form {'mail_id': b'00RbMiUxZ0FY'} Which leads to a delete query like: DELETE FROM msgs WHERE msgs.mail_id IN ('b\'0-coPXAvB1If\'') This doesn't find the corresponding record Changing the model to: mail_id = models.BinaryField(max_length=12, primary_key=True) Changes the delete query to: DELETE FROM msgs WHERE msgs.mail_id IN ('0-coPXAvB1If') And everything is working.

The problem is existing in the web GUI as well - in msgs and quarantine tables the records are not deleted. I don't know how this change reflects the other part of the system.

dymitar commented 3 years ago

This change breaks showing the content of quarantined messages.

dymitar commented 3 years ago

Changed the database to mariadb 10, the problem persists. Changing the field type of mail id in some tables in amavis database from varbinary to varchar solves the problem without known side effects.

unikulin commented 3 years ago

Same for me. The manage.py qcleanup command is not deleting quarantined messages. database is MariaDB 10.3 manage.py qcleanup --DEBUG generate this line of code: DELETE FROM msgs WHERE msgs.mail_id IN ('b\'zrFD-gH1QXjF\''); Query OK, 0 rows affected (0.000 sec) but it doesn’t work. the correct syntax should be: DELETE FROM msgs WHERE msgs.mail_id IN ('zrFD-gH1QXjF'); Query OK, 1 row affected (0.004 sec)

unikulin commented 3 years ago

update in model.py msgs.mail_id definition. from mail_id = models.CharField(max_length=12, primary_key=True) to mail_id = models.BinaryField(max_length=16, primary_key=True) max_lenght set to 16, as amavis db schema define this field as "mail_id varbinary(16) NOT NULL"

Now all works as expected. Cleaning up is working

bigwillystyle42 commented 2 years ago

update in model.py msgs.mail_id definition. from mail_id = models.CharField(max_length=12, primary_key=True) to mail_id = models.BinaryField(max_length=16, primary_key=True) max_lenght set to 16, as amavis db schema define this field as "mail_id varbinary(16) NOT NULL"

Now all works as expected. Cleaning up is working

I've made the same change to my installation and it is currently deleting all the emails that have piled up for the past two years. Note that the file name is models.py not model.py.