Hive Metastore Upgrade Failed with Error: Specified key was too long; max key length is 1000 bytes

Sympton:

After upgrading Cloudera Manager (CM) from CDH5.3 to CDH5.4, the requirement was to also upgrade the Hive Metastore Database Schema:

upgrade-hive-metastore-database-schema

However, it failed with the following message:

mysql-1000-bytes-error

It is not immediately apparent what needs to be done. Initially you might think that some of the indexes contained too many characters. However, it was not the case in this scenario.

Cause:

After examination, we have found out that this was caused by corrupted InnoDB log files in MySQL.

So the story was that the MySQL Metastore was configured to use InnoDB Engine, however, when InnoDB log files got corrupted, all tables were reverted back to MyISAM, even though they were created using InnoDB.

mysql> SHOW ENGINES;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)

This showed that InnoDB was not supported, however, in my.cnf file, there was no such config to disable InnoDB specifically.

Resolution:

The solution was simple, all we need to do is to stop MySQL service, remove those problematic log files of InnoDB located at /var/lib/mysql directory (of course, depends on your installation), and finally bring MySQL back online:

[root@localhost ~]# service mysqld stop
[root@localhost ~]# ll /var/lib/mysql/
total 36876
drwx------ 2 mysql mysql     4096 Jun 26 06:46 hive_metastore
-rw-rw---- 1 mysql mysql 27262976 Jun 26 06:47 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jun 26 06:47 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun 26 06:46 ib_logfile1
drwx------ 2 mysql mysql     4096 Jun 11 01:20 mysql
srwxrwxrwx 1 mysql mysql        0 Jun 11 01:20 mysql.sock
drwx------ 2 mysql mysql     4096 Jun 26 05:44 test
[root@localhost ~]#

So simply run:

[root@localhost ~]# cd /var/lib/mysql
[root@localhost ~]# rm -f ib_logfile0 ib_logfile1
[root@localhost ~]# service mysqld start

If you don’t feel confident, you can also just rename those files

[root@localhost ~]# cd /var/lib/mysql
[root@localhost ~]# mv ib_logfile0 ib_logfile0.bak
[root@localhost ~]# mv ib_logfile1 ib_logfile1.bak
[root@localhost ~]# service mysqld start

And then re-run the “Upgrade Hive Metastore Database Schema”, and finally restart HMS.

This of course is not the solution for all cases, but worth a try.

    Hive METASTORE exited unexpectedly, and no apparent error in the error log

    This article explains how to trouble shoot the issue when Hive METASTORE dies with no apparent error message in the log.

    Cloudera Manager (short for CM) has a mechanism to kill the processes that have reached its memory allocations. So when this scenario happens, it usually means that Hive METASTORE has memory issues and the process got killed by CM.

    The reason that no errors in the server logs is because the server process got killed by CM agent process and it did not get any chance to log its errors.

    The actual errors captured by CM is located in /var/run/cloudera-scm-agent/process/XXX-hive-HIVEMETASTORE/logs/*, where XXX is a number that represents each process ID that’s allocated to the server daemon every time it is restarted.

    If you can find the following error in those files:

    java.lang.OutOfMemoryError: Java heap space
    Dumping heap to /tmp/hive_hive-HIVEMETASTORE-592629fdad78ff2feb384c5dfbac4c5b_pid$$.hprof …
    Heap dump file created [102740174 bytes in 0.777 secs]
    #
    # java.lang.OutOfMemoryError: Java heap space
    # -XX:OnOutOfMemoryError=”/usr/lib64/cmf/service/common/killparent.sh”
    # Executing /bin/sh -c “/usr/lib64/cmf/service/common/killparent.sh”…

    you can confirm that METASTORE is hitting the OOM error.

    To fix it, simply log into CM and increase the HIVEMETASTORE Heaps to 2-4 GB depending on your cluster size, see screenshot below:

    hive-server-memory-config

    And then restart the server afterwards.

    The same trouble shooting technique can also apply to Hive Server 2 and other server processes that are managed by CM.