Modify

Opened 6 years ago

Last modified 11 months ago

#3088 assigned defect

Error in MySQL Query Syntax

Reported by: abidoon@… Owned by: rjollos
Priority: highest Component: StractisticsPlugin
Severity: blocker Keywords:
Cc: dagomez@…, mjrecena@… Trac Release: 0.11

Description (last modified by rjollos)

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

Attachments (0)

Change History (13)

comment:1 Changed 6 years ago by 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.

comment:2 Changed 6 years ago by 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

comment:3 Changed 6 years ago by anonymous

  • Cc dagomez@… mjrecena@… added; anonymous removed
  • Owner changed from dagomez to anonymous
  • Status changed from new to assigned

comment:4 Changed 6 years ago by anonymous

  • Owner changed from anonymous to mjrecena@…
  • Status changed from assigned to new

comment:5 Changed 6 years ago by dupond

Duplicate Ticket #4730 closed

comment:6 Changed 6 years ago by anonymous

  • Cc chris.bennell@… added

comment:7 Changed 6 years ago by 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

comment:8 Changed 6 years ago by 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 :-/

comment:9 Changed 6 years ago by 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

comment:10 Changed 4 years ago by rjollos

  • Description modified (diff)

comment:11 Changed 4 years ago by rjollos

  • Owner changed from mjrecena@… to rjollos

comment:12 Changed 3 years ago by anonymous

  • Cc chris.bennell@… removed

comment:13 Changed 11 months ago by rjollos

  • Status changed from new to assigned

Add Comment

Modify Ticket

Action
as assigned The owner will remain rjollos.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.