Can not approve if input the params of <next_state>

[xuziheng1002]

This is my states:
init,doing,doing2,done

this is my transition meta approvals;
init->doing
init->doing2
doing->done
doing2->done

django.db raise following error when I try to approval to next state:

from django.contrib.auth.models import User
from river.models import State
from tickets.models import Ticket
u = User.objects.get(username='admin')
t = Ticket.objects.create()
t.river.status.approve(as_user=u, next_state=State.objects.get(label='doing'))

errors:

The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "<input>", line 6, in <module>
  File "/usr/local/python/lib/python3.6/contextlib.py", line 52, in inner
    return func(*args, **kwds)
  File "/devel/xzh/epimetheus/river/core/instanceworkflowobject.py", line 139, in approve
    self.cancel_impossible_future(approval)
  File "/usr/local/python/lib/python3.6/contextlib.py", line 52, in inner
    return func(*args, **kwds)
  File "/devel/xzh/epimetheus/river/core/instanceworkflowobject.py", line 203, in cancel_impossible_future
    actual_cancelled_transitions.update(status=CANCELLED)
  File "/usr/local/python/lib/python3.6/site-packages/django/db/models/query.py", line 650, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
  File "/usr/local/python/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1204, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "/usr/local/python/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 899, in execute_sql
    raise original_exception
  File "/usr/local/python/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 889, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/python/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/python/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/python/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 101, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/python/lib/python3.6/site-packages/pymysql/cursors.py", line 165, in execute
    result = self._query(query)
  File "/usr/local/python/lib/python3.6/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/usr/local/python/lib/python3.6/site-packages/pymysql/connections.py", line 860, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/python/lib/python3.6/site-packages/pymysql/connections.py", line 1061, in _read_query_result
    result.read()
  File "/usr/local/python/lib/python3.6/site-packages/pymysql/connections.py", line 1349, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/python/lib/python3.6/site-packages/pymysql/connections.py", line 1018, in _read_packet
    packet.check_error()
  File "/usr/local/python/lib/python3.6/site-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/python/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
django.db.utils.InternalError: (1093, "You can't specify target table 'river_transition' for update in FROM clause")

mysql:8.0
django:1.11
django-river:3.1.0
python:3.6.x

[xuziheng1002]

I think that is a bug between Django-ORM and MySQL,
following is the one of solutions, but it will cost more performance,

Iterative the queryset then update/save the object one by one, instead of useing update function in queryset:
river\core\instanceworkflowobject.py: 203

# actual_cancelled_transitions.update(status=CANCELLED) 
for i in actual_cancelled_transitions:
    i.status = CANCELLED
    i.save()

[javrasya]

Hey @xuziheng1002 , I will look into this and try to run the tests with MySQL @xuziheng1002 .

[xuziheng1002]

Hey @xuziheng1002 , I will look into this and try to run the tests with MySQL @xuziheng1002 .

this issue not only appears at approva(next_state) but also appears at jump_to.
I think they are same issue

[javrasya]

This is a very weird behavior of MySQL it doesn't allow the tables that are also in the select query to be updated. Since those items are not too many, it is fine just to loop them in python and save them. I tested it with MySQL 8.0 but tests are still complaining about older versions (5.6 and 5.7). I will work more on that to see what it is that is not liked with the older versions.

[javrasya]

Hey @xuziheng1002 , This PR should fix your problem out of the box. I will release this as soon as the tests are passed on the PR and you can use the next patch version which is 3.1.2 when it is published.

Just so you know, It is not possible to support lower version of MySQL. It is only MySQL 8.0 which is supported.

[javrasya]

The new version is published. Closing this issue.