Uploaded image for project: 'Service Packs and Hot Fixes'
  1. Service Packs and Hot Fixes
  2. MNT-15007

CLONE - String values when migrating from MySQL to other DBs

    Details

      Description

      Clone for merging the changes in r114311 from V4.2-BUG-FIX to 5.0.N.

      A question about migrating from MySQL, where the strings are stored as TEXT to Oracle, where the strings have a maximum upper bound:

      ..., we’ve a red situation with our customer ***. They’ve asked us to migrate their existing Alfresco MYSQL database onto Oracle. They are in Alfresco 4.1.2.9.8 and they gave us a copy of their production database for we to migrate.

      Our action plan was the following :
      • Install a vanilla instance of Alfresco-4.1.2.9.8 onto an Oracle Database
      • Extract the Oracle Schema DDL and install it onto the target Database
      • Disable All imported Constraints until the migration process is completed
      • Use SQL Developer tool to generate a MySQL data migration project
      • Use SQL Developer tool to migrate the MySQL data into the Oracle target
      • Validate that data migration completed in a 1:1 ratio for all tables.
      • ….

      This procedure was executed sucessfully on a customer in US (with a much smaller database) by Alex Mahabir and Brian Long.

      We’ve found a problem on the alf_node_properties table on the STRING_VALUE field. Their original MYSQL alf_node_properties had 60 million records, out of those 60 million, 17000 records were not migrated because their data, on the string_value field didn’t fit on the Oracle target string_value VARCHAR2(1024 CHAR).

      Our first approach was to increase the size of this field to (3500 CHAR) but this has proven to be insufficient (they have aprox. 6000 records that need more than 3500 chars). In fact we’ve checked that their biggest record needs around (15000 CHAR). The MAX size for VARCHAR2 in char for Oracle is 4000 so there’s no way of raising it up to 15000 to accommodate their data.

      We could potentially change this to VARCHAR2(15000 BYTE) but that raises the following concerns :
      • This new field definition will probably break our Alfresco DAO and the product may have un-expected behaviour.
      • Assuming it’s safe to do this change, if we do it, what will happen during schema updates when they migrate to 4.2.X … 5.0.X

      Another question (this one internal and for myself) is if we could have predicted this without having to analyse their full source database contents for possible size conflicts as the one we’re having now….

      Alfresco DDLs for the alf_node_properties table on this version of Alfresco for both MYSQL and Oracle are below.

      CREATE TABLE alf_node_properties
      (
      node_id BIGINT NOT NULL,
      actual_type_n INTEGER NOT NULL,
      persisted_type_n INTEGER NOT NULL,
      boolean_value bit,
      long_value BIGINT,
      float_value float,
      double_value double precision,
      string_value text,
      serializable_value blob,
      qname_id BIGINT NOT NULL,
      list_index INTEGER NOT NULL,
      locale_id BIGINT NOT NULL,
      PRIMARY KEY (node_id, qname_id, list_index, locale_id),
      KEY fk_alf_nprop_n (node_id),
      KEY fk_alf_nprop_qn (qname_id),
      KEY fk_alf_nprop_loc (locale_id),
      CONSTRAINT fk_alf_nprop_loc FOREIGN KEY (locale_id) REFERENCES alf_locale (id),
      CONSTRAINT fk_alf_nprop_n FOREIGN KEY (node_id) REFERENCES alf_node (id),
      CONSTRAINT fk_alf_nprop_qn FOREIGN KEY (qname_id) REFERENCES alf_qname (id)
      ) ENGINE=InnoDB;

      Oracle:
      CREATE TABLE alf_node_properties
      (
      node_id NUMBER(19,0) NOT NULL,
      actual_type_n NUMBER(10,0) NOT NULL,
      persisted_type_n NUMBER(10,0) NOT NULL,
      boolean_value NUMBER(1,0),
      long_value NUMBER(19,0),
      float_value FLOAT,
      double_value DOUBLE PRECISION,
      string_value VARCHAR2(1024 CHAR),
      serializable_value BLOB,
      qname_id NUMBER(19,0) NOT NULL,
      list_index NUMBER(10,0) NOT NULL,
      locale_id NUMBER(19,0) NOT NULL,
      PRIMARY KEY (node_id, qname_id, list_index, locale_id),
      CONSTRAINT fk_alf_nprop_loc FOREIGN KEY (locale_id) REFERENCES alf_locale (id),
      CONSTRAINT fk_alf_nprop_n FOREIGN KEY (node_id) REFERENCES alf_node (id),
      CONSTRAINT fk_alf_nprop_qn FOREIGN KEY (qname_id) REFERENCES alf_qname (id)
      );

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  closedbugs Closed Bugs (Inactive)
                  Reporter:
                  dhulley Derek Hulley [X] (Inactive)
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  12 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 1 day, 6 hours, 5 minutes
                    1d 6h 5m

                      Structure Helper Panel