Could not connect to HMS from HS2 and Hive Interactive com.mysql.jdbc.PacketTooBigException: Packet for query is too large (6518628 > 4194304)

[#1]

I have 3 HMS and 3 HS2 with HA enabled, from yesterday HS2 and Hive Interactive fails to connect to HMS.

In Debug mode, the HMS logs shows the below output

' DEBUG [HikariPool-1 housekeeper]: pool.HikariPool (HikariPool.java:logPoolState(390)) - HikariPool-1 - Pool stats (total=20, active=1, idle=19, waiting=0)
 DEBUG [HikariPool-2 housekeeper]: pool.HikariPool (HikariPool.java:logPoolState(390)) - HikariPool-2 - Pool stats (total=20, active=0, idle=20, waiting=0)
DEBUG [Timer for 'hivemetastore' metrics system]: impl.MetricsSystemImpl (MetricsSystemImpl.java:snapshotMetrics(422)) - Snapshotted source UgiMetrics
DEBUG [Timer for 'hivemetastore' metrics system]: impl.MetricsSystemImpl (MetricsSystemImpl.java:snapshotMetrics(422)) - Snapshotted source hivemetastore
DEBUG [Timer for 'hivemetastore' metrics system]: impl.MetricsSystemImpl (MetricsSystemImpl.java:snapshotMetrics(422)) - Snapshotted source MetricsSystem,sub=Stats
DEBUG [Timer for 'hivemetastore' metrics system]: impl.MetricsSinkAdapter (MetricsSinkAdapter.java:putMetrics(98)) - enqueue, logicalTime=190000
DEBUG [timeline]: impl.MetricsSinkAdapter (MetricsSinkAdapter.java:consume(181)) - Pushing record UgiMetrics.ugi.UgiMetrics to timeline
 DEBUG [timeline]: impl.MetricsSinkAdapter (MetricsSinkAdapter.java:consume(181)) - Pushing record hivemetastore.default.General to timeline
DEBUG [timeline]: impl.MetricsSinkAdapter (MetricsSinkAdapter.java:consume(181)) - Pushing record MetricsSystem,sub=Stats.metricssystem.MetricsSystem to timeline
DEBUG [timeline]: impl.MetricsSinkAdapter (MetricsSinkAdapter.java:consume(199)) - Done'

'Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (6518628 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:612) ~[mysql-connector-java.jar:?]'

The HMS starts with no error but it is not listening on port 9083 and HS2 and Interactive fails to start.

HDP -3.1.0

HIVE -3.0.0.3.1

MySql - 5.7

MySql connector - 5.1.25
 

[#2]

Can you provide the output from MySQL

show variables like 'max_allowed_packet';

Please also increase the variable in MySQL: https://dev.mysql.com/doc/refman/8.0/en/packet-too-large.html 

[#3]

'max_allowed_packet' = 4194304

This issue started occurring after I set this property in my.cnf max_allowed_packet= 10M and restarted MySql server. HMS was stuck in sinking metrics and was not connecting to MySql hence HS2 and Interactive failed to connect.

So I reverted the setting in my.cnf and HIVE started working for few hours but fails again.

[#4]

COuld you adjust the value bit more than  6518628  and check?

[#5]

I've set the value to 32M, but still facing this connection issue between HMS and HS2 servers.

[#6]

Please share the updated logs.I want to check the error