InvalidTextRepresentation: invalid input syntax for integer: ""

[massimo.b@…]

How to Reproduce

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

We have a custom field duedate:

[ticket-custom]
duedate = time                                                                                                                                                                                                                                                                                                             
duedate.format = date
duedate.label = Due date
duedate.order = 2

Doing a ticket query with 'Due date between 2019-10-01 and 2019-10-31' is failing like this:

Request parameters:

{u'col': [u'id',
          u'summary',
          u'status',
          u'type',
          u'priority',
          u'milestone',
          u'component'],
 u'duedate': u'2019-10-01..2019-10-31',
 u'order': u'priority',
 u'owner': u'MB',
 u'status': [u'accepted', u'assigned', u'new', u'reopened']}

User agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.9) Goanna/4.4 PaleMoon/28.7.1

System Information

Trac	1.2.3
Docutils	0.14
FullBlog	0.1.6.3
Genshi	0.7 (with speedups)
GIT	2.19.2
psycopg2	2.8.3 (dt dec pq3 ext lo64)
Pygments	2.4.2
Python	2.7.16 (default, Jun 26 2019, 15:04:57) [GCC 9.1.0]
pytz	2018.9
RPC	1.1.8.dev0
setuptools	41.2.0
Textile	3.0.3
jQuery	1.11.3
jQuery UI	1.11.4
jQuery Timepicker	1.5.5

Enabled Plugins

AZ-NotificationSubscriber	N/A
ComponentDependencyPlugin	0.1
FieldTooltip	0.7.3
IncludeSource	0.4
SimpleMultiProject	0.6.0.dev0
ticketsboardplugin	1.2.3.dev0
Trac-jsGantt	1.2.0.0.dev0
trac-NewTicketLikeThisPlugin	0.2
TracAccountManager	0.5.1.dev0
TracAutocompleteUsersPlugin	0.4.6.dev0
TracCodeReviewer	1.0.0.dev0
TracDiscussion	1.2.dev0
TracDynamicFields	2.3.0.dev0
TracEstimationTools	0.5.0.dev0
TracFullBlogPlugin	0.1.6.3
TracHtmlNotificationPlugin	0.12.0.1
TracHTTPAuth	1.2
TracIncludeMacro	3.2.0.dev0
TracMenusPlugin	0.3.0.dev0
TracQuiet	1.2.1.dev0
TracSubTicketsPlugin	0.5.4.dev0
TracTags	0.11.dev0
TracTicketFieldsLayoutPlugin	0.12.0.5
TracWikiExtras	1.3.1.dev0
TracWorkflowAdmin	0.12.0.5
TracXMLRPC	1.1.8.dev0
WikiAutoComplete	1.4.dev0
XMailPlugin	0.4.3

Interface Customization

shared-htdocs
shared-templates
site-htdocs	at_h64.png, at_h64_christmas.png, at_h64_test.png, at_h64_tracker.png, favicon.ico, logo, soft_h64.png, style.css, tracworkflowadmin
site-templates	account_details.html, admin_accountsconfig.html, admin_accountsnotification.html, admin_users.html, db_cleanup.html, login.html, prefs_account.html, register.html, reset_password.html, site.html, user_table.html, verify_email.html

Python Traceback

Traceback (most recent call last):
  File "/usr/lib64/python2.7/site-packages/trac/web/main.py", line 647, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/lib64/python2.7/site-packages/trac/web/main.py", line 248, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/lib64/python2.7/site-packages/trac/ticket/query.py", line 1016, in process_request
    return self.display_html(req, query)
  File "/usr/lib64/python2.7/site-packages/trac/ticket/query.py", line 1114, in display_html
    tickets = query.execute(req)
  File "/usr/lib64/python2.7/site-packages/trac/ticket/query.py", line 303, in execute
    self.num_items = self._count(sql, args)
  File "/usr/lib64/python2.7/site-packages/trac/ticket/query.py", line 284, in _count
    % sql, args)[0][0]
  File "/usr/lib64/python2.7/site-packages/trac/db/api.py", line 122, in execute
    return db.execute(query, params)
  File "/usr/lib64/python2.7/site-packages/trac/db/util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "/usr/lib64/python2.7/site-packages/trac/db/util.py", line 72, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
InvalidTextRepresentation: invalid input syntax for integer: ""

From Postgres DEBUG logs the statement looks like this:

Oct 30 09:36:00 [postgres] [7-1] 2019-10-30 09:36:00.770 CET [32241] ERROR:  invalid input syntax for integer: ""
Oct 30 09:36:00 [postgres] [7-2] 2019-10-30 09:36:00.770 CET [32241] STATEMENT:  SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.status AS status,t.type AS type,t.priority AS priority,t.milestone AS milestone,t.component AS component,t.time AS time,t.changetime AS changetime,t.owner AS owner,priority.value AS _priority_value,"duedate".value AS "duedate"
Oct 30 09:36:00 [postgres] [7-3] _FROM ticket AS t
Oct 30 09:36:00 [postgres] [7-4] _  LEFT OUTER JOIN ticket_custom AS "duedate" ON ("duedate".ticket=t.id AND "duedate".name='duedate')
Oct 30 09:36:00 [postgres] [7-5] _  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=t.priority)
Oct 30 09:36:00 [postgres] [7-6] _WHERE ((COALESCE(t.owner,'')='MB') AND COALESCE(t.status,'') IN ('accepted','assigned','new','reopened') AND ((CAST("duedate".value AS bigint)>=1569880800000000 AND CAST("duedate".value AS bigint)<1572476400000000)))
Oct 30 09:36:00 [postgres] [7-7] _ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id) AS x

[Jun Omae]

That is caused by empty duedate values in your ticket_custom table. Work around is to update NULL for the duedate values:

UPDATE ticket_custom SET value=NULL WHERE name='duedate' AND value='';

[Jun Omae]

Work around is to update NULL for the duedate values:

UPDATE ticket_custom SET value=NULL WHERE name='duedate' AND value='';

Well, better work around to delete such records rather than:

DELETE FROM ticket_custom WHERE name='duedate' AND value='';

[Jun Omae]

A duplicate of #13078 and it seems that you've reported the same issue on the mailing list at ​​gdiscussion:trac-users:e7NvYuzgQKk. Please upgrade to Trac 1.2.4+.

[massimo.b@…]

The workaround works temporarilly. Touching other tickets there are value='' created again in the database. Then there is no way to create a query for "not set" or "not between" in Trac. It is only possible to create query for "duedate between dateA and dateB"…

[Jun Omae]

Then there is no way to create a query for "not set" or "not between" in Trac. It is only possible to create query for "duedate between dateA and dateB"…

not between condition for time fields is not available in query view, but it is available via query: link, e.g. time=!datetime1..datetime2

query:id=13210-13220&time=!2019-10-30T08:47:27Z..2019-10-30T08:47:28Z

Please create new ticket for enhancement request after searching the same request.

[massimo.b@…]

Replying to Jun Omae:

Then there is no way to create a query for "not set" or "not between" in Trac. It is only possible to create query for "duedate between dateA and dateB"…

Please create new ticket for enhancement request after searching the same request.

→ #13222