EX SSL-VPN: SQL errors displayed when attempting to view "User Session" in the AMC

03/26/2020 5 10358

DESCRIPTION:
EX SSL-VPN: SQL errors displayed when attempting to view "User Session" in the AMC

RESOLUTION:

Question:

When attempting to view user’s session via AMC, the following error has been seen: “The
session list could not be obtained due to a database error. Please view these
log messages for the possible cause. "


********Snippet of management. Logs**********
When trying to view user sessions we get the following error: "The session list could not be obtained due to a database error. Please view these log messages for the possible cause. "

The management message log shows:
"Caught exception while trying to get session list: java.sql.SQLException: SQL exception executing: SELECT si.session_id, si.username, si.longUsername, si.successfulAuthentication, rt.name AS realm, rt.xml_id AS realmId, rt.object_id AS realmDb, ct.name AS community, ct.xml_id AS communityId, ct.object_id AS communityDb, zt.name AS zone, zt.xml_id AS zoneId, zt.object_id AS zoneDb, st.name AS signature, st.xml_id AS signatureId, st.object_id AS signatureDb, si.accessAgents AS accessAgents, zt.extra AS epcAgent, si.remoteAddress, si.localAddress, IF(si.endTime > NOW(), si.licensed, IF(si.endTime = 0, si.licensed, "no")) AS licensed, si.node_id, si.startTime, si.endTime, TIMESTAMPDIFF(SECOND, si.startTime, IF(si.endTime > NOW(), NOW(), IF(si.endTime = 0, NOW(), si.endTime))) AS elapsedSecs, bc.inbound + bc.outbound AS totalData, ((bc.inbound + bc.outbound) - (bs.inbound + bs.outbound)) / ( TIMESTAMPDIFF(SECOND, bs.time, bc.updateTime) / 60 ) AS aveData FROM sessionInformation AS si LEFT JOIN policyInformation AS rt ON si.realm_id =
rt.object_id LEFT JOIN policyInformation AS ct ON si.community... "

Resolution/Workaround:

This is caused by DB table became corruption. The fix forthis by running the following command in an SSH session to the appliance:

-- SSH to appliance
-- Using putty client on both the nodes
# mysql troubleshooting
mysql> repair table byteCountSnapshots;
mysql> quit

The following log message shows points to this byteCountSnapshots table is corrupted: -java.sql.SQLException: Table './troubleshooting/byteCountSnapshots' is marked as crashed and should be repaired, If you run this command and it still fails, check for other crashed tables and re-run the repair command for other tables.

SonicWall and Aventail engineering team investigated and added a fix in version-10.0.4.

Enginnering team added a step at appliance boot time that will run a repair on the db tables automatically. This will be in 10.0.4 and 10.5.1.

Troubleshooting:

Refer the /var/log/aventail/management logs for SQL error and database errors.

Tracking ID: 87219