Bug Report: VTOrc runs an unnecessary PRS

[GuptaManan100]

Overview of the Issue

VTOrc ran a PRS when there was already a primary which was successfully promoted.

The following steps happended -

1. Tabelt A was promoted to the primary but getting its MySQL information fails. This causes the database_instance table for this instacne to be empty.
2. VTOrc runs a PRS trying to promote a different primary.

Reproduction Steps

1. Start a new cluster and promote a primary via vtcltd.
2. Repeat multiple times until VTOrc runs an unnecessary PRS

Binary Version

main

Operating System and Environment details

main

Log Fragments

No response

[GuptaManan100]

This issue was introduced in #9409 when the LEFT JOIN with database_instance was converted to JOIN. There is discussion as to why this was done here - #9409 (comment).

This scenario however was overlooked. Having a JOIN there means that the tablet which is the primary isn't returned from the query, which leads VTOrc to think that there is no primary in the given shard.

[GuptaManan100]

The fix is to convert the JOIN back to a LEFT JOIN. We will however have to fix the problem specified in #9409 (comment) in a different way then.

[GuptaManan100]

Before the actual bug fix, a cleanup for the cluster_name is required. It is currently stored in the database_instance which means that just as we switch to a LEFT JOIN we'll have the problem of the cluster name being empty. Instead we should be using keyspace and shard. This refactor has been accomplished in #12012