Schema upgrade needs to be manually fixed on clusters that were init'd before 1.2.1000

[vt0r]

@saumilmayani found and resolved an issue on our dev clusters where the schematool upgrade seems to have missed some important ALTER TABLE statements in the NOTIFICATION_LOG table. This issue will be updated with the associated JIRA when it's reported upstream. The fix can be manually applied as follows, but we'll want to automate this process:

Resolution:

Applied: HIVE-10562 patch for Mysql Schema Update. (/metastore/scripts/upgrade/mysql/038-HIVE-10562.mysql.sql)
https://jira.apache.org/jira/secure/attachment/12851962/HIVE-10562.5.patch

-- Connect to Mysql dB.
USE 
-- Step 1: Add the column for format
ALTER TABLE `NOTIFICATION_LOG` ADD `MESSAGE_FORMAT` varchar(16);

-- Step 2 : Change the type of the MESSAGE field from mediumtext to longtext
ALTER TABLE `NOTIFICATION_LOG` MODIFY `MESSAGE` longtext;

Finally:

Restart hive-metastore on the heads

service hive-metastore restart

---

The relevant logs the tenant found in the metastore logs:

ERROR metastore.RetryingHMSHandler (RetryingHMSHandler.java:invokeInternal(203)) - MetaException(message:Filtering is supported only on partition keys of type string)
        at org.apache.hadoop.hive.metastore.parser.ExpressionTree$FilterBuilder.setError(ExpressionTree.java:185)
        at org.apache.hadoop.hive.metastore.parser.ExpressionTree$LeafNode.getJdoFilterPushdownParam(ExpressionTree.java:440)
        at org.apache.hadoop.hive.metastore.parser.ExpressionTree$LeafNode.generateJDOFilterOverPartitions(ExpressionTree.java:357)
        at org.apache.hadoop.hive.metastore.parser.ExpressionTree$LeafNode.generateJDOFilter(ExpressionTree.java:279)
        at org.apache.hadoop.hive.metastore.parser.ExpressionTree.generateJDOFilterFragment(ExpressionTree.java:578)
        at org.apache.hadoop.hive.metastore.ObjectStore.makeQueryFilterString(ObjectStore.java:2810)
        at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsViaOrmFilter(ObjectStore.java:2381)
        at org.apache.hadoop.hive.metastore.ObjectStore.access$500(ObjectStore.java:182)
        at org.apache.hadoop.hive.metastore.ObjectStore$5.getJdoResult(ObjectStore.java:2725)
        at org.apache.hadoop.hive.metastore.ObjectStore$5.getJdoResult(ObjectStore.java:2710)
        at org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2573)
        at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByFilterInternal(ObjectStore.java:2727)
        at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByFilter(ObjectStore.java:2517)
        at sun.reflect.GeneratedMethodAccessor78.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:103)
        at com.sun.proxy.$Proxy10.getPartitionsByFilter(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_partitions_by_filter(HiveMetaStore.java:4957)
        at sun.reflect.GeneratedMethodAccessor77.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:147)
        at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105)
        at com.sun.proxy.$Proxy12.get_partitions_by_filter(Unknown Source)
        at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_filter.getResult(ThriftHiveMetastore.java:10644)
        at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$get_partitions_by_filter.getResult(ThriftHiveMetastore.java:10628)
        at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
        at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
        at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge.java:551)
        at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge.java:546)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:422)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1866)
        at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:546)
        at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:748)

What @saumilmayani found in the metastore logs:

WARN  metastore.MetaStoreDirectSql (MetaStoreDirectSql.java:ensureDbInit(240)) - Database initialization failed; direct SQL is disabled
javax.jdo.JDOException: Exception thrown when executing query : SELECT 'org.apache.hadoop.hive.metastore.model.MNotificationLog' AS `NUCLEUS_TYPE`,`A0`.`DB_NAME`,`A0`.`EVENT_ID`,`A0`.`EVENT_TIME`,`A0`.`EVENT_TYPE`,`A0`.`MESSAGE`,`A0`.`MESSAGE_FORMAT`,`A0`.`TBL_NAME`,`A0`.`NL_ID` FROM `NOTIFICATION_LOG` `A0` WHERE `A0`.`DB_NAME` = ''
...
...
NestedThrowablesStackTrace:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'A0.MESSAGE_FORMAT' in 'field list'

[aespinosa]

Is this a problem with schematool that comes with HDP 2.6.3?

[saumilmayani]

As per, @sujithortonworks mentions we need to upgrade to hive2 schematool.

Hi Saumil,

Unfortunately, that is a documentation error. Let me go ahead and create a documentation bug on that.

We recommend to do following changes in relevance to documentation
1. Install hive2 package for your build on one of the hive metastore machines ex. hive2_2_6_4_0_91 
2. for step3, use /usr/hdp/current/hive-server2-hive2/bin/schematool instead.

FYI, I see following command being used by Ambari while starting Hive Metastore.
export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server ; /usr/hdp/current/hive-server2-hive2/bin/schematool -initSchema -dbType mysql -userName hdpdba -passWord [PROTECTED] -verbose'] {'not_if': u"ambari-sudo.sh su hive -l -s /bin/bash -c 'export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server ; /usr/hdp/current/hive-server2-hive2/bin/schematool -info -dbType mysql -userName hdpdba -passWord [PROTECTED] -verbose'", 'user': 'hive'

Let me know if you have further questions.

Thanks
Sujit

[saumilmayani]

Need to install hive2 package and upgrade hive metastore schema to 2.x.

Hi Saumil,

Here is our recommendation to resolve this or upgrade metastore schema:

-Take a backup of underlying hive sql database
-Install hive2 package for your build on one of the hive metastore machines ex. hive2_2_6_4_0_91 
-Run the schematool upgradeSchema option with following command:
su - hive -c "/usr/hdp/current/hive-server2-hive2/bin/schematool -upgradeSchema -dbType <$databaseType>"
-Restart metastore and then Hiveservers

This is similar to how rolling upgrade is done.

If you have any hive start scripts that run this schematool from hive version, they would need to be updated as well.

Let me know if you have further questions.

Thanks
Sujit