opentaxii-delete-blocks creates mysqldb exception of operationalError

[nafooesi]

opentaxii==0.1.12
mysql version: 5.7.31-0ubuntu0.16.04.1 (Ubuntu)

run: opentaxii-delete-blocks -c collection-a --begin '2020-07-01'
yielded the following error: (skip stack trace for brevity)

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1093, "You can't specify target table 'content_blocks' for update in FROM clause")

[SQL: DELETE FROM content_blocks WHERE content_blocks.id IN (SELECT content_blocks.id
FROM data_collections INNER JOIN collection_to_content_block AS collection_to_content_block_1 ON data_collections.id = collection_to_content_block_1.collection_id INNER JOIN content_blocks ON content_blocks.id = collection_to_content_block_1.content_block_id
WHERE data_collections.id = %s AND content_blocks.timestamp_label > %s)]

[arcsector]

Looks like this might be an issue with DB spec and we may need to specify table and DB so DELETE FROM taxii.content_blocks as opposed to shorthand.

[eric-eclecticiq]

Hi, thanks for opening an issue!

It is indeed a bug and we’d love to have it fixed, however it’s not a high priority for our team at the moment, so we can’t promise when it will get fixed. Still, we’re very open to external contributions - if you know how to fix this problem and you can open a PR with a fix, we will be very grateful.

[erwin-eiq]

Thanks for reporting this @nafooesi and sorry for taking so long to reproduce and fix it. It was a bug that only manifested itself on sqlalchemy 1.3 combined with mysql (not mariadb, only mysql), which meant that I had to do some plumbing work before I could reproduce it at all.

Good news is that we now have a tox build matrix that tests all combinations of python 3.6-3.9, sqlalchemy 1.3,1.4 and sqlite,mysql,mariadb :)