Defect #26544

SQL-Error occurs sometimes when clicking on "calendar" tab

Added by Florian S. 3 months ago. Updated 12 days ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Calendar
Target version:-
Resolution:Duplicate Affected version:3.4.2

Description

An error occurs sometimes (and mostly not) when clicking at calendar.
Second (third and so on) click works fine but first does not. After a break the "first" click also does not work again.
Maybe there is a caching problem, too?

Extract from logfile:

Query::StatementInvalid: Mysql2::Error: Not unique table/alias: 'enumerations': SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`position` AS t1_r3, `issue_statuses`.`default_done_ratio` AS t1_r4, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12, `projects`.`default_version_id` AS t2_r13, `projects`.`default_assigned_to_id` AS t2_r14, `trackers`.`id` AS t3_r0, `trackers`.`name` AS t3_r1, `trackers`.`is_in_chlog` AS t3_r2, `trackers`.`position` AS t3_r3, `trackers`.`is_in_roadmap` AS t3_r4, `trackers`.`fields_bits` AS t3_r5, `trackers`.`default_status_id` AS t3_r6, `users`.`id` AS t4_r0, `users`.`login` AS t4_r1, `users`.`hashed_password` AS t4_r2, `users`.`firstname` AS t4_r3, `users`.`lastname` AS t4_r4, `users`.`admin` AS t4_r5, `users`.`status` AS t4_r6, `users`.`last_login_on` AS t4_r7, `users`.`language` AS t4_r8, `users`.`auth_source_id` AS t4_r9, `users`.`created_on` AS t4_r10, `users`.`updated_on` AS t4_r11, `users`.`type` AS t4_r12, `users`.`identity_url` AS t4_r13, `users`.`mail_notification` AS t4_r14, `users`.`salt` AS t4_r15, `users`.`must_change_passwd` AS t4_r16, `users`.`passwd_changed_on` AS t4_r17, `enumerations`.`id` AS t5_r0, `enumerations`.`name` AS t5_r1, `enumerations`.`position` AS t5_r2, `enumerations`.`is_default` AS t5_r3, `enumerations`.`type` AS t5_r4, `enumerations`.`active` AS t5_r5, `enumerations`.`project_id` AS t5_r6, `enumerations`.`parent_id` AS t5_r7, `enumerations`.`position_name` AS t5_r8 FROM `issues` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority') LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id WHERE (((projects.status <> 9 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND (((projects.is_public = 1 AND projects.id NOT IN (SELECT project_id FROM members WHERE user_id IN (6,47))) AND ((issues.is_private = 0 OR issues.author_id = 6 OR issues.assigned_to_id IN (6)))) OR (projects.id IN (1,4,6,7) AND ((issues.is_private = 0 OR issues.author_id = 6 OR issues.assigned_to_id IN (6))))))) AND ((issues.fixed_version_id IS NULL) AND issues.is_private IN (0) AND projects.id = 1) AND (((start_date BETWEEN '2017-06-26' AND '2017-08-06') OR (due_date BETWEEN '2017-06-26' AND '2017-08-06')))  ORDER BY enumerations.position DESC

executing this query manually in mysql/mariadb I get the result

 #1066 - Tabellenname/Alias 'enumerations' nicht eindeutig

=> Table name / alias "enumeration" not unique

sql.txt Magnifier (6.83 KB) Toshi MARUYAMA, 2017-08-15 21:58


Related issues

Duplicates Redmine - Defect #27153: Custom query breaks calendar view with error 500 Confirmed

History

#1 Updated by Toshi MARUYAMA 3 months ago

  • Status changed from New to Needs feedback
  • Priority changed from High to Normal

What version is your MySQL?

#2 Updated by Florian S. 3 months ago

Toshi MARUYAMA wrote:

What version is your MySQL?

Standard debian 9 db package:
Server-Typ: MariaDB
Server-Version: 10.1.23-MariaDB-9+deb9u1 - Debian 9.0

#3 Updated by Toshi MARUYAMA 3 months ago

MariaDB is not supported.

#4 Updated by Florian S. 3 months ago

Hmm that's a pity due to the fact that debian replaced mysql with mariadb in the latest debian version (9)

#5 Updated by Florian S. 3 months ago

I actually also tried this sql statement at a mysql database:

#1066 - Not unique table/alias: 'enumerations'

Server Version: 5.5.49-0+deb7u1
Protokoll-Version: 10

So it seems as if it is not a mariadb-specific problem..

#6 Updated by Toshi MARUYAMA 2 months ago

  • File sql.txt added

I cannot reproduce on my CentOS 7 and Redmine 3.4.2.

$ ruby --version
ruby 2.2.7p470 (2017-03-28 revision 58194) [x86_64-linux]

$ rpm -qa | grep mysql
php-mysql-5.4.16-42.el7.x86_64
mysql-community-server-5.5.57-2.el7.x86_64
mysql-community-client-5.5.57-2.el7.x86_64
mysql-community-common-5.5.57-2.el7.x86_64
mysql-community-libs-5.5.57-2.el7.x86_64
mysql57-community-release-el7-11.noarch
mysql-community-devel-5.5.57-2.el7.x86_64

$ bundle show | grep mysql
  * mysql2 (0.4.9)

#7 Updated by Toshi MARUYAMA 2 months ago

  • File deleted (sql.txt)

#8 Updated by Toshi MARUYAMA 2 months ago

#9 Updated by Jitendra Chandani 2 months ago

I am also having this issue: https://www.screencast.com/t/J1ajHPH6fnP

ruby --version
ruby 2.2.4p230 (2015-12-16 revision 53155) [x86_64-linux]

rpm -qa | grep mysql
mysql-5.1.73-5.el6_7.1.x86_64
mysql-devel-5.1.73-5.el6_7.1.x86_64
php56u-mysqlnd-5.6.28-1.ius.el6.x86_64
mysql-libs-5.1.73-5.el6_7.1.x86_64

bundle show | grep mysql
 * mysql2 (0.4.8)

Error logs:

Query::StatementInvalid: Mysql2::Error: Not unique table/alias: 'enumerations': SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`position` AS t1_r3, `issue_statuses`.`default_done_ratio` AS t1_r4, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12, `projects`.`default_version_id` AS t2_r13, `projects`.`default_assigned_to_id` AS t2_r14, `trackers`.`id` AS t3_r0, `trackers`.`name` AS t3_r1, `trackers`.`is_in_chlog` AS t3_r2, `trackers`.`position` AS t3_r3, `trackers`.`is_in_roadmap` AS t3_r4, `trackers`.`fields_bits` AS t3_r5, `trackers`.`default_status_id` AS t3_r6, `users`.`id` AS t4_r0, `users`.`login` AS t4_r1, `users`.`hashed_password` AS t4_r2, `users`.`firstname` AS t4_r3, `users`.`lastname` AS t4_r4, `users`.`mail_notification` AS t4_r5, `users`.`admin` AS t4_r6, `users`.`status` AS t4_r7, `users`.`last_login_on` AS t4_r8, `users`.`language` AS t4_r9, `users`.`auth_source_id` AS t4_r10, `users`.`created_on` AS t4_r11, `users`.`updated_on` AS t4_r12, `users`.`type` AS t4_r13, `users`.`identity_url` AS t4_r14, `users`.`salt` AS t4_r15, `users`.`must_change_passwd` AS t4_r16, `users`.`passwd_changed_on` AS t4_r17, `enumerations`.`id` AS t5_r0, `enumerations`.`name` AS t5_r1, `enumerations`.`position` AS t5_r2, `enumerations`.`is_default` AS t5_r3, `enumerations`.`type` AS t5_r4, `enumerations`.`active` AS t5_r5, `enumerations`.`project_id` AS t5_r6, `enumerations`.`parent_id` AS t5_r7, `enumerations`.`position_name` AS t5_r8, `agile_data`.`id` AS t6_r0, `agile_data`.`issue_id` AS t6_r1, `agile_data`.`position` AS t6_r2, `agile_data`.`story_points` AS t6_r3 FROM `issues` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority') LEFT OUTER JOIN `agile_data` ON `agile_data`.`issue_id` = `issues`.`id` LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id WHERE (((projects.status <> 9 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND ((projects.id IN (7,15,23,48,52,54,59,60,63,64,67,68,69,70,71,72,73,74,75,76,79,81,82,83,84,85,86,87,88,89,90,91,92,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,113,116,117,118,119,120,122,123,124,125,127,128,129,130,131,132,133,136,137,138,139,140,141,142,143,144,145,146,148,149,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,170,171) AND ((issues.is_private = 0 OR issues.author_id = 79 OR issues.assigned_to_id IN (79)))) OR (projects.id IN (20,55,74,79,88,124,127,147) AND ((issues.is_private = 0 OR issues.author_id = 79 OR issues.assigned_to_id IN (79))))))) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND (issues.assigned_to_id IN ('79'))) AND (((start_date BETWEEN '2017-07-30' AND '2017-09-02') OR (due_date BETWEEN '2017-07-30' AND '2017-09-02')))  ORDER BY enumerations.position DESC, issues.updated_on DESC
  Rendered common/error.html.erb within layouts/base (0.1ms)
Completed 500 Internal Server Error in 83ms (Views: 21.1ms | ActiveRecord: 37.3ms)

#10 Updated by Hapee de Groot 28 days ago

Completely the same error as above

ruby 2.3.1p112 
mysql  Ver 14.14 Distrib 5.7.19
mysql2 (0.4.9)

Same error output
Mysql2::Error: Not unique table/alias: 'enumerations':

was somebody able to fix it?

#11 Updated by Andrey Semenov 27 days ago

Have same error.

Docker.io/redmine
Docker.io/mariadb

(In my case, I did an upgrade 3.0 to 3.4)

Everything works well, except for the calendar

"Custom queries" get 100% error

#12 Updated by Hapee de Groot 26 days ago

Is there a way to escalated this or give the ticket a status bug with a higher priority?

#13 Updated by Andrey Semenov 26 days ago

I will try with Compose file:

services:
  redmine:
    image: redmine
    environment:
      REDMINE_DB_MYSQL: db
      REDMINE_DB_PASSWORD: example
    depends_on:
      - db
  db:
    image: mysql:5.7                                                                                                                                                                                                 
    environment:
      MYSQL_ROOT_PASSWORD: example
      MYSQL_DATABASE: redmine

Have same error, without mariadb

#14 Updated by Hapee de Groot 19 days ago

So I repeat my question specially with regards to Toshi MARUYAMA who changed priority to normal instead of high:
Is there a way to escalated this or give the ticket a status bug with a higher priority?

#15 Updated by Toshi MARUYAMA 19 days ago

Hapee de Groot wrote:

So I repeat my question specially with regards to Toshi MARUYAMA who changed priority to normal instead of high:
Is there a way to escalated this or give the ticket a status bug with a higher priority?

As I noted, I cannot reproduce on vanilla Redmine.
Please describe your environment on submissions and how to reproduce.

#16 Updated by Hapee de Groot 19 days ago

Environment:
  Redmine version                3.4.2.devel
  Ruby version                   2.3.1-p112 (2016-04-26) [x86_64-linux-gnu]
  Rails version                  5.1.2
  Environment                    production
  Database adapter               Mysql2
SCM:
  Subversion                     1.9.3
  Git                            2.7.4
  Filesystem                     

And I got the same error as above repeated here:

Query::StatementInvalid: Mysql2::Error: Not unique table/alias: 'enumerations': SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`position` AS t1_r3, `issue_statuses`.`default_done_ratio` AS t1_r4, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12, `projects`.`default_version_id` AS t2_r13, `projects`.`default_assigned_to_id` AS t2_r14, `trackers`.`id` AS t3_r0, `trackers`.`name` AS t3_r1, `trackers`.`is_in_chlog` AS t3_r2, `trackers`.`position` AS t3_r3, `trackers`.`is_in_roadmap` AS t3_r4, `trackers`.`fields_bits` AS t3_r5, `trackers`.`default_status_id` AS t3_r6, `users`.`id` AS t4_r0, `users`.`login` AS t4_r1, `users`.`hashed_password` AS t4_r2, `users`.`firstname` AS t4_r3, `users`.`lastname` AS t4_r4, `users`.`admin` AS t4_r5, `users`.`status` AS t4_r6, `users`.`last_login_on` AS t4_r7, `users`.`language` AS t4_r8, `users`.`auth_source_id` AS t4_r9, `users`.`created_on` AS t4_r10, `users`.`updated_on` AS t4_r11, `users`.`type` AS t4_r12, `users`.`identity_url` AS t4_r13, `users`.`mail_notification` AS t4_r14, `users`.`salt` AS t4_r15, `users`.`must_change_passwd` AS t4_r16, `users`.`passwd_changed_on` AS t4_r17, `enumerations`.`id` AS t5_r0, `enumerations`.`name` AS t5_r1, `enumerations`.`position` AS t5_r2, `enumerations`.`is_default` AS t5_r3, `enumerations`.`type` AS t5_r4, `enumerations`.`active` AS t5_r5, `enumerations`.`project_id` AS t5_r6, `enumerations`.`parent_id` AS t5_r7, `enumerations`.`position_name` AS t5_r8 FROM `issues` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority') LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id WHERE (((projects.status <> 9 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND (((projects.is_public = 1 AND projects.id NOT IN (SELECT project_id FROM members WHERE user_id IN (6,47))) AND ((issues.is_private = 0 OR issues.author_id = 6 OR issues.assigned_to_id IN (6)))) OR (projects.id IN (1,4,6,7) AND ((issues.is_private = 0 OR issues.author_id = 6 OR issues.assigned_to_id IN (6))))))) AND ((issues.fixed_version_id IS NULL) AND issues.is_private IN (0) AND projects.id = 1) AND (((start_date BETWEEN '2017-06-26' AND '2017-08-06') OR (due_date BETWEEN '2017-06-26' AND '2017-08-06')))  ORDER BY enumerations.position DESC

in short:
Not unique table/alias: 'enumerations':

#17 Updated by Marius BALTEANU 18 days ago

Redmine 3.4.2.devel (current trunk) is not a stable version. You should use the latest Redmine 3.4.2 version. You can check this page for more info: Download.

#18 Updated by Andrey Semenov 18 days ago

In my example

Environment:
  Redmine version                3.4.2.stable
  Ruby version                   2.4.2-p198 (2017-09-14) [x86_64-linux]
  Rails version                  4.2.8
  Environment                    production
  Database adapter               Mysql2
SCM:
  Subversion                     1.8.10
  Mercurial                      3.1.2
  Bazaar                         2.7.0
  Git                            2.1.4
  Filesystem                     

#19 Updated by Hapee de Groot 18 days ago

To Marius, so you are saying you actually have the same problem running on the 3.4.2.stable version?

Than I am not going to downgrade existing version to end up with the same error.

The error is very straight forward:
Mysql2::Error: Not unique table/alias: 'enumerations'

Is none of the Redmine specialists able to tell us how to make the table/alias enumarations unique?

#20 Updated by Hapee de Groot 16 days ago

just tested in on stable:

Environment:
  Redmine version                3.4.2.stable
  Ruby version                   2.3.1-p112 (2016-04-26) [x86_64-linux-gnu]
  Rails version                  5.1.2
  Environment                    production
  Database adapter               Mysql2
SCM:
  Subversion                     1.9.3
  Git                            2.7.4
  Filesystem                     

and indeed same issue, so can we not have a serious look at what is causing:
Mysql2::Error: Not unique table/alias: 'enumerations'

#21 Updated by Marius BALTEANU 16 days ago

Did you run bundle install? I'm asking because Redmine 3.4.2 works with Rails 4.2.8 (source:branches/3.4-stable/Gemfile#L7), and not with 5.1.2. Also, please post your entire environment information (including Plugin section).

#22 Updated by Hapee de Groot 16 days ago

For Marius I did it all over again:

Environment:
  Redmine version                3.4.2.stable
  Ruby version                   2.3.3-p222 (2016-11-21) [x86_64-linux-gnu]
  Rails version                  4.2.8
  Environment                    production
  Database adapter               Mysql2
SCM:
  Filesystem                     

Query::StatementInvalid: Mysql2::Error: Not unique table/alias: 'enumerations'

And now I would like to have a serious answer on the error instead of pointing me to wrong installations

#23 Updated by Marius BALTEANU 16 days ago

Hapee de Groot wrote:

For Marius I did it all over again:

[...]

Query::StatementInvalid: Mysql2::Error: Not unique table/alias: 'enumerations'

And now I would like to have a serious answer on the error instead of pointing me to wrong installations

First of all, I'm not pointing you to the wrong installations, I'm trying to help you based on the information provided. Until now, I saw two problems: you tried to install a version that is not stable and after that, you tried to install a stable version, but with another Rails version (each Redmine version sticks with a specific Rails version - Rails 5.1.* it'll be supported by Redmine 4.0).

Can you provide the plugins list and the entire log? (please see submissions for more details).

#24 Updated by Marius BALTEANU 16 days ago

Also, here you can see the tests results for each Redmine version with the supported Ruby versions and databases.

#25 Updated by Hapee de Groot 16 days ago

Thanks Marius, but so far nobody ever went into the specifics of the error but anyway, that is what it is.

No plugins and the complete error:

Started GET "/projects/techissues/issues/calendar?query_id=54" for 127.0.0.1 at 2017-10-07 18:18:36 +0200
Processing by CalendarsController#show as HTML
  Parameters: {"query_id"=>"54", "project_id"=>"techissues"}
  Current user: hapee (id=237)
Query::StatementInvalid: Mysql2::Error: Not unique table/alias: 'enumerations': SELECT `issues`.`id` AS t0_r0, `issues`.`tracker_id` AS t0_r1, `issues`.`project_id` AS t0_r2, `issues`.`subject` AS t0_r3, `issues`.`description` AS t0_r4, `issues`.`due_date` AS t0_r5, `issues`.`category_id` AS t0_r6, `issues`.`status_id` AS t0_r7, `issues`.`assigned_to_id` AS t0_r8, `issues`.`priority_id` AS t0_r9, `issues`.`fixed_version_id` AS t0_r10, `issues`.`author_id` AS t0_r11, `issues`.`lock_version` AS t0_r12, `issues`.`created_on` AS t0_r13, `issues`.`updated_on` AS t0_r14, `issues`.`start_date` AS t0_r15, `issues`.`done_ratio` AS t0_r16, `issues`.`estimated_hours` AS t0_r17, `issues`.`parent_id` AS t0_r18, `issues`.`root_id` AS t0_r19, `issues`.`lft` AS t0_r20, `issues`.`rgt` AS t0_r21, `issues`.`is_private` AS t0_r22, `issues`.`closed_on` AS t0_r23, `issue_statuses`.`id` AS t1_r0, `issue_statuses`.`name` AS t1_r1, `issue_statuses`.`is_closed` AS t1_r2, `issue_statuses`.`position` AS t1_r3, `issue_statuses`.`default_done_ratio` AS t1_r4, `projects`.`id` AS t2_r0, `projects`.`name` AS t2_r1, `projects`.`description` AS t2_r2, `projects`.`homepage` AS t2_r3, `projects`.`is_public` AS t2_r4, `projects`.`parent_id` AS t2_r5, `projects`.`created_on` AS t2_r6, `projects`.`updated_on` AS t2_r7, `projects`.`identifier` AS t2_r8, `projects`.`status` AS t2_r9, `projects`.`lft` AS t2_r10, `projects`.`rgt` AS t2_r11, `projects`.`inherit_members` AS t2_r12, `projects`.`default_version_id` AS t2_r13, `projects`.`default_assigned_to_id` AS t2_r14, `trackers`.`id` AS t3_r0, `trackers`.`name` AS t3_r1, `trackers`.`is_in_chlog` AS t3_r2, `trackers`.`position` AS t3_r3, `trackers`.`is_in_roadmap` AS t3_r4, `trackers`.`fields_bits` AS t3_r5, `trackers`.`default_status_id` AS t3_r6, `users`.`id` AS t4_r0, `users`.`login` AS t4_r1, `users`.`hashed_password` AS t4_r2, `users`.`firstname` AS t4_r3, `users`.`lastname` AS t4_r4, `users`.`admin` AS t4_r5, `users`.`status` AS t4_r6, `users`.`last_login_on` AS t4_r7, `users`.`language` AS t4_r8, `users`.`auth_source_id` AS t4_r9, `users`.`created_on` AS t4_r10, `users`.`updated_on` AS t4_r11, `users`.`type` AS t4_r12, `users`.`identity_url` AS t4_r13, `users`.`mail_notification` AS t4_r14, `users`.`salt` AS t4_r15, `users`.`must_change_passwd` AS t4_r16, `users`.`passwd_changed_on` AS t4_r17, `enumerations`.`id` AS t5_r0, `enumerations`.`name` AS t5_r1, `enumerations`.`position` AS t5_r2, `enumerations`.`is_default` AS t5_r3, `enumerations`.`type` AS t5_r4, `enumerations`.`active` AS t5_r5, `enumerations`.`project_id` AS t5_r6, `enumerations`.`parent_id` AS t5_r7, `enumerations`.`position_name` AS t5_r8 FROM `issues` INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` INNER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority') LEFT OUTER JOIN enumerations ON enumerations.id = issues.priority_id WHERE (((projects.status <> 9 AND EXISTS (SELECT 1 AS one FROM enabled_modules em WHERE em.project_id = projects.id AND em.name='issue_tracking')) AND ((projects.id IN (5) AND ((issues.is_private = 0 OR issues.author_id = 237 OR issues.assigned_to_id IN (237))))))) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND (issues.assigned_to_id IN ('237')) AND projects.id = 5) AND (((start_date BETWEEN '2017-10-01' AND '2017-11-04') OR (due_date BETWEEN '2017-10-01' AND '2017-11-04')))  ORDER BY enumerations.position DESC
  Rendered common/error.html.erb within layouts/base (2.2ms)
Completed 500 Internal Server Error in 257ms (Views: 89.9ms | ActiveRecord: 49.0ms)

#26 Updated by Hapee de Groot 16 days ago

what I am actually dealing with is a mysql database coming from
Redmine version 2.6.10.stable
to a new redmine installation
Redmine version 3.4.2.stable
so the question probably is how do I change the database in such a way that 'enumerations' becomes a unique table/alias

#27 Updated by Bernhard Rohloff 13 days ago

It seems like I've reported the same issue in #27153 today.
I can reproduce the failure with particular custom queries (with a second sort criteria) as described in my ticket.

Can anybody affected by this issue double check my assumptions?

#28 Updated by Bernhard Rohloff 12 days ago

For the last couple of hours, I've taken a deep dive into Redmine sources (and Ruby and MySQL).
So here is the thing,...
As I mentioned above the issue occurs if you have chosen a custom query before entering the calendar. This adds additional sort criteria which results in a bad SQL query similar to this.
The issue initially occured back in the days in #7451 and was fixed in source:trunk/app/controllers/issues_controller.rb@3379#L430 by deleting the group_by member of the query.
In r16390 a method sort_clause was added into the issues method in source:trunk/app/models/issue_query.rb@16390 which adds the sort_criteria member of the custom query.

Right now, there a three ways to fix this issue...

1. Fix the SQL Query

I think this would be the best solution for the problem, but it's also the most difficult. And I have no solution, yet...

2. Delete the sort_criteria member in the calendars_controller

Just like it's done with the group_by member.

@calendar = Redmine::Helpers::Calendar.new(Date.civil(@year, @month, 1), current_language, :month)
retrieve_query
@query.group_by = nil
@query.sort_criteria = nil #insert this into app/controllers/calendars_controller.rb

3. Set the issues order explicitly to [] in the query issues method in calendars_controller.rb

This prohibits the execution of sort_clause which isn't necessary in this case.

events = []
events += @query.issues(:include => [:tracker, :assigned_to, :priority],
                        :conditions => ["((start_date BETWEEN ? AND ?) OR (due_date BETWEEN ? AND ?))", @calendar.startdt, @calendar.enddt, @calendar.startdt, @calendar.enddt],
                        :order => [] # add this line into app/controllers/calendars_controller.rb
                        )   

So I think I would prefer the third solution.
Although it's the same solution as in r3379, removing the sort_criteria from the query object feels like a 'quick & dirty' hack to me.
Setting the sort order explicitly to [] in the query seems like a more comprehensible approach.

#29 Updated by Marius BALTEANU 12 days ago

  • Duplicates Defect #27153: Custom query breaks calendar view with error 500 added

#30 Updated by Marius BALTEANU 12 days ago

  • Status changed from Needs feedback to Closed
  • Resolution set to Duplicate

This problem will be tracked in #27153. Closing this one.

Also available in: Atom PDF