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

[nigelsim]

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.

[trac@…]

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

This changed inside plugin sources...

[Adrian Fritz]

Seems related #4277.

[Steffen Hoffmann]

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?

[Steffen Hoffmann]

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

tagsplugin/trunk/tractags/ticket.py

@@ -52 +52 @@
         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)

[Steffen Hoffmann]

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.

[Steffen Hoffmann]

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

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

[Steffen Hoffmann]

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

[Steffen Hoffmann]

Counting in myself here, but my deployment has been on MySQL 5, so still not a real reference.

[Steffen Hoffmann]

In 13815:

TagsPlugin: Completing preparation for v0.7 release.

Availability of that code as stable, tagged release
closes #2429, #3359, #3610, #3624, #3677, #3754, #3864, #3947, #3983, #4078, #4277, #4503, #4799, #5523, #7787, #7857, #8638, #9057, #9058, #9059, #9060, #9061, #9062, #9063, #9149, #9210, #9521, #9630, #9636, #10032, #10416, #10636, #11096, #11147, #11152, #11274, #11302, #11658 and #11659.

Additionally there are some issues and enhancement requests showing progress,
but known to require more work to resolve them satisfactorily, specifically
refs #2804, #4200, #8747 and #9064.

Thanks to all contributors and followers, that enabled and encouraged a good
portion of this development work.