API Gateway: Error seen when importing ssg database during expedited upgrade: "ERROR 2013 (HY000) at line ###: Lost connection to MySQL server during query"

Products

• CA API Gateway

Issue/Introduction

In some cases - typically if the SQL file exported is too large - importing the SQL file to MySQL will raise the following error and fail to import:

ERROR 2013 (HY000) at line 915: Lost connection to MySQL server during query

The line number may change of course, but the rest of the error should be as above. Instructions being followed for the expedited upgrade where this issue may have been encountered is documented here.

Environment

This article applies to all supported versions of API Gateway, including Gateway 10.

Cause

There can be multiple causes, but the most common one is that the exported SQL file is too large and MySQL takes too long to process the import that it times out and loses the connection.

Resolution

The simplest solution is below, with an alternative solution below that.

Simple solution:

1. Truncate the audit tables before exporting the database, in which case you can continue to follow the rest of the documented process as-is.
   • This is basically just one extra step to be done before exporting the database, right at the beginning, and the rest of the documented steps can then be performed as written. This option has the least impact with the same gain and would be the recommended approach if it's possible in the environment.
   • The commands to run are as follows to truncate the audit tables: 
     • SET FOREIGN_KEY_CHECKS = 0;
       truncate table audit_admin;
       optimize table audit_admin;
       truncate table audit_detail;
       optimize table audit_detail;
       truncate table audit_detail_params;
       optimize table audit_detail_params;
       truncate table audit_main;
       optimize table audit_main;
       truncate table audit_message;
       optimize table audit_message;
       truncate table audit_system;
       optimize table audit_system;
       SET FOREIGN_KEY_CHECKS = 1;
       exit

If the above solution is not possible in the environment as you cannot afford to lose the audits from an existing Gateway for example, then the more complicated (and discouraged) approach is below if needed:

1. Export the SQL file from source without the audit tables. This will leave your existing audits in-place for the active node, but the SQL file it generates won't contain any of the audit tables, causing the file to be much smaller in size and a better candidate for importing.
   • The command to export the database while ignoring the audit tables is as follows: mysqldump ssg --routines --ignore-table ssg.audit_admin --ignore-table ssg.audit_detail --ignore-table ssg.audit_detail_params --ignore-table ssg.audit_main --ignore-table ssg.audit_message --ignore-table ssg.audit_system > /home/ssgconfig/<source_Gateway>.sql
   • The command above is to replace the export command written in the documentation here. Make sure you are still running the 'sed' command afterwards, per the documentation.
2. Create the SSG database on the target Gateway node if it's not already been setup. This includes running through the ssgconfig menu and creating the database from there where it will then create the schema needed for the import of the SQL file. If this step was already done, than it may be skipped.
3. Once the SSG database was created via ssgconfig menu, next step is to import the SQL output file from #1 above, and import into the target Gateway node using this command: mysql ssg < /home/ssgconfig/<source_Gateway>.sql

Additional Information

A reminder that audits should ideally not be enabled especially in production environments due to performance impacts and issues that'd be avoided if the audits were external to a syslog server somewhere else. If that was being done, then - indirectly - this issue should not ever be encountered.