Unit test failure on MySQL

[Ryan J Ollos]

$ 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)

[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

[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

@@ -104 +106 @@
     resource.version = 0
     if realm == 'ticket':
         for tkt_changes, in env.db_query("""
-                SELECT SUM(c.change) FROM (
-                    SELECT 1 as change
-                      FROM ticket_change
-                     WHERE ticket=%s
-                     GROUP BY time) AS c
+                SELECT COUNT(DISTINCT time) FROM ticket_change WHERE ticket=%s
                 """, (resource.id,)):
             resource.version = tkt_changes
     elif realm == 'wiki':

[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

@@ -48 +48 @@
     the current version has to be retrieved separately.
     """
     realm = resource.realm
-    resource.version = 0
     if realm == 'ticket':
-        for tkt_changes, in env.db_query("""
-                SELECT SUM(c.change) FROM (
-                  SELECT 1 as change
-                    FROM ticket_change
-                  WHERE ticket=%s
-                  GROUP BY time) AS c
+        for count, in env.db_query("""
+                SELECT COUNT(DISTINCT time)
+                FROM ticket_change WHERE ticket=%s
                 """, (resource.id,)):
-            resource.version = tkt_changes
+            if count !=0:
+                resource.version = count
     elif realm == 'wiki':
         for version, in env.db_query("""
                 SELECT version

[Ryan J Ollos]

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.