Backup failing due to database being in use

[rickgardner077]

I have a single user Friendica instance on a shared host. Every night a backup is started but fails due to the database being in use.

Aug 04 03:50:54 vega.uberspace.de uberspace-dump-sqldbs[31762]: mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `process` at row: 0

Aug 04 03:50:54 vega.uberspace.de uberspace-dump-sqldbs[31762]: failed to dump 'rick6_friendica' (0)`

Is there a way to lock the database prior to backing up? Thanks

[AlfredSK]

I enable the maintenance mode when the backup is running. Like this...

cd /path/to/friendica
sudo -u www-data php bin/console.php maintenance 1 "Database full backup is running. Maintenance mode enabled."
sleep 120
mysqldump --quick --single-transaction -h localhost -u dbuser dbname > /path/to/backup/friendica/friendicadb_`date +"%Y%m%d"`.sql
cd /path/to/friendica
sudo -u www-data php bin/console.php maintenance 0

[annando]

Some smaller tables with a lot of inserts and deletes (like the cache or session table) are regularly optimized. This seem to confuse your backup command.

[SpcCw]

I also use maintenance mode before backups.

If for some reason you don't want to (but try it - it really works nice) then you can just setup your backup script to retry on error until success, somehow like this:

for i in {1..40}
do
   echo "Attempt $i..."
   /usr/sbin/backup-manager
   if [ $? -eq 0 ]
      then
          echo "Succesful backup on $i attempt at $(date)"
          break
   fi
   sleep 120
done

I used to have this and it managed to do backups but sometimes took as much as 10-20 attempts.