Modify

Opened 6 years ago

Closed 5 months ago

#3359 closed defect (fixed)

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

Reported by: nigelsim Owned by: 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 (0)

Change History (9)

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

Seems related #4277.

comment:3 Changed 3 years ago by hasienda

  • Cc rjollos otaku42 added
  • Keywords MySQL SQL syntax compatibility added
  • Owner changed from athomas to hasienda

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 3 years ago 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):

  • tagsplugin/trunk/tractags/ticket.py

    diff --git a/tagsplugin/trunk/tractags/ticket.py b/tagsplugin/trunk/tractags/ticket.py
    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 != ''") 
    6669 
    6770        if constraints: 
    6871            sql += " WHERE " + " AND ".join(constraints) 

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

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

comment:7 Changed 2 years ago by hasienda

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

comment:8 Changed 7 months ago by hasienda

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

comment:9 Changed 5 months ago by hasienda

  • Resolution set to fixed
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


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

 
Note: See TracTickets for help on using tickets.