Opened 9 years ago

Closed 3 years ago

#3359 closed defect (fixed)

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

Reported by: nigelsim Owned by: Steffen Hoffmann
Priority: normal Component: TagsPlugin
Severity: normal Keywords: MySQL SQL syntax compatibility
Cc: Ryan J Ollos, Michael Renzmann Trac Release: 0.10


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/", 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 (0)

Change History (9)

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

comment:2 Changed 7 years ago by Adrian Fritz

Seems related #4277.

comment:3 Changed 5 years ago by Steffen Hoffmann

Cc: Ryan J Ollos Michael Renzmann added; anonymous removed
Keywords: MySQL SQL syntax compatibility added
Owner: changed from Alec Thomas to 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?

comment:4 Changed 5 years ago by 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/

    diff --git a/tagsplugin/trunk/tractags/ b/tagsplugin/trunk/tractags/
    a b  
    5252        ignore = ''
    5353        if self.ignore_closed_tickets:
    5454            ignore = " WHERE status != 'closed'"
    55         sql = "SELECT * FROM (SELECT id, %s, %s AS fields FROM ticket%s) s" % (
    56             ','.join(self.fields),
    57             '||'.join(["COALESCE(%s, '')" % f for f in self.fields]),
    58             ignore)
     55        sql = """SELECT *
     56                   FROM (SELECT id, %s, %s
     57                             AS std_fields
     58                           FROM ticket%s) s
     59              """ % (','.join(self.fields),
     60                     '||'.join(["COALESCE(%s, '')" % f for f in self.fields]),
     61                     ignore)
    5962        constraints = []
    6063        if tags:
    6164            constraints.append(
    62                 "(" + ' OR '.join(["fields LIKE %s" for t in tags]) + ")")
     65                "(" + ' OR '.join(["std_fields LIKE %s" for t in tags]) + ")")
    6366            args += ['%' + t + '%' for t in tags]
    6467        else:
    65             constraints.append("fields != ''")
     68            constraints.append("std_fields != ''")
    6770        if constraints:
    6871            sql += " WHERE " + " AND ".join(constraints)

comment:5 Changed 5 years ago by 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.

comment:6 Changed 5 years ago by 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.

comment:7 Changed 4 years ago by Steffen Hoffmann

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

comment:8 Changed 3 years ago by Steffen Hoffmann

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

comment:9 Changed 3 years ago by Steffen Hoffmann

Resolution: fixed
Status: newclosed

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.

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain Steffen Hoffmann.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment

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

Note: See TracTickets for help on using tickets.