Batch Edit in Sp6 creates undelete-able workbench datasets

Get Help
1

When using Batch Edit in Specify 6, a Workbench Dataset is created that does not get automatically deleted after the changes are committed. It also seems there is no way to delete it from within Specify.

Is this intentional behavior? Does anyone know if deleting these datasets directly from the database would introduce any issues?

Any guidance is appreciated!

[!info]+ Background

:desktop_computer: Tech Specs
Specify 6.8.03
MySQL 5.7.39

I am working on removing database “bloat” by exporting workbench datasets left behind by former collections managers and assistants. Some of these may still be helpful in fixing several database errors, so I do not want to bulk-delete everything.

The problem is that the Workbench (WB) datasets are scattered across so many users, and I have no clue who has what. Rather than logging in as each user (which would involve resetting passwords and granting WB access), I used the following SQL query to get a count of datasets per user.

SELECT 
   COUNT(wb.Name) AS DatasetCount
   ,su.Name
FROM workbench wb
JOIN specifyuser su ON wb.SpecifyUserID = su.SpecifyUserID
GROUP BY wb.SpecifyUserID
ORDER BY DatasetCount DESC;

To my surprise, my own user account had 85 datasets:skull:… but I know I should only have 2.

To get some clues for the mystery sets, I ran this query:

SELECT wb.Name
   ,wb.SrcFilePath
    ,su.Name
FROM workbench wb
JOIN specifyuser su ON wb.SpecifyUserID = su.SpecifyUserID
WHERE su.Name = 'emd0083'; --THIS IS MY USERNAME

It turns out many of the “mystery” datasets were named after queries I regularly use for batch editing. This leads me to believe that every time I use Batch Edit, it leaves behind a WB dataset that cannot be seen or deleted from within Specify 6.

Luckily, all of these datasets from batch edits have -- in their name so they can be easily found by inserting this into the query.

WHERE Name LIKE '%--%'

I have tested deleting a single dataset directly from the database using a series of DELETE statements (i.e., delete from workbenchdataitem, workbenchrow, workbench, workbenchtemplatemappingitem, and workbenchtemplate). This seems to work fine, but I’m not positive it isn’t breaking anything.

1 Like
2

Hi @emd0083,

Thank you for your question and for all of the useful context you shared! I can confirm that Specify 6 does indeed add new WorkBench data sets (in the workbench table) each time a user performs a Batch Edit.

This behavior was intentional when implemented, but it has since been reconsidered. Specify 7’s new Batch Edit feature retains data sets until the user deletes them, and users can remove them at any time.

Deleting these datasets directly should not cause any issues as long as you are only removing data from the workbenchdataitem, workbenchrow, workbench, workbenchtemplatemappingitem, and workbenchtemplate tables. It sounds like you’ve already developed a workflow to perform these deletions in the correct order, so you should be totally OK to proceed with their removal.

Before running any SQL statements on the database, you should take a complete backup, verify that it is functioning as expected, and then perform the steps on a copy to ensure everything is in order.

3

Yet another fantastic reason for upgrading to Sp7! I’ll add that one to the list…

For now, I successfully deleted those lurking datasets from my database (~5.5% decrease in total db backup size :tada:)

Thanks, @grant :slight_smile: