The request destroys all free disk space

Added by Vladislav Titar 3 months ago

I am new to Redmine and Ruby, and also apologize in advance for my bad English.

After the transition from version redmine 3.2.0 to 4.1.0, problems are observed in work.

SELECT members.id AS t0_r0,
members.user_id AS t0_r1,
members.project_id AS t0_r2,
members.created_on AS t0_r3,
members.mail_notification AS t0_r4,
member_roles.id AS t1_r0,
member_roles.member_id AS t1_r1,
member_roles.role_id AS t1_r2,
member_roles.inherited_from AS t1_r3,
roles.id AS t2_r0,
roles.name AS t2_r1,
roles.position AS t2_r2,
roles.assignable AS t2_r3,
roles.builtin AS t2_r4,
roles.permissions AS t2_r5,
roles.issues_visibility AS t2_r6,
roles.users_visibility AS t2_r7,
roles.time_entries_visibility AS t2_r8,
roles.all_roles_managed AS t2_r9,
roles.settings AS t2_r10,

users.id AS t3_r0,
        users.login AS t3_r1,
        users.hashed_password AS t3_r2,
        users.firstname AS t3_r3,
        users.lastname AS t3_r4,
        users.admin AS t3_r5,
        users.status AS t3_r6,
        users.last_login_on AS t3_r7,
        users.language AS t3_r8,
        users.auth_source_id AS t3_r9,
        users.created_on AS t3_r10,
        users.updated_on AS t3_r11,
        users.type AS t3_r12,
        users.identity_url AS t3_r13,
        users.mail_notification AS t3_r14,
        users.salt AS t3_r15,
        users.must_change_passwd AS t3_r16,
        users.passwd_changed_on AS t3_r17,
        users.otp_secret_key AS t3_r18,
        users.otp_time AS t3_r19,
        users.tfa_login AS t3_r20,
        users.logout_time AS t3_r21
    FROM members 
        LEFT OUTER JOIN member_roles ON member_roles.member_id = members.id 
        LEFT OUTER JOIN member_roles member_roles_members_join ON member_roles_members_join.member_id = members.id 
        LEFT OUTER JOIN roles ON roles.id = member_roles_members_join.role_id 
        LEFT OUTER JOIN users ON users.id = members.user_id 
    WHERE members.project_id = 185 
ORDER BY roles.position,
users.type DESC,
users.lastname,
users.firstname,
users.id

When you try to open a large project, one problematic above specified, problematic request comes to the database. This request destroys all free disk space (temporary mysql space) and the mysql base becomes unusable.

If you remove the extra "LEFT OUTER JOIN member_roles member_roles_members_join" then the request fulfills normally.

SELECT members.id AS t0_r0,
members.user_id AS t0_r1,
members.project_id AS t0_r2,
members.created_on AS t0_r3,
members.mail_notification AS t0_r4,
member_roles.id AS t1_r0,
member_roles.member_id AS t1_r1,
member_roles.role_id AS t1_r2,
member_roles.inherited_from AS t1_r3,
roles.id AS t2_r0,
roles.name AS t2_r1,
roles.position AS t2_r2,
roles.assignable AS t2_r3,
roles.builtin AS t2_r4,
roles.permissions AS t2_r5,
roles.issues_visibility AS t2_r6,
roles.users_visibility AS t2_r7,
roles.time_entries_visibility AS t2_r8,
roles.all_roles_managed AS t2_r9,

users.id AS t3_r0,
         users.login AS t3_r1,
         users.hashed_password AS t3_r2,
         users.firstname AS t3_r3,
         users.lastname AS t3_r4,
         users.admin AS t3_r5,
         users.status AS t3_r6,
         users.last_login_on AS t3_r7,
         users.language AS t3_r8,
         users.auth_source_id AS t3_r9,
         users.created_on AS t3_r10,
         users.updated_on AS t3_r11,
         users.TYPE AS t3_r12,
         users.identity_url AS t3_r13,
         users.mail_notification AS t3_r14,
         users.salt AS t3_r15,
         users.must_change_passwd AS t3_r16,
         users.passwd_changed_on AS t3_r17,
         users.otp_secret_key AS t3_r18,
         users.otp_time AS t3_r19,
         users.tfa_login AS t3_r20,
         users.logout_time AS t3_r21
    FROM members
         LEFT OUTER JOIN member_roles ON member_roles.member_id = members.id
         LEFT OUTER JOIN roles ON roles.id = member_roles.role_id
         LEFT OUTER JOIN users ON users.id = members.user_id
   WHERE members.project_id = 185
ORDER BY roles.position,
         users.TYPE DESC,
         users.lastname,
         users.firstname,
         users.id;

[redmine@redmine-dev-02 ~]$ RAILS_ENV=production bin/about

sh: svn: command not found
sh: hg: command not found
sh: cvs: command not found
sh: bzr: command not found
Environment:
  Redmine version                4.1.0.stable
  Ruby version                   2.5.5-p157 (2019-03-15) [x86_64-linux]
  Rails version                  5.2.4.1
  Environment                    production
  Database adapter               Mysql2
  Mailer queue                   ActiveJob::QueueAdapters::AsyncAdapter
  Mailer delivery                smtp
SCM:
  Git                            2.18.2
  Filesystem
Redmine plugins:
  redmine_custom_workflows       1.0.2

How can I solve this problem?
I would be very grateful for any help.