ProgrammingError: You cannot execute SELECT statements in executemany().

[matteo.devilli@…]

How to Reproduce

While doing a POST operation on /newticket, Trac issued an internal error.

(please provide additional details here)

Request parameters:

{'__FORM_TOKEN': u'5eabd14b40fbec3d0683fa58',
 'field_billable': u'1',
 'field_blockedby': u'',
 'field_blocking': u'',
 'field_cc': u'',
 'field_checkbox_billable': u'1',
 'field_component': u'HW-Mecc/El',
 'field_description': u'',
 'field_estimatedhours': u'0',
 'field_hours': u'0',
 'field_keywords': u'',
 'field_milestone': u'',
 'field_owner': u'',
 'field_parent': u'',
 'field_parents': u'',
 'field_priority': u'major',
 'field_project': u'',
 'field_reporter': u'devilli',
 'field_summary': u'prova',
 'field_type': u'sviluppo',
 'field_userfinish': u'2013-11-13',
 'field_userstart': u'2013-11-13',
 'submit': u'Create ticket'}

User agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:25.0) Gecko/20100101 Firefox/25.0

System Information

Trac	1.0.1
Genshi	0.6 (without speedups)
Pygments	1.4
pysqlite	2.4.1
Python	2.6.5 (r265:79063, Apr 16 2010, 13:09:56) [GCC 4.4.3]
setuptools	0.6c11
SQLite	3.6.22
Subversion	1.6.6 (r40053)
jQuery	1.7.2

Enabled Plugins

BatchModify	0.8.1-trac0.12dev
SimpleMultiProject	0.0.3dev
timingandestimationplugin	1.3.7
Trac-jsGantt	0.10-r13401
Tracchildtickets	2.5.2
TracSubTicketsPlugin	0.2.0.dev-20131113

Python Traceback

Traceback (most recent call last):
  File "build/bdist.linux-i686/egg/trac/web/main.py", line 497, in _dispatch_request
    dispatcher.dispatch(req)
  File "build/bdist.linux-i686/egg/trac/web/main.py", line 214, in dispatch
    resp = chosen_handler.process_request(req)
  File "build/bdist.linux-i686/egg/trac/ticket/web_ui.py", line 180, in process_request
    return self._process_newticket_request(req)
  File "build/bdist.linux-i686/egg/trac/ticket/web_ui.py", line 465, in _process_newticket_request
    self._do_create(req, ticket) # (redirected if successful)
  File "build/bdist.linux-i686/egg/trac/ticket/web_ui.py", line 1289, in _do_create
    ticket.insert()
  File "build/bdist.linux-i686/egg/trac/ticket/model.py", line 256, in insert
    listener.ticket_created(self)
  File "build/bdist.linux-i686/egg/tracjsgantt/tracpm.py", line 2946, in ticket_created
    self.rescheduleTickets(ticket, {})
  File "build/bdist.linux-i686/egg/tracjsgantt/tracpm.py", line 2741, in rescheduleTickets
    ids = self._findAffected(ticket, old_values)
  File "build/bdist.linux-i686/egg/tracjsgantt/tracpm.py", line 2443, in _findAffected
    toExplore = more(border)
  File "build/bdist.linux-i686/egg/tracjsgantt/tracpm.py", line 2402, in more
    newOwners = ownersOf(n)
  File "build/bdist.linux-i686/egg/tracjsgantt/tracpm.py", line 2322, in ownersOf
    cursor.executemany(query, values)
  File "build/bdist.linux-i686/egg/trac/db/util.py", line 85, in executemany
    return self.cursor.executemany(sql_escape_percent(sql), args)
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 62, in executemany
    args)
  File "build/bdist.linux-i686/egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
ProgrammingError: You cannot execute SELECT statements in executemany().

[anonymous]

Is there a way to fix this so that the plugin will work on Trac 1.0.1?

[anonymous]

here is the tested fix:

tracjsgantt/tracpm.py

@@ -2323 +2323 @@
             values = []
             for tid in ids:
                 values.append([ tid ])
-            cursor.executemany(query, values)
-            owners = [row[0] for row in cursor]
+            owners = []
+            for id in values:
+                cursor.execute(query, id)
+                rows = cursor.fetchall()
+                owner.append(row[0])
             return set(owners)
 
         # Helper to find open tickets by owners

[anonymous]

shouldn't line 2330

owner.append(row[0])

be

owner.append(rows[0])

?

[roiji]

owner.append(rows[0]) to owners.append(rows[0])

owners with an s

[roiji <neoxlaws@…>]

when i implemented the above fix, i got this error:

Trac detected an internal error:
    InterfaceError: Error binding parameter 1 - probably unsupported type.

did I break anything?

will installing a 1.1.x dev build instead remove this?

Python Traceback

Most recent call last:
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/web/main.py", line 497, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/web/main.py", line 214, in dispatch
  resp = chosen_handler.process_request(req)
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/ticket/web_ui.py", line 180, in process_request
  return self._process_newticket_request(req)
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/ticket/web_ui.py", line 465, in _process_newticket_request
  self._do_create(req, ticket) # (redirected if successful)
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/ticket/web_ui.py", line 1289, in _do_create
  ticket.insert()
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/ticket/model.py", line 256, in insert
  listener.ticket_created(self)
File "/usr/local/lib/python2.7/dist-packages/Trac_jsGantt-0.10_r0-py2.7.egg/tracjsgantt/tracpm.py", line 2953, in ticket_created
  self.rescheduleTickets(ticket, {})
File "/usr/local/lib/python2.7/dist-packages/Trac_jsGantt-0.10_r0-py2.7.egg/tracjsgantt/tracpm.py", line 2748, in rescheduleTickets
  ids = self._findAffected(ticket, old_values)
File "/usr/local/lib/python2.7/dist-packages/Trac_jsGantt-0.10_r0-py2.7.egg/tracjsgantt/tracpm.py", line 2450, in _findAffected
  toExplore = more(border)
File "/usr/local/lib/python2.7/dist-packages/Trac_jsGantt-0.10_r0-py2.7.egg/tracjsgantt/tracpm.py", line 2420, in more
  x = openByOwner(newOwners)
File "/usr/local/lib/python2.7/dist-packages/Trac_jsGantt-0.10_r0-py2.7.egg/tracjsgantt/tracpm.py", line 2346, in openByOwner
  ['closed'] + list(owners))
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/db/util.py", line 65, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/db/sqlite_backend.py", line 78, in execute
  result = PyFormatCursor.execute(self, *args)
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/db/sqlite_backend.py", line 56, in execute
  args or [])
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.1-py2.7.egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
  return function(self, *args, **kwargs)

System Information

Trac	1.0.1
Genshi	0.7 (with speedups)
mod_wsgi	3.4 (WSGIProcessGroup WSGIApplicationGroup %{GLOBAL})
Pygments	1.6
pysqlite	2.6.0
Python	2.7.5+ (default, Sep 19 2013, 13:52:09) [GCC 4.8.1]
setuptools	0.6
SQLite	3.7.17
jQuery	1.7.2

Enabled PLugins

Trac-jsGantt	0.10-r0	/usr/local/lib/python2.7/dist-packages/Trac_jsGantt-0.10_r0-py2.7.egg
TracAccountManager	0.4.3	/usr/local/lib/python2.7/dist-packages/TracAccountManager-0.4.3-py2.7.egg
TracDateField	1.1.0-r0	/usr/local/lib/python2.7/dist-packages/TracDateField-1.1.0_r0-py2.7.egg

installed using easy_install

[anonymous]

Replying to anonymous:

here is the tested fix:

tracjsgantt/tracpm.py

@@ -2323 +2323 @@
             values = []
             for tid in ids:
                 values.append([ tid ])
-            cursor.executemany(query, values)
-            owners = [row[0] for row in cursor]
+            owners = []
+            for id in values:
+                cursor.execute(query, id)
+                rows = cursor.fetchall()
+                owner.append(row[0])
             return set(owners)
 
         # Helper to find open tickets by owners

This change produces server errors for us.

[anonymous]

Replying to anonymous:

Replying to anonymous:

here is the tested fix:

tracjsgantt/tracpm.py

@@ -2323 +2323 @@
             values = []
             for tid in ids:
                 values.append([ tid ])
-            cursor.executemany(query, values)
-            owners = [row[0] for row in cursor]
+            owners = []
+            for id in values:
+                cursor.execute(query, id)
+                rows = cursor.fetchall()
+                owner.append(row[0])
             return set(owners)
 
         # Helper to find open tickets by owners

This change produces server errors for us.'

Should note, the errors were caused by not refreshing apache, however once done we're still having issues with the plugin, looks like its related to variable types now.

[Ryan J Ollos]

Replying to anonymous:

Should note, the errors were caused by not refreshing apache, however once done we're still having issues with the plugin, looks like its related to variable types now.

Did you read comment:7?

[krixkrix]

I have exactly the problem as anonymous (trac 1.0)

Original problem: "You cannot execute SELECT statements in executemany()"

=> added the patch listed above (including the 2x s)

Next problem: InterfaceError: Error binding parameter 1 - probably unsupported type

=> the patch causes tuples to be added to the set of "owners", where it should be plain strings.

Though I am not sure if this is a proper fix, I can say that is helps to pull out the first item in the tupple:

tracjsgantt/tracpm.py

@@ -2323 +2323 @@
             values = []
             for tid in ids:
                 values.append([ tid ])
-            cursor.executemany(query, values)
-            owners = [row[0] for row in cursor]
+            owners = []
+            for id in values:
+                cursor.execute(query, id)
+                rows = cursor.fetchall()
+                # rows[0] is a tupple!
+                owners.append(rows[0][0])
             return set(owners)
 
         # Helper to find open tickets by owners

[anonymous]

I have the exact same problem and this fix doesn't help...

Trac detected an internal error:
ProgrammingError: You cannot execute SELECT statements in executemany().

Python Traceback

Most recent call last:
File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 497, in _dispatch_request
  dispatcher.dispatch(req)
File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 214, in dispatch
  resp = chosen_handler.process_request(req)
File "build/bdist.linux-x86_64/egg/trac/ticket/web_ui.py", line 179, in process_request
  return self._process_ticket_request(req)
File "build/bdist.linux-x86_64/egg/trac/ticket/web_ui.py", line 614, in _process_ticket_request
  self._do_save(req, ticket, action)
File "build/bdist.linux-x86_64/egg/trac/ticket/web_ui.py", line 1328, in _do_save
  replyto=req.args.get('replyto'))
File "build/bdist.linux-x86_64/egg/trac/ticket/model.py", line 365, in save_changes
  listener.ticket_changed(self, comment, author, old_values)
File "build/bdist.linux-x86_64/egg/tracjsgantt/tracpm.py", line 2963, in ticket_changed
  self.rescheduleTickets(ticket, old_values)
File "build/bdist.linux-x86_64/egg/tracjsgantt/tracpm.py", line 2751, in rescheduleTickets
  ids = self._findAffected(ticket, old_values)
File "build/bdist.linux-x86_64/egg/tracjsgantt/tracpm.py", line 2453, in _findAffected
  toExplore = more(border)
File "build/bdist.linux-x86_64/egg/tracjsgantt/tracpm.py", line 2412, in more
  newOwners = ownersOf(n)
File "build/bdist.linux-x86_64/egg/tracjsgantt/tracpm.py", line 2326, in ownersOf
  cursor.executemany(query, values)
File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 85, in executemany
  return self.cursor.executemany(sql_escape_percent(sql), args)
File "build/bdist.linux-x86_64/egg/trac/db/sqlite_backend.py", line 62, in executemany
  args)
File "build/bdist.linux-x86_64/egg/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
  return function(self, *args, **kwargs)

[krixkrix]

Are you sure you made the change (remove red lines, insert green lines) ?

Your stack trace shows an "executemany" call in line 2326, which should not be there if you have applied the patch

[tsmelyanskiy@…]

I found mistake in my server. Thanks alot for your help. Now it works fine.

[tsmelyanskiy@…]

I have another problem. I installed Trac 1.0 and installed TracJsGanttPlugin as described. But I don't see any changes in my Trac interface. I don'r see Start and End date for tickets and I don't see Gantt diagram. Also I don't see this plugin in the list of installed plugins from Trac. Does this plugim maitain Trac 1.0?

[Chris Nelson]

Replying to tsmelyanskiy@…:

I have another problem. I installed Trac 1.0 and installed TracJsGanttPlugin as described. But I don't see any changes in my Trac interface. I don'r see Start and End date for tickets and I don't see Gantt diagram. Also I don't see this plugin in the list of installed plugins from Trac. Does this plugim maitain Trac 1.0?

We are presently working on moving our installation from 0.11.6 to 1.x. We'll make sure this works there as we move. It may take a month or more. Sorry.

[Chris Nelson]

r13705 may address this. Not sure why that didn't link to this ticket.

[Ryan J Ollos]

Replying to ChrisNelson:

r13705 may address this. Not sure why that didn't link to this ticket.

Each ticket id needs to be prefix with a hash: Refs #11287, 9648, 11385 -> Refs #11287, #9648, #11385

[tsmelyanskiy@…]

Replying to Chris.Nelson@…:

Replying to tsmelyanskiy@…:

I have another problem. I installed Trac 1.0 and installed TracJsGanttPlugin as described. But I don't see any changes in my Trac interface. I don'r see Start and End date for tickets and I don't see Gantt diagram. Also I don't see this plugin in the list of installed plugins from Trac. Does this plugim maitain Trac 1.0?

We are presently working on moving our installation from 0.11.6 to 1.x. We'll make sure this works there as we move. It may take a month or more. Sorry.

Ok. Thaks. We will find all new information here "TracJsGanttPlugin", right?

[Chris Nelson]

Replying to tsmelyanskiy@…:

... Ok. Thaks. We will find all new information here "TracJsGanttPlugin", right?

Yes.

[anonymous]

Using the TracJsGanttPlugin 13669, trac 12.5 with embedded SQLite3, the error occurs with the same traceback seen in this ticket body...

[Chris Nelson]

The code referenced above does not exist in the current code. Please let me know if changes pushed to T-H recently fix the problem for you.