Redmine

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

MariaDB is not supported.

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

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..

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)

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?

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

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

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

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?

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.

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':

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.

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                     

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?

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'

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

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

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

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

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)

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

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?

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.