ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') and time_started >= 1292889600 and time_started < 1293556199' at line 1")

[christer]

How to Reproduce

While doing a GET operation on /hours, Trac issued an internal error.

(please provide additional details here)

Request parameters:

{/trac/hours}

User agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.12) Gecko/20101027 Fedora/3.6.12-1.fc13 Firefox/3.6.12

System Information

Trac	0.12
Babel	0.9.5
Docutils	0.6
Genshi	0.6
mod_wsgi	3.2 (WSGIProcessGroup WSGIApplicationGroup %{GLOBAL})
MySQL	server: "5.0.77-log", client: "5.1.52", thread-safe: 0
MySQLdb	1.2.3c1
Pygments	1.3.1
Python	2.7 (r27:82500, Sep 16 2010, 18:02:00) [GCC 4.5.1 20100907 (Red Hat 4.5.1-3)]
setuptools	0.6
Subversion	1.6.13 (r1002816)
jQuery	1.4.2

Enabled Plugins

TracHoursPlugin

0.5.2

Python Traceback

Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/web/main.py", line 513, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/web/main.py", line 235, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib/python2.7/site-packages/TracHoursPlugin-0.5.2-py2.7.egg/trachours/hours.py", line 243, in process_request
    return self.process_timeline(req)
  File "/usr/lib/python2.7/site-packages/TracHoursPlugin-0.5.2-py2.7.egg/trachours/hours.py", line 518, in process_timeline
    return self.display_html(req, query)
  File "/usr/lib/python2.7/site-packages/TracHoursPlugin-0.5.2-py2.7.egg/trachours/hours.py", line 711, in display_html
    time_records = self.get_ticket_hours(ticket_ids, from_date=from_date, to_date=to_date, worker_filter=data['cur_worker_filter'])
  File "/usr/lib/python2.7/site-packages/TracHoursPlugin-0.5.2-py2.7.egg/trachours/hours.py", line 144, in get_ticket_hours
    result = get_all_dict(self.env, sql, *args)
  File "/usr/lib/python2.7/site-packages/TracSQLHelper-0.2.1-py2.7.egg/tracsqlhelper/__init__.py", line 51, in get_all_dict
    desc, rows = get_all(env, sql, *params);
  File "/usr/lib/python2.7/site-packages/TracSQLHelper-0.2.1-py2.7.egg/tracsqlhelper/__init__.py", line 23, in __call__
    cur.execute(sql, params)
  File "/usr/lib/python2.7/site-packages/Trac-0.12-py2.7.egg/trac/db/util.py", line 65, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') and time_started >= 1292889600 and time_started < 1293556199' at line 1")

[christer@…]

It seems like this might be due to no tickets being in the system yet.

I bypassed the issue by making the following change. I am not familiar enough with Python to know if all of the changes are required or not.

123c123
<     def get_ticket_hours(self, ticket_id, from_date=None, to_date=None, worker_filter=None):
---
>     def get_ticket_hours(self, ticket_id=None, from_date=None, to_date=None, worker_filter=None):
124a125
>         where = None
128c129
<         else:
---
>         elif ticket_id != None && len(ticket_id) > 0:
132c133,135
<             where += " and time_started >= %s"
---
>             if where != None:
>                 where += " and"
>             where += " time_started >= %s"
136c139,141
<             where += " and time_started < %s"
---
>             if where != None:
>                 where += " and"
>             where += " time_started < %s"
140c145,147
<             where += " and worker = %s"
---
>             if where != None:
>                 where += " and"
>             where += " worker = %s"

[Ryan J Ollos]

Seems to work fine with no tickets in the system under SQLite, so might be a MySQL specific issue.

[Ryan J Ollos]

#8391 has a patch for a similar issue under PostgreSQL. That patch is very simple. Would you be able to test it out if I can apply it to the trunk? I'm currently only setup to develop with SQLite, so I won't otherwise be able to deal with this issue for a little while.

[Ryan J Ollos]

This issue should be fixed in [12204]. Please report back if it is working well for you now.