Error in MySQL Query Syntax

[abidoon@…]

Trac detected an internal error:

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 ')\n     GROUP BY r.author \n     ORDER BY commits DESC \n     LIMIT 5' at line 3")

Most recent call last:

    * File "/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/web/main.py", line 417, in _dispatch_request
      Code fragment:
       412. try:
       413. if not env and env_error:
       414. raise HTTPInternalError(env_error)
       415. try:
       416. dispatcher = RequestDispatcher(env)
       417. dispatcher.dispatch(req)
       418. except RequestDone:
       419. pass
       420. resp = req._response or []
       421.  
       422. except HTTPException, e:
      Local variables:
      Name  Value
      after     [u' except RequestDone:', u' pass', u' resp = ...
      before    [u' try:', u' if not env and env_error:', u' raise ...
      dispatcher    <trac.web.main.RequestDispatcher object at 0x2aaad0d9ab10>
      e     <_mysql_exceptions.ProgrammingError instance at 0x2aaad0d82518>
      env   <trac.env.Environment object at 0x2aaaccfc4ad0>
      env_error     None
      exc_info  (<class _mysql_exceptions.ProgrammingError at 0x2aaace3e24d0>, ...
      filename  '/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/web/main.py'
      frames    [{'function': '_dispatch_request', 'lines_before': [u' try:', u' ...
      has_admin     True
      line  u' dispatcher.dispatch(req)'
      lineno    416
      message   u'ProgrammingError: (1064, "You have an error in your SQL syntax; check ...
      req   <Request "GET u'/stractistics'">
      resp  []
      tb    <traceback object at 0x2aaad0d82c68>
      tb_hide   None
      traceback     'Traceback (most recent call last):\n File ...
    * File "/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/web/main.py", line 197, in dispatch
      Code fragment:
       192. req.args.get('__FORM_TOKEN') != req.form_token:
       193. raise HTTPBadRequest('Missing or invalid form token. '
       194. 'Do you have cookies enabled?')
       195.  
       196. # Process the request and render the template
       197. resp = chosen_handler.process_request(req)
       198. if resp:
       199. if len(resp) == 2: # Clearsilver
       200. chrome.populate_hdf(req)
       201. template, content_type = \
       202. self._post_process_request(req, *resp)
      Local variables:
      Name  Value
      chosen_handler    <stractistics.web_ui.StractisticsModule object at 0x2aaad0d80cd0>
      chrome    <trac.web.chrome.Chrome object at 0x2aaad0d9a790>
      err   (<class _mysql_exceptions.ProgrammingError at 0x2aaace3e24d0>, ...
      handler   <stractistics.web_ui.StractisticsModule object at 0x2aaad0d80cd0>
      req   <Request "GET u'/stractistics'">
      self  <trac.web.main.RequestDispatcher object at 0x2aaad0d9ab10>
    * File "build/bdist.linux-x86_64/egg/stractistics/web_ui.py", line 116, in process_request
      Local variables:
      Name  Value
      config    {'wiki_ignored_authors': [], 'repository_ignored_authors': [], ...
      db    <trac.db.pool.PooledConnection object at 0x2aaad0d82440>
      elem  ('User reports', 'user_reports')
      links     [('Project reports', '/stractistics/project_reports'), ('User reports', ...
      module    None
      req   <Request "GET u'/stractistics'">
      self  <stractistics.web_ui.StractisticsModule object at 0x2aaad0d80cd0>
      strac_ref     '/stractistics'
    * File "build/bdist.linux-x86_64/egg/stractistics/global_reports.py", line 31, in global_reports
      Local variables:
      Name  Value
      config    {'wiki_ignored_authors': [], 'repository_ignored_authors': [], ...
      db    <trac.db.pool.PooledConnection object at 0x2aaad0d82440>
      end_date  datetime.datetime(2008, 5, 28, 11, 9, 29, 652479)
      req   <Request "GET u'/stractistics'">
      start_date    datetime.datetime(2008, 3, 5, 11, 9, 29, 652479)
      weeks_back    12
    * File "build/bdist.linux-x86_64/egg/stractistics/global_reports.py", line 64, in _repository_activity
      Local variables:
      Name  Value
      AUTHORS_LIMIT     5
      WEEKS_NUMBER  12
      config    {'wiki_ignored_authors': [], 'repository_ignored_authors': [], ...
      db    <trac.db.pool.PooledConnection object at 0x2aaad0d82440>
      end_date  datetime.datetime(2008, 5, 28, 11, 9, 29, 652479)
      ignored_authors   ''
      req   <Request "GET u'/stractistics'">
      start_date    datetime.datetime(2008, 3, 5, 11, 9, 29, 652479)
      weeks_back    12
    * File "build/bdist.linux-x86_64/egg/stractistics/global_reports.py", line 113, in _most_active_repository_authors
      Local variables:
      Name  Value
      AUTHORS_LIMIT     5
      authors   []
      db    <trac.db.pool.PooledConnection object at 0x2aaad0d82440>
      end_date  datetime.datetime(2008, 5, 28, 11, 9, 29, 652479)
      ignored_authors   ''
      map_rows  <function map_rows at 0x2aaad0d855f0>
      sql_expr  '\n SELECT r.author AS author, COUNT( r.author ) AS commits \n FROM ...
      start_date    datetime.datetime(2008, 3, 5, 11, 9, 29, 652479)
    * File "build/bdist.linux-x86_64/egg/stractistics/util.py", line 117, in execute_sql_expression
      Local variables:
      Name  Value
      cursor    <trac.db.util.IterableCursor object at 0x2aaad0d9e6a8>
      db    <trac.db.pool.PooledConnection object at 0x2aaad0d82440>
      map_rows  <function map_rows at 0x2aaad0d855f0>
      sql_expr  '\n SELECT r.author AS author, COUNT( r.author ) AS commits \n FROM ...
    * File "/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/db/util.py", line 51, in execute
      Code fragment:
        46. # -- In case of SQL errors, uncomment the following 'print' statements
        47. # print 'execute', repr(sql)
        48. if args:
        49. # print repr(args)
        50. return self.cursor.execute(sql_escape_percent(sql), args)
        51. return self.cursor.execute(sql)
        52.  
        53. def executemany(self, sql, args=None):
        54. # print 'executemany', repr(sql)
        55. if args:
        56. # print repr(args)
      Local variables:
      Name  Value
      args  None
      self  <trac.db.util.IterableCursor object at 0x2aaad0d9e6a8>
      sql   '\n SELECT r.author AS author, COUNT( r.author ) AS commits \n FROM ...
    * File "/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/db/util.py", line 51, in execute
      Code fragment:
        46. # -- In case of SQL errors, uncomment the following 'print' statements
        47. # print 'execute', repr(sql)
        48. if args:
        49. # print repr(args)
        50. return self.cursor.execute(sql_escape_percent(sql), args)
        51. return self.cursor.execute(sql)
        52.  
        53. def executemany(self, sql, args=None):
        54. # print 'executemany', repr(sql)
        55. if args:
        56. # print repr(args)
      Local variables:
      Name  Value
      args  None
      self  <trac.db.util.IterableCursor object at 0x2aaad0d9ea28>
      sql   '\n SELECT r.author AS author, COUNT( r.author ) AS commits \n FROM ...
    * File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
      Code fragment:
       158. self.errorhandler(self, TypeError, m)
       159. except:
       160. exc, value, tb = exc_info()
       161. del tb
       162. self.messages.append((exc, value))
       163. self.errorhandler(self, exc, value)
       164. self._executed = query
       165. self._warning_check()
       166. return r
       167.  
       168. def executemany(self, query, args):
      Local variables:
      Name  Value
      ListType  <type 'list'>
      TupleType     <type 'tuple'>
      args  None
      charset   'utf8'
      db    <weakproxy at 0x2aaace3f8d08 to Connection at 0x2aaabcc7dfe0>
      exc   <class _mysql_exceptions.ProgrammingError at 0x2aaace3e24d0>
      exc_info  <built-in function exc_info>
      query     '\n SELECT r.author AS author, COUNT( r.author ) AS commits \n FROM ...
      self  <MySQLdb.cursors.Cursor object at 0x2aaad0d9b190>
      value     <_mysql_exceptions.ProgrammingError instance at 0x2aaad0d82518>
    * File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
      Code fragment:
        30. cursor.messages.append(error)
        31. else:
        32. connection.messages.append(error)
        33. del cursor
        34. del connection
        35. raise errorclass, errorvalue
        36.  
        37.  
        38. class Connection(_mysql.connection):
        39.  
        40. """MySQL Database Connection Object"""
      Local variables:
      Name  Value
      error     (<class _mysql_exceptions.ProgrammingError at 0x2aaace3e24d0>, ...
      errorclass    <class _mysql_exceptions.ProgrammingError at 0x2aaace3e24d0>
      errorvalue    <_mysql_exceptions.ProgrammingError instance at 0x2aaad0d82518>

File "/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/web/main.py", line 417, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/web/main.py", line 197, in dispatch
  resp = chosen_handler.process_request(req)
File "build/bdist.linux-x86_64/egg/stractistics/web_ui.py", line 116, in process_requestFile "build/bdist.linux-x86_64/egg/stractistics/global_reports.py", line 31, in global_reportsFile "build/bdist.linux-x86_64/egg/stractistics/global_reports.py", line 64, in _repository_activityFile "build/bdist.linux-x86_64/egg/stractistics/global_reports.py", line 113, in _most_active_repository_authorsFile "build/bdist.linux-x86_64/egg/stractistics/util.py", line 117, in execute_sql_expressionFile "/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/db/util.py", line 51, in execute
  return self.cursor.execute(sql)
File "/usr/lib/python2.4/site-packages/Trac-0.11rc1-py2.4.egg/trac/db/util.py", line 51, in execute
  return self.cursor.execute(sql)
File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
  self.errorhandler(self, exc, value)
File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
  raise errorclass, errorvalue

System Information:

User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14
Trac: 	0.11rc1
Python: 	2.4.3 (#1, Mar 14 2007, 19:01:42) [GCC 4.1.1 20070105 (Red Hat 4.1.1-52)]
setuptools: 	0.6c8
MySQL: 	server: "5.1.22-rc-log", client: "5.0.22", thread-safe: 0
MySQLdb: 	1.2.1
Genshi: 	0.5dev-r852
mod_python: 	3.2.8
Subversion: 	1.4.2 (r22196)
jQuery:	1.2.3

[hopson]

MySQL seems to require at least one value in an IN() clause. The ignored_repository_authors setting is causing this error.

I specified a user I didn't care about in my trac config>

[stractistics]
ignored_repository_authors = root

I don't know what the preferred Trac approach to getting around database-specific issues is, but I'll try to figure it out and submit a patch.

[anonymous]

I tried this :

ignored_repository_authors = root

but it does not seems to work... ???? any other idea, to run this cool pluggin ???

System Information:

User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.9.0.6) Gecko/2009011913 Firefox/3.0.6
Trac: 	0.11.2.1
Python: 	2.5.2 (r252:60911, Jul 31 2008, 17:44:49) [GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)]
setuptools: 	0.6c8
MySQL: 	server: "5.0.51a-3ubuntu5.1", client: "5.0.51a", thread-safe: 0
MySQLdb: 	1.2.2
Genshi: 	0.5.1
Subversion: 	1.4.6 (r28521)
mod_python: 	3.3.1
jQuery:	1.2.6

[dupond]

Duplicate Ticket #4730 closed

[dupond]

Further info from closed ticket #4730: If I copy and paste the SQL that it is complaining about into MySQL query browser and execute it, it runs fine. (after substituting %s for an integer - 5 in this case)

    SELECT r.author AS author, COUNT( r.author ) AS commits
     FROM revision r
     WHERE r.time > 5 AND r.time < 5 AND r.author NOT IN (5)
     GROUP BY r.author
     ORDER BY commits DESC
     LIMIT 5

[dupond]

in below code :

source:/stractisticsplugin/0.10/tags/0.4.2/stractistics/global_reports.py@head#L98

97 	    sql_expr = """
98 	    SELECT r.author AS author, COUNT( r.author ) AS commits
99 	     FROM revision r
100 	     WHERE r.time > %s AND r.time < %s AND r.author NOT IN (%s)
101 	     GROUP BY r.author
102 	     ORDER BY commits DESC
103 	     LIMIT %s
104 	    """

in comparing to the error generated :

(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 ')\n GROUP BY r.author \n ORDER BY commits DESC \n LIMIT 5' at line 3") 

It seems %s string in Python code is ended by the escape newline character "\n", and maybe MySQL does not like this kind of '\n' character inside the querry ?

It's just an idea to solve the bug, but unfortunately i don't speak SQL or Python :-/

[dupond]

i have tried with "%s" instead of %s, it's better

but in fact the following section in trac.ini solved the problem

[stractistics]
ignored_repository_authors = root
ignored_wiki_authors = root
repository_authors_limit = 5
wiki_authors_limit = 5