Modify

Opened 9 years ago

Closed 9 years ago

#12351 closed defect (fixed)

Unit test failure on MySQL

Reported by: Ryan J Ollos Owned by: Ryan J Ollos
Priority: normal Component: VotePlugin
Severity: normal Keywords:
Cc: Steffen Hoffmann Trac Release:

Description

$ TRAC_TEST_DB_URI=mysql://trac:trac@localhost/trac python setup.py test
running test
running egg_info
writing requirements to TracVote.egg-info/requires.txt
writing TracVote.egg-info/PKG-INFO
writing top-level names to TracVote.egg-info/top_level.txt
writing dependency_links to TracVote.egg-info/dependency_links.txt
writing entry points to TracVote.egg-info/entry_points.txt
reading manifest file 'TracVote.egg-info/SOURCES.txt'
writing manifest file 'TracVote.egg-info/SOURCES.txt'
running build_ext
test_new_install (tracvote.tests.main.EnvironmentSetupTestCase) ... ok
test_upgrade_v1_to_current (tracvote.tests.main.EnvironmentSetupTestCase) ... ERROR
test_available_actions_full_perms (tracvote.tests.main.VoteSystemTestCase) ... ok
test_available_actions_no_perms (tracvote.tests.main.VoteSystemTestCase) ... ok
test_resource_provider (tracvote.tests.main.VoteSystemTestCase) ... ok

======================================================================
ERROR: test_upgrade_v1_to_current (tracvote.tests.main.EnvironmentSetupTestCase)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/user/Workspace/trachacks.git/voteplugin/trunk/tracvote/tests/main.py", line 123, in test_upgrade_v1_to_current
    self.votes.upgrade_environment()
  File "/home/user/Workspace/trachacks.git/voteplugin/trunk/tracvote/__init__.py", line 550, in upgrade_environment
    script.do_upgrade(self.env, i, cursor)
  File "/home/user/Workspace/trachacks.git/voteplugin/trunk/tracvote/upgrades/db2.py", line 48, in do_upgrade
    resource = resource_from_path(env, vote.pop('resource'))
  File "/home/user/Workspace/trachacks.git/voteplugin/trunk/tracvote/__init__.py", line 88, in resource_from_path
    return get_versioned_resource(env, resource)
  File "/home/user/Workspace/trachacks.git/voteplugin/trunk/tracvote/__init__.py", line 60, in get_versioned_resource
    """, (resource.id,)):
  File "/home/user/Workspace/th12180/teo-rjollos.git/trac/db/api.py", line 123, in execute
    return db.execute(query, params)
  File "/home/user/Workspace/th12180/teo-rjollos.git/trac/db/util.py", line 128, in execute
    cursor.execute(query, params if params is not None else [])
  File "/home/user/Workspace/th12180/teo-rjollos.git/trac/db/util.py", line 61, in execute
    r = self.cursor.execute(sql_escape_percent(sql), args)
  File "/home/user/Workspace/th12180/py2.7/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/user/Workspace/th12180/py2.7/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
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 'change\n                      FROM ticket_change\n                     WHERE ticke' at line 2")

----------------------------------------------------------------------
Ran 5 tests in 0.266s

FAILED (errors=1)

Attachments (0)

Change History (4)

comment:1 Changed 9 years ago by Ryan J Ollos

I don't know MySQL well enough to spot the error:

  SELECT SUM(c.change) FROM (
    SELECT 1 as change
      FROM ticket_change
     WHERE ticket=%s
     GROUP BY time) AS c

comment:2 Changed 9 years ago by Jun Omae

The change is a reserved word in MySQL. It seems the word cannot be used in AS ....

mysql> SELECT 1 AS change;
ERROR 1064 (42000): 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 'change' at line 1

We could simply use COUNT(DISTINCT time) rather than such the sub query with GROUP BY.

  • voteplugin/trunk/tracvote/__init__.py

    diff --git a/voteplugin/trunk/tracvote/__init__.py b/voteplugin/trunk/tracvote/__init__.py
    index 0b483d2..316c2da 100644
    a b def get_versioned_resource(env, resource): 
    104106    resource.version = 0
    105107    if realm == 'ticket':
    106108        for tkt_changes, in env.db_query("""
    107                 SELECT SUM(c.change) FROM (
    108                     SELECT 1 as change
    109                       FROM ticket_change
    110                      WHERE ticket=%s
    111                      GROUP BY time) AS c
     109                SELECT COUNT(DISTINCT time) FROM ticket_change WHERE ticket=%s
    112110                """, (resource.id,)):
    113111            resource.version = tkt_changes
    114112    elif realm == 'wiki':

comment:3 Changed 9 years ago by Ryan J Ollos

Thanks, tests pass for all 3 databases with your patch and another modification to get_versioned_resource:

  • voteplugin/trunk/tracvote/__init__.py

    diff --git a/voteplugin/trunk/tracvote/__init__.py b/voteplugin/trunk/tracvote/_
    index c185a55..c6d3a01 100644
    a b def get_versioned_resource(env, resource): 
    4848    the current version has to be retrieved separately.
    4949    """
    5050    realm = resource.realm
    51     resource.version = 0
    5251    if realm == 'ticket':
    53         for tkt_changes, in env.db_query("""
    54                 SELECT SUM(c.change) FROM (
    55                   SELECT 1 as change
    56                     FROM ticket_change
    57                   WHERE ticket=%s
    58                   GROUP BY time) AS c
     52        for count, in env.db_query("""
     53                SELECT COUNT(DISTINCT time)
     54                FROM ticket_change WHERE ticket=%s
    5955                """, (resource.id,)):
    60             resource.version = tkt_changes
     56            if count !=0:
     57                resource.version = count
    6158    elif realm == 'wiki':
    6259        for version, in env.db_query("""
    6360                SELECT version

comment:4 Changed 9 years ago by Ryan J Ollos

Resolution: fixed
Status: newclosed

In 14594:

0.3dev: Fix test failure on MySQL. Fixes #12351.

The query used MySQL reserved word change, so plugin failures on MySQL were likely, but not confirmed. Thanks to Jun Omae for the fix.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Ryan J Ollos.
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.