Actually, here’s what I came up with :

diff users.py ARCHIVES/users.py_20150205_9.28.18

23c23

<         q = sa.select( ( sa.func.date( galaxy.model.User.table.c.create_time ).label( 'date' ),

---

>         q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ),

26c26

<                        group_by = [ sa.func.year ( galaxy.model.User.table.c.create_time ), sa.func.month ( galaxy.model.User.table.c.create_time ) ],

---

>                        group_by = [ sa.func.date_trunc( 'month', sa.func.date( galaxy.model.User.table.c.create_time ) ) ],

48c48

<         q = sa.select( ( sa.func.date( galaxy.model.User.table.c.create_time ).label( 'date' ),

---

>         q = sa.select( ( sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ),

53c53

<                        group_by = [ sa.func.day( sa.func.date( galaxy.model.User.table.c.create_time ) ) ],

---

>                        group_by = [ sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ) ],

79c79

<         q = sa.select( ( sa.func.date( galaxy.model.User.table.c.create_time ).label( 'date' ),

---

>         q = sa.select( ( sa.func.date_trunc( 'day', sa.func.date( galaxy.model.User.table.c.create_time ) ).label( 'date' ),

diff jobs.py ARCHIVES/jobs.py_20150205_9.28.22

282c282

<         jobs_by_month = sa.select( ( sa.func.date( model.Job.table.c.create_time ).label( 'date' ),

---

>         jobs_by_month = sa.select( ( sa.func.date_trunc( 'month', model.Job.table.c.create_time ).label( 'date' ),

315c315

<         jobs_in_error_by_month = sa.select( ( sa.func.date( model.Job.table.c.create_time ).label( 'date' ),

---

>         jobs_in_error_by_month = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ),

320c320

<                                            group_by=[ sa.func.year ( model.Job.table.c.create_time ), sa.func.month ( sa.func.date( model.Job.table.c.create_time ) ) ],

---

>                                            group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ],

364c364

<         q = sa.select( ( sa.func.date( model.Job.table.c.create_time ).label( 'date' ),

---

>         q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ),

371c371

<                        group_by=[ sa.func.year ( model.Job.table.c.create_time ), sa.func.month ( sa.func.date( model.Job.table.c.create_time ) ) ],

---

>                        group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ],

420c420

<         q = sa.select( ( sa.func.date( model.Job.table.c.create_time ).label( 'date' ),

---

>         q = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ),

425c425

<                        group_by=[ sa.func.year ( model.Job.table.c.create_time ), sa.func.month ( sa.func.date( model.Job.table.c.create_time ) ) ],

---

>                        group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ],

Let me know what you think!!!

Cordialement / Regards,

Edgar Fernandez

De : Fernandez Edgar
Envoyé : February-05-15 9:49 AM
À : 'galaxy-dev@bx.psu.edu'
Objet : galaxy report page - server error solution?

Hello guys,

So I’ve been working on the Galaxy report page.

It works fine however I’m using MySQL and I’m getting some server errors on some webpages.

I found the solution: https://lists.galaxyproject.org/pipermail/galaxy-dev/2009-August/000641.html

But can some please confirm the following:

1. The location of the files users.py and jobs.py is /home/galaxy/galaxy-prod/lib/galaxy/webapps/reports/controllers

2. There is three (3) "select closes" using five (5) "date_trunc" functions in users.py.

3. There is eight (8) "select closes" using fourteen (4) "date_trunc" functions in jobs.py.

4. Here is an example of what I can come up with. Is it correct?

· WRONG:

jobs_in_error_by_month = sa.select( ( sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ).label( 'date' ), sa.func.count( model.Job.table.c.id ).label('total_jobs' ) ),

whereclause=sa.and_ ( model.Job.table.c.state == 'error',

model.Job.table.c.user_id != monitor_user_id ),

from_obj=[ model.Job.table ],

group_by=[ sa.func.date_trunc( 'month', sa.func.date( model.Job.table.c.create_time ) ) ],

order_by=[ sa.desc( 'date' ) ] )

· RIGHT:

jobs_in_error_by_month = sa.select( ( sa.func.count( model.Job.table.c.id ).label('total_jobs' ) ),

whereclause=sa.and_ ( model.Job.table.c.state == 'error',

model.Job.table.c.user_id != monitor_user_id ),

from_obj=[ model.Job.table ],

group_by=[ day(model.Job.table.c.create_time), month(model.Job.table.c.create_time), year(model.Job.table.c.create_time) ],

order_by=[ sa.desc( 'date' ) ] )

Cordialement / Regards,

Edgar Fernandez

System Administrator (Linux)

Direction Générale des Technologies de l'Information et de la Communication

(  Bur. : 1-514-343-6111 poste 16568

Université de Montréal

PAVILLON ROGER-GAUDRY, bureau X-218