Deleting members

Functionality discussions and feature requests

Moderators: hugo, alexandre, rmvanarkel

Post Reply
nana
Posts: 29
Joined: Sat Jul 14, 2012 4:04 am

Deleting members

Post by nana »

Hello,

can someone help me with deleting all members from my instance so i start anew? i tried to delete one member in the mySql database but got this error:

ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails (`cyclos3/accounts`, CONSTRAINT `FK809DBBE6EAE0AB57` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`))

SQL Statement:

DELETE FROM `cyclos3`.`members` WHERE `id`='6'

Do i have to take the cyclos program offline before deleting members?

thanks,

Nana

simonjwoolf
Posts: 111
Joined: Mon May 28, 2012 8:49 am

Re: Deleting members

Post by simonjwoolf »

Hi Nana,

I assume you are in a testing/staging phase? Because normally you would never delete information from cyclos in this way - as a financial transactional system, it requires that you mark members as "removed" in the admin, rather than deleting them, which also removes the audit trail.

However, like you, I've sometimes needed to play around with configuring a system and then wiping all the member info (but keeping the config). It's quite complex to do this, because mysql retains referential integrity between the different tables, and stops you deleting any information where there are related transactions/logs/properties elsewhere in the system. So I developed this script, which removes everything apart from your system configuration and administrator accounts.

Code: Select all

USE cyclos3; 

SET FOREIGN_KEY_CHECKS = 0;

DELETE FROM images WHERE subclass = 'ad';
TRUNCATE TABLE ads;
TRUNCATE TABLE ad_interests;
TRUNCATE TABLE account_status;
TRUNCATE TABLE member_account_fee_logs;
TRUNCATE TABLE transfers;
TRUNCATE TABLE external_transfers;
DELETE FROM accounts WHERE subclass = 'M';
TRUNCATE TABLE custom_field_values;
TRUNCATE TABLE group_history_logs;
TRUNCATE TABLE member_sms_status;
TRUNCATE TABLE members_channels;
TRUNCATE TABLE messages;
TRUNCATE TABLE notification_preferences;
TRUNCATE TABLE remarks;
TRUNCATE TABLE sms_logs;
TRUNCATE TABLE sms_mailings_groups;
TRUNCATE TABLE sms_mailings;
TRUNCATE TABLE tickets;
TRUNCATE TABLE username_change_logs;
DELETE FROM login_history WHERE NOT(user_id IN (SELECT id FROM users WHERE subclass = 'A'));
DELETE FROM password_history WHERE NOT(user_id IN (SELECT id FROM users WHERE subclass = 'A'));
TRUNCATE TABLE error_log_entry_parameters;
TRUNCATE TABLE error_log_entries;
DELETE FROM users WHERE subclass = 'M';
TRUNCATE TABLE invoices;
DELETE FROM alerts WHERE subclass = 'M';
DELETE FROM members WHERE subclass = 'M';

SET FOREIGN_KEY_CHECKS = 1;
---
Simon Woolf
Cyclos Consultant

nana
Posts: 29
Joined: Sat Jul 14, 2012 4:04 am

Re: Deleting members

Post by nana »

Thanks Simon for the script. Yes it's a test stage application but i have no idea what to do with this script.
Do i copy it into a file? cyclos.properties? Is there a way to export some members to .csv format and then import it into a newly built cyclos?

simonjwoolf
Posts: 111
Joined: Mon May 28, 2012 8:49 am

Re: Deleting members

Post by simonjwoolf »

Nana,

This is a mysql script. Run it in your mysql client, just like you were running your delete command previously.

Simon.
---
Simon Woolf
Cyclos Consultant

Post Reply