[mailpit+rqlite] FOREIGN KEY constraint failed error on deleting messages

[hpoznanski]

I've deployed mailpit on my GKE cluster with rqlitedb. Everything works fine but theres a problem after I will add tags to mail. Im not able to delete them because im getting: "FOREIGN KEY constraint failed". Im not able to remove all or even one by one.

mailpit: v1.20.6
rqlite: 8.31.2

[axllent]

@hpoznanski I think I found the issue (assuming you are using the tenant ID option?). Could you please test using the latest axllent/mailpit:edge docker image (instead of axllent/mailpit:latest) and confirm for me whether this fixes your issue? Thanks.

[axllent]

I have released a v1.20.7 with the fix for this (I believe) so I'm closing this issue. If the problem persists then please feel free to re-open this.

[matteovivona]

Same issue here. Always on GKE.

Mailpit: v1.21.6
Rqlite: v8.35.0

[axllent]

@matteovivona I am unable to reproduce this error, so I do not think it is actually the same issue. Can you please provide more information to help me understand the error you have, what you were trying to do at the time, the error message etc? Thanks.

[matteovivona]

I'm trying to delete some emails, either globally or for a specific tag, but I ran into an error message about the FOREIGN KEY constraint failing. I'm not sure if this info will be useful for troubleshooting, but I have rqlite set up with 3 instances

[axllent]

It actually may be useful - the fact that you have 3 instances (I assume you mean replicated?) could mean that the replicated changes are potentially happening in an unexpected order. Leave this with me and I'll see if I can work on a solution 👍

[otoolep]

are potentially happening in an unexpected order.

This doesn't really make much sense. All changes e.g. DELETE always take place in a well-defined order, that's what Raft does for you.

As long as you wait for the HTTP 200 from a given write request, any subsequent write will take place against a system that reflects the first write. It doesn't matter which nodes you send the write to, since all writes go through the Leader. A node will transparently redirect its write to the Leader if necessary.

As for reads, just use weak consistency (the default). That way all reads will go through the Leader too, and result in generally consistent results. And, if a node receives a read request, and it's not the Leader, that node will transparently forward the request to the Leader.

[otoolep]

If this issue is reproducible I would try it with a single-node cluster, just to remove the clustering complexity.

I'm not saying that clustering isn't an issue here (though I doubt it). This feels more like a rqlite client issue, where the client i.e. mailpit thinks it has deleted some records (deletes the wrong records, is ignoring an error from rqlite, is not waiting for a response to write request, something like that), goes to delete more, only to hit an FK constraint.

But this can't be caused by some "unexpected replication" since rqlite is 100% deterministic when it comes to replicating changes across the cluster, and does do in a very robust manner. Writes are always serialized on the cluster. They cannot overlap, and are never re-ordered relative to the order in which the client sends them.

[axllent]

Thanks for the clarification @otoolep - I suspected that was the case, however in saying that, the message_tags table does have two (unused) REFERENCES to the message & tag IDs (part of an original idea to cascade deletes which I never implemented). Mailpit wraps a series of deletes from multiple tables in a single transaction (which I believe wasn't a feature in rqlite due to the TCP / REST nature?) which is maybe why it is failing (deleting things in the wrong order from the Mailpit side). I think the simplest option is for me to remove the REFERENCES references altogether from the table. I just need a little more time to test and work on a solution and dig a little deeper.

[otoolep]

rqlite supports wrapping a single HTTP request in a transaction.

https://rqlite.io/docs/api/api/#transactions

If you are putting the "series of deletes" in such a transaction they will all succeed, or none will succeed.

[otoolep]

If you are doing explicit BEGIN, COMMIT , and so on -- that is not officially supported by rqlite.

[otoolep]

If you are putting the "series of deletes" in such a transaction they will all succeed, or none will succeed.

To give you an example. If a HTTP request , which does not set the transaction query param, contains two DELETE statements (bulk request) and one of those DELETEs works fine, but the other violates a FK constraint, then the first DELETE will remain effective after you receive the response from rqlite. However if you set transaction then neither DELETE will have been effective when you receive the response from rqlite.

It's safe to set the transaction flag even for a single request. So you could just set it for all your requests. I think the gorqlite driver does exactly that.

[axllent]

If you are putting the "series of deletes" in such a transaction they will all succeed, or none will succeed.

It's safe to set the transaction flag even for a single request. So you could just set it for all your requests. I think the gorqlite driver does exactly that.

From my understanding the transaction would not work the way I'm using it, and that rqlite handles that as completely separate requests. Furthermore, the BeginTx() and tx.Commit() are in the driver as really just placeholders that don't do anything - if I understand that correctly.

I suspect that the REFERENCES don't trigger an error with a local SQLite database because of the transaction, however with independent queries to rqlite something sometimes goes wrong. I've tested with a single local instance of rqlite without an issue however, so I don't know why it works on my end, but here we are.

I could change the order the tables are deleted from, however the safest and best solution here would be for me to simply remove those unused REFERENCES altogether to prevent future issues. If rqlite handles that as separate transactions then that's not an issue - these are just bulk deletes from the various tables anyway.

[axllent]

@matteovivona I have just released v.1.21.8 with a fix for this (I'm pretty confident it will at least). I'd appreciate if you could test and report back whether it resolves your issue.

Thanks again for your input @otoolep - as always appreciated to get input from the top! I realise I could go down the path to get the this transaction to work with rqlite, however this was the cleaner solution as those two REFERENCES weren't being used anyway and had the potential to cause other issues down the track. If the message deletions were being cascaded through to the message tags table (my original plan) it may have worked as originally intended, however it's a fancy feature I wasn't using.

[otoolep]

Ack, got it. Yes those calls probably don't do anything in the driver, since they are not supported by rqlite at this time. I have ideas, but it would be major work.

[matteovivona]

@matteovivona I have just released v.1.21.8 with a fix for this (I'm pretty confident it will at least). I'd appreciate if you could test and report back whether it resolves your issue.

@axllent thanks for the speed! I confirm that the patch works

[axllent]

Excellent! Thanks again for bringing this to my attention @matteovivona.