Opened 11 years ago

Closed 5 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 10 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 9 years ago by Adrian Fritz

Seems related #4277.

comment:3 Changed 8 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 8 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 8 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 8 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 7 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 5 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 5 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.

Add Comment

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

Note: See TracTickets for help on using tickets.