Modify

Opened 5 years ago

Closed 5 years ago

#6179 closed defect (worksforme)

MySQL Error

Reported by: shubham_chakraborty@… Owned by: bobbysmith007
Priority: high Component: TimingAndEstimationPlugin
Severity: blocker Keywords: waiting-for-feedback
Cc: Trac Release: 0.11

Description

Report execution failed: (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 'DECIMAL) END as Estimated_work,\r\n CASE WHEN totalhours.value = OR totalhou' at line 10")

what is the query syntax error i need to change to make the timing and estimation plugin work in sync with Mysql database..?

Here is the query for Ticket Hours report????

SELECT color, style, ticket, summary, component ,version, severity,

milestone, status, owner, Estimated_work, Total_work, billable,_ord

FROM (

SELECT p.value AS color,

as style, t.id AS ticket, summary AS summary, -- ## Break line here component,version, severity, milestone, status, owner, CASE WHEN EstimatedHours.value = OR EstimatedHours.value IS NULL THEN 0

ELSE CAST( EstimatedHours.value AS DECIMAL) END as Estimated_work,

CASE WHEN totalhours.value = OR totalhours.value IS NULL THEN 0

ELSE CAST( totalhours.value AS DECIMAL ) END as Total_work,

CASE WHEN billable.value = '1' THEN 'Y' else 'N' END as billable, time AS created, changetime AS modified, -- ## Dates are formatted description AS _description_, -- ## Uses a full row changetime AS _changetime, reporter AS _reporter ,0 as _ord

FROM ticket as t JOIN enum as p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'

AND EstimatedHours.Ticket = t.Id

LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'

AND totalhours.Ticket = t.Id

LEFT JOIN ticket_custom as billable ON billable.name='billable'

AND billable.Ticket = t.Id

WHERE t.status IN ($NEEDINFO, $REOPENED, $ASSIGNED, $CLOSED, $NEW, $ACCEPTED, $PENDING)

AND billable.value in ($BILLABLE, $UNBILLABLE)

UNION

SELECT '1' AS color,

'background-color:#DFE;' as style, 0 as ticket, 'Total' AS summary, NULL as component,NULL as version, NULL as severity, NULL as milestone, 'Time Remaining: ' as status, CAST(

SUM(CASE WHEN EstimatedHours.value = OR EstimatedHours.value IS NULL THEN 0

ELSE CAST( EstimatedHours.value AS DECIMAL ) END) -

SUM(CASE WHEN totalhours.value = OR totalhours.value IS NULL THEN 0

ELSE CAST( totalhours.value AS DECIMAL ) END) AS CHAR(512)) as owner, SUM(CASE WHEN EstimatedHours.value = OR EstimatedHours.value IS NULL THEN 0

ELSE CAST( EstimatedHours.value AS DECIMAL ) END) as Estimated_work,

SUM(CASE WHEN totalhours.value = OR totalhours.value IS NULL THEN 0

ELSE CAST( totalhours.value AS DECIMAL ) END) as Total_work,

NULL as billable, NULL as created, NULL as modified, -- ## Dates are formatted

NULL AS _description_, NULL AS _changetime, NULL AS _reporter ,1 as _ord

FROM ticket as t JOIN enum as p ON p.name=t.priority AND p.type='priority'

LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'

AND EstimatedHours.Ticket = t.Id

LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'

AND totalhours.Ticket = t.Id

LEFT JOIN ticket_custom as billable ON billable.name='billable'

AND billable.Ticket = t.Id

WHERE t.status IN ($NEEDINFO, $REOPENED, $ASSIGNED, $CLOSED, $NEW, $ACCEPTED, $PENDING)

AND billable.value in ($BILLABLE, $UNBILLABLE)

) as tbl ORDER BY _ord ASC, ticket

Attachments (1)

TnE.jpg (119.4 KB) - added by shubham_chakraborty@… 5 years ago.
TnE 0.0 with 'unsigned' instead of 'decimal' in the report

Download all attachments as: .zip

Change History (8)

comment:1 Changed 5 years ago by bobbysmith007

  • Summary changed from mYSQL eRROR to MySQL Error

These reports have worked with mysql in the past, so I am going to need more information to help you out.

  • What version of mysql are you using?
  • What version of Timing and Estimation plugin are you using.
  • Please paste your system info (this is on the about page when logged in as trac-admin)
    • This will include many relevant version numbers

Thanks for your help

Russ

comment:2 Changed 5 years ago by anonymous

System Information Trac: 0.11 Python: 2.5.4 (r254:67916, Dec 23 2008, 15:10:54) [MSC v.1310 32 bit (Intel)] setuptools: 0.6c9 MySQL: server: "4.1.22-community-nt", client: "5.0.27", thread-safe: 1 MySQLdb: 1.2.2 Genshi: 0.5.1 mod_python: 3.3.1 CustomFieldAdmin: 0.2.2 jQuery: 1.2.3

Do you need the INI file as well....The above error was removed in the normal VIEW TICKETS when i changed the 'int' type to 'unsigned' Does DECIMAL here create the same problem???? I had replaced 'DECIMAL' with 'unsigned'to check if that is the cause of the problem, and that seemed to have removed the error message, although I am not getting results after that...maybe because the type never matches..or IS IT SOMETHING ELSE????

comment:3 Changed 5 years ago by shubham_chakraborty@…

http://trac-hacks.org/browser/timingandestimationplugin/branches/trac0.11-Permissions

This is the version of Timinig anf Estimation Plugin I am using.. The error and Report SQL query code is in the description above..

sry, i didnt put my name in the prev post.

comment:4 Changed 5 years ago by shubham_chakraborty@…

I found a new error, replacing DECIMAL with unsigned makes all the calculations to 0.0 in the hours column. Please provide a solution as tracking effort is very important in our project.

Changed 5 years ago by shubham_chakraborty@…

TnE 0.0 with 'unsigned' instead of 'decimal' in the report

comment:5 follow-up: Changed 5 years ago by bobbysmith007

Based on the documentation, DECIMAL and INT are both valid MySQL4 datatypes. As such I am not sure why you are experiencing this problem. Perhaps you need to enable the datatypes somehow? (I am not really a mysql user so I cant tell you the answer here). Also please save backups of your reports once you are done editing them (they can be overridden in an upgrade).

Changing DECIMAL to INT will definitely cause rounding errors everywhere (such as all partial hours to 0). However, you might be able to change DECIMAL to REAL or DOUBLE PRECISION and get reasonable results (though if you dont have the decimal type, I am not sure you will have those either).

comment:6 Changed 5 years ago by anonymous

  • Keywords waiting-for-feedback added

comment:7 in reply to: ↑ 5 Changed 5 years ago by bobbysmith007

  • Resolution set to worksforme
  • Status changed from new to closed

Replying to bobbysmith007:

Also please save backups of your reports once you are done editing them (they can be overridden in an upgrade).

You can also prevent this overwrite by editing the table custom_report in the database and set the version really high, or change the UUID to something else (which will allow it to reinstall the original reports while still having your reports in there).

Also since there have been no other reports, I am going to assume this is solved. Please reopen if you continue to have this problem

HTH, Russ

Add Comment

Modify Ticket

Action
as closed The owner will remain bobbysmith007.
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.