"You can't specify target table 'session_attribute' for update in FROM clause" when using MySQL

[anonymous]

2012-01-22 12:06:23,218 Trac[main] ERROR: Internal Server Error: 
Traceback (most recent call last):
  File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 511, in _dispatch_request
    dispatcher.dispatch(req)
  File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 237, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-x86_64/egg/trac/ticket/web_ui.py", line 170, in process_request
    return self._process_newticket_request(req)
  File "build/bdist.linux-x86_64/egg/trac/ticket/web_ui.py", line 396, in _process_newticket_request
    valid = self._validate_ticket(req, ticket)
  File "build/bdist.linux-x86_64/egg/trac/ticket/web_ui.py", line 1182, in _validate_ticket
    for field, message in manipulator.validate_ticket(req, ticket):
  File "/srv/trac/lib/python2.6/site-packages/TracSpamFilter-0.4.8dev_r10888-py2.6.egg/tracspamfilter/adapters.py", line 77, in validate_ticket
    FilterSystem(self.env).test(req, author, changes, ip)
  File "/srv/trac/lib/python2.6/site-packages/TracSpamFilter-0.4.8dev_r10888-py2.6.egg/tracspamfilter/api.py", line 193, in test
    'potential spam %s' % msg)
  File "/srv/trac/lib/python2.6/site-packages/TracSpamFilter-0.4.8dev_r10888-py2.6.egg/tracspamfilter/captcha/api.py", line 78, in reject_content
    self._cleanup()
  File "/srv/trac/lib/python2.6/site-packages/TracSpamFilter-0.4.8dev_r10888-py2.6.egg/tracspamfilter/captcha/api.py", line 205, in _cleanup
    db.cast('value', 'int')), (tc,t))
  File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1093, "You can't specify target table 'session_attribute' for update in FROM clause")

TracSpamFilter is not usable with MySQL currently as a result.

[anonymous]

Running MySQL 5.0.67 and Trac 0.12.2.

[Stefan]

Updated version number according to comment:1.

[Christian Boos]

See also ticket:10548#comment:5 for information on the 1093 error.

[Dirk Stöcker]

I have no idea how to fix this issue.

[ludovic.belliere@…]

I believe you need to use a temporary table :

DELETE FROM session_attribute
WHERE name LIKE 'captcha%%'
AND (name != 'captcha_verified' OR %s < %%s)
AND (
    sid IN (
        SELECT * FROM (
            SELECT sid FROM session_attribute WHERE name = 'captcha_reject_time' AND %s < %%s
        ) AS tmp1
    ) OR sid NOT IN (
        SELECT * FROM (
            SELECT sid FROM session_attribute WHERE name = 'captcha_reject_time'
        ) AS tmp2
    )
)

[Dirk Stöcker]

Can you test it and supply a patch? I have no MySQL.

[ludovic.belliere@…]

I've been running it for quite some times now. And it seems to work. I don't know if this query will work with anything else than mysql though.

[Christian Boos]

A stylistic note: we're using multiline """...""" strings for long SQL queries.

[ludovic.belliere@…]

I must admit that in this state, the query is unreadable. For my defence I just copied the previous style, not knowing what do to or how to format the query.

My apologies.

[Dirk Stöcker]

In r11108.