How to delete transfer data from Console but keep configuration

Description

In some cases you may need to delete session data on all connections but retain the information on all managed nodes in Console.

Environment
  • Product: Console
  • Version: 2.3 to 3.0.6

Instructions

1. Make sure there are no backups or purges running in Console - if so, cancel them

2. Shut down Console

# asctl console:stop

3. Edit the MySQL configuration to make it listen only on the local interface

Open the following in a text editor:

  • Linux: /opt/aspera/common/mysql/my.cnf
  • Windows: C:\Program Files (x86)\Common Files\Aspera\Common\mysql\my.ini

Find the section starting with [mysqld]. Immediately underneath that line, insert the following line:

bind-address=127.0.0.1

4. Restart MySQL

# asctl mysql:restart

5. Create a file with the necessary SQL commands

Create a text file with a name of your choice containing the SQL commands below (or alternatively download the file of these commands attached at the bottom of this article):

USE aspera_console;
SET foreign_key_checks = 0;

TRUNCATE asp_queue_spots;
TRUNCATE canonicalizer_tmp_fasp_file_details;
TRUNCATE canonicalizer_tmp_fasp_files;
TRUNCATE canonicalizer_tmp_ff_sum;
TRUNCATE canonicalizer_transfer_ids_with_file_activity;

UPDATE cf_fields SET recalc_status="needed";

TRUNCATE dbu_fasp_file_ids;
TRUNCATE dbu_fasp_rate_ids;
TRUNCATE dbu_fasp_session_ids;
TRUNCATE dbu_files;
TRUNCATE dbu_job_ids;
TRUNCATE dbu_job_step_email_event_ids;
TRUNCATE dbu_job_step_email_ids;
TRUNCATE dbu_job_step_ids;
TRUNCATE dbu_sent_email_event_ids;
TRUNCATE dbu_sent_email_ids;

TRUNCATE dbu_tasks;

TRUNCATE dbu_transfer_file_ids;
TRUNCATE dbu_transfer_ids;
TRUNCATE dbu_transfer_session_file_ids;
TRUNCATE dbu_transfer_session_file_side_ids;
TRUNCATE dbu_transfer_session_ids;
TRUNCATE dbu_transfer_session_side_ids;

TRUNCATE fasp_files;
TRUNCATE fasp_rates;
TRUNCATE fasp_sessions;

TRUNCATE job_step_email_events;
TRUNCATE job_step_emails;
TRUNCATE job_steps;

TRUNCATE jobs;

TRUNCATE purge_fasp_file_ids;
TRUNCATE purge_fasp_rate_ids;
TRUNCATE purge_fasp_session_ids;
TRUNCATE purge_files;
TRUNCATE purge_job_ids;
TRUNCATE purge_job_step_email_event_ids;
TRUNCATE purge_job_step_email_ids;
TRUNCATE purge_job_step_ids;
TRUNCATE purge_sent_email_event_ids;
TRUNCATE purge_sent_email_ids;
TRUNCATE purge_transfer_file_ids;
TRUNCATE purge_transfer_ids;
TRUNCATE purge_transfer_session_file_ids;
TRUNCATE purge_transfer_session_file_side_ids;
TRUNCATE purge_transfer_session_ids;
TRUNCATE purge_transfer_session_side_ids;

TRUNCATE sent_email_events;
TRUNCATE sent_emails;

TRUNCATE transfer_files;
TRUNCATE transfer_session_file_sides;
TRUNCATE transfer_session_files;
TRUNCATE transfer_session_sides;
TRUNCATE transfer_sessions;
TRUNCATE transfers;

OPTIMIZE TABLE asp_queue_spots;
OPTIMIZE TABLE canonicalizer_tmp_fasp_file_details;
OPTIMIZE TABLE canonicalizer_tmp_fasp_files;
OPTIMIZE TABLE canonicalizer_tmp_ff_sum;
OPTIMIZE TABLE canonicalizer_transfer_ids_with_file_activity;
OPTIMIZE TABLE dbu_fasp_file_ids;
OPTIMIZE TABLE dbu_fasp_rate_ids;
OPTIMIZE TABLE dbu_fasp_session_ids;
OPTIMIZE TABLE dbu_files;
OPTIMIZE TABLE dbu_job_ids;
OPTIMIZE TABLE dbu_job_step_email_event_ids;
OPTIMIZE TABLE dbu_job_step_email_ids;
OPTIMIZE TABLE dbu_job_step_ids;
OPTIMIZE TABLE dbu_sent_email_event_ids;
OPTIMIZE TABLE dbu_sent_email_ids;

OPTIMIZE TABLE dbu_tasks;

OPTIMIZE TABLE dbu_transfer_file_ids;
OPTIMIZE TABLE dbu_transfer_ids;
OPTIMIZE TABLE dbu_transfer_session_file_ids;
OPTIMIZE TABLE dbu_transfer_session_file_side_ids;
OPTIMIZE TABLE dbu_transfer_session_ids;
OPTIMIZE TABLE dbu_transfer_session_side_ids;

OPTIMIZE TABLE fasp_files;
OPTIMIZE TABLE fasp_rates;
OPTIMIZE TABLE fasp_sessions;

OPTIMIZE TABLE job_step_email_events;
OPTIMIZE TABLE job_step_emails;
OPTIMIZE TABLE job_steps;

OPTIMIZE TABLE jobs;

OPTIMIZE TABLE purge_fasp_file_ids;
OPTIMIZE TABLE purge_fasp_rate_ids;
OPTIMIZE TABLE purge_fasp_session_ids;
OPTIMIZE TABLE purge_files;
OPTIMIZE TABLE purge_job_ids;
OPTIMIZE TABLE purge_job_step_email_event_ids;
OPTIMIZE TABLE purge_job_step_email_ids;
OPTIMIZE TABLE purge_job_step_ids;
OPTIMIZE TABLE purge_sent_email_event_ids;
OPTIMIZE TABLE purge_sent_email_ids;
OPTIMIZE TABLE purge_transfer_file_ids;
OPTIMIZE TABLE purge_transfer_ids;
OPTIMIZE TABLE purge_transfer_session_file_ids;
OPTIMIZE TABLE purge_transfer_session_file_side_ids;
OPTIMIZE TABLE purge_transfer_session_ids;
OPTIMIZE TABLE purge_transfer_session_side_ids;

OPTIMIZE TABLE sent_email_events;
OPTIMIZE TABLE sent_emails;

OPTIMIZE TABLE transfer_files;
OPTIMIZE TABLE transfer_session_file_sides;
OPTIMIZE TABLE transfer_session_files;
OPTIMIZE TABLE transfer_session_sides;
OPTIMIZE TABLE transfer_sessions;
OPTIMIZE TABLE transfers;

SET foreign_key_checks = 1;

UPDATE canonicalizer_max_ids SET max_fasp_file_id=0, prev_max_fasp_file_id=0, max_fasp_session_id=0;

5. Run the MySQL command line and feed in the text file from the previous step as input

Linux
# /opt/aspera/common/mysql/bin/mysql -uroot -p < NAME_OF_TEXT_FILE
Windows
> C:\Program Files (x86)\Common Files\Aspera\Common\mysql\bin\mysql.exe -uroot -p < NAME_OF_TEXT_FILE
Note: When executing the above command, you will see a lot of 'Table does not support optimize, doing recreate + analyze instead' messages. This is expected.

6. Edit the MySQL configuration file to undo the change from step #3

Remove or comment out the line you added:

bind-address=127.0.0.1

7. Restart MySQL and Console

# asctl mysql:restart
# asctl console:start

8. In Console web interface, go to Configuration > Custom Fields

If there are any custom fields, click the Recalculate link for each.

9. In Console web interface, go to the Transfers page to look at smart transfers

If there are any smart transfers that have schedules, click edit and save for each to force Console to recalculate next scheduled time for each

 

Attachments

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk