How to Disable innodb_file_per_table in MySQL After 9.3.00 Gateway Upgrade

Document ID : KB000071696
Last Modified Date : 26/02/2018
Show Technical Document Details
Issue:
  • 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.
Environment:
  • Gateway 9.3.00 including CR1.
Cause:
  • 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.
Resolution:
​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.
  1. Stop the SSG service on both nodes: service ssg stop
  2. Modify the /etc/my.cnf file on both nodes. Under the [mysqld] section, add the following on a new line: innodb_file_per_table=0
  3. Run the following commands in MySQL (mysql) to remove audit records from the database on the primary node prior to backing it up.
    use ssg;
    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;
  4. Back up the ssg 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
  5. 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
  6. Drop the ssg database on the primary node: mysqladmin drop ssg
  7. Restart the MySQL service on both nodes: service mysqld restart
  8. Create the ssg database on the primary node: mysqladmin create ssg
  9. Restore the content of the ssg database (from step 3 above) on the primary node: mysql ssg < /root/sql-pre-innodb-change-ssg.sql
  10. 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.
Additional Information:
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.