MySQL backup failed with "Error 1412: Table definition has changed..." (4258525)

MySQL backup failed with "Error 1412: Table definition has changed..."

The online backup of the Foglight MySQL database is failing with the following error:

"Error 1412: Table definition has changed, please retry transaction when dumping table `obs_metric_aggregate_0514` at row: 0 Like described at "http://mysql-nordic.blogspot.de/2013/05/mysqldump-now-safe-for-innodb.html"

The used command for the only backup is:

"/usr/bin/mysqldump -u foglight --password=xxxx -P 3306 -h localhost --single-transaction --routines --max_allowed_packet=128M foglight > foglight.bac"

The same time the backup job is running the Foglight maintenance job ran. Both jobs are impacting each other.

RESOLUTION:  Make sure that the backup is not running at the same time the Foglight nightly maintenance job runs. By default the maintenance job is scheduled to start each night at 02:00.  The maintenance job could run up to 6 hours depending on the size of the database.

It is recommended to schedule the backup before the nightly maintenance job starts for example at 21:00 or 22:00 o'clock.

WORKAROUND:    Start the FMS with the following switch to disable the use of short-term IOT tables.

   -Dserver.persistence.disable_short_term_iot=true

An entry like the following can be added to the server.config file so that the FMS starts with the switch:

   server.vm.optionX="-Dserver.persistence.disable_short_term_iot=true";

Disabling the short-term IOT tables will probably make the short term tables up to 40% larger. There will also be some more I/O to query those table, but that could vary based on the query that is being run. Note that the short-term tables are just those in the first generation that cover the last three days, so the bulk of the data would not be affected. Some queries might be a little slower which might increase the dashboard rendering times. It probably wouldn't affect dashboards unless a time range of 8 hours or more was being used.