mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ ....

I am running a simple pre-backup script to dump all my DBs to files:


#!/bin/bash

FINALDIR=/var/spool/bacula/mysqlbackup
TEMPDIR=$FINALDIR-tmp
LOG=/var/log/bacula/mysqldump.log
USER=bacula
PASS=****

for D in $FINALDIR $TEMPDIR; do
  if  ! -d "$D" ]; then
    mkdir "$D"
    chown bacula:bacula "$D"
    chmod 775 "$D"
  fi
done

rm "$TEMPDIR"/* >/dev/null 2>&1


# Dump each DB in separate file
for I in $(mysql -u$USER -p$PASS -e 'show databases' -s --skip-column-names); do mysqldump -u$USER -p$PASS --skip-dump-date --single-transaction --lock-tables $I > "$TEMPDIR"/$I.sql; done 2>"$LOG"

rsync -lIc --remove-source-files "$TEMPDIR"/* "$FINALDIR"

rm "$TEMPDIR"/* >/dev/null 2>&1

Everything works and the DBs are dumped. However in the log file I get:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `PROFILING`': The 'SHOW PROFILE' feature is disabled; you need MariaDB built with 'enable-profiling' to have it working (1289)

and because of this error I cannot use this script as a RunBeforeJob in Bacula. It fails the backup job because it returns a bad exit code.

Is there a way to fix this without having to build MariaDB from source? All I need is a proper backup of the DBs.

Not sure how many databases you wish to backup but an alternative script would be a list of separate databases created with a series of mysqldump commands - unless, of course, that means you get the same error with each dump.

I want to backup all databases automatically.

Anyway I found a workaround. I simply added ‘exit 0’ to the end of the script and now Bacula proceeds without problem.