mailman-web migrate fails with sqlite3.IntegrityError: UNIQUE constraint failed: account_emailaddress.email
Dear Users,
trying to update mailman3 to lastest version, we fail during
mailman-web migrate with:
Operations to perform: Apply all migrations: account, admin, auth, contenttypes, django_mailman3, django_q, hyperkitty, postorius, sessions, sites, socialaccount Running migrations: Applying account.0006_emailaddress_lower...Traceback (most recent call last): File "/opt/mailman/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute return self.cursor.execute(sql, params) File "/opt/mailman/venv/lib/python3.10/site-packages/django/db/backends/sqlite3/base.py", line 328, in execute return super().execute(query, params) sqlite3.IntegrityError: UNIQUE constraint failed: account_emailaddress.email
We found a workaround to fake the migration with
migrate --fake Operations to perform:
Apply all migrations: account, admin, auth, contenttypes, django_mailman3, django_q, hyperkitty, postorius, sessions, sites, socialaccount
Running migrations:
Applying account.0006_emailaddress_lower... FAKED
Applying account.0007_emailaddress_idx_email... FAKED
Applying account.0008_emailaddress_unique_primary_email_fixup... FAKED
Applying account.0009_emailaddress_unique_primary_email... FAKED
Applying django_q.0015_alter_schedule_schedule_type... FAKED
Applying django_q.0016_schedule_intended_date_kwarg... FAKED
Applying django_q.0017_task_cluster_alter... FAKED
Applying django_q.0018_task_success_index... FAKED
Applying postorius.0019_alter_emailtemplate_data... FAKED
And mailman 3 is still processing mails. I'm unsure if i should recover from snapshot or keep it going?
Could this be some duplicate users with same mail-address?
I have at least spotted one user and delete him.
thank you.
Stefan
On 3/3/25 07:52, Stefan Bauer via Mailman-users wrote:
trying to update mailman3 to lastest version, we fail during
mailman-web migrate with:
Operations to perform: Apply all migrations: account, admin, auth, contenttypes, django_mailman3, django_q, hyperkitty, postorius, sessions, sites, socialaccount Running migrations: Applying account.0006_emailaddress_lower...Traceback (most recent call last): File "/opt/mailman/venv/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute return self.cursor.execute(sql, params) File "/opt/mailman/venv/lib/python3.10/site-packages/django/db/backends/sqlite3/base.py", line 328, in execute return super().execute(query, params) sqlite3.IntegrityError: UNIQUE constraint failed: account_emailaddress.email
This issue is due to changes in Django allauth to prevent creating multiple addresses differing only in case. The problem is if you have such multiple addresses in your account_emailaddress table, this migration fails. There is a script at https://www.msapiro.net/scripts/UC_fix that will fix this. You may need to adjust the shebang and replace psycopg2 with sqlite3.
See https://github.com/pennersr/django-allauth/issues/3019 perhaps starting at https://github.com/pennersr/django-allauth/issues/3019#issuecomment-17480282...
We found a workaround to fake the migration with
migrate --fake
You should fix the issue with the DB and run the migrations.
Could this be some duplicate users with same mail-address?
This is not related to Mailman users. It is Django users.
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
Dear Mark,
thank you for your time.
After patching your script according to https://github.com/maxking/docker-mailman/issues/729#issuecomment-2447542238
It fails with
/opt/mailman/venv/data# /usr/local/bin/UC_fix /opt/mailman/venv/data/mailman3web.db Traceback (most recent call last): File "/usr/local/bin/UC_fix", line 77, in <module> c.execute("delete from account_emailaddress where email = %s;", sqlite3.OperationalError: near "%": syntax error
for reference:
67 for id, emails in d.items(): 68 ems = find(emails) 69 if ems: 70 em1, em2 = ems 71 if em1.lower() == em1: 72 c.execute("delete from account_emailaddress where email = %s;", 73 [em2]) 74 c.execute("""update account_emailaddress set "primary" = 't' 75 where email = %s;""", [em1]) 76 elif em2.lower() == em2: 77 c.execute("delete from account_emailaddress where email = %s;", 78 [em1]) 79 c.execute("""update account_emailaddress set "primary" = 't' 80 where email = %s;""", [em2]) 81 else: 82 print(f'No LC email for {ems}')
However the sql connection itself works, and the select also reports addresses:
sqlite> select email, user_id from account_emailaddress;
... John.Doe@my.domain|85 john.doe@my.domain|85 ..... Mike.Doe@my.domain|96 mike.doe@my.domain|96 ....
Is there something wrong on my side?
Thank you.
Stefan
Am Mo., 3. März 2025 um 18:40 Uhr schrieb Mark Sapiro <mark@msapiro.net>:
On 3/3/25 07:52, Stefan Bauer via Mailman-users wrote:
trying to update mailman3 to lastest version, we fail during
mailman-web migrate with:
Operations to perform: Apply all migrations: account, admin, auth, contenttypes,
django_mailman3,
django_q, hyperkitty, postorius, sessions, sites, socialaccount Running migrations: Applying account.0006_emailaddress_lower...Traceback (most recent call last): File
"/opt/mailman/venv/lib/python3.10/site-packages/django/db/backends/utils.py",
line 89, in _execute return self.cursor.execute(sql, params) File
"/opt/mailman/venv/lib/python3.10/site-packages/django/db/backends/sqlite3/base.py",
line 328, in execute return super().execute(query, params) sqlite3.IntegrityError: UNIQUE constraint failed: account_emailaddress.email
This issue is due to changes in Django allauth to prevent creating multiple addresses differing only in case. The problem is if you have such multiple addresses in your account_emailaddress table, this migration fails. There is a script at https://www.msapiro.net/scripts/UC_fix that will fix this. You may need to adjust the shebang and replace psycopg2 with sqlite3.
See https://github.com/pennersr/django-allauth/issues/3019 perhaps starting at
https://github.com/pennersr/django-allauth/issues/3019#issuecomment-17480282...
We found a workaround to fake the migration with
migrate --fake
You should fix the issue with the DB and run the migrations.
Could this be some duplicate users with same mail-address?
This is not related to Mailman users. It is Django users.
-- 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://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/ Archived at: https://lists.mailman3.org/archives/list/mailman-users@mailman3.org/message/...
This message sent to cubewerk@gmail.com
On 3/14/25 3:55 AM, Stefan Bauer via Mailman-users wrote:
Dear Mark,
thank you for your time.
After patching your script according to https://github.com/maxking/docker-mailman/issues/729#issuecomment-2447542238
It fails with
/opt/mailman/venv/data# /usr/local/bin/UC_fix /opt/mailman/venv/data/mailman3web.db Traceback (most recent call last): File "/usr/local/bin/UC_fix", line 77, in <module> c.execute("delete from account_emailaddress where email = %s;", sqlite3.OperationalError: near "%": syntax error
That patch still assumes the database is PostgreSQL. You need to replace
import psycopg2
with
import sqlite3
and
db = psycopg2.connect(...)
with
db = sqlite3.connect(...)
but it appears you've done that.
It appears that the sqlite3 module doesn't accept the '... %s', value' syntax. See https://docs.python.org/3/library/sqlite3.html
You need to change things like
c.execute("""update account_emailaddress set "primary" = 't'
where email = %s;""", [em1])
to
c.execute("""update account_emailaddress set "primary" = 't'
where email = %s;""" % em1)
-- Mark Sapiro <mark@msapiro.net> The highway is for gamblers, San Francisco Bay Area, California better use your sense - B. Dylan
I ran this query from the "sqlite3" CLI:
SELECT email FROM account_emailaddress GROUP BY lower(email) HAVING COUNT(*) > 1
And to inspect the differences:
SELECT * FROM account_emailaddress WHERE lower(email) IN( SELECT lower(email) FROM account_emailaddress GROUP BY lower(email) HAVING COUNT(*) > 1) ORDER BY email;
".schema account_emailaddress" did show the table structure
Check all the data:
SELECT "id", "verified", "primary", "user_id", "auth_user", "email" FROM account_emailaddress WHERE lower(email) IN( SELECT lower(email) FROM account_emailaddress GROUP BY lower(email) HAVING COUNT(*) > 1) ORDER BY lower(email);
Clean the non lower case accounts found: DELETE FROM account_emailaddress WHERE id IN (xxx,xxx,xxx,xxx);
Then apt dist-upgrade could finish it's work.
participants (3)
-
Mark Sapiro -
Stefan Bauer -
William Desportes