Deleting a batch from a mysql database

[Mette Hansen]

Hello stacks users,

We are trying out various settings, and quickly running out of space on our mysql drives. I have deleted whole databases before, but now I really need to delete some of the batch runs that we are not using from within specific databases.

I have no idea how to do that. If someone could please help with the mysql syntax for getting rid of a specific batch ONLY while preserving the database itself, I would be very grateful.

Thanks a bunch!

Mette

[ananta]

I am not sure if there s an easy way. but I have installed phpMyAdmin and do some editing over there. For example 

runing  

DELETE FROM `**_radtags`.`catalog_snps` WHERE `catalog_snps`.`batch_id` = x;

and from more tables where you have batch_id (parent tables)

DELETE FROM `**_radtags`.`snps` WHERE `snp_id` Not In (SELECT snp_id from catalog_snps);

and from more rows if you have undesired rows of data in child tables

I am not SQL expert though...try with your own risk. or what about copying database before running the commands and deleting later.

[Julian Catchen]

Good advice from Ananta.

You can save a lot of disk space by removing the stacks from batches you are no
longer going to use. These data are stored in the unique_tags table.

SELECT * FROM samples WHERE batch_id=X;

will give you a list of all sample IDs for a particular batch ID. Then, you can
take advantage of the sample IDs to delete rows from unique_tags.

First, check that you are getting the data you want:

SELECT * FROM unique_tags WHERE sample_id=Y LIMIT 10;

Now, do the deletion:

DELETE FROM unique_tags WHERE sample_id=Y;

This will take some time to complete. Repeat for each sample in the batch you
are removing. Of course, this will destroy data, so if you have the space, back
up the whole database first, then do your deletions before removing the back up.

There are lots of faster/fancier ways to do this if you want to learn more SQL.
Unfortunately there is no single button available to completely delete an
individual batch, if anyone has a script to do this, I'm sure it would be useful
to the group.

I tend to delete whole databases, which is a lot easier/faster. When you have
millions of reads/hundreds of samples, deleting all the records from the
database can take the SQL server a very long time. But, you can prune out a
bunch of data from the unique_tags data.

Best,

julian

[Fábio Pértille]

To dell all samples in one time:

USE unique_radtags;

SELECT * FROM samples WHERE batch_id=X; (in my case=1)
DELETE FROM samples WHERE batch_id=X;

Best,
Fábio