Lists cannot be deleted if the have acceptable alias records

Bug #1432239 reported by Andrew Stuart on 2015-03-14
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
GNU Mailman
High
Barry Warsaw

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;

tags: added: mailman3
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
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.

tags: added: mailman3-suite-blocker
Barry Warsaw (barry) on 2015-03-25
Changed in mailman:
assignee: nobody → Barry Warsaw (barry)
importance: Undecided → High
status: Triaged → In Progress
Barry Warsaw (barry) on 2015-03-25
Changed in mailman:
status: In Progress → Fix Committed
Barry Warsaw (barry) on 2015-04-28
Changed in mailman:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers