GNU Mailman

Lists cannot be deleted if the have acceptable alias records

Bug #1432239 reported by Andrew Stuart
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
GNU Mailman
Fix Released
High
Barry Warsaw
GNU Mailman 3.0.0 "Show Don't Tell"

Bug Description

Hi Andrew,

Actually you have suspected right. Before removing the mailing_list record, we need to remove all records from `acceptablealias` table. We can either use a `MailingListDeleteEvent` event or a new `__delete__` method in `MailingList` model.

The error is caused because deleting domains deletes all the mailing list associated with it, and since acceptablealias.mailing_list_id is set to `nullable=False` the deletion of a list is not possible.

@barry: this looks like a bug to me, what do you say?

Abhilash

On 5 Mar 2015, at 11:16 am, Andrew Stuart <email address hidden> wrote:

Hi Abhilash

Requesting your help - do you have some time to help me with this one please? You seem to know a fair bit about the database and sqlalchemy.

Do you have any guesses as to what is going on here? It’s becoming quite a problem for me because the database in its stuck state won’t complete my test suite.

thanks

Andrew

On 2 Mar 2015, at 7:41 am, Andrew Stuart <email address hidden> wrote:

After I run my tests, the Mailman database seems to get into a state in which it is not possible to delete my test mailing list and test domain.

I figure it is something to do with the database state but I can’t see what at this stage.

#########################################################################################################
###################################Attempting to delete a domain gives sqlalchemy error
#########################################################################################################

curl -X DELETE --verbose --header "authorization: Basic cmVzdGFkbWluOnJlc3RwYXNz" http://localhost:8001/3.0/domains/mail.example.org

Traceback (most recent call last):
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 951, in _execute_context
 context)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/default.py", line 436, in do_execute
 cursor.execute(statement, parameters)
sqlite3.IntegrityError: NOT NULL constraint failed: acceptablealias.mailing_list_id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/usr/lib/python3.4/wsgiref/handlers.py", line 137, in run
 self.result = application(self.environ, self.start_response)
File "/home/ubuntu/mailman/src/mailman/database/transaction.py", line 57, in wrapper
 config.db.commit()
File "/home/ubuntu/mailman/src/mailman/database/base.py", line 56, in commit
 self.store.commit()
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 776, in commit
 self.transaction.commit()
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 377, in commit
 self._prepare_impl()
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 357, in _prepare_impl
 self.session.flush()
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 1919, in flush
 self._flush(objects)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 2037, in _flush
 transaction.rollback(_capture_exception=True)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
 compat.reraise(exc_type, exc_value, exc_tb)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 182, in reraise
 raise value
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 2001, in _flush
 flush_context.execute()
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/unitofwork.py", line 372, in execute
 rec.execute(self)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/unitofwork.py", line 526, in execute
 uow
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/persistence.py", line 60, in save_obj
 mapper, table, update)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/persistence.py", line 518, in _emit_update_statements
 execute(statement, params)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 729, in execute
 return meth(self, multiparams, params)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
 return connection._execute_clauseelement(self, multiparams, params)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
 compiled_sql, distilled_params
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 958, in _execute_context
 context)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
 exc_info
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 188, in raise_from_cause
 reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 181, in reraise
 raise value.with_traceback(tb)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 951, in _execute_context
 context)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/default.py", line 436, in do_execute
 cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (IntegrityError) NOT NULL constraint failed: acceptablealias.mailing_list_id 'UPDATE acceptablealias SET mailing_list_id=? WHERE acceptablealias.id = ?' (None, 1)

#########################################################################################################
###################################Attempting to delete a mailing list gives sqlalchemy error
#########################################################################################################

curl -X DELETE --verbose --header "authorization: Basic cmVzdGFkbWluOnJlc3RwYXNz" http://localhost:8001/3.0/lists/testlist1.mail.example.org

Traceback (most recent call last):
File "/usr/lib/python3.4/wsgiref/handlers.py", line 137, in run
 self.result = application(self.environ, self.start_response)
File "/home/ubuntu/mailman/src/mailman/database/transaction.py", line 56, in wrapper
 rtn = function(*args, **kws)
File "/home/ubuntu/mailman/src/mailman/rest/wsgiapp.py", line 62, in __call__
 environ, start_response)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/falcon/api.py", line 175, in __call__
 responder(req, resp, **params)
File "/home/ubuntu/mailman/src/mailman/rest/lists.py", line 145, in on_delete
 remove_list(self._mlist)
File "/home/ubuntu/mailman/src/mailman/app/lifecycle.py", line 101, in remove_list
 call_name(config.mta.incoming).delete(mlist)
File "/home/ubuntu/mailman/src/mailman/mta/postfix.py", line 69, in create
 self.regenerate()
File "/home/ubuntu/mailman/src/mailman/mta/postfix.py", line 83, in regenerate
 self._generate_lmtp_file(fp)
File "/home/ubuntu/mailman/src/mailman/mta/postfix.py", line 116, in _generate_lmtp_file
 for list_name, mail_host in list_manager.name_components:
File "/home/ubuntu/mailman/src/mailman/model/listmanager.py", line 119, in name_components
 MailingList.list_name):
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/query.py", line 1011, in values
 return iter(q)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/query.py", line 2437, in __iter__
 self.session._autoflush()
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 1208, in _autoflush
 util.raise_from_cause(e)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 188, in raise_from_cause
 reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 182, in reraise
 raise value
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 1198, in _autoflush
 self.flush()
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 1919, in flush
 self._flush(objects)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 2037, in _flush
 transaction.rollback(_capture_exception=True)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/langhelpers.py", line 60, in __exit__
 compat.reraise(exc_type, exc_value, exc_tb)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 182, in reraise
 raise value
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/session.py", line 2001, in _flush
 flush_context.execute()
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/unitofwork.py", line 372, in execute
 rec.execute(self)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/unitofwork.py", line 526, in execute
 uow
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/persistence.py", line 60, in save_obj
 mapper, table, update)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/orm/persistence.py", line 518, in _emit_update_statements
 execute(statement, params)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 729, in execute
 return meth(self, multiparams, params)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
 return connection._execute_clauseelement(self, multiparams, params)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
 compiled_sql, distilled_params
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 958, in _execute_context
 context)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
 exc_info
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 188, in raise_from_cause
 reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/util/compat.py", line 181, in reraise
 raise value.with_traceback(tb)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/base.py", line 951, in _execute_context
 context)
File "/home/ubuntu/venv3.4/lib/python3.4/site-packages/SQLAlchemy-0.9.8-py3.4.egg/sqlalchemy/engine/default.py", line 436, in do_execute
 cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (IntegrityError) NOT NULL constraint failed: acceptablealias.mailing_list_id 'UPDATE acceptablealias SET mailing_list_id=? WHERE acceptablealias.id = ?' (None, 1)

#########################################################################################################
This is the database at the time of the errors - note I have had to remove an accumulation of UID and preferences INSERTS.
#########################################################################################################

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE pended (
 id INTEGER NOT NULL,
 token VARCHAR,
 expiration_date DATETIME,
 PRIMARY KEY (id)
);
CREATE TABLE preferences (
 id INTEGER NOT NULL,
 acknowledge_posts BOOLEAN,
 hide_address BOOLEAN,
 preferred_language VARCHAR,
 receive_list_copy BOOLEAN,
 receive_own_postings BOOLEAN,
 delivery_mode INTEGER,
 delivery_status INTEGER,
 PRIMARY KEY (id),
 CHECK (acknowledge_posts IN (0, 1)),
 CHECK (hide_address IN (0, 1)),
 CHECK (receive_list_copy IN (0, 1)),
 CHECK (receive_own_postings IN (0, 1))
);
CREATE TABLE domain (
 id INTEGER NOT NULL,
 mail_host VARCHAR,
 base_url VARCHAR,
 description VARCHAR,
 contact_address VARCHAR,
 PRIMARY KEY (id)
);
INSERT INTO "domain" VALUES(1,'mail.example.org','http://mail.example.org',NULL,'<email address hidden>');
CREATE TABLE message (
 id INTEGER NOT NULL,
 message_id VARCHAR,
 message_id_hash VARCHAR,
 path VARCHAR,
 PRIMARY KEY (id)
);
CREATE TABLE mailinglist (
 id INTEGER NOT NULL,
 list_name VARCHAR,
 mail_host VARCHAR,
 list_id VARCHAR,
 allow_list_posts BOOLEAN,
 include_rfc2369_headers BOOLEAN,
 advertised BOOLEAN,
 anonymous_list BOOLEAN,
 created_at DATETIME,
 next_request_id INTEGER,
 next_digest_number INTEGER,
 digest_last_sent_at DATETIME,
 volume INTEGER,
 last_post_at DATETIME,
 accept_these_nonmembers BLOB,
 admin_immed_notify BOOLEAN,
 admin_notify_mchanges BOOLEAN,
 administrivia BOOLEAN,
 archive_policy INTEGER,
 autoresponse_grace_period DATETIME,
 autorespond_owner INTEGER,
 autoresponse_owner_text VARCHAR,
 autorespond_postings INTEGER,
 autoresponse_postings_text VARCHAR,
 autorespond_requests INTEGER,
 autoresponse_request_text VARCHAR,
 filter_action INTEGER,
 filter_content BOOLEAN,
 collapse_alternatives BOOLEAN,
 convert_html_to_plaintext BOOLEAN,
 bounce_info_stale_after DATETIME,
 bounce_matching_headers VARCHAR,
 bounce_notify_owner_on_disable BOOLEAN,
 bounce_notify_owner_on_removal BOOLEAN,
 bounce_score_threshold INTEGER,
 bounce_you_are_disabled_warnings INTEGER,
 bounce_you_are_disabled_warnings_interval DATETIME,
 forward_unrecognized_bounces_to INTEGER,
 process_bounces BOOLEAN,
 default_member_action INTEGER,
 default_nonmember_action INTEGER,
 description VARCHAR,
 digest_footer_uri VARCHAR,
 digest_header_uri VARCHAR,
 digest_is_default BOOLEAN,
 digest_send_periodic BOOLEAN,
 digest_size_threshold FLOAT,
 digest_volume_frequency INTEGER,
 digestable BOOLEAN,
 discard_these_nonmembers BLOB,
 emergency BOOLEAN,
 encode_ascii_prefixes BOOLEAN,
 first_strip_reply_to BOOLEAN,
 footer_uri VARCHAR,
 forward_auto_discards BOOLEAN,
 gateway_to_mail BOOLEAN,
 gateway_to_news BOOLEAN,
 goodbye_message_uri VARCHAR,
 header_matches BLOB,
 header_uri VARCHAR,
 hold_these_nonmembers BLOB,
 info VARCHAR,
 linked_newsgroup VARCHAR,
 max_days_to_hold INTEGER,
 max_message_size INTEGER,
 max_num_recipients INTEGER,
 member_moderation_notice VARCHAR,
 mime_is_default_digest BOOLEAN,
 moderator_password BLOB,
 newsgroup_moderation INTEGER,
 nntp_prefix_subject_too BOOLEAN,
 nondigestable BOOLEAN,
 nonmember_rejection_notice VARCHAR,
 obscure_addresses BOOLEAN,
 owner_chain VARCHAR,
 owner_pipeline VARCHAR,
 personalize INTEGER,
 post_id INTEGER,
 posting_chain VARCHAR,
 posting_pipeline VARCHAR,
 preferred_language VARCHAR,
 display_name VARCHAR,
 reject_these_nonmembers BLOB,
 reply_goes_to_list INTEGER,
 reply_to_address VARCHAR,
 require_explicit_destination BOOLEAN,
 respond_to_post_requests BOOLEAN,
 scrub_nondigest BOOLEAN,
 send_goodbye_message BOOLEAN,
 send_welcome_message BOOLEAN,
 subject_prefix VARCHAR,
 topics BLOB,
 topics_bodylines_limit INTEGER,
 topics_enabled BOOLEAN,
 welcome_message_uri VARCHAR,
 PRIMARY KEY (id),
 CHECK (allow_list_posts IN (0, 1)),
 CHECK (include_rfc2369_headers IN (0, 1)),
 CHECK (advertised IN (0, 1)),
 CHECK (anonymous_list IN (0, 1)),
 CHECK (admin_immed_notify IN (0, 1)),
 CHECK (admin_notify_mchanges IN (0, 1)),
 CHECK (administrivia IN (0, 1)),
 CHECK (filter_content IN (0, 1)),
 CHECK (collapse_alternatives IN (0, 1)),
 CHECK (convert_html_to_plaintext IN (0, 1)),
 CHECK (bounce_notify_owner_on_disable IN (0, 1)),
 CHECK (bounce_notify_owner_on_removal IN (0, 1)),
 CHECK (process_bounces IN (0, 1)),
 CHECK (digest_is_default IN (0, 1)),
 CHECK (digest_send_periodic IN (0, 1)),
 CHECK (digestable IN (0, 1)),
 CHECK (emergency IN (0, 1)),
 CHECK (encode_ascii_prefixes IN (0, 1)),
 CHECK (first_strip_reply_to IN (0, 1)),
 CHECK (forward_auto_discards IN (0, 1)),
 CHECK (gateway_to_mail IN (0, 1)),
 CHECK (gateway_to_news IN (0, 1)),
 CHECK (mime_is_default_digest IN (0, 1)),
 CHECK (nntp_prefix_subject_too IN (0, 1)),
 CHECK (nondigestable IN (0, 1)),
 CHECK (obscure_addresses IN (0, 1)),
 CHECK (require_explicit_destination IN (0, 1)),
 CHECK (respond_to_post_requests IN (0, 1)),
 CHECK (scrub_nondigest IN (0, 1)),
 CHECK (send_goodbye_message IN (0, 1)),
 CHECK (send_welcome_message IN (0, 1)),
 CHECK (topics_enabled IN (0, 1))
);
INSERT INTO "mailinglist" VALUES(1,'testlist1','mail.example.org','testlist1.mail.example.org',0,0,0,1,'2015-03-01 20:04:21.186669',1,1,NULL,1,NULL,X'80049503000000000000005D942E',0,1,0,0,'1970-02-15 00:00:00.000000',1,'the owner',2,'the mailing list',1,'the robot',2,1,0,1,'1970-01-08 00:00:00.000000','
# Lines that *start* with a ''#'' are comments.
to: <email address hidden>
message-id: relay.comanche.denmark.eu
from: <email address hidden>
from: .*@uplinkpro.com
',1,1,5,3,'1970-01-08 00:00:00.000000',1,1,0,2,'This is my mailing list','mailman:///$listname/$language/footer-generic.txt',NULL,0,1,10.5,1,1,X'80049503000000000000005D942E',0,0,1,'mailman:///$listname/$language/footer-generic.txt',1,0,0,'',X'80049503000000000000005D942E',NULL,X'80049503000000000000005D942E','','',0,40,10,'',0,NULL,0,1,1,'',1,'default-owner-chain','default-owner-pipeline',0,1,'default-posting-chain','virgin','en','Fnords',X'80049503000000000000005D942E',1,'<email address hidden>',1,1,0,1,0,'[ant]',X'80049503000000000000005D942E',5,0,'mailman:///welcome.txt');
CREATE TABLE bounceevent (
 id INTEGER NOT NULL,
 list_id VARCHAR,
 email VARCHAR,
 timestamp DATETIME,
 message_id VARCHAR,
 context INTEGER,
 processed BOOLEAN,
 PRIMARY KEY (id),
 CHECK (processed IN (0, 1))
);
INSERT INTO "bounceevent" VALUES(1,'testlist1.mail.example.org','<email address hidden>','2015-03-01 20:01:54.950333','<email address hidden>',1,0);
INSERT INTO "bounceevent" VALUES(2,'testlist1.mail.example.org','<email address hidden>','2015-03-01 20:01:56.083932','<email address hidden>',1,0);
INSERT INTO "bounceevent" VALUES(3,'testlist1.mail.example.org','<email address hidden>','2015-03-01 20:02:20.319257','<email address hidden>',1,0);
INSERT INTO "bounceevent" VALUES(4,'testlist1.mail.example.org','<email address hidden>','2015-03-01 20:03:14.618731','<email address hidden>',1,0);
CREATE TABLE uid (
 id INTEGER NOT NULL,
 uid CHAR(32),
 PRIMARY KEY (id)
);
CREATE TABLE ban (
 id INTEGER NOT NULL,
 email VARCHAR,
 list_id VARCHAR,
 PRIMARY KEY (id)
);
CREATE TABLE user (
 id INTEGER NOT NULL,
 display_name VARCHAR,
 password VARCHAR,
 _user_id CHAR(32),
 _created_on DATETIME,
 _preferred_address_id INTEGER,
 preferences_id INTEGER,
 PRIMARY KEY (id),
 FOREIGN KEY(preferences_id) REFERENCES preferences (id)
);
INSERT INTO "user" VALUES(1,'Server Owner','$6$rounds=101751$cOyechNgTQqeyfTJ$2lHEhtTCYTUuQ37CkZ/ncyptVRW3I9T61WxW8BV6sbzMa2KDJBoU6uRpiWyowbtVej.QTZnL9Ww0YXXM3lPdb/','4e0a784aeaa84b37b0e731aef00683aa','2015-03-01 20:00:41.531120',NULL,2);
INSERT INTO "user" VALUES(2,'test user 1','$6$rounds=90188$eLE.L0U2hfbpa9QG$SKzS7PNvGiEvPjKSTDjG5PgDtIvsjARyt6HOtia24zT3eT3w99TCuXlL2ZrL.t7B3xLxX4PuWTolrjd1lZ6Qg.','23856abb3f414114b9cfde92d9557ecb','2015-03-01 20:00:58.143566',NULL,34);
INSERT INTO "user" VALUES(4,'','$6$rounds=94146$ydwCyzFScrDi8mJt$Ia4w6zMnEdePdWvm3vuBrn8d0MpWstdmN0mLtImqJ3k/aLaEkZShgw.0aZU4Q7eh1sTuxUAtY7SLkSOWYj0RN.','c5e5dfd8a11e439a868cba6bdedc966e','2015-03-01 20:01:20.944564',NULL,92);
CREATE TABLE listarchiver (
 id INTEGER NOT NULL,
 mailing_list_id INTEGER NOT NULL,
 name VARCHAR NOT NULL,
 _is_enabled BOOLEAN,
 PRIMARY KEY (id),
 FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id),
 CHECK (_is_enabled IN (0, 1))
);
INSERT INTO "listarchiver" VALUES(1,1,'prototype',1);
INSERT INTO "listarchiver" VALUES(2,1,'mail-archive',1);
INSERT INTO "listarchiver" VALUES(3,1,'mhonarc',1);
CREATE TABLE acceptablealias (
 id INTEGER NOT NULL,
 mailing_list_id INTEGER NOT NULL,
 alias VARCHAR NOT NULL,
 PRIMARY KEY (id),
 FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
);
INSERT INTO "acceptablealias" VALUES(1,1,'<email address hidden>');
INSERT INTO "acceptablealias" VALUES(2,1,'<email address hidden>');
CREATE TABLE pendedkeyvalue (
 id INTEGER NOT NULL,
 "key" VARCHAR,
 value VARCHAR,
 pended_id INTEGER,
 PRIMARY KEY (id),
 FOREIGN KEY(pended_id) REFERENCES pended (id)
);
CREATE TABLE contentfilter (
 id INTEGER NOT NULL,
 mailing_list_id INTEGER,
 filter_type INTEGER,
 filter_pattern VARCHAR,
 PRIMARY KEY (id),
 FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
);
CREATE TABLE _request (
 id INTEGER NOT NULL,
 "key" VARCHAR,
 request_type INTEGER,
 data_hash VARCHAR,
 mailing_list_id INTEGER,
 PRIMARY KEY (id),
 FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
);
CREATE TABLE address (
 id INTEGER NOT NULL,
 email VARCHAR,
 _original VARCHAR,
 display_name VARCHAR,
 verified_on DATETIME,
 registered_on DATETIME,
 user_id INTEGER,
 preferences_id INTEGER,
 PRIMARY KEY (id),
 FOREIGN KEY(user_id) REFERENCES user (id),
 FOREIGN KEY(preferences_id) REFERENCES preferences (id)
);
INSERT INTO "address" VALUES(1,'<email address hidden>',NULL,'Server Owner',NULL,'2015-03-01 20:00:41.530793',1,1);
CREATE TABLE autoresponserecord (
 id INTEGER NOT NULL,
 address_id INTEGER,
 mailing_list_id INTEGER,
 response_type INTEGER,
 date_sent DATE,
 PRIMARY KEY (id),
 FOREIGN KEY(address_id) REFERENCES address (id),
 FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id)
);
CREATE TABLE onelastdigest (
 id INTEGER NOT NULL,
 mailing_list_id INTEGER,
 address_id INTEGER,
 delivery_mode INTEGER,
 PRIMARY KEY (id),
 FOREIGN KEY(mailing_list_id) REFERENCES mailinglist (id),
 FOREIGN KEY(address_id) REFERENCES address (id)
);
CREATE TABLE member (
 id INTEGER NOT NULL,
 _member_id CHAR(32),
 role INTEGER,
 list_id VARCHAR,
 moderation_action INTEGER,
 address_id INTEGER,
 preferences_id INTEGER,
 user_id INTEGER,
 PRIMARY KEY (id),
 FOREIGN KEY(address_id) REFERENCES address (id),
 FOREIGN KEY(preferences_id) REFERENCES preferences (id),
 FOREIGN KEY(user_id) REFERENCES user (id)
);
CREATE TABLE alembic_version (
 version_num VARCHAR(32) NOT NULL
);
INSERT INTO "alembic_version" VALUES('33e1f5f6fa8');
CREATE INDEX ix_uid_uid ON uid (uid);
CREATE INDEX ix_user_preferences_id ON user (preferences_id);
CREATE INDEX ix_user__user_id ON user (_user_id);
CREATE INDEX ix_listarchiver_mailing_list_id ON listarchiver (mailing_list_id);
CREATE INDEX ix_acceptablealias_mailing_list_id ON acceptablealias (mailing_list_id);
CREATE INDEX ix_acceptablealias_alias ON acceptablealias (alias);
CREATE INDEX ix_pendedkeyvalue_pended_id ON pendedkeyvalue (pended_id);
CREATE INDEX ix_contentfilter_mailing_list_id ON contentfilter (mailing_list_id);
CREATE INDEX ix__request_mailing_list_id ON _request (mailing_list_id);
CREATE INDEX ix_address_user_id ON address (user_id);
CREATE INDEX ix_address_preferences_id ON address (preferences_id);
CREATE INDEX ix_autoresponserecord_address_id ON autoresponserecord (address_id);
CREATE INDEX ix_autoresponserecord_mailing_list_id ON autoresponserecord (mailing_list_id);
COMMIT;

Andrew Stuart (andrew-stuart)
tags: added: mailman3
Revision history for this message
Barry Warsaw (barry) wrote :

I think this is a real bug. I think you're right that a new ListDeletingEvent should be added to clear out any aliases for the mailing list. I actually tried to write a test for this in src/mailman/model/tests/test_mailinglist.py but the AceptableAliasSet.add() method doesn't seem to add the record to the acceptablealias table.

class TestAcceptableAliases(unittest.TestCase):
    layer = ConfigLayer

    def setUp(self):
        self._mlist = create_list('<email address hidden>')

    def test_delete_list_with_acceptable_aliases(self):
        # LP: #1432239 - deleting a mailing list with acceptable aliases
        # causes a SQLAlchemy error. The aliases must be deleted first.
        alias_set = IAcceptableAliasSet(self._mlist)
        alias_set.add('<email address hidden>')
        self.assertEqual(['<email address hidden>'],
                         [alias.alias for alias in alias_set.aliases])
        getUtility(IListManager).delete(self._mlist)
        self.assertEqual(len(alias.alias for alias in alias_set.aliases), 0)

$ .tox/py34/bin/python -m nose2 -P test_delete_list_with_acceptable_aliases
F
======================================================================
FAIL: test_delete_list_with_acceptable_aliases (mailman.model.tests.test_mailinglist.TestAcceptableAliases)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/barry/projects/mailman/lp1432239/src/mailman/model/tests/test_mailinglist.py", line 162, in test_delete_list_with_acceptable_aliases
    [alias.alias for alias in alias_set.aliases])
AssertionError: Lists differ: ['<email address hidden>'] != []

First list contains 1 additional elements.
First extra element 0:
<email address hidden>

- ['<email address hidden>']
+ []

----------------------------------------------------------------------
Ran 1 test in 0.173s

FAILED (failures=1)

I'd rather not add an explicit commit here because IIUC, it isn't necessary in other model layer tests. I think if the test can be made to fail, the fix should be easy.

Changed in mailman:
status: New → Triaged
milestone: none → 3.0.0b6
Revision history for this message
Barry Warsaw (barry) wrote :

Just to follow up: this does need to be fixed in the model. The REST failure is just a byproduct. And remember that the REST tests must commit changes to the database because the test and the REST server run in different processes.

Abhilash Raj (raj-abhilash1)
tags: added: mailman3-suite-blocker
Barry Warsaw (barry)
Changed in mailman:
assignee: nobody → Barry Warsaw (barry)
importance: Undecided → High
status: Triaged → In Progress
Barry Warsaw (barry)
Changed in mailman:
status: In Progress → Fix Committed
Barry Warsaw (barry)
Changed in mailman:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.