MantisBT Schema Comparison and Update

This pages describes a process to compare a given MantisBT schema with a standard installation, and update it if necessary.

The SQL was written for a MySQL installation, and would not work on other RDBMS without adjustments due to use of metadata.

The script and process was tested using a dump from mantisbt.org bugs database taken on 13-Sep-2013.

• First thing, backup the schema to compare and update, e.g.

  mysqldump bugtracker >backup.sql

• Perform a fresh install of MantisBT to use a reference, in a separate schema, e.g. mantis_12x
• Copy the SQL script below to your favorite editor

  /*
   * Detect differences betweeen a reference MantisBT schema and a target schema
   * and generate a SQL script to fix the target schema so that it is identical
   * to the reference one.
   *
   * Usage
   * 1. Update the current and reference schemas
   * 2. Save the script
   * 3. Run it from command line:
   *    mysql -N < this_script.sql >update_script.sql
   * 4. Update the database
   *    mysql target_schema -v <update_script.sql
   *
   * Revision History
   * 2013-09-10  dregad  created
   * 2013-09-13  dregad  handle unique index creation
   * 2013-09-14  dregad  fix default value setting for ''
   * 2013-09-16  dregad  exclude mantis_upgrade_table from tables to drop
   */
   
   
  -- Set current schema (to update)
  USE bugtracker
   
  -- Define reference schema
  SET @schema_reference = 'mantis_12x';
   
  -- No update below this line
  -- ---------------------------------------------------------------------
   
  SET @schema_to_update = DATABASE();
   
  -- Creating Helper views
  SELECT '-- Create Temporary Helper views';
   
  -- Need functions as it's not possible to use variables within views
  CREATE FUNCTION sch_12x() RETURNS VARCHAR(20) RETURN @schema_reference;
  CREATE FUNCTION sch_upd() RETURNS VARCHAR(20) RETURN @schema_to_update;
   
  CREATE OR REPLACE VIEW v_tables AS
      SELECT org.table_schema sch, org.table_name tbl, 12x.table_name std_12x
      FROM information_schema.tables org
      LEFT JOIN information_schema.tables 12x
          ON 12x.table_schema = sch_12x() AND 12x.table_name = org.table_name
      WHERE org.table_schema = sch_upd()
        AND org.table_type = 'BASE TABLE';
   
  CREATE OR REPLACE VIEW v_indexes AS
      SELECT
          table_schema sch,
          TABLE_NAME tbl,
          index_name idx,
          GROUP_CONCAT(column_name ORDER BY seq_in_index) cols,
          non_unique
      FROM information_schema.statistics
      GROUP BY 1,2,3;
   
  -- ---------------------------------------------------------------------
  -- Generate SQL to update the schema
  -- ---------------------------------------------------------------------
   
  SELECT '-- Update Column type and Nullable attribute';
   
  SELECT
      CONCAT_WS(' ', 'ALTER TABLE', tbl, 'MODIFY', col, 12x_type,
          IF(12x_nullable = 'NO', 'NOT NULL', ''),
          IF(ISNULL(12x_default), '', CONCAT('DEFAULT ', IF(12x_default = '', '''''', 12x_default))),
          IF(12x_extra = 'auto_increment', 'AUTO_INCREMENT', ''),
          ';'
      ) "Update columns Type & Nullable"
  FROM (
   
      SELECT
          org.table_schema sch,
          org.table_name tbl,
          org.column_name col,
          12x.column_type 12x_type,
          org.column_type curr_type,
          12x.is_nullable 12x_nullable,
          org.is_nullable curr_nullable,
          12x.column_default 12x_default,
          org.column_default org_default,
          12x.extra 12x_extra,
          org.extra curr_extra
      FROM information_schema.columns 12x
      JOIN information_schema.columns org
          ON 12x.table_schema = @schema_reference
         AND 12x.table_name = org.table_name
         AND 12x.column_name = org.column_name
      WHERE org.table_schema = @schema_to_update
        AND (
              (12x.column_type != org.column_type)
           OR (12x.is_nullable != org.is_nullable)
           OR (12x.extra != org.extra)
            )
   
  ) delta;
   
   
  -- ---------------------------------------------------------------------
  SELECT '-- Default value';
   
  SELECT
      CONCAT_WS(' ', 'ALTER TABLE', tbl, 'ALTER COLUMN', col,
          IF( ISNULL(std_12x),
              'DROP DEFAULT',
              CONCAT( 'SET DEFAULT ', IF( std_12x = '', '''''', std_12x))
          ),
          ';'
      ) "Update columns default value"
  FROM (
   
      SELECT
          org.table_schema sch,
          org.table_name tbl,
          org.column_name col,
          org.column_default curr_val,
          12x.column_default std_12x
      FROM information_schema.columns 12x
      JOIN information_schema.columns org
          ON 12x.table_schema = @schema_reference
         AND 12x.table_name = org.table_name
         AND 12x.column_name = org.column_name
      WHERE org.table_schema = @schema_to_update
      AND (
              (12x.column_default IS NULL AND org.column_default IS NOT NULL)
           OR (12x.column_default IS NOT NULL AND org.column_default IS NULL)
           OR (12x.column_default != org.column_default)
          )
   
  ) delta;
   
   
  -- ---------------------------------------------------------------------
  SELECT '-- Tables';
   
  SELECT
      CONCAT_WS(' ', 'DROP TABLE', tbl, ';') "Drop non-standard tables"
  FROM (
   
      SELECT *
      FROM v_tables
      WHERE std_12x IS NULL
        AND tbl NOT LIKE '%_plugin_%'
        AND tbl <> 'mantis_upgrade_table'
   
  ) delta;
   
   
  -- ---------------------------------------------------------------------
  SELECT '-- Indexes';
  -- autoincrement ?
   
  SELECT
      CASE
          WHEN ISNULL(std_12x) THEN
              -- Drop indexes not existing in standard 1.2.x
              CONCAT_WS(' ', 'ALTER TABLE', tbl, 'DROP INDEX', idx, ';')
          WHEN ISNULL(curr_val) THEN
              -- Create missing indexes existing in standard 1.2.x
              CONCAT_WS(' ',
                  'CREATE', IF(non_unique = 0, 'UNIQUE', ''), 'INDEX',
                  idx, 'ON', tbl, '(', std_12x, ')', ';'
              )
          ELSE CONCAT('-- @TODO: Index update ', idx)
          END "Update indexes"
  FROM (
   
      SELECT
          org.sch, org.tbl, org.idx, org.cols curr_val,
          12x.cols std_12x, 12x.non_unique
      FROM (SELECT * FROM v_indexes WHERE sch = @schema_to_update) org
      LEFT JOIN (SELECT * FROM v_indexes WHERE sch = @schema_reference) 12x
          ON org.tbl = 12x.tbl AND org.idx = 12x.idx
      WHERE isnull(12x.cols) OR org.cols <> 12x.cols
      UNION -- Emulate full outer join
      SELECT
          12x.sch, 12x.tbl, 12x.idx, org.cols curr_val,
          12x.cols std_12x, 12x.non_unique
      FROM (SELECT * FROM v_indexes WHERE sch = @schema_to_update) org
      RIGHT JOIN (SELECT * FROM v_indexes WHERE sch = @schema_reference) 12x
          ON org.tbl = 12x.tbl AND org.idx = 12x.idx
      WHERE org.cols IS NULL
      ORDER BY 2,3
   
  ) delta
  WHERE tbl IN (SELECT tbl FROM v_tables WHERE std_12x IS NOT NULL);
   
   
  -- ---------------------------------------------------------------------
  SELECT '-- Drop Temporary Helper views';
   
  DROP VIEW v_tables;
  DROP VIEW v_indexes;
  DROP FUNCTION sch_12x;
  DROP FUNCTION sch_upd;

• Update the schema names as appropriate
• Save the Generation Script on your computer

• Create the Update Script (using the Generation Script created in the previous section); this will generate a series of SQL commands to update the schema

  mysql -N </path/to/generate_script.sql >/path/to/update_script.sql

  Note: temporary functions and views are created by the script in the current schema, then deleted at the end of execution.

• If you just want to compare the definitions of the schemas, you can use the sub-select statements at each step.

• Align the schema to the reference using the Update Script

  mysql bugtracker -v </path/to/update_script.sql

• Alternatively, you can combine the two steps

  mysql -N </path/to/generate_script.sql |mysql bugtracker -v

At this point, the two schemas should be identical. Double-check, test and restore from backup in case of issues.

SELECT table_schema, TABLE_NAME, index_name, non_unique, seq_in_index, column_name, sub_part, packed, NULLABLE
FROM information_schema.statistics
ORDER BY 1,2,3,5

Excluding plugin-specific tables, the following differences were found

• 2 extra tables (mantis_tasks_table, mantis_upgrade_table)
• 49 differences in indexes (extra, missing, different name, different definition, e.g. unique)
• 130 column type differences,
• 61 default values differences
• 1 column which should be “not null” but is not

Additionally, when attempting to create a missing unique index on username column, 2 cases of duplicate keys in mantis_user_table were discovered, (users updater, 2 records, and dmok, 3 records). After carefully checking that these accounts were not referenced anywhere in the database, the extra records were deleted through Manage Users page (keeping only the one that was created first).

As of this writing, the official tracker has not yet been updated; a message was sent to the mantisbt-dev mailing list to get feedback on the process first.