Issue: MySQL Error 1093 on recursive instance deletion
Author: timlehr
State: closed
Created: 2018-05-21 19:54:09

Hi Charles,

I'm using the recursive delete instance functionality of peewee quite a bit and so far it always worked out quite well. However, I recently added a self-referencing FK to one of my models `File`:
`_source_file = ForeignKeyField(column_name="sourcefile", model='self', null=True, field='dbid')`

I'm accessing / using that field via a `hybrid_property`, as I do with most of my fields. Now when I'm deleting a model that is an dependency to File, MySQL stops with the following error:

> InternalError: (1093, u"Table 'File' is specified twice, both as a target for 'UPDATE' and as a separate source for data")

I found a couple of suggestions on the web on how to fix the query, but since it's created automatically by peewee for me (awesome!), there is not much I can do. I hope I attached enough information for you to take a look at this.

Thank you!

Cheers,
Tim 

### Log
> ('DELETE FROM `FileDependency` WHERE (`File_dbid` IN (SELECT `t1`.`dbid` FROM `File` AS `t1` WHERE (`t1`.`Version_dbid` IN (SELECT `t2`.`dbid` FROM `Version` AS `t2` WHERE (`t2`.`Variant_dbid` IN (SELECT `t3`.`dbid` FROM `Variant` AS `t3` WHERE (`t3`.`Task_dbid` IN (SELECT `t4`.`dbid` FROM `Task` AS `t4` WHERE (`t4`.`Asset_dbid` IN (SELECT `t5`.`dbid` FROM `Asset` AS `t5` WHERE (`t5`.`Category_dbid` IN (SELECT `t6`.`dbid` FROM `Category` AS `t6` WHERE (`t6`.`dbid` = %s)))))))))))))', [40])

> ('DELETE FROM `FileDependency` WHERE (`dependency_dbid` IN (SELECT `t1`.`dbid` FROM `File` AS `t1` WHERE (`t1`.`Version_dbid` IN (SELECT `t2`.`dbid` FROM `Version` AS `t2` WHERE (`t2`.`Variant_dbid` IN (SELECT `t3`.`dbid` FROM `Variant` AS `t3` WHERE (`t3`.`Task_dbid` IN (SELECT `t4`.`dbid` FROM `Task` AS `t4` WHERE (`t4`.`Asset_dbid` IN (SELECT `t5`.`dbid` FROM `Asset` AS `t5` WHERE (`t5`.`Category_dbid` IN (SELECT `t6`.`dbid` FROM `Category` AS `t6` WHERE (`t6`.`dbid` = %s)))))))))))))', [40])

>  ('UPDATE `File` SET `sourcefile` = %s WHERE (`sourcefile` IN (SELECT `File`.`dbid` FROM `File` AS `File` WHERE (`File`.`Version_dbid` IN (SELECT `t1`.`dbid` FROM `Version` AS `t1` WHERE (`t1`.`Variant_dbid` IN (SELECT `t2`.`dbid` FROM `Variant` AS `t2` WHERE (`t2`.`Task_dbid` IN (SELECT `t3`.`dbid` FROM `Task` AS `t3` WHERE (`t3`.`Asset_dbid` IN (SELECT `t4`.`dbid` FROM `Asset` AS `t4` WHERE (`t4`.`Category_dbid` IN (SELECT `t5`.`dbid` FROM `Category` AS `t5` WHERE (`t5`.`dbid` = %s)))))))))))))', [None, 40])

### Traceback
>   File "/home/tim/dev-root/scarif-dev/scarif-db/src/scarif/db/models/dbbasemodel.py", line 70, in delete_instance
    super(DBBaseModel, self).delete_instance(recursive, delete_nullable)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/peewee.py", line 5407, in delete_instance
    model.update(**{fk.name: None}).where(query).execute()
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/peewee.py", line 1522, in inner
    return method(self, database, *args, **kwargs)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/peewee.py", line 1593, in execute
    return self._execute(database)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/peewee.py", line 2014, in _execute
    cursor = database.execute(self)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/peewee.py", line 2589, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/peewee.py", line 2583, in execute_sql
    self.commit()
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/peewee.py", line 2376, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/peewee.py", line 2576, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/tim/Scarif/projects/NewPro-SCivCsp/_python_env/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
InternalError: (1093, u"Table 'File' is specified twice, both as a target for 'UPDATE' and as a separate source for data")

From the MySQL [update docs](https://dev.mysql.com/doc/refman/8.0/en/update.html):

> You cannot update a table and select from the same table in a subquery.

See also [delete docs](https://dev.mysql.com/doc/refman/8.0/en/delete.html#idm139870165521360).

This appears to be a MySQL limitation. You can try to work around it in your code, but for now Peewee does not implement a workaround for this specific issue.

Hi Charles,

Yeah, I was worried that would be the case.
Any suggestions on how I could tackle this? Any function in the model I could re-implement to make it work? 

The [fixes suggested on stackoverflow](https://stackoverflow.com/a/44971214/9288624) seem easy enough, but Idon't have enough insight into peewee yet to know where to start.

Thank you for the quick reply by the way!
Your support for this project is outstanding.

I didn't realize it was possible to even do the above...one would think that the MySQL query engine would be able to rewrite the problematic query as a selection from a derived table. Alas.

For starters, peewee `Model` does expose a `dependencies` method, which yields 2-tuples of the form `(query, foreign-key)` and provides access to all dependent objects. You could look into the code there for some ideas on how to resolve dependencies. Another option which may or may-not work is to use a common table expression (requires newer mariadb / mysql 8 if I recall correctly).

Without really getting into the nitty-gritty I can't be more specific right now.

Alright, thank you. Would be fantastic if an official solution would find it's way into Peewee in the future. Meanwhile I will have a closer look into the `dependencies` method. CTEs are no option for me unfortunately (because of the new requirements). If I find a suitable, generic workaround I'll post it here. 


An alternative might be to simply specify your foreign-keys as "ON DELETE CASCADE" and then just delete the parent row (or "ON DELETE SET NULL" as the case may be). The ForeignKeyField() accepts "on_delete" and "on_update" parameters, e.g.

```python
fk_field = ForeignKeyField(SomeModel, on_delete='CASCADE')
```