Ticket #3359 (new defect)

Opened 5 years ago

Last modified 8 months ago

Tickets tags don't work when using MySQL 4.x

Reported by: nigelsim Assigned to: hasienda
Priority: normal Component: TagsPlugin
Severity: normal Keywords: MySQL SQL syntax compatibility
Cc: rjollos, otaku42 Trac Release: 0.10

Description

If you try to enable the tickets tags module, while using MySQL 4.x you get the following error: 

ProgrammingError: (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 'fields FROM ticket WHERE status != 'closed') s WHERE fields != '' ORDER BY id' at line 1")

 File "/opt/trac-0.11/src/tractags/tags/0.6/tractags/ticket.py", line 66, in  get_tagged_resources

The issue is that this SQL stanza 

SELECT id, %s, %s AS fields FROM ticket%s

fails, because MySQL seems to think fields is a keyword. On my site I renamed fields to fieldss. Another option is using backticks ` around the field name, but I'm not sure this is compatible with other databases.

Attachments

Change History

06/15/09 13:12:15 changed by trac@alwins-world.de

Same problem here, I had to change the name into another one so there is no conflict with mysql.

This changed inside plugin sources...

08/27/10 04:46:29 changed by AdrianFritz

Seems related #4277.

10/08/11 15:25:50 changed by hasienda

  • keywords set to MySQL SQL syntax compatibility.
  • owner changed from athomas to hasienda.
  • cc set to rjollos, otaku42.

Would like to fix as many real bugs as possible for next stable release.

Anyone ready for testing a patch so that I don't need to do a MySQL setup here on my own?

10/08/11 15:35:33 changed by hasienda

While I'm investigating #4277 you could already check this patch (essentially the suggested field group alias rename plus some SQL statement re-formatting): 

diff --git a/tagsplugin/trunk/tractags/ticket.py b/tagsplugin/trunk/tractags/ticket.py
--- a/tagsplugin/trunk/tractags/ticket.py
+++ b/tagsplugin/trunk/tractags/ticket.py
@@ -52,17 +52,20 @@
         ignore = ''
         if self.ignore_closed_tickets:
             ignore = " WHERE status != 'closed'"
-        sql = "SELECT * FROM (SELECT id, %s, %s AS fields FROM ticket%s) s" % (
-            ','.join(self.fields),
-            '||'.join(["COALESCE(%s, '')" % f for f in self.fields]),
-            ignore)
+        sql = """SELECT *
+                   FROM (SELECT id, %s, %s
+                             AS std_fields
+                           FROM ticket%s) s
+              """ % (','.join(self.fields),
+                     '||'.join(["COALESCE(%s, '')" % f for f in self.fields]),
+                     ignore)
         constraints = []
         if tags:
             constraints.append(
-                "(" + ' OR '.join(["fields LIKE %s" for t in tags]) + ")")
+                "(" + ' OR '.join(["std_fields LIKE %s" for t in tags]) + ")")
             args += ['%' + t + '%' for t in tags]
         else:
-            constraints.append("fields != ''")
+            constraints.append("std_fields != ''")
 
         if constraints:
             sql += " WHERE " + " AND ".join(constraints)

10/08/11 22:42:26 changed by hasienda

I noticed, that just quoting strings falsely misinterpreted as keywords would solve the problem as well. Comments?

And that 'fields' isn't on the list of any relevant database system - strange.

10/14/11 00:37:57 changed by hasienda

(In [10774]) TagsPlugin: Use a different alias name for (standard ticket) fields, refs #3359.

Prevent misinterpretation as keyword, that has been reported for MySQL.

10/01/12 23:48:49 changed by hasienda

Would be nice to have a success report here, because I'm unable to test against MySQL yet.

Add/Change #3359 (Tickets tags don't work when using MySQL 4.x)




Change Properties
Action