Modify

Opened 8 years ago

Closed 7 years ago

Last modified 4 years ago

#5568 closed defect (fixed)

[Patch] SQL query failures with MySQL

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

Description (last modified by Ryan J Ollos)

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 Ogryczak 8 years ago.
patch for the MySQL issues

Download all attachments as: .zip

Change History (12)

comment:1 Changed 8 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 8 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 8 years ago by Bart Ogryczak

Attachment: mysql.patch added

patch for the MySQL issues

comment:3 Changed 7 years ago by Ryan J Ollos

Summary: Mysql requests issues with TracTicketStatsPlugin[Patch] SQL query failures with MySQL

comment:4 Changed 7 years ago by Ryan J Ollos

Owner: changed from Prentice Wongvibulsin to Ryan J Ollos

Reassigning ticket to new maintainer.

comment:5 Changed 7 years ago by Stefan Rungardt

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 7 years ago by Stefan Rungardt

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

comment:7 Changed 7 years ago by Ryan J Ollos

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 7 years ago by Ryan J Ollos

Description: modified (diff)
Status: newassigned

comment:9 Changed 7 years ago by Ryan J Ollos

Resolution: fixed
Status: assignedclosed

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

comment:10 Changed 7 years ago by Ryan J Ollos

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

comment:11 Changed 4 years ago by Ryan J Ollos

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

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Ryan J Ollos.
The resolution will be deleted.

Add Comment


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

 
Note: See TracTickets for help on using tickets.