mysqldump: Couldn't execute 'SHOW FIELDS FROM `host_summary`':

Question

mysqldump: Couldn't execute 'SHOW FIELDS FROM `host_summary`':

fernandovs4 opened this issue 9 months ago · 3 comments

Hello! Pleasure! Can i help me?
i have its docker-compose

services:
  db-mysql-5.7.21:

    image: mysql:5.7.21
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 'mypassword'
    ports:
      - '3306:3306'
    expose:
      - '3306'
    volumes:
      - /volume1/docker_compose/db_mysql_5.7.21_una/appdir/config:/etc/mysql/conf.d
      - /volume1/docker_compose/db_mysql_5.7.21_una/appdir/data:/var/lib/mysql
    networks:
      - myNetwork

  backup:
    image: databack/mysql-backup
    restart: always
    volumes:
      - /volume1/docker_compose/db_mysql_5.7.21_una/db_dumps2:/db
    environment:   # Usando a chave correta 'environment' para definir variáveis de ambiente
      - DB_DUMP_TARGET=/db
      - DB_USER=myuser
      - DB_PASS=mypass
      - DB_DUMP_FREQ=1
      - DB_SERVER=db-mysql-5.7.21
    depends_on:
      - db-mysql-5.7.21
    networks:
      - myNetwork

networks:
  myNetwork:
    driver: bridge

but i find this error in databack/mysql-backup
mysqldump: Couldn't execute 'SHOW FIELDS FROM host_summary': View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
cp: cannot stat '/tmp/backups/db_backup_2024-03-06T21:31:14Z.tgz': No such file or directory
rm: cannot remove '/tmp/backups/db_backup_2024-03-06T21:31:14Z.tgz': No such file or directory

Answer 1 · 2024-03-07T08:48:18.000Z

Looks like you are running the legacy bash-based version. Can you enable debug logging and post here? Just be sure to filter out any sensitive information.

  backup:
    image: databack/mysql-backup
    restart: always
    volumes:
      - /volume1/docker_compose/db_mysql_5.7.21_una/db_dumps2:/db
    environment:   # Usando a chave correta 'environment' para definir variáveis de ambiente
      - DB_DUMP_TARGET=/db
      - DB_USER=myuser
      - DB_PASS=mypass
      - DB_DUMP_FREQ=1
      - DB_SERVER=db-mysql-5.7.21
      - DB_DUMP_DEBUG=true    # <--- THIS IS THE CHANGE
    depends_on:
      - db-mysql-5.7.21
    networks:
      - myNetwork

Answer 2 · 2024-03-08T20:30:00.000Z

Apologies for the delay:
i run on debug mode:

 file_env DB_SERVER
+ local var=DB_SERVER
+ local fileVar=DB_SERVER_FILE
+ local def=
+ '[' db-mysql-5.7.21 ']'
+ '[' '' ']'
+ local val=
+ '[' db-mysql-5.7.21 ']'
+ val=db-mysql-5.7.21
+ export DB_SERVER=db-mysql-5.7.21
+ DB_SERVER=db-mysql-5.7.21
+ unset DB_SERVER_FILE
+ file_env DB_PORT
+ local var=DB_PORT
+ local fileVar=DB_PORT_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_PORT=
+ DB_PORT=
+ unset DB_PORT_FILE
+ file_env DB_USER
+ local var=DB_USER
+ local fileVar=DB_USER_FILE
+ local def=
+ '[' root ']'
+ '[' '' ']'
+ local val=
+ '[' root ']'
+ val=root
+ export DB_USER=root
+ DB_USER=root
+ unset DB_USER_FILE
+ file_env DB_PASS
+ local var=DB_PASS
+ local fileVar=DB_PASS_FILE
+ local def=
+ '[' mypassword']'
+ '[' '' ']'
+ local val=
+ '[' mypassword ']'
+ val=mypassword
+ export DB_PASS=mypassword
+ DB_PASS=mypassword
+ unset DB_PASS_FILE
+ file_env DB_NAMES
+ local var=DB_NAMES
+ local fileVar=DB_NAMES_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_NAMES=
+ DB_NAMES=
+ unset DB_NAMES_FILE
+ file_env DB_NAMES_EXCLUDE
+ local var=DB_NAMES_EXCLUDE
+ local fileVar=DB_NAMES_EXCLUDE_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_NAMES_EXCLUDE=
+ DB_NAMES_EXCLUDE=
+ unset DB_NAMES_EXCLUDE_FILE
+ file_env DB_DUMP_FREQ 1440
+ local var=DB_DUMP_FREQ
+ local fileVar=DB_DUMP_FREQ_FILE
+ local def=1440
+ '[' 1 ']'
+ '[' '' ']'
+ local val=1440
+ '[' 1 ']'
+ val=1
+ export DB_DUMP_FREQ=1
+ DB_DUMP_FREQ=1
+ unset DB_DUMP_FREQ_FILE
+ file_env DB_DUMP_BEGIN +0
+ local var=DB_DUMP_BEGIN
+ local fileVar=DB_DUMP_BEGIN_FILE
+ local def=+0
+ '[' '' ']'
+ local val=+0
+ '[' '' ']'
+ '[' '' ']'
+ export DB_DUMP_BEGIN=+0
+ DB_DUMP_BEGIN=+0
+ unset DB_DUMP_BEGIN_FILE
+ file_env DB_DUMP_DEBUG
+ local var=DB_DUMP_DEBUG
+ local fileVar=DB_DUMP_DEBUG_FILE
+ local def=
+ '[' true ']'
+ '[' '' ']'
+ local val=
+ '[' true ']'
+ val=true
+ export DB_DUMP_DEBUG=true
+ DB_DUMP_DEBUG=true
+ unset DB_DUMP_DEBUG_FILE
+ file_env DB_DUMP_TARGET /backup
+ local var=DB_DUMP_TARGET
+ local fileVar=DB_DUMP_TARGET_FILE
+ local def=/backup
+ '[' /db ']'
+ '[' '' ']'
+ local val=/backup
+ '[' /db ']'
+ val=/db
+ export DB_DUMP_TARGET=/db
+ DB_DUMP_TARGET=/db
+ unset DB_DUMP_TARGET_FILE
+ file_env DB_DUMP_BY_SCHEMA
+ local var=DB_DUMP_BY_SCHEMA
+ local fileVar=DB_DUMP_BY_SCHEMA_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_DUMP_BY_SCHEMA=
+ DB_DUMP_BY_SCHEMA=
+ unset DB_DUMP_BY_SCHEMA_FILE
+ file_env DB_DUMP_KEEP_PERMISSIONS true
+ local var=DB_DUMP_KEEP_PERMISSIONS
+ local fileVar=DB_DUMP_KEEP_PERMISSIONS_FILE
+ local def=true
+ '[' '' ']'
+ local val=true
+ '[' '' ']'
+ '[' '' ']'
+ export DB_DUMP_KEEP_PERMISSIONS=true
+ DB_DUMP_KEEP_PERMISSIONS=true
+ unset DB_DUMP_KEEP_PERMISSIONS_FILE
+ file_env DB_RESTORE_TARGET
+ local var=DB_RESTORE_TARGET
+ local fileVar=DB_RESTORE_TARGET_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export DB_RESTORE_TARGET=
+ DB_RESTORE_TARGET=
+ unset DB_RESTORE_TARGET_FILE
+ file_env AWS_ENDPOINT_URL
+ local var=AWS_ENDPOINT_URL
+ local fileVar=AWS_ENDPOINT_URL_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_ENDPOINT_URL=
+ AWS_ENDPOINT_URL=
+ unset AWS_ENDPOINT_URL_FILE
+ file_env AWS_ENDPOINT_OPT
+ local var=AWS_ENDPOINT_OPT
+ local fileVar=AWS_ENDPOINT_OPT_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_ENDPOINT_OPT=
+ AWS_ENDPOINT_OPT=
+ unset AWS_ENDPOINT_OPT_FILE
+ file_env AWS_CLI_OPTS
+ local var=AWS_CLI_OPTS
+ local fileVar=AWS_CLI_OPTS_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_CLI_OPTS=
+ AWS_CLI_OPTS=
+ unset AWS_CLI_OPTS_FILE
+ file_env AWS_CLI_S3_CP_OPTS
+ local var=AWS_CLI_S3_CP_OPTS
+ local fileVar=AWS_CLI_S3_CP_OPTS_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_CLI_S3_CP_OPTS=
+ AWS_CLI_S3_CP_OPTS=
+ unset AWS_CLI_S3_CP_OPTS_FILE
+ file_env AWS_ACCESS_KEY_ID
+ local var=AWS_ACCESS_KEY_ID
+ local fileVar=AWS_ACCESS_KEY_ID_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_ACCESS_KEY_ID=
+ AWS_ACCESS_KEY_ID=
+ unset AWS_ACCESS_KEY_ID_FILE
+ file_env AWS_SECRET_ACCESS_KEY
+ local var=AWS_SECRET_ACCESS_KEY
+ local fileVar=AWS_SECRET_ACCESS_KEY_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_SECRET_ACCESS_KEY=
+ AWS_SECRET_ACCESS_KEY=
+ unset AWS_SECRET_ACCESS_KEY_FILE
+ file_env AWS_DEFAULT_REGION
+ local var=AWS_DEFAULT_REGION
+ local fileVar=AWS_DEFAULT_REGION_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export AWS_DEFAULT_REGION=
+ AWS_DEFAULT_REGION=
+ unset AWS_DEFAULT_REGION_FILE
+ file_env SMB_USER
+ local var=SMB_USER
+ local fileVar=SMB_USER_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export SMB_USER=
+ SMB_USER=
+ unset SMB_USER_FILE
+ file_env SMB_PASS
+ local var=SMB_PASS
+ local fileVar=SMB_PASS_FILE
+ local def=
+ '[' '' ']'
+ local val=
+ '[' '' ']'
+ '[' '' ']'
+ export SMB_PASS=
+ SMB_PASS=
+ unset SMB_PASS_FILE
+ file_env TMP_PATH /tmp
+ local var=TMP_PATH
+ local fileVar=TMP_PATH_FILE
+ local def=/tmp
+ '[' '' ']'
+ local val=/tmp
+ '[' '' ']'
+ '[' '' ']'
+ export TMP_PATH=/tmp
+ TMP_PATH=/tmp
+ unset TMP_PATH_FILE
+ file_env COMPRESSION gzip
+ local var=COMPRESSION
+ local fileVar=COMPRESSION_FILE
+ local def=gzip
+ '[' '' ']'
+ local val=gzip
+ '[' '' ']'
+ '[' '' ']'
+ export COMPRESSION=gzip
+ COMPRESSION=gzip
+ unset COMPRESSION_FILE
+ [[ -n true ]]
+ set -x
+ MYSQLDUMP_OPTS=
+ '[' -n root ']'
+ DBUSER=-uroot
+ '[' -n mypassword ']'
+ DBPASS=-pmypassword
+ '[' -z db-mysql-5.7.21 ']'
+ '[' -z '' ']'
+ echo 'DB_PORT not provided, defaulting to 3306'
DB_PORT not provided, defaulting to 3306
+ DB_PORT=3306
+ COMPRESS=
+ UNCOMPRESS=
+ case $COMPRESSION in
+ COMPRESS=gzip
+ UNCOMPRESS=gunzip
+ EXTENSION=tgz
+ TMPDIR=/tmp/backups
+ TMPRESTORE=/tmp/restorefile
+ declare -A uri
+ [[ -n '' ]]
++ date
+ echo Starting at Fri Mar 8 20:20:17 UTC 2024
+ last_run=0
Starting at Fri Mar 8 20:20:17 UTC 2024
++ date +%s
+ current_time=1709929217
+ freq_time=60
+ '[' -n '' ']'
+ [[ +0 =~ ^\+(.*)$ ]]
+ waittime=0
+ target_time=1709929217
+ '[' -z '' ']'
+ sleep 0
++ date +%s
+ last_run=1709929217
+ exit_code=0
+ true
+ mkdir -p /tmp/backups
+ do_dump
++ date -u +%Y-%m-%dT%H:%M:%SZ
+ now=2024-03-08T20:20:17Z
+ '[' -n '' ']'
+ SOURCE=db_backup_2024-03-08T20:20:17Z.tgz
+ TARGET=db_backup_2024-03-08T20:20:17Z.tgz
+ '[' -d /scripts.d/pre-backup/ ']'
+ workdir=/tmp/backup.1
+ rm -rf /tmp/backup.1
+ mkdir -p /tmp/backup.1
+ NICE_CMD=
+ '[' '' = true ']'
+ '[' -n '' -a '' = true ']'
+ '[' '' = true ']'
+ [[ -n '' ]]
+ DB_LIST=-A
+ mysqldump -h db-mysql-5.7.21 -P 3306 -uroot -pmypassword -A
mysqldump: Couldn't execute 'SHOW FIELDS FROM `host_summary`': View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
+ '[' 2 -ne 0 ']'
+ return 1
+ '[' 1 -ne 0 ']'
+ exit_code=1
+ for target in ${DB_DUMP_TARGET}
+ backup_target /db
+ local target=/db
+ uri_parser /db
+ uri=()
+ full=/db
+ full=/db
+ full=/db
+ [[ / == \/ ]]
+ full=file://localhost/db
+ [[ file://l == \f\i\l\e\:\/\/\/ ]]
+ pattern='^(([a-z0-9]{2,5})://)?((([^:\/]+)(:([^@\/]*))?@)?([^:\/?]+)(:([0-9]+))?)(\/[^?]*)?(\?[^#]*)?(#.*)?$'
+ [[ file://localhost/db =~ ^(([a-z0-9]{2,5})://)?((([^:\/]+)(:([^@\/]*))?@)?([^:\/?]+)(:([0-9]+))?)(\/[^?]*)?(\?[^#]*)?(#.*)?$ ]]
+ full=file://localhost/db
+ uri[uri]=file://localhost/db
+ uri[schema]=file
+ uri[address]=localhost
+ uri[user]=
+ uri[password]=
+ uri[host]=localhost
+ uri[port]=
+ uri[path]=/db
+ uri[query]=
+ uri[fragment]=
+ [[ file == \s\m\b ]]
+ [[ -n '' ]]
+ return 0
+ case "${uri[schema]}" in
+ mkdir -p /db
+ cpOpts=-a
+ '[' -n true -a true = false ']'
+ cp -a /tmp/backups/db_backup_2024-03-08T20:20:17Z.tgz /db/db_backup_2024-03-08T20:20:17Z.tgz
cp: cannot stat '/tmp/backups/db_backup_2024-03-08T20:20:17Z.tgz': No such file or directory
+ '[' 1 -ne 0 ']'
+ return 1
+ '[' 1 -ne 0 ']'
+ exit_code=1
+ /bin/rm /tmp/backups/db_backup_2024-03-08T20:20:17Z.tgz
rm: cannot remove '/tmp/backups/db_backup_2024-03-08T20:20:17Z.tgz': No such file or directory
++ date +%s
+ current_time=1709929224
+ '[' -n '' ']'
+ '[' -n '' ']'
++ date +%s
+ current_time=1709929224
+ backup_time=7
+ freq_time_count=0
+ freq_time_count_to_add=1
+ extra_time=60
+ target_time=1709929277
+ waittime=53

Answer 3 · 2024-03-11T09:12:18.000Z

Been digging into it, I remain stumped. There are various reports of similar issues. I thought it might have to do with mysql-backup using a newer version of mysqldump than your database, since the usage of sys tables changed. But I could not recreate it.

What happens if you log into the database container directly and run mysqldump?