Uploaded image for project: 'Infrastructure'
  1. Infrastructure
  2. INFRA-292

Convert MyISAM tables to InnoDB

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Component/s: jira
    • Labels:
      None
    • Similar Issues:

      Description

      Arnaud Héritier pointed out that some of our tables run on MyISAM, and JIRA recommends us to switch to InnoDB.

      I vaguely recall that he attempted this back in the days and had some troubles converting tables. See the discussion.

      I recommend Arnaud to use this as an opportunity to get the local containerized JIRA development set up, then he can try converting all the tables via ALTER TABLE cwd_membership ENGINE=InnoDB; to see if any table has a problem in the way its index is set up, etc.

      Once the dry-run is successful, we can make that change in production DB.

        Attachments

          Issue Links

            Activity

            Hide
            aheritier Arnaud Héritier added a comment -

            It is also reported in the admin UI : https://issues.jenkins-ci.org/secure/admin/ViewSystemInfo.jspa

            Warnings
            WARNING: You are using MySQL and the MyISAM engine. This configuration can introduce performance problems. For more information please see this article.

            With a link to https://jira.atlassian.com/browse/JRA-24124

            Show
            aheritier Arnaud Héritier added a comment - It is also reported in the admin UI : https://issues.jenkins-ci.org/secure/admin/ViewSystemInfo.jspa Warnings WARNING: You are using MySQL and the MyISAM engine. This configuration can introduce performance problems. For more information please see this article. With a link to https://jira.atlassian.com/browse/JRA-24124
            Hide
            rtyler R. Tyler Croy added a comment -

            I've provided Arnaud Héritier with a backup DB dump to validate the migration from MyISAM to InnoDB with. We're hoping to accomplish this before the end of the year.

            Show
            rtyler R. Tyler Croy added a comment - I've provided Arnaud Héritier with a backup DB dump to validate the migration from MyISAM to InnoDB with. We're hoping to accomplish this before the end of the year.
            Hide
            aheritier Arnaud Héritier added a comment - - edited

            R. Tyler Croy As I didn't have the FS backup I only did my test on mysql within the docker environment

            I just followed https://jira.atlassian.com/browse/JRA-24124 and adapted it to docker

            To describe all tables

            echo "SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'jiradb';" | docker exec -i mariadb mysql --user=root --password=s3cr3t jiradb

            there are 108 tables in MyISAM.

            To generate the list of alter table commands :

            echo "select concat('alter table ',  TABLE_NAME, ' engine = InnoDB;') from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'jiradb' and engine = 'MyISAM'" | docker exec -i mariadb mysql --user=root --password=s3cr3t jiradb > convert.sql

            remove the first line of convert.sql and execute it

            cat convert.sql | docker exec -i mariadb mysql --user=root --password=s3cr3t jiradb > convert.sql

            All tables are correctly moved to InnoDB. If you have a full backup of Jira give it a try to verify that it correctly restarts but I think it is safe to do the same in production.

            Show
            aheritier Arnaud Héritier added a comment - - edited R. Tyler Croy As I didn't have the FS backup I only did my test on mysql within the docker environment I just followed https://jira.atlassian.com/browse/JRA-24124 and adapted it to docker To describe all tables echo "SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'jiradb' ;" | docker exec -i mariadb mysql --user=root --password=s3cr3t jiradb there are 108 tables in MyISAM. To generate the list of alter table commands : echo "select concat( 'alter table ' , TABLE_NAME, ' engine = InnoDB;' ) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'jiradb' and engine = 'MyISAM' " | docker exec -i mariadb mysql --user=root --password=s3cr3t jiradb > convert.sql remove the first line of convert.sql and execute it cat convert.sql | docker exec -i mariadb mysql --user=root --password=s3cr3t jiradb > convert.sql All tables are correctly moved to InnoDB. If you have a full backup of Jira give it a try to verify that it correctly restarts but I think it is safe to do the same in production.
            Hide
            rtyler R. Tyler Croy added a comment -

            This work has been completed thanks to some help from the OSUOSL

            Show
            rtyler R. Tyler Croy added a comment - This work has been completed thanks to some help from the OSUOSL
            Hide
            aheritier Arnaud Héritier added a comment -

            perfect guys. Thx

            Show
            aheritier Arnaud Héritier added a comment - perfect guys. Thx

              People

              • Assignee:
                rtyler R. Tyler Croy
                Reporter:
                kohsuke Kohsuke Kawaguchi
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: