Modify

Opened 3 years ago

Closed 23 months ago

#8998 closed defect (fixed)

stray u in sql query

Reported by: erez0001@… Owned by: ChrisNelson
Priority: high Component: TracJsGanttPlugin
Severity: blocker Keywords:
Cc: rjollos, falkb Trac Release: 0.11

Description

wiki page containing:

[[TracJSGanttChart(milestone=FieldTest1)]]

does not display a gantt chart, but rather displays:

Error: Macro TracJSGanttChart(milestone=FieldTest1) failed
expected string or buffer

revision is 10480.

log contains the folowing sql query:

SELECT t.id AS id,t.description AS description,t.owner AS owner,t.type AS type,t.status AS status,t.summary AS summary,t.milestone AS milestone,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,blockedby.value AS blockedby,blocking.value AS blocking
FROM ticket AS t LEFT OUTER JOIN ticket_custom AS blockedby ON (id=blockedby.ticket AND blockedby.name='blockedby')LEFT OUTER JOIN ticket_custom AS blocking ON (id=blocking.ticket AND blocking.name='blocking')LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE COALESCE(t.milestone,'')=u'FieldTest1' ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id

trying to type it directly to sqlite3 - sqlite3 complains about the stray u before the 'FieldTest1'
i.e. COALESCE(t.milestone,'')=u'FieldTest1' instead of
COALESCE(t.milestone,'')='FieldTest1'

removing the stray u on the sqlite3 sql query, and it seems to be a valid query.

log:

2011-07-17 12:46:25,657 Trac[query] DEBUG: Query SQL: SELECT t.id AS id,t.description AS description,t.owner AS owner,t.type AS type,t.status AS status,t.summary AS summary,t.milestone AS milestone,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,blockedby.value AS blockedby,blocking.value AS blocking
FROM ticket AS t
  LEFT OUTER JOIN ticket_custom AS blockedby ON (id=blockedby.ticket AND blockedby.name='blockedby')
  LEFT OUTER JOIN ticket_custom AS blocking ON (id=blocking.ticket AND blocking.name='blocking')
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE COALESCE(t.milestone,'')=u'FieldTest1'
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS int),t.id
2011-07-17 12:46:25,658 Trac[formatter] ERROR: Macro TracJSGanttChart(milestone=FieldTest1) failed: 
Traceback (most recent call last):
  File "/usr/lib/python2.6/dist-packages/trac/wiki/formatter.py", line 484, in _macro_formatter
    return macro.process(args, in_paragraph=True)
  File "/usr/lib/python2.6/dist-packages/trac/wiki/formatter.py", line 180, in process
    text = self.processor(text)
  File "/usr/lib/python2.6/dist-packages/trac/wiki/formatter.py", line 167, in _macro_processor
    text)
  File "/usr/local/lib/python2.6/dist-packages/Trac_jsGantt-0.9_r10480-py2.6.egg/tracjsgantt/tracjsgantt.py", line 809, in expand_macro
    tasks = self._add_tasks(options)
  File "/usr/local/lib/python2.6/dist-packages/Trac_jsGantt-0.9_r10480-py2.6.egg/tracjsgantt/tracjsgantt.py", line 767, in _add_tasks
    self._schedule_tasks()
  File "/usr/local/lib/python2.6/dist-packages/Trac_jsGantt-0.9_r10480-py2.6.egg/tracjsgantt/tracjsgantt.py", line 472, in _schedule_tasks
    t['calc_start'] = _start(t)
  File "/usr/local/lib/python2.6/dist-packages/Trac_jsGantt-0.9_r10480-py2.6.egg/tracjsgantt/tracjsgantt.py", line 434, in _start
    finish = datetime.datetime(*time.strptime(_finish(ticket), self.pyDateFormat)[0:7])
  File "/usr/local/lib/python2.6/dist-packages/Trac_jsGantt-0.9_r10480-py2.6.egg/tracjsgantt/tracjsgantt.py", line 459, in _finish
    f = datetime.datetime(*time.strptime(succ[self.fields['finish']], self.dbDateFormat)[0:7])
  File "/usr/lib/python2.6/_strptime.py", line 454, in _strptime_time
    return _strptime(data_string, format)[0]
  File "/usr/lib/python2.6/_strptime.py", line 322, in _strptime
    found = format_regex.match(data_string)
TypeError: expected string or buffer
2011-07-17 12:46:25,685 Trac[main] DEBUG: Dispatching <Request "GET u'/chrome/tracjsgantt/jsgantt.css'">
2011-07-17 12:46:25,686 Trac[main] DEBUG: Dispatching <Request "GET u'/chrome/tracjsgantt/tracjsgantt.css'">
2011-07-17 12:46:25,732 Trac[main] DEBUG: Dispatching <Request "GET u'/chrome/tracjsgantt/jsgantt.js'">

Attachments (0)

Change History (19)

comment:1 Changed 3 years ago by rjollos

  • Cc rjollos added

I saw a similar issue posted recently for another plugin. My understanding is that the u types the string as unicode, and I doubt the correct solution is to remove the u.

Some information about your Trac installation might be helpful (can be copied from the System Information section on the About page).

comment:2 Changed 3 years ago by erez0001@…

At least the sqlite3 commandline tool does not know how to handle the u.

system information:

System Information
Trac: 	0.11.7
Python: 	2.6.6 (r266:84292, Sep 15 2010, 16:41:53) [GCC 4.4.5]
setuptools: 	0.6
SQLite: 	3.7.2
pysqlite: 	2.4.1
Genshi: 	0.6
mod_python: 	3.3.1
Pygments: 	1.3.1
Subversion: 	1.6.12 (r955767)
CustomFieldAdmin: 	0.2.2
jQuery:	1.4.2
Configuration
Section 	Name 	Value
account-manager 	account_changes_notify_addresses 	
authentication_url 	
force_passwd_change 	True
generated_password_length 	8
hash_method 	HtDigestHashMethod
htdigest_realm 	
notify_actions 	
password_file 	
password_format 	
password_store 	
persistent_sessions 	
reset_password 	True
attachment 	max_size 	262144
render_unsafe_content 	false
browser 	color_scale 	True
downloadable_paths 	/trunk, /branches/*, /tags/*
hide_properties 	svk:merge
intermediate_color 	
intermediate_point 	
newest_color 	(255, 136, 136)
oldest_color 	(136, 136, 255)
oneliner_properties 	trac:summary
render_unsafe_content 	false
wiki_properties 	trac:description
changeset 	max_diff_bytes 	10000000
max_diff_files 	0
wiki_format_messages 	true
components 	customfieldadmin.api.customfields 	enabled
customfieldadmin.customfieldadmin.customfieldadminpage 	enabled
graphviz.graphviz.graphviz 	enabled
mastertickets.* 	enabled
timingandestimationplugin.api.timetrackingsetupparticipant 	enabled
timingandestimationplugin.query_webui.querywebuiaddon 	enabled
timingandestimationplugin.stopwatch.ticketstopwatch 	disabled
timingandestimationplugin.tande_filters.reportsfilter 	disabled
timingandestimationplugin.tande_filters.timeclickfilter 	disabled
timingandestimationplugin.tande_filters.totalhoursfilter 	disabled
timingandestimationplugin.ticket_daemon.timetrackingticketobserver 	disabled
timingandestimationplugin.ticket_daemon.timetrackingticketvalidator 	disabled
timingandestimationplugin.ticket_webui.ticketwebuiaddon 	enabled
timingandestimationplugin.webui.timingestimationandbillingpage 	disabled
tracjsgantt.* 	enabled
tracwysiwyg.templateprovider 	enabled
tracwysiwyg.wysiwygwikifilter 	enabled
wikirename.web_ui.wikirenamemodule 	enabled
graphviz 	cache_dir 	gvcache
cache_manager 	
cache_max_count 	2000
cache_max_size 	10485760
cache_min_count 	1500
cache_min_size 	5242880
cmd_path 	
default_graph_dpi 	96
encoding 	utf-8
out_format 	png
png_antialias 	
processor 	dot
rsvg_path 	
header_logo 	alt 	(please configure the [header_logo] section in trac.ini)
height 	-1
link 	
src 	/w.gif
width 	-1
inherit 	plugins_dir 	
templates_dir 	
logging 	log_file 	trac.log
log_format 	
log_level 	DEBUG
log_type 	file
mastertickets 	dot_path 	dot
gs_path 	gs
use_gs 	
milestone 	stats_provider 	DefaultTicketGroupStatsProvider
mimeviewer 	enscript_modes 	text/x-dylan:dylan:4
enscript_path 	enscript
max_preview_size 	262144
mime_map 	text/x-dylan:dylan,text/x-idl:ice,text/x-ada:ads:adb
php_path 	php
pygments_default_style 	trac
pygments_modes 	
tab_width 	8
treat_as_binary 	application/octet-stream,application/pdf,application/postscript,application/rtf
notification 	admit_domains 	
always_notify_owner 	true
always_notify_reporter 	true
always_notify_updater 	true
ignore_domains 	
mime_encoding 	none
smtp_always_bcc 	
smtp_always_cc 	
smtp_default_domain 	
smtp_enabled 	true
smtp_from 	trac@gmail.com
smtp_from_name 	Trac
smtp_password 	
smtp_port 	25
smtp_replyto 	erez0001@gmail.com
smtp_server 	mail.netvision.net.il
smtp_subject_prefix 	[wsim]
smtp_user 	
ticket_subject_template 	$prefix #$ticket.id: $summary
use_public_cc 	false
use_short_addr 	false
use_tls 	false
project 	admin 	
admin_trac_url 	.
descr 	Buddy

icon 	common/trac.ico
name 	Buddy
url 	
query 	default_anonymous_query 	status!=closed&cc~=$USER
default_query 	status!=closed&owner=$USER
items_per_page 	100
report 	items_per_page 	100
items_per_page_rss 	0
revisionlog 	default_log_limit 	100
roadmap 	stats_provider 	DefaultTicketGroupStatsProvider
search 	min_query_length 	3
svn 	branches 	trunk,branches/*
tags 	tags/*
ticket 	default_cc 	
default_component 	
default_description 	
default_keywords 	
default_milestone 	milestone1
default_owner 	
default_priority 	major
default_resolution 	fixed
default_severity 	
default_summary 	
default_type 	defect
default_version 	1.0
max_comment_size 	262144
max_description_size 	262144
preserve_newlines 	default
restrict_owner 	false
workflow 	ConfigurableTicketWorkflow
ticket-custom 	billable 	checkbox
billable.label 	Billable?
billable.order 	3
billable.value 	1
blockedby 	text
blockedby.label 	Blocked By
blocking 	text
blocking.label 	Blocking
estimatedhours 	text
estimatedhours.label 	Estimated Number of Hours
estimatedhours.order 	1
estimatedhours.value 	0
hours 	text
hours.label 	Add Hours to Ticket
hours.order 	2
hours.value 	0
totalhours 	text
totalhours.label 	Total Hours
totalhours.order 	4
totalhours.value 	0
ticket-workflow 	accept 	new,assigned,accepted,reopened -> accepted
accept.operations 	set_owner_to_self
accept.permissions 	TICKET_MODIFY
leave 	* -> *
leave.default 	1
leave.operations 	leave_status
reassign 	new,assigned,accepted,reopened -> assigned
reassign.operations 	set_owner
reassign.permissions 	TICKET_MODIFY
reopen 	closed -> reopened
reopen.operations 	del_resolution
reopen.permissions 	TICKET_CREATE
resolve 	new,assigned,accepted,reopened -> closed
resolve.operations 	set_resolution
resolve.permissions 	TICKET_MODIFY
timeline 	abbreviated_messages 	True
changeset_collapse_events 	false
changeset_long_messages 	false
changeset_show_files 	0
default_daysback 	30
max_daysback 	90
newticket_formatter 	oneliner
ticket_show_details 	false
trac 	authz_file 	
authz_module_name 	
auto_reload 	False
backup_dir 	db
base_url 	
check_auth_ip 	false
database 	sqlite:db/trac.db
debug_sql 	False
default_charset 	utf-8
default_handler 	WikiModule
default_timezone 	
htdocs_location 	
ignore_auth_case 	false
mainnav 	wiki,timeline,roadmap,browser,tickets,newticket,search
metanav 	login,logout,prefs,help,about
mysqldump_path 	mysqldump
never_obfuscate_mailto 	false
permission_policies 	DefaultPermissionPolicy, LegacyAttachmentPolicy
permission_store 	DefaultPermissionStore
pg_dump_path 	pg_dump
repository_dir 	/home/repos/repo1
repository_type 	svn
request_filters 	
secure_cookies 	False
show_email_addresses 	false
show_ip_addresses 	false
timeout 	20
use_base_url_for_redirect 	False
trac-jsgantt 	fields.pred 	blockedby
fields.succ 	blocking
wiki 	ignore_missing_pages 	false
max_size 	262144
render_unsafe_content 	false
split_page_names 	false

comment:3 Changed 3 years ago by falkb

With current version http://trac-hacks.org/browser/tracjsganttplugin/0.11/tracjsgantt/tracjsgantt.py?rev=10837 I can easily provoke a similar error by just typing a bad datetime in the finish field of a ticket. Here I typed a 55 as finish time of my test ticket and get the following traceback. Probably the user simply has to correct his bad value in the ticket field, whatever he typed there in, it was not reported anyway. Though I cannot trigger the exact original error message, likely because I have other versions of Trac, Python, OS and the plugin itself.

2011-10-31 21:34:48,292 Trac[query] DEBUG: Query SQL: SELECT t.id AS id,t.description AS description,t.owner AS owner,t.type AS type,t.status AS status,t.summary AS summary,t.milestone AS milestone,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,`userfinish`.value AS `userfinish`,`parents`.value AS `parents`,`blockedby`.value AS `blockedby`,`userstart`.value AS `userstart`,`blocking`.value AS `blocking`,`estimatedhours`.value AS `estimatedhours`,`totalhours`.value AS `totalhours`
FROM ticket AS t
  LEFT OUTER JOIN ticket_custom AS `userfinish` ON (id=`userfinish`.ticket AND `userfinish`.name='userfinish')
  LEFT OUTER JOIN ticket_custom AS `parents` ON (id=`parents`.ticket AND `parents`.name='parents')
  LEFT OUTER JOIN ticket_custom AS `blockedby` ON (id=`blockedby`.ticket AND `blockedby`.name='blockedby')
  LEFT OUTER JOIN ticket_custom AS `userstart` ON (id=`userstart`.ticket AND `userstart`.name='userstart')
  LEFT OUTER JOIN ticket_custom AS `blocking` ON (id=`blocking`.ticket AND `blocking`.name='blocking')
  LEFT OUTER JOIN ticket_custom AS `estimatedhours` ON (id=`estimatedhours`.ticket AND `estimatedhours`.name='estimatedhours')
  LEFT OUTER JOIN ticket_custom AS `totalhours` ON (id=`totalhours`.ticket AND `totalhours`.name='totalhours')
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
WHERE ((COALESCE(t.milestone,'')=u'milestone1'))
ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS integer),t.id
2011-10-31 21:34:48,292 Trac[formatter] ERROR: Macro TracJSGanttChart(milestone=milestone1) failed: 
Traceback (most recent call last):
  File "build\bdist.win32\egg\trac\wiki\formatter.py", line 717, in _macro_formatter
    return macro.process(args, in_paragraph=True)
  File "build\bdist.win32\egg\trac\wiki\formatter.py", line 304, in process
    text = self.processor(text)
  File "build\bdist.win32\egg\trac\wiki\formatter.py", line 291, in _macro_processor
    text)
  File "build\bdist.win32\egg\tracjsgantt\tracjsgantt.py", line 1105, in expand_macro
    tasks = self._add_tasks(options)
  File "build\bdist.win32\egg\tracjsgantt\tracjsgantt.py", line 1063, in _add_tasks
    self._schedule_tasks(options)
  File "build\bdist.win32\egg\tracjsgantt\tracjsgantt.py", line 729, in _schedule_tasks
    _schedule_task_alap(t)
  File "build\bdist.win32\egg\tracjsgantt\tracjsgantt.py", line 607, in _schedule_task_alap
    self.dbDateFormat)[0:7])
  File "C:\Programme\BitNami Trac Stack\python\lib\_strptime.py", line 454, in _strptime_time
    return _strptime(data_string, format)[0]
  File "C:\Programme\BitNami Trac Stack\python\lib\_strptime.py", line 325, in _strptime
    (data_string, format))
ValueError: time data u'55' does not match format '%Y-%m-%d'

The web interface correctly reports that error in a red box

Error: Macro TracJSGanttChart(milestone=milestone1) failed
time data u'55' does not match format '%Y-%m-%d'

comment:4 follow-up: Changed 3 years ago by falkb

After update to [10866] the error turns into

...
  File "build\bdist.win32\egg\tracjsgantt\tracjsgantt.py", line 1060, in _add_tasks
    self._add_milestones(options)
  File "build\bdist.win32\egg\tracjsgantt\tracjsgantt.py", line 785, in _add_milestones
    ts.strftime(self.dbDateFormat)
AttributeError: 'long' object has no attribute 'strftime'

The reported error on the web page is new but again very unspecific. One cannot see which ticket and field causes the problem, and predicts it as plugin source bug.

A consistency check of all involved tickets before using them could help.

comment:5 in reply to: ↑ 4 Changed 3 years ago by falkb

Replying to falkb:

After update to [10866] the error turns into
AttributeError: 'long' object has no attribute 'strftime'

oh sorry, the new error has nothing to do with this ticket; it even so happens also if I remove that value 55 from ticket field userfinish. Seems to be a new problem after the update!

comment:6 Changed 3 years ago by ChrisNelson

(In [10876]) An attempt and forcing unicode to string. Refs #8998.

Not sure this is the right thing to do but it's worth a shot.

comment:7 Changed 3 years ago by ChrisNelson

Do you use EstimationToolsPlugin? If so, can you display Workload charts by milestone? (That is, substitute [[WorkloadChart(milestone=FieldTest1)]] for [[TracJSGanttChart(milestone=FieldTest1)]]?) If so, I'm really puzzled because I modeled my query usage on that plugin.

comment:8 follow-up: Changed 3 years ago by falkb

Now with

  1. [10876]
  2. and a bad string 55 in field userfinish (which I think is the actual problem)

I get:

...
  WHERE ((COALESCE(t.milestone,'')=u'FieldTest1'))
...
  File "build\bdist.win32\egg\tracjsgantt\tracjsgantt.py", line 733, in _schedule_tasks
    _schedule_task_alap(t)
  File "build\bdist.win32\egg\tracjsgantt\tracjsgantt.py", line 611, in _schedule_task_alap
    self.dbDateFormat)[0:7])
  File "D:\BITNAM~1.2\apache2\bin\lib\_strptime.py", line 330, in strptime
    (data_string, format))
ValueError: time data did not match format:  data=55  fmt=%Y-%m-%d

You can see we still have that u'FieldTest1'. And charts of EstimationToolsPlugin work fine with that milestone data.

If I correct the 55 to a proper datetime value or remove it, everything works well.

comment:9 in reply to: ↑ 8 ; follow-up: Changed 3 years ago by ChrisNelson

Replying to falkb:

Except that non-ASCII characters in a milestone name will keep it from displaying. I don't understand what I'm doing so different from EstimationTools that it works and the Gantt doesn't.

comment:10 in reply to: ↑ 9 ; follow-up: Changed 3 years ago by falkb

Replying to ChrisNelson:

Except that non-ASCII characters in a milestone name will keep it from displaying.

But non-ASCII chars in milestone names work. Look at #9277.

comment:11 Changed 3 years ago by anonymous

Output like u'FieldTest1' is usually a good indication that something uses the representation of the string (as repr(), %r, __repr__() or similar) instead of just the string. Is there anything that manipulates fields in a way that makes the field not provide a straight string version of itself anymore?

comment:12 in reply to: ↑ 10 ; follow-up: Changed 3 years ago by ChrisNelson

Replying to falkb:

Replying to ChrisNelson:

Except that non-ASCII characters in a milestone name will keep it from displaying.

But non-ASCII chars in milestone names work. Look at #9277.

They shouldn't after [10876] which calls str() on the milestone name before passing it to the query engine.

comment:13 in reply to: ↑ 12 Changed 3 years ago by falkb

Replying to ChrisNelson:

They shouldn't after [10876] which calls str() on the milestone name before passing it to the query engine.

Now I've fully understood this ticket. :) The actual problem here is not the failed call to _strptime() but just that little "u" in the query. Then I looked through the src of your plugin and EstimationToolsPlugin but don't see the right place to convert from unicode to ascii either.

I, for one, would read+convert to (and use from) internal plugin structures as much as possible; I mean instead of direct use of ticket data. That would also allow to call consistency checks on chart startup before using the data in your further processing.

comment:14 Changed 23 months ago by ChrisNelson

Is this working for you now? I had thought [10876] would help but don't see that I got a clear answer.

comment:15 follow-up: Changed 23 months ago by falkb

8 says value 55 in field userfinish is still a problem even after [10876]. Did you test that?

comment:16 Changed 23 months ago by ChrisNelson

(In [12169]) Catch parsing errors in user-supplied start and finish dates. Refs #8998.

comment:17 in reply to: ↑ 15 Changed 23 months ago by ChrisNelson

  • Status changed from new to assigned

Replying to falkb:

8 says value 55 in field userfinish is still a problem even after [10876]. Did you test that?

No. I wasn't too concerned with how it behaved with bad data. It now displays an error like:

Ticket 237 has an invalid userfinish value, "11". It should match the format "%Y-%m-%d".

comment:18 Changed 23 months ago by ChrisNelson

  • Cc falkb added

Can I close this?

comment:19 Changed 23 months ago by falkb

  • Resolution set to fixed
  • Status changed from assigned to closed

Tested, works good. Now it blocks on parsing errors with an error message that helps the user well to fix the problem.

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


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

 
Note: See TracTickets for help on using tickets.