Cannot export/backup DB

[Kinzi]

Description of problem

Running dokku mysql:export my-db > data.dump I get mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

If I export without piping into a file it works.

How reproducible

Steps to Reproduce

1. Create a DB with option --default-authentication-plugin=mysql_native_password
2. Try to export or aws-backup

Actual Results

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Expected Results

I'm getting a mysql dump

Environment Information

dokku report APP_NAME output

-----> uname: Linux staging 5.4.0-121-generic #137-Ubuntu SMP Wed Jun 15 13:33:07 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
-----> memory:
                     total        used        free      shared  buff/cache   available
       Mem:           3931        1250        1137          24        1543        2367
       Swap:          4095          20        4075
-----> docker version:
       Client: Docker Engine - Community
        Version:           20.10.17
        API version:       1.41
        Go version:        go1.17.11
        Git commit:        100c701
        Built:             Mon Jun  6 23:02:57 2022
        OS/Arch:           linux/amd64
        Context:           default
        Experimental:      true

       Server: Docker Engine - Community
        Engine:
         Version:          20.10.17
         API version:      1.41 (minimum version 1.12)
         Go version:       go1.17.11
         Git commit:       a89b842
         Built:            Mon Jun  6 23:01:03 2022
         OS/Arch:          linux/amd64
         Experimental:     false
        containerd:
         Version:          1.6.6
         GitCommit:        10c12954828e7c7c9b6e0ea9b0c02b01407d3ae1
        runc:
         Version:          1.1.2
         GitCommit:        v1.1.2-0-ga916309
        docker-init:
         Version:          0.19.0
         GitCommit:        de40ad0
-----> docker daemon info:
       Client:
        Context:    default
        Debug Mode: true
        Plugins:
         app: Docker App (Docker Inc., v0.9.1-beta3)
         buildx: Docker Buildx (Docker Inc., v0.8.2-docker)
         scan: Docker Scan (Docker Inc., v0.17.0)

       Server:
        Containers: 11
         Running: 11
         Paused: 0
         Stopped: 0
        Images: 77
        Server Version: 20.10.17
        Storage Driver: overlay2
         Backing Filesystem: extfs
         Supports d_type: true
         Native Overlay Diff: true
         userxattr: false
        Logging Driver: json-file
        Cgroup Driver: cgroupfs
        Cgroup Version: 1
        Plugins:
         Volume: local
         Network: bridge host ipvlan macvlan null overlay
         Log: awslogs fluentd gcplogs gelf journald json-file local logentries splunk syslog
        Swarm: inactive
        Runtimes: io.containerd.runtime.v1.linux runc io.containerd.runc.v2
        Default Runtime: runc
        Init Binary: docker-init
        containerd version: 10c12954828e7c7c9b6e0ea9b0c02b01407d3ae1
        runc version: v1.1.2-0-ga916309
        init version: de40ad0
        Security Options:
         apparmor
         seccomp
          Profile: default
        Kernel Version: 5.4.0-121-generic
        Operating System: Ubuntu 20.04.4 LTS
        OSType: linux
        Architecture: x86_64
        CPUs: 2
        Total Memory: 3.84GiB
        Name: staging
        ID: PEEB:CHZO:EMP7:XJR5:2ZB7:QM5F:TPDW:PHRP:MO73:X7YR:V5PN:LJDA
        Docker Root Dir: /var/lib/docker
        Debug Mode: false
        Registry: https://index.docker.io/v1/
        Labels:
        Experimental: false
        Insecure Registries:
         127.0.0.0/8
        Live Restore Enabled: false

-----> git version: git version 2.25.1
-----> sigil version: 0.9.0build+bc921b7
-----> herokuish version:
       herokuish: 0.5.36
       buildpacks:
         heroku-buildpack-multi     v1.2.0
         heroku-buildpack-ruby      v240
         heroku-buildpack-nodejs    v196
         heroku-buildpack-clojure   v87
         heroku-buildpack-python    v211
         heroku-buildpack-java      v70
         heroku-buildpack-gradle    v36
         heroku-buildpack-scala     v92
         heroku-buildpack-play      v26
         heroku-buildpack-php       v218
         heroku-buildpack-go        v162
         heroku-buildpack-nginx     v16
         buildpack-null             v3
-----> dokku version: dokku version 0.27.7
-----> plugn version: plugn: 0.12.0build+3a27594
-----> dokku plugins:
         00_dokku-standard    0.27.7 enabled    dokku core standard plugin
         20_events            0.27.7 enabled    dokku core events logging plugin
         app-json             0.27.7 enabled    dokku core app-json plugin
         apps                 0.27.7 enabled    dokku core apps plugin
         builder              0.27.7 enabled    dokku core builder plugin
         builder-dockerfile   0.27.7 enabled    dokku core builder-dockerfile plugin
         builder-herokuish    0.27.7 enabled    dokku core builder-herokuish plugin
         builder-null         0.27.7 enabled    dokku core builder-null plugin
         builder-pack         0.27.7 enabled    dokku core builder-pack plugin
         buildpacks           0.27.7 enabled    dokku core buildpacks plugin
         certs                0.27.7 enabled    dokku core certificate management plugin
         checks               0.27.7 enabled    dokku core checks plugin
         common               0.27.7 enabled    dokku core common plugin
         config               0.27.7 enabled    dokku core config plugin
         cron                 0.27.7 enabled    dokku core cron plugin
         docker-options       0.27.7 enabled    dokku core docker-options plugin
         domains              0.27.7 enabled    dokku core domains plugin
         enter                0.27.7 enabled    dokku core enter plugin
         git                  0.27.7 enabled    dokku core git plugin
         letsencrypt          0.16.3 enabled    Automated installation of let's encrypt TLS certificates
         logs                 0.27.7 enabled    dokku core logs plugin
         mysql                1.19.4 enabled    dokku mysql service plugin
         network              0.27.7 enabled    dokku core network plugin
         nginx-vhosts         0.27.7 enabled    dokku core nginx-vhosts plugin
         plugin               0.27.7 enabled    dokku core plugin plugin
         proxy                0.27.7 enabled    dokku core proxy plugin
         ps                   0.27.7 enabled    dokku core ps plugin
         registry             0.27.7 enabled    dokku core registry plugin
         repo                 0.27.7 enabled    dokku core repo plugin
         resource             0.27.7 enabled    dokku core resource plugin
         run                  0.27.7 enabled    dokku core run plugin
         scheduler            0.27.7 enabled    dokku core scheduler plugin
         scheduler-docker-local 0.27.7 enabled    dokku core scheduler-docker-local plugin
         scheduler-null       0.27.7 enabled    dokku core scheduler-null plugin
         shell                0.27.7 enabled    dokku core shell plugin
         ssh-keys             0.27.7 enabled    dokku core ssh-keys plugin
         storage              0.27.7 enabled    dokku core storage plugin
         trace                0.27.7 enabled    dokku core trace plugin
 !     App my-db does not exist

How (deb/make/rpm) and where (AWS, VirtualBox, physical, etc.) was Dokku installed?:

Additional information

DB Details:

=====> my-db mysql service information
       Config dir:          /var/lib/dokku/services/mysql/my-db/config
       Config options:      --default-authentication-plugin=mysql_native_password
       Data dir:            /var/lib/dokku/services/mysql/my-db/data
       Dsn:                 mysql://mysql:12345678910@dokku-mysql-my-db:3306/my-db
       Exposed ports:       3306->24971
       Id:                  1b189d693d0e7c0e17692efc0cfd381e82946596d0fb270a0cda531146cf6c4f
       Internal ip:         172.17.0.5
       Links:               cms-production
       Service root:        /var/lib/dokku/services/mysql/my-db
       Status:              running
       Version:             mysql:8.0.28
       ```

- App container inspect output (if applicable) via `dokku ps:inspect APP_NAME`
- The nginx configuration (if applicable) via `dokku nginx:show-config APP_NAME`
- Link to the exact repository being deployed (if possible/applicable):
- Output of failing Dokku commands after running `dokku trace:on`
  (BEWARE: `trace:on` will print environment variables for some commands, be sure you're not exposing any sensitive information when posting issues. You may replace these values with XXXXXX):

[josegonzalez]

What does that option you passed in do to authentication? If you don't try to redirect output, does it prompt for anything like a password?

[Kinzi]

Here is the explanation for the flag: https://dev.mysql.com/blog-archive/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/

And no, I get the full dump in the console right away.

[igormatkovic]

I can confirm the exact same issue is happening without any additional config options included.

Dokku 0.27.7 Ubuntu: Ubuntu 22.04 LTS

store mysql service information
       Config dir:          /var/lib/dokku/services/mysql/store/config
       Config options:
       Data dir:            /var/lib/dokku/services/mysql/store/data
       Dsn:                 mysql://mysql:XXXXXXX@dokku-mysql-store:3306/store
       Exposed ports:       3306->3333
       Id:                  bd6d27a103df958eXXXXXXXXa3ceb5b91da9c0dfc9a
       Internal ip:         172.17.0.5
       Links:               mainstore
       Service root:        /var/lib/dokku/services/mysql/store
       Status:              running
       Version:             mysql:8.0.29`

dokku mysql:export store > /var/www/backup/store.dump mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Adding the flag: --no-tablespaces to file /functions https://github.com/igormatkovic/dokku-mysql/blob/master/functions#L120 docker exec "$SERVICE_NAME" mysqldump --defaults-extra-file=/root/credentials.cnf --user=mysql --no-tablespaces --single-transaction --quick "$DATABASE_NAME"

Will solve the issue and allow the backup to proceed.

[Kinzi]

@josegonzalez any chance to get the flag @igormatkovic mentioned ?

[bitkidd]

Just faced that same issue. Here is a link to official docs/changelog

[Kinzi]

@josegonzalez can we add the flag? Happy to create a PR or something....

[Kinzi]

thanks @igormatkovic

[wdiechmann]

I can second the comment that adding --no-tablespaces in line 120 in /var/lib/dokku/plugins/available/mysql/function

https://github.com/igormatkovic/dokku-mysql/blob/master/functions#L120

will infact allow you to run dokku mysql:export [service] > [file]

and @josegonzalez - sorry that I am not able to offer a PR for this - but I bare am able to c/p what other geniuses discover 😢 - my bad!