Deleting account logic

[nemini]

In a member driven environment I offer to the members the opportunity to delete part of or their whole account
The process is quite involving
Deleting/updating/inserting in six tables
Opening and deleting from a video repository
Opening and deleting from an img gallery

As is it works fine

But I am very concerned by any potential glitches that will half way delete the account and leave me with a mess

So I am wondering if my new sight on the logic is fine or if you have a better way of dealing with it.

Before deleting, create a temp table and load data as those to be soon deleted
Copy img and video in a temp
If all systems go then del all temps
If not go back to what failed and reload, while sending detailed message to the site admin

 

[feherke]

Hi

If your database supports transactions, use them. ( See MySQL Transactional and Locking Statements for MySQL or Concurrency Control for PostgreSQL. )

Combined with your previously described file deletion trick would be :

begin transaction
delete from six tables
if delete files==success
  commit transaction
else
  rollback transaction

Feherke.

http://rootshell.be/~feherke/

 

[nemini]

I simply love it, did not think about it, simplier!
Thanks

 

[jpadie]

be a bit careful as only some database engines used by mysql support transactions (the last time i looked anyway). the chaps in mysql forum will be delighted to help, assuming you are using mysql, that is.

 

[nemini]

Yes you are correct, I use MySQL and will have to alter some tables from myISAM to InnoDB
I might post to MySQL but it will spread out the thread
for now the real simple last question becomes:
Is there any problem to foresee while operating a DB with a mixed table engine combo made of MyISAM & InnoDB?

 

[jpadie]

i must admit that i have never done so. i have always used myISAM except where atomic transactions are necessary.

 

[ingresman]

The manual at

http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html

says it makes perfect seinse to mix !, I'd be a bit cautious mixing myisam iand innodb in a recoverable transaction though.
Might be worth a seperate thread in the mysql forum asking that question rather than your app based issue here.

 

[nemini]

Thanks all, then I will post in MySQL but will report the results here in order to finish documenting that thread

 

[nemini]

There are no problems in mixing both emgines
however one should pay attention to the following
<<< // from manual
for best results, you should not mix different storage engines within a transaction with autocommit disabled.
>>>