Modify

Opened 5 years ago

Closed 4 years ago

Last modified 15 months ago

#5568 closed defect (fixed)

[Patch] SQL query failures with MySQL

Reported by: scouret@… Owned by: rjollos
Priority: normal Component: TracTicketStatsPlugin
Severity: blocker Keywords: mysql
Cc: Trac Release: 0.11

Description (last modified by rjollos)

Hello,

I ran into SQL requests issues while running TracTicketStatsPlugin with a mysql backend :

  1. (1054, "Unknown column 'created' in 'where clause'") This is due to Mysql poor Alias handling, i propose to use the column name (t.time) instead of alias name(created) as shown in following patch :
ticketstats.py 
@@ -88,13 +88,13 @@
-               cursor.execute("SELECT t.type AS type, owner, status, time AS created FROM ticket t, enum p WHERE p.name = t.priority AND p.type = 'priority' AND created <= %s" % to_timestamp(at_date))
+               cursor.execute("SELECT t.type AS type, owner, status, time AS created FROM ticket t, enum p WHERE p.name = t.priority AND p.type = 'priority' AND t.time <= %s" % to_timestamp(at_date))
  1. The next issue is related with the use of 'enum p' . Here comes the proposed patch :
ticketstats.py 
@@ -61,7 +61,7 @@
-               cursor.execute("SELECT t.id, tc.field, tc.time, tc.oldvalue, tc.newvalue, t.priority FROM ticket_change tc, enum p INNER JOIN ticket t ON t.id = tc.ticket AND tc.time > %s AND tc.time <= %s WHERE p.name = t.priority AND p.type = 'priority' ORDER BY tc.time" % (to_timestamp(from_date), to_timestamp(at_date)))
+               cursor.execute("SELECT t.id, tc.field, tc.time, tc.oldvalue, tc.newvalue, t.priority FROM enum p, ticket_change tc INNER JOIN ticket t ON t.id = tc.ticket AND tc.time > %s AND tc.time <= %s WHERE p.name = t.priority AND p.type = 'priority' ORDER BY tc.time" % (to_timestamp(from_date), to_timestamp(at_date)))

Hope this helps. Thanks for all your good job with this plugin

Attachments (1)

mysql.patch (2.3 KB) - added by bart 5 years ago.
patch for the MySQL issues

Download all attachments as: .zip

Change History (12)

comment:1 follow-up: Changed 5 years ago by anonymous

omg, thanks for the fix was driving me crazy... could it be that enum is some reserved word?

comment:2 in reply to: ↑ 1 Changed 5 years ago by anonymous

Replying to anonymous:

omg, thanks for the fix was driving me crazy... could it be that enum is some reserved word?

No.

First issue is that the column aliases are not allowed in WHERE clause.
http://dev.mysql.com/doc/refman/5.1/en/problems-with-alias.html

Second is that the JOIN has the higher precedence, so the table being joined must be next to JOIN clause (i.e. must go after tables not participating in JOIN).
http://dev.mysql.com/doc/refman/5.1/en/join.html

BTW. I was about to submit same patches.

Changed 5 years ago by bart

patch for the MySQL issues

comment:3 Changed 4 years ago by rjollos

  • Summary changed from Mysql requests issues with TracTicketStatsPlugin to [Patch] SQL query failures with MySQL

comment:4 Changed 4 years ago by rjollos

  • Owner changed from echo0101 to rjollos

Reassigning ticket to new maintainer.

comment:5 Changed 4 years ago by sterun

After applying the patch it works fine as long as Milestone = 'All Milestones'. If I choose a specific milestone I get a similar error though:

2010-12-09 16:10:54,163 Trac[main] ERROR: column "<milestone_name>" does not exist

comment:6 Changed 4 years ago by sterun

Please ignore the previous comment. I installed the patch incorrectly.

comment:7 Changed 4 years ago by rjollos

Thanks for letting me know that the patch works with MySQL. I'll test it with SQLite and apply if it doesn't break anything.

comment:8 Changed 4 years ago by rjollos

  • Description modified (diff)
  • Status changed from new to assigned

comment:9 Changed 4 years ago by rjollos

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

(In [9660]) Fixed SQL queries that were incompatible with MySQL. Fixes #5568.

comment:10 Changed 4 years ago by rjollos

(In [9661]) Merged [9660] from trunk to branches/0.11 and branches/0.12. Refs #5568.

comment:11 Changed 15 months ago by rjollos

(In [13104]) Refs #8600, #5568, #3594: Removed the 0.11 and 0.12 branches. The trunk will be kept compatible with 0.11 and higher for now.

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from rjollos. Next status will be '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.