However, it failed with the following message:
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 startIf 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 startAnd 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.
Related articles
