Enabling innodb_file_per_table

[chrisduncansb]

I’m attempting to reduce the size of my CDR and CEL database. I have reviewed every post on this topic but I’m stuck. At some point recently (perhaps v16?) the database was converted to innodb from MyISAM (or so I assume since deleting rows from CDR and CEL tables no longer reduces the disk utilization of the databases).

@dicko pointed to this post on serverfault to address this - basically enable innodb_file_per_table which allows optimize databases mysql command to reclaim the disk space used by the deleted rows. I’ve been working on implementing this for the past few days and after about a dozen attempts I’m throwing in the towel and posting this here for some help and to guide anyone else out there who is stuck like I am.

Below are the steps I’ve taken. Everything appears to complete succesfully, but when I attempt to start fwconsole I get the error below. It appears the databases aren’t importing properly.

mysqldump --all-databases > all-db.sql
(backup all databases to all-db.sql)

mysql -e “DROP database asteriskcdrdb;”
mysql -e “DROP database asterisk;”
mysql -e “DROP database performance_schema;”
mysql -e “DROP database test;”
(drop all databases except mysql)

rm -f /var/lib/mysql/ibdata1 && rm -f /var/lib/mysql/ib_logfile0 && rm -f /var/lib/mysql/ib_logfile1
(delete database files)

Edit /etc/my.cnf to add innodb_file_per_table=ON to [mysqld] section

mysql < all-db.sql
(Import databases from previously created backup)

mysql_upgrade --force
(re-generate performance_schema)

reboot

After reboot, fwconsole fails to start with the following error:

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘asterisk.freepbx_settings’ doesn’t exist in file /var/www/html/admin/libraries/BMO/Config.class.php on line 218
Stack trace:

1. PDOException->() /var/www/html/admin/libraries/BMO/Config.class.php:218
2. PDOStatement->execute() /var/www/html/admin/libraries/BMO/Config.class.php:218
3. FreePBX\Config->__construct() /var/www/html/admin/libraries/BMO/Self_Helper.class.php:124
4. FreePBX\Self_Helper->autoLoad() /var/www/html/admin/libraries/BMO/Self_Helper.class.php:37
5. FreePBX\Self_Helper->__get() /var/www/html/admin/libraries/BMO/Freepbx_conf.class.php:20
6. FreePBX\Freepbx_conf->__construct() /var/www/html/admin/libraries/BMO/Self_Helper.class.php:124
7. FreePBX\Self_Helper->autoLoad() /var/www/html/admin/libraries/BMO/Self_Helper.class.php:53
8. FreePBX\Self_Helper->__call() /var/www/html/admin/bootstrap.php:164
9. require_once() /etc/freepbx.conf:11
10. include_once() /var/lib/asterisk/bin/fwconsole:12