[YSQL] upgrade issue from 2.0.5.7 to 2.2.2.1 - FATAL: Query error: Bad schema: Cannot have both cotable ID and pgtable ID

[rao-vasireddy]

Jira Link: DB-4847
When upgrading a universe running 2.0.5.7 and having YSQL enabled with YSQL tables/indexes present, got an error when connecting to the db using ysqlsh

[yugabyte@ip-10-241-0-146 bin]$ ./ysqlsh -h 10.241.0.146
ysqlsh: FATAL: Query error: Bad schema: Cannot have both cotable ID and pgtable ID

[jaki]

In debug mode, you hit an earlier check:

  void set_cotable_id(const Uuid& cotable_id) {
    if (!cotable_id.IsNil()) {
      DCHECK_EQ(pgtable_id_, 0);
    }
    cotable_id_ = cotable_id;
  }

It seems that the TableInfo has a pgtable_id, but it's trying to add cotable_id. This seems to happen for system tables, so their having a pgtable_id is wrong, even though the number is itself correct.

For those who don't know, system tables use cotable id because all system tables from all databases go in the system catalog tablet on master, and using pgtable id would not be able to distinguish between system tables across databases.

[jaki]

Found it. The issue is with is_ysql_catalog_table.

git log -S is_ysql_catalog_table --oneline

6dfe2f039 colocation: set pgtable_id for SetSchema (#4986)
f16e5caf9 #4233: Check for properties equivalence when comparing schemas for cdc replication
94e2c3ad5 [YSQL] Support alter table for colocated tables: #4293
f4d4ea7d9 #3365: [Colocation] Use 4 byte PG table ID as dockey prefix
b68b84bfd #3108 Limited transactional DDL: each DDL statement in its own transaction

Commit b68b84b introduced is_ysql_catalog_table field in TablePropertiesPB.

1. TablePropertiesPB is used in SchemaPB
2. SchemaPB is used in TableInfoPB

Commit f4d4ea7 changes KvStoreInfo::LoadTablesFromPB:

diff --git a/src/yb/tablet/tablet_metadata.cc b/src/yb/tablet/tablet_metadata.cc
index 36d130fa7..e19e2efa8 100644
--- a/src/yb/tablet/tablet_metadata.cc
+++ b/src/yb/tablet/tablet_metadata.cc
@@ -40,6 +40,7 @@
 #include <boost/optional.hpp>
 #include "yb/rocksdb/db.h"
 #include "yb/rocksdb/options.h"
+#include "yb/common/entity_ids.h"
 #include "yb/common/wire_protocol.h"
 #include "yb/consensus/opid_util.h"
 #include "yb/docdb/docdb_rocksdb_util.h"
@@ -171,11 +172,16 @@ Status KvStoreInfo::LoadTablesFromPB(
     auto table_info = std::make_unique<TableInfo>();
     RETURN_NOT_OK(table_info->LoadFromPB(table_pb));
     if (table_info->table_id != primary_table_id) {
-      Uuid cotable_id;
-      CHECK_OK(cotable_id.FromHexString(table_info->table_id));
-      // TODO(#79): when adding for multiple KV-stores per Raft group support - check if we need
-      // to set cotable ID.
-      table_info->schema.set_cotable_id(cotable_id);
+      if (table_pb.schema().table_properties().is_ysql_catalog_table()) {
+        Uuid cotable_id;
+        CHECK_OK(cotable_id.FromHexString(table_info->table_id));
+        // TODO(#79): when adding for multiple KV-stores per Raft group support - check if we need
+        // to set cotable ID.
+        table_info->schema.set_cotable_id(cotable_id);
+      } else {
+        auto pgtable_id = VERIFY_RESULT(GetPgsqlTableOid(table_info->table_id));
+        table_info->schema.set_pgtable_id(pgtable_id);
+      }
     }
     tables[table_info->table_id] = std::move(table_info);
   }

The PBs won't have is_ysql_catalog_table on version 2.0.5.7 because that's before commit b68b84b. Upgrading those clusters to versions including and after commit f4d4ea7 will cause the table infos of postgres system tables to get incorrectly populated with pgtable_id rather than cotable_id because we'll always have is_ysql_catalog_table as false.

To solve this,

• I saw commit b68b84b had some upgrade handling, so maybe we can use that
• I saw a MigrateSuperblockForD5900 in tablet_metadata.cc that we could also possibly imitate
• I think upgrading to v2.0.9 or v2.0.10 as an intermediate step might work since those releases are in between the mentioned commits

[jaki]

Repro:

1. Get releases:
   • 2.0.5.2: https://downloads.yugabyte.com/yugabyte-2.0.5.2-linux.tar.gz
   • 2.2.2.0: https://downloads.yugabyte.com/yugabyte-2.2.2.0-linux.tar.gz
   • 2.6.11.0: https://downloads.yugabyte.com/releases/2.6.11.0/yugabyte-2.6.11.0-b3-linux-x86_64.tar.gz (possibly can replace this with latest)
2. Create rf 3 node using 2.0.5.2
3. Rolling upgrade that cluster to 2.2.2.0
4. Rolling upgrade that cluster to 2.6.11.0 (when new master starts up, it should FATAL within a minute)

Upgrading to debug build can make it fail earlier as mentioned in #5658 (comment). That can be avoided by applying patch in #11229 (comment), but there's still another issue (described in that same comment).

[frozenspider]

Current fix-in-the-works for #7378 does not alter this behaviour.

[m-iancu]

Closing as 2.0.5 is very old version, and rolling upgrade from 2.0.9 onwards should work.