Duplicate Data In Datto Automate Plugin

Something changed over the weekend in the Datto Automate plugin, which broke the integration and our internal backup checks.

Specifically, it looks like the “Volume” element is now reporting as a name, like the “Agent” attribute always has.

However, this caused the Automate plugin to create new IDs for almost every server because it uses the “Volume” element as a key.

Take a look at this example:

id device_serial_number ClientID LocationID type volume operating_system agent
123456 111111111111 1 1 Agent exampleserver Windows Server 2008R2 exampleserver
123457 111111111111 1 1 Agent 192.0.2.1 Windows Server 2008R2 exampleserver

Notice that the volume column is different and that difference created a new row with a different id value.

In our system, we need to delete all of these old rows to fix the monitors.  To find them, here’s a quick and dirty SQL statement:

SELECT volumes.id FROM plugin_datto_backup_volumes volumes
LEFT JOIN plugin_datto_backup_volumes AS volumes2 ON ((volumes.agent = volumes2.agent) AND (volumes.device_serial_number = volumes2.device_serial_number))
WHERE volumes.id < volumes2.id
AND (volumes.type = 'Agent')

I don't claim to be any kind of SQL expert, so deleting these is at your own risk.  The above finds everything in the backup_volumes table that's a duplicate, based on the hostname and device serial number, then grabs the lower id, which is the older entry.

If you try to just throw that into a DELETE statement, you’ll find that there is an issue with self-referencing tables like that.  See a discussion about this issue on Stack Overflow here.

To fix that, you need to remove the references and make them full-table subqueries like this:

DELETE FROM plugin_datto_backup_volumes WHERE id IN
(
SELECT volumes.id FROM (SELECT * FROM plugin_datto_backup_volumes) AS volumes
LEFT JOIN (SELECT * FROM plugin_datto_backup_volumes) AS volumes2 ON ((volumes.agent = volumes2.agent) AND (volumes.device_serial_number = volumes2.device_serial_number))
WHERE volumes.id < volumes2.id
AND (volumes.type = 'Agent')
) LIMIT 30

Not the cleanest query ever, but it worked for me and closed out the mountain of tickets this generated for us.

I did this in batches broken up by LIMIT clauses because I hate deleting things.

There is, of course, absolutely NO WARRANTY or ANY SUPPORT PROVIDED for the above.   If it breaks your database, restore from backup.  It worked here.

Leave a comment