Hi Achim, I dont understand the "dual" keyword in the first statement, but if you just want to delete all records older than X, then you can simply use:
DELETE FROM `workflow` WHERE workflow_last_update < 2011-01-01. If you want a dynamic timestamp use: DELETE FROM `workflow` WHERE workflow_last_update < date_sub ( now(), interval 1 month) Removing the orphans from the other tables can be done with a negation (note the *not* in the where clause!) NB: You will need enough temp space on the database to create a virtual table for the ids which can become quite big! delete from workflow_context where workflow_id not in ( select workflow_id from workflow ); delete from workflow_history where workflow_id not in ( select workflow_id from workflow ); As you said, you can make the both cascaded delete queries automatic with triggers: delimiter | create trigger cleanup_workflow BEFORE DELETE ON workflow FOR EACH ROW BEGIN delete from workflow_context where workflow_id = old.workflow_id; delete from workflow_history where workflow_id = old.workflow_id; end; | delimiter ; Oliver On 01.08.2012 09:09, Joachim Astel wrote: > As you know, OpenXPKI generates workflows, which blow up the > database after some years. Not only the workflow rows itself, > but also some other contexts are written with information, > which is outdated then. > > There's a cleanup script around with appropriate sql scripts, which > have been written for Oracle databases. Oracle has a nice feature > named the "INNER" statement. Unfortunately this "INNER" statement > doesn't exist with MySQL databases, you would have to workaround > this functionality in a specific way. As I am no database expert, > would somebody have the time to code these MySQL statements (as > I have heard, "trigger" statements would be good for this). > > Greetings > -Achim > > > Here the original Oracle statements: > > -- delete all SCEP workflows in FAILURE older than one month > delete from l2openxpki.workflow > where workflow_type = 'I18N_OPENXPKI_WF_TYPE_SCEP_REQUEST' > and workflow_state = 'FAILURE' > and workflow_last_update < (select to_char(add_months(sysdate, -1), > 'YYYY-MM-DD') from > dual); > > -- now we have dangling workflow contexts and workflow history, clean up > delete from l2openxpki.workflow_history > where workflow_id in ( > select distinct l2openxpki.workflow_history.workflow_id > from l2openxpki.workflow_history left outer join l2openxpki.workflow > on l2openxpki.workflow_history.workflow_id = > l2openxpki.workflow.workflow_id > where l2openxpki.workflow.workflow_id is NULL > ); > > delete from l2openxpki.workflow_context > where workflow_id in ( > select distinct l2openxpki.workflow_context.workflow_id > from l2openxpki.workflow_context left outer join l2openxpki.workflow > on l2openxpki.workflow_context.workflow_id = > l2openxpki.workflow.workflow_id > where l2openxpki.workflow.workflow_id is NULL > ); > > commit; > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > OpenXPKI-devel mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/openxpki-devel -- Protect your environment - close windows and adopt a penguin! PGP-Key: 3B2C 8095 A7DF 8BB5 2CFF 8168 CAB7 B0DD 3985 1721
smime.p7s
Description: S/MIME Cryptographic Signature
------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________ OpenXPKI-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openxpki-devel
