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

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.

    Leave a Reply

    Your email address will not be published.

    My new Snowflake Blog is now live. I will not be updating this blog anymore but will continue with new contents in the Snowflake world!