Hive
  1. Hive
  2. HIVE-3999

Mysql metastore upgrade script will end up with different schema than the full schema load

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.11.0
    • Component/s: Metastore
    • Labels:
      None
    • Hadoop Flags:
      Reviewed

      Description

      I've noticed that the file hive-schema-0.10.0.mysql.sql is creating table SDS with following column:

        `IS_STOREDASSUBDIRECTORIES` bit(1) NOT NULL,
      

      However the upgrade script 011-HIVE-3649.mysql.sql will create the column differently:

      ALTER TABLE `SDS` ADD `IS_STOREDASSUBDIRECTORIES` bit(1) ;
      

      Thus user will get slightly different schema each time - once with "NOT NULL" and secondly with "NULL" definition.

      1. mysql_upgrade_issue.patch
        0.8 kB
        Jarek Jarcec Cecho

        Issue Links

          Activity

          Hide
          caofangkun added a comment -

          Hi Jarek Jarcec Cecho,
          I agree with you now. Your solution is the most suitable way.

          Show
          caofangkun added a comment - Hi Jarek Jarcec Cecho, I agree with you now. Your solution is the most suitable way.
          Hide
          Jarek Jarcec Cecho added a comment -

          Hi sir,
          setting the default value was one of the options that I've explored when working on this JIRA. However I've decided not to use it and rather use three step column addition (please checkout the patch). One of the reasons behind this decision is that with no explicit default value, running old hive against new metastore will fail. I believe that this is much safer as otherwise Hive would continue to operate on updated schema repository and might eventually corrupt the data.

          Jarcec

          Show
          Jarek Jarcec Cecho added a comment - Hi sir, setting the default value was one of the options that I've explored when working on this JIRA. However I've decided not to use it and rather use three step column addition (please checkout the patch). One of the reasons behind this decision is that with no explicit default value, running old hive against new metastore will fail. I believe that this is much safer as otherwise Hive would continue to operate on updated schema repository and might eventually corrupt the data. Jarcec
          Hide
          caofangkun added a comment -

          We'd better set a default value for column IS_STOREDASSUBDIRECTORIES:

          ALTER TABLE `SDS` ADD `IS_STOREDASSUBDIRECTORIES` BIT(1) NOT NULL;
          ALTER TABLE `SDS` ALTER `IS_STOREDASSUBDIRECTORIES` SET DEFAULT 0;

          or Query May Throw :
          Failed with exception javax.jdo.JDODataStoreException: Insert of object "org.apache.hadoop.hive.metastore.model.MStorageDescriptor@c3c44" using statement "INSERT INTO `SDS` (`SD_ID`,`OUTPUT_FORMAT`,`CD_ID`,`NUM_BUCKETS`,`INPUT_FORMAT`,`SERDE_ID`,`IS_COMPRESSED`,`LOCATION`) VALUES (?,?,?,?,?,?,?,?)" failed : Field 'IS_STOREDASSUBDIRECTORIES' doesn't have a default value
          NestedThrowables:
          java.sql.SQLException: Field 'IS_STOREDASSUBDIRECTORIES' doesn't have a default value

          Show
          caofangkun added a comment - We'd better set a default value for column IS_STOREDASSUBDIRECTORIES: ALTER TABLE `SDS` ADD `IS_STOREDASSUBDIRECTORIES` BIT(1) NOT NULL; ALTER TABLE `SDS` ALTER `IS_STOREDASSUBDIRECTORIES` SET DEFAULT 0; or Query May Throw : Failed with exception javax.jdo.JDODataStoreException: Insert of object "org.apache.hadoop.hive.metastore.model.MStorageDescriptor@c3c44" using statement "INSERT INTO `SDS` (`SD_ID`,`OUTPUT_FORMAT`,`CD_ID`,`NUM_BUCKETS`,`INPUT_FORMAT`,`SERDE_ID`,`IS_COMPRESSED`,`LOCATION`) VALUES (?,?,?,?,?,?,?,?)" failed : Field 'IS_STOREDASSUBDIRECTORIES' doesn't have a default value NestedThrowables: java.sql.SQLException: Field 'IS_STOREDASSUBDIRECTORIES' doesn't have a default value
          Hide
          Hudson added a comment -

          Integrated in Hive-trunk-h0.21 #1961 (See https://builds.apache.org/job/Hive-trunk-h0.21/1961/)
          HIVE-3999 Mysql metastore upgrade script will end up with different schema than
          the full schema load (Jarek and Mark via namit) (Revision 1443952)

          Result = SUCCESS
          namit : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1443952
          Files :

          • /hive/trunk/metastore/scripts/upgrade/mysql/011-HIVE-3649.mysql.sql
          Show
          Hudson added a comment - Integrated in Hive-trunk-h0.21 #1961 (See https://builds.apache.org/job/Hive-trunk-h0.21/1961/ ) HIVE-3999 Mysql metastore upgrade script will end up with different schema than the full schema load (Jarek and Mark via namit) (Revision 1443952) Result = SUCCESS namit : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1443952 Files : /hive/trunk/metastore/scripts/upgrade/mysql/011- HIVE-3649 .mysql.sql
          Hide
          Hudson added a comment -

          Integrated in Hive-trunk-hadoop2 #112 (See https://builds.apache.org/job/Hive-trunk-hadoop2/112/)
          HIVE-3999 Mysql metastore upgrade script will end up with different schema than
          the full schema load (Jarek and Mark via namit) (Revision 1443952)

          Result = FAILURE
          namit : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1443952
          Files :

          • /hive/trunk/metastore/scripts/upgrade/mysql/011-HIVE-3649.mysql.sql
          Show
          Hudson added a comment - Integrated in Hive-trunk-hadoop2 #112 (See https://builds.apache.org/job/Hive-trunk-hadoop2/112/ ) HIVE-3999 Mysql metastore upgrade script will end up with different schema than the full schema load (Jarek and Mark via namit) (Revision 1443952) Result = FAILURE namit : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1443952 Files : /hive/trunk/metastore/scripts/upgrade/mysql/011- HIVE-3649 .mysql.sql
          Hide
          Mark Grover added a comment -

          Thanks Namit!

          Show
          Mark Grover added a comment - Thanks Namit!
          Hide
          Hudson added a comment -

          Integrated in hive-trunk-hadoop1 #73 (See https://builds.apache.org/job/hive-trunk-hadoop1/73/)
          HIVE-3999 Mysql metastore upgrade script will end up with different schema than
          the full schema load (Jarek and Mark via namit) (Revision 1443952)

          Result = ABORTED
          namit : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1443952
          Files :

          • /hive/trunk/metastore/scripts/upgrade/mysql/011-HIVE-3649.mysql.sql
          Show
          Hudson added a comment - Integrated in hive-trunk-hadoop1 #73 (See https://builds.apache.org/job/hive-trunk-hadoop1/73/ ) HIVE-3999 Mysql metastore upgrade script will end up with different schema than the full schema load (Jarek and Mark via namit) (Revision 1443952) Result = ABORTED namit : http://svn.apache.org/viewcvs.cgi/?root=Apache-SVN&view=rev&rev=1443952 Files : /hive/trunk/metastore/scripts/upgrade/mysql/011- HIVE-3649 .mysql.sql
          Hide
          Namit Jain added a comment -

          Committed. Thanks Jarek and Mark

          Show
          Namit Jain added a comment - Committed. Thanks Jarek and Mark
          Hide
          Mark Grover added a comment -

          +1 (non-committer)

          Show
          Mark Grover added a comment - +1 (non-committer)

            People

            • Assignee:
              Jarek Jarcec Cecho
              Reporter:
              Jarek Jarcec Cecho
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development