Wednesday, February 6, 2019

"msck repair" of a partition table fails with error "Expecting only one partition but more than one partitions are found.".

Symptom:

In spark-shell, "msck repair" of a partition table named "database_name.table_name" fails with error "Expecting only one partition but more than one partitions are found.".

Env:

Hive 2.1(with MySQL as the backend database for Hive Metastore)
Spark 2.2.1

Troubleshooting:

The source code for this error message is :
metastore/src/java/org/apache/hadoop/hive/metastore/ObjectStore.java
      query =
          pm.newQuery(MPartition.class,
              "table.tableName == t1 && table.database.name == t2 && partitionName == t3");
      query.declareParameters("java.lang.String t1, java.lang.String t2, java.lang.String t3");
      mparts = (List<MPartition>) query.execute(tableName, dbName, name);
      pm.retrieveAll(mparts);
      commited = commitTransaction();
      // We need to compare partition name with requested name since some DBs
      // (like MySQL, Derby) considers 'a' = 'a ' whereas others like (Postgres,
      // Oracle) doesn't exhibit this problem.
      if (mparts != null && mparts.size() > 0) {
        if (mparts.size() > 1) {
          throw new MetaException(
              "Expecting only one partition but more than one partitions are found.");
        }

Basically it will generate a query in MySQL(Hive Metastore backend database) to check if there are any duplicate entries based on Table Name, Database Name and Partition Name.
Then we can run below query in MySQL to find out the duplicate entries from PARTITIONS table for that specific Hive partition table -- database_name.table_name:
select p.PART_NAME,count(*)
from TBLS t, PARTITIONS p, DBS d
where d.DB_ID=t.DB_ID
and p.TBL_ID=t.TBL_ID
and t.TBL_NAME='table_name' and d.NAME='database_name'
group by p.PART_NAME
having count(*)>1;

If above query returns one partition name as "key=0", then it means there are 2 or more entries inside PARTITIONS table for that specific partition "key=0".
Then we need to list all the data for that partition:
select p.*
from TBLS t, PARTITIONS p, DBS d
where d.DB_ID=t.DB_ID
and p.TBL_ID=t.TBL_ID
and t.TBL_NAME='table_name' and d.NAME='database_name'
and p.PART_NAME='key=0';
For example, if above query returns two entries as below:
+---------+-------------+------------------+-----------+-------+--------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME | SD_ID | TBL_ID |
+---------+-------------+------------------+-----------+-------+--------+
|     255 |  1536697318 |                0 | key=0     |   262 |      7 |
|     256 |  1536697319 |                0 | key=0     |   263 |      7 |
+---------+-------------+------------------+-----------+-------+--------+
So in all, above results show there is already inconsistency such as duplicate entries in Hive Metastore backend database.

Root Cause:

For this specific case, we found that there were no constraints created for those Hive Metastore backend MySQL tables.
Take "PARTITIONS" table for example, we can run "show create table PARTITIONS;" in MySQL and check if below 2 FOREIGN KEY CONSTRAINT exists or not:
  CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`),
  CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`)

Solution:

1. Short term fix

The safest way to clean the duplicate entries is to use Hive command instead of manually deleting entries in MySQL.
We just need to drop and re-create that problematic Hive partition, and after that, those duplicate entries are cleaned.
For example:
ALTER TABLE database_name.table_name DROP PARTITION(key=0);
After that, double confirm all the duplicate entries are gone in MySQL:
select p.PART_NAME,count(*)
from TBLS t, PARTITIONS p, DBS d
where d.DB_ID=t.DB_ID
and p.TBL_ID=t.TBL_ID
and t.TBL_NAME='table_name' and d.NAME='database_name'
group by p.PART_NAME
having count(*)>1;

2. Long term fix

Since this issue is due to wrong DDLs in Hive Metastore, we would suggest backup the mysql database and re-create the Hive Metastore backend database from scratch using Hive schema tool.


18 comments:

  1. Finally, and unfortunately, there are many locksmiths out there who are not licensed locksmiths home security

    ReplyDelete
  2. Excellent and very exciting site. Love to watch. Keep Rocking. Air Conditioning Repair in Santa Barbara

    ReplyDelete
  3. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. Sub Zero Appliance Repair in Los Angeles

    ReplyDelete
  4. I finally found great post here.I will get back here. I just added your blog to my bookmark sites. thanks.Quality posts is the crucial to invite the visitors to visit the web page, that's what this web page is providing. SUB ZERO APPLIANCE REPAIR & SERVICE IN SANTA BARBARA

    ReplyDelete
  5. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. CARLSBAD APPLIANCE REPAIR & Service

    ReplyDelete
  6. Excellent and very exciting site. Love to watch. Keep Rocking. STOVE & RANGE REPAIR in San Diego

    ReplyDelete
  7. I have read all the comments and suggestions posted by the visitors for this article are very fine,We will wait for your next article so only.Thanks! Viking , Wolf, Dacor Stove & Range Repair Marina Del Rey

    ReplyDelete
  8. This particular papers fabulous, and My spouse and i enjoy each of the perform that you have placed into this. I’m sure that you will be making a really useful place. I has been additionally pleased. Good perform! SubZero, Viking Ice Maker Repair Monterey Park

    ReplyDelete
  9. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. My Thermador Oven is Not Heating

    ReplyDelete
  10. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. รีแพร์

    ReplyDelete
  11. I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often. Viking , Wolf, Dacor Stove & Range Repair Near Me

    ReplyDelete
  12. This is my first visit to your web journal! We are a group of volunteers and new activities in the same specialty. Website gave us helpful data to work. SubZero, Viking, Scotsman Ice Maker Repair in SANTA MONICA SubZero, Viking, Scotsman Ice Maker Repair in SANTA MONICA

    ReplyDelete
  13. The second type of this bottom-freezer refrigerator is within the materials used, the color, and the door styles. Colors were available in stainless steel, beige, wood color, black and even the essential white. While the doors styles were in variety of French doors, full doors, and armoire doors. Assistenza frigoriferi Liebherr

    ReplyDelete
  14. cool stuff you have got and you keep update all of us. instagram likes app download 2019

    ReplyDelete
  15. From the above organizations tables are intended to fit into each space of your life according to your prerequisites. ELECTROLUX APPLIANCE REPAIR & SERVICE IN SANTA BARBARA

    ReplyDelete
  16. A few people consider the vibe of a coffee table their main need and may incline toward an exceptionally huge coffee table for most extreme visual effect. top wheelcoffee tables

    ReplyDelete
  17. I recently noticed your website back i are generally looking through which on a daily basis. You’ve got a loads of information at this site so i actually like your look to the web a tad too. Maintain the best show results! Tumble Dryer Repairs

    ReplyDelete

Popular Posts