How to Disable innodb_file_per_table in MySQL After 9.3.00 Gateway Upgrade
Document ID :
Last Modified Date :
Show Technical Document Details
CA API Management Gateway
CA API Management Gateway:Release:9.3
MySQL data is stored using a different method than expected.
MySQL standard operating procedures result in unexpected errors (e.g. when shrinking the ibdata file).
In environments with audits enabled, components normally in place to monitor and alert on disk usage concerns fail to recognize the disk utilization from the database, which can cause disks to fill up in certain circumstances.
Gateway 9.3.00 including CR1.
Gateway 9.3.00 ("Gateway") was delivered with an upgrade of MySQL from 5.5 to 5.7.
MySQL 5.7 had innodb_file_per_table enabled by default, which should have been disabled by default.
The following steps will need to be completed in order to get the 9.3.00 (CR1 or base) back to the expected configuration. Unless noted, all commands should be run from a root prompt. An assumption is made that this is a cluster of two nodes holding the database role and that replication is running.
Stop the SSG service on both nodes:
service ssg stop
Modify the /etc/my.cnf file on both nodes. Under the
section, add the following on a new line:
Run the following commands in MySQL (
) to remove audit records from the database on the primary node prior to backing it up.
SET FOREIGN_KEY_CHECKS = 0;
truncate table audit_admin;
truncate table audit_detail;
truncate table audit_detail_params;
truncate table audit_main;
truncate table audit_message;
truncate table audit_system;
SET FOREIGN_KEY_CHECKS = 1;
Back up the
database alone on the primary node by running the following command at a root prompt:
mysqldump --routines ssg > /root/sql-pre-innodb-change-ssg.sql
Back up all of the databases on the primary node as an extra precaution by running the following command at a root prompt:
mysqldump --routines --skip-lock-tables --all-databases > /root/sql-pre-innodb-change-all.sql
database on the primary node:
mysqladmin drop ssg
Restart the MySQL service on both nodes:
service mysqld restart
database on the primary node:
mysqladmin create ssg
Restore the content of the
database (from step 3 above) on the primary node:
mysql ssg < /root/sql-pre-innodb-change-ssg.sql
Start the SSG service on both nodes:
service ssg start
Depending on the environmental circumstances, the following may also need to be performed:
Replication may need to be restarted
before step 8 above in the cluster if replication is not currently working successfully. This way the database content restored to the primary node in step 9 above will also make its way to the secondary node automatically.
In a future release of the Gateway, the option above is expected to be disabled again with a value of "0". In the meantime, we recommend that customers manually disable this feature and follow the resolution on this article.
Was this information helpful?