Project

General

Profile

Change to new database server

Added by marcio costa over 5 years ago

Hi!!

I have this enviroment in production:

Environment:
Redmine version 2.3.0.stable
Ruby version 2.1.4 (x86_64-linux)
Rails version 3.2.13
Environment production
Database adapter Mysql2
Redmine plugins:
redmine_close_button 0.0.8
redmine_issue_checklist 2.0.5
redmine_monitoring_controlling 0.1.1
timelog_timer 2.0.0

I need change the database server from MariaDB 10.0.23 to Perconna XtraDB Cluster 5.7.23. After dump restore to new server I have some errors and a search in web I believe that the error maybe related to sql_mode... now the field used in ORDER BY must be explicity in select command.

See the error when I click on files in project page:

Started GET "/projects/project_test/files" for 192.168.10.100 at 2018-11-11 16:33:12 -0200
Processing by FilesController#index as HTML
Parameters: {"project_id"=>"project_test"}
Current user: costa (id=95)
Completed 500 Internal Server Error in 18ms

ActiveRecord::StatementInvalid (Mysql2::Error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'redmine.attachments.filename' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT `projects`.id FROM `projects` LEFT OUTER JOIN `attachments` ON `attachments`.`container_id` = `projects`.`id` AND `attachments`.`container_type` = 'Project' WHERE `projects`.`id` = 70 ORDER BY attachments.filename LIMIT 1):
app/controllers/files_controller.rb:34:in `index'

See the error when I click on gantt in project page:

Started GET "/projects/information/issues/gantt" for 192.168.10.100 at 2018-11-11 15:20:56 -0200
Processing by GanttsController#show as HTML
Parameters: {"project_id"=>"information"}
Current user: costa (id=95)
Rendered queries/_filters.html.erb (19.7ms)
Rendered gantts/show.html.erb within layouts/base (38.2ms)
Query::StatementInvalid: Mysql2::Error: Expression #1 of ORDER BY clause is not in SELECT list, references column 'redmine.projects.lft' which is not in SELECT list; this is incompatible with DISTINCT: SELECT DISTINCT `issues`.id FROM `issues` LEFT OUTER JOIN `projects` ON `projects`.`id` = `issues`.`project_id` LEFT OUTER JOIN `watchers` ON `watchers`.`watchable_id` = `issues`.`id` AND `watchers`.`watchable_type` = 'Issue' LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.`id` = `issues`.`status_id` LEFT OUTER JOIN `users` ON `users`.`id` = `issues`.`assigned_to_id` LEFT OUTER JOIN `trackers` ON `trackers`.`id` = `issues`.`tracker_id` LEFT OUTER JOIN `enumerations` ON `enumerations`.`id` = `issues`.`priority_id` AND `enumerations`.`type` IN ('IssuePriority') LEFT OUTER JOIN `issue_categories` ON `issue_categories`.`id` = `issues`.`category_id` LEFT OUTER JOIN `versions` ON `versions`.`id` = `issues`.`fixed_version_id` WHERE (projects.status <> 9 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='issue_tracking')) AND ((issues.status_id IN (SELECT id FROM issue_statuses WHERE is_closed=0)) AND (issues.tracker_id IN ('3')) AND projects.id IN (24,26,33,38,34,29,28,30,31,32,27,41,35,42,37,43,39,36,40,25,70,8,19,21,20,47,9,18,23,3,11,59,12,13,14,10,4,7,57,58,68,6,5,45,53,49,61,62,44,54)) ORDER BY projects.lft ASC, issues.id ASC LIMIT 500
Rendered common/error.html.erb within layouts/base (0.1ms)
Rendered plugins/timelog_timer/app/views/timelog/_timer.html.erb (0.6ms)
Completed 500 Internal Server Error in 111ms (Views: 14.3ms | ActiveRecord: 7.9ms)

I tried [[https://github.com/rails/rails/issues/25924]], but not works.

How I can (if possible) edit the sql code to solve it ?

Best Regards.