Modify

Opened 6 years ago

Closed 12 days 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 19 months ago by hasienda

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

comment:8 Changed 2 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 12 days 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 .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from hasienda. Next status will be '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.