HIVE-17306: Support MySQL InnoDB Cluster

[KiranVelumuri]

What changes were proposed in this pull request?

Introduce primary key columns to support HA for MySQL cluster. This works for MySQL 8.0.23 and above.

Why are the changes needed?

HIVE-17306

Does this PR introduce any user-facing change?

No

Is the change a dependency upgrade?

No

How was this patch tested?

Perform schema upgrade and verify transactions are working fine, and the meta tables are getting updated as earlier.

[KiranVelumuri]

@zabetak @okumin Could you please help to review? Thank you.

[sonarqubecloud[bot]]

Quality Gate passed

Issues
0 New issues
0 Accepted issues

Measures
0 Security Hotspots
0.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarQube Cloud

[KiranVelumuri]

Ref: https://www.trevorlasn.com/blog/invisible-columns-in-sql

[KiranVelumuri]

@chinnaraolalam @deniskuzZ @abstractdog @zhangbutao @wecharyu @dengzhhu653 Could you please help to review? Thank you.

[deniskuzZ]

@KiranVelumuri, I don't understand what it solves or why you are changing the schema for all the supported DBs.

[KiranVelumuri]

@deniskuzZ To implement High Availability (HA) for MySQL using the InnoDB cluster, it is a requirement that primary keys be present for all the tables. So, this PR is for adding primary keys to all such tables in Hive's schema which do not have a primary key defined. For the case of tables which cannot have unique primary keys, I added additional invisible column.

[deniskuzZ]

@KiranVelumuri thanks for the explanation. It duplicates partially https://github.com/apache/hive/pull/3214 cc @kovjanos

[chinnaraolalam]

@KiranVelumuri Is all the supported DB's support invisible columns. Even though if syntax supports what test are performed to confirm this.

[KiranVelumuri]

@KiranVelumuri Is all the supported DB's support invisible columns. Even though if syntax supports what test are performed to confirm this.

Not all DBs support invisible columns. Please refer to my earlier comment. We have Docker tests in our Jenkins that validate fresh install and upgrade scenarios, which have all passed.

[deniskuzZ]

I am not sure if we can accept these changes. Looping HMS folks: @saihemanth-cloudera, @dengzhhu653

[github-actions[bot]]

This pull request has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Feel free to reach out on the [email protected] list if the patch is in need of reviews.