Edgewall Software
Modify

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#13216 closed defect (duplicate)

InvalidTextRepresentation: invalid input syntax for integer: ""

Reported by: massimo.b@… Owned by:
Priority: normal Milestone:
Component: query system Version: 1.2.3
Severity: normal Keywords: postgresql datetime
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description (last modified by 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

Attachments (0)

Change History (7)

comment:1 by massimo.b@…, 5 years ago

Description: modified (diff)

comment:2 by Jun Omae, 5 years ago

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='';

in reply to:  2 comment:3 by Jun Omae, 5 years ago

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='';

comment:4 by Jun Omae, 5 years ago

Component: generalquery system
Keywords: postgresql datetime added; ticket-custom removed
Resolution: duplicate
Status: newclosed

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+.

comment:5 by massimo.b@…, 5 years ago

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"…

Last edited 5 years ago by Jun Omae (previous) (diff)

in reply to:  5 ; comment:6 by Jun Omae, 5 years ago

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.

in reply to:  6 comment:7 by massimo.b@…, 5 years ago

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

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.