User Tools

  • Logged in as: anonymous (anonymous)
  • Log Out

Site Tools


mantisbt:schema_compare_update

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.

Preparation

  • 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

Process execution

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

Notes

Comparing Indexes

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

Updating mantisbt.org bugtracker

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.

mantisbt/schema_compare_update.txt · Last modified: 2013/09/16 03:56 by dregad