Hello, Here's a fix for the Galaxy-Reports + MySQL problem. Note: this patch is incompatible with PostgreSQL, so DO NOT apply it unless you know what you're doing. Bug description --------------- The galaxy reports webapp shows jobs-per-month, errors-per-month and users-per-month reports. All these controllers use Postgres's 'date_trunc()' function, which MySQL doesn't have. The result is the exception shown in the email below. Details ------- jobs,py and users.py call date_trunc() in two ways: 1. In the SELECT clause to extract/format the result 2. In the GROUP BY clause to group results by day/month/year. Solution -------- 1. In the SELECT part, the date_trunc() is removed. IMHO, it should not affect any functionality, because the SELECT returns a DATE object which is later formatted with strftime. So having the date value contain extra information (such as day/hour/minute/second etc.) is not a problem. This is also the reason similar MySQL functions can't be used - they return a string field and not a DATE field -> causes Python to complain about "object type string doesn't have a strftime method" (or something like that). 2. In the GROUP BY part, date_trunc() is replaced by three MySQL functions: day(), month(), year(). Tested with MySQL 5.0.45 and the latest galaxy version - and seems to work. Comments are welcomed, gordon. Assaf Gordon wrote, On 06/26/2009 01:18 PM:

Hello,

I have a galaxy server running with a MySQL database. When I load the galaxy-reports server, several pages fail with missing SQL function 'DATE_TRUNC' error, like so: ------------------ Module paste.exceptions.errormiddleware:144 in __call__ Module paste.debug.prints:98 in __call__ Module paste.wsgilib:539 in intercept_output Module beaker.session:103 in __call__ Module paste.recursive:80 in __call__ Module paste.httpexceptions:632 in __call__ Module galaxy.web.framework.base:126 in __call__ << kwargs.pop( '_', None ) try: body = method( trans, **kwargs ) except Exception, e: body = self.handle_controller_exception( e, trans, **kwargs )>> body = method( trans, **kwargs ) Module galaxy.webapps.reports.controllers.jobs:311 in per_month_in_error << order_by = [ sa.desc( 'date' ) ] ) jobs = [] for row in q.execute(): jobs.append( ( row.date.strftime( "%Y-%m" ), row.total_jobs,>> for row in q.execute(): Module sqlalchemy.sql.expression:1087 in execute Module sqlalchemy.engine.base:1219 in execute_clauseelement Module sqlalchemy.engine.base:895 in execute_clauseelement Module sqlalchemy.engine.base:907 in _execute_compiled Module sqlalchemy.engine.base:916 in __execute_raw Module sqlalchemy.engine.base:960 in _cursor_execute Module sqlalchemy.engine.base:942 in _handle_dbapi_exception OperationalError: (OperationalError) (1305, 'FUNCTION publicgalaxy.date_trunc does not exist') u'SELECT date_trunc(%s, date(job.create_time)) AS date, count(job.id) AS total_jobs \nFROM job \nWHERE job.state = %s GROUP BY date_trunc(%s, date(job.create_time)) ORDER BY date DESC' ['month', 'error', 'month'] ---------------

Googling for 'DATE_TRUNC' shows that this is a PostgreSQL function, which probably explains why it is missing in MySQL.

Is there a quick workaround for this ? Maybe create a MySQL stored procedure named 'DATE_TRUNC' that acts the same as the PostgreSQL one ?

Thanks, Gordon.

_______________________________________________ galaxy-bugs mailing list galaxy-bugs@bx.psu.edu http://mail.bx.psu.edu/cgi-bin/mailman/listinfo/galaxy-bugs