Multiple deletes

[chomedey]

Hi,

 

I'm coding a "cancel account" page for the first time and realizing it is more complicated than I thought.

 

By the time the user gets to the point of canceling their account they may have contributed a bunch of stuff to the site, with all that stuff contained in a number of different tables (i.e. comments, postings, preferences, users etc.)

 

When it comes to deleting all that stuff, I would like to do so in such a way that either the whole shabang goes, or it all stays and gives an error message.  But I don't know if it's possible to do that.

 

Right now I have a "DELETE from comments ..." query, that may or may not fail, followed by a "DELETE from preferences ..." query etc.  If any one of these fails, I would like them all to fail.  But by the time one of them fails, the others may well have succeeded.

 

Is there a way of getting what I want here, or do I just have to accept that a user account may be half-cancelled, so to speak, leaving the administrator to fix the mess manually?

 

Thanks.

 

Julian

 

P.S. Does anyone know what the norm is with comments.  I figure a person's comments on someone else's post should survive the cancellation of the commenter's account.  Right now I have it set that the comments remain, but are re-credited to an "ex-member" with a generic photo.  Is that acceptable when it comes to privacy etc.?

 

Cheers.

 

[simshaun]

What you need are transactions. See http://dev.mysql.com/doc/refman/5.0/en/commit.html.

[cunoodle2]

You could also do this..

 

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3

WHERE t1.id=t2.id AND t2.id=t3.id;

 

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

 

Another great reference is here..

How to delete items from multiple tables at once.

[aeroswat]

Hi,

 

I'm coding a "cancel account" page for the first time and realizing it is more complicated than I thought.

 

By the time the user gets to the point of canceling their account they may have contributed a bunch of stuff to the site, with all that stuff contained in a number of different tables (i.e. comments, postings, preferences, users etc.)

 

When it comes to deleting all that stuff, I would like to do so in such a way that either the whole shabang goes, or it all stays and gives an error message.  But I don't know if it's possible to do that.

 

Right now I have a "DELETE from comments ..." query, that may or may not fail, followed by a "DELETE from preferences ..." query etc.  If any one of these fails, I would like them all to fail.  But by the time one of them fails, the others may well have succeeded.

 

Is there a way of getting what I want here, or do I just have to accept that a user account may be half-cancelled, so to speak, leaving the administrator to fix the mess manually?

 

Thanks.

 

Julian

 

P.S. Does anyone know what the norm is with comments.  I figure a person's comments on someone else's post should survive the cancellation of the commenter's account.  Right now I have it set that the comments remain, but are re-credited to an "ex-member" with a generic photo.  Is that acceptable when it comes to privacy etc.?

 

Cheers.

 

That's what myspace does. They just replace it with a generic account. It's also what I do. You don't want to get rid of records even if a person cancels an account especially if they are comments. What if someone else is responding to their comment? It won't make sense if the comments get deleted. Keep the placeholder account.

[chomedey]

Thanks very much for all of this - snarky url included.

 

I will get to grips with transactions ...

 

Best,

 

Julian

[coupe-r]

I would also use transactions.  But instead of using DELETE FROM, I always put a deleteStatus column in my table and toggle between 0 and 1.  0 active, 1 deleted.  That way you never have a DELETE query, which "could" be harmful.  Called soft delete.

[rameshfaj]

what about stored procedures? Aren't they easy to manage for atomicity?

[chomedey]

Hi coupe-r.

 

So you add a check on that delete column to every WHERE clause?

 

I'm wary of doing that because there was a big fuss over the fact that facebook didn't truly delete your data a while back.

 

Julian

[chomedey]

Also one other question:

 

To do transactions I need my tables to be innodb if I'm not mistaken.  Right now I have not specified a type for my tables.  Does that mean they are not innodb?  Are there any disadvantages to altering them all to be innodb?

 

Thanks.

 

Julian

[simshaun]

They may or may not be InnoDB.  Run a SHOW TABLE STATUS query and you'll find out.

 

Most likely they are going to be MyISAM, which is fine, but not if you need transactions.  MyISAM is great if you don't like fine-tuning your database.  I actually prefer InnoDB over MyISAM, but I also take the time to make sure my MySQL installation is configured properly so InnoDB is more efficient.

 

This is an "old" article (3yrs), but still good to read: http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/.

[chomedey]

Thanks again.

Very helpful.

 

For my purposes it doesn't seem there are any significant disadvantages to using innoDB.

 

I have no idea if my host - justhost - has optimized their mysql installation for innoDB, but like I said, I'm not expecting so much traffic that a few milliseconds are going to make all the difference (unless I get lucky).

 

Thanks.

 

Julian