Unable to backup DB

[johndwells]

I don't know if this is a duplicate of craftcms/nitro#139 but since the error I'm seeing is totally different, I'll just throw this one out as anew...

I'm unable to run a DB backup, fails both from the CLI and web. From the CLI the error reads:

Backing up the database ... error: The shell command "mysqldump --defaults-extra-file="/home/ubuntu/sites/tqm/turquoise-mountain-craft3/storage/runtime/temp/my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --default-character-set=utf8 --set-charset --triggers --single-transaction --no-data --result-file="/home/ubuntu/sites/tqm/turquoise-mountain-craft3/storage/backups/turquoise-mountain_200610_211441_bflmrdergr_v3.4.22.1.sql" turquoisemountain && mysqldump --defaults-extra-file="/home/ubuntu/sites/tqm/turquoise-mountain-craft3/storage/runtime/temp/my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --default-character-set=utf8 --set-charset --triggers --no-create-info --ignore-table=turquoisemountain.assetindexdata --ignore-table=turquoisemountain.assettransformindex --ignore-table=turquoisemountain.sessions --ignore-table=turquoisemountain.templatecaches --ignore-table=turquoisemountain.templatecachequeries --ignore-table=turquoisemountain.templatecacheelements --ignore-table=turquoisemountain.cache --ignore-table=turquoisemountain.templatecachecriteria turquoisemountain >> "/home/ubuntu/sites/tqm/turquoise-mountain-craft3/storage/backups/turquoise-mountain_200610_211441_bflmrdergr_v3.4.22.1.sql"" failed with exit code 2: mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'turquoisemountain' AND TABLE_NAME = 'assetindexdata';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

I'm leaving the project & db names intact, in case it's relevant - I am mounting my folder of all projects, and have databases created for each site I want to run...

I've never had reason to look into the information_schema table before, so I have no idea what I could be looking at, but I can confirm that there is no table named COLUMN_STATISTICS.

Let me know what other info I can provide. This is all running nitro 1.0.0-beta.10.

[jasonmccallister]

@johndwells this is something that is caused by the Ubuntu 20.04 update. The mysqldump tool available on 20.04 is setup for MySQL 8.0 and this is an issue that occurs specifically when backing up a MySQL 5.x database using the 8.0 tool.

For now, can you try nitro db backup and see if that works?

We also released an update that creates some files for configuring the cnf for mysql. Can you also run nitro ssh and ls -la /home/ubuntu/.nitro/databases/mysql/conf.d/ and see if the output has the 5 and 8` directories?

Also, while you are in the vm, can you run docker inspect mysql_5.7_3306 but replacing the version and port on your install (nitro context and tell you which ports and version if you don't remember). If you place that output here that would be helpful as well. Thanks!

[johndwells]

@jasonmccallister Gotcha.

nitro db backup works.

Running ls -la /home/ubuntu/.nitro/databases/mysql/conf.d/ does show 5 and 8 directories.

Results of docker inspect are:

[
    {
        "Id": "88cdf6fb0881eb11e75444835037f9f0bc93d4bb6d01d633e5794c2475d4eb35",
        "Created": "2020-06-06T10:43:54.89433432Z",
        "Path": "docker-entrypoint.sh",
        "Args": [
            "mysqld"
        ],
        "State": {
            "Status": "running",
            "Running": true,
            "Paused": false,
            "Restarting": false,
            "OOMKilled": false,
            "Dead": false,
            "Pid": 30532,
            "ExitCode": 0,
            "Error": "",
            "StartedAt": "2020-06-06T10:43:58.043060542Z",
            "FinishedAt": "0001-01-01T00:00:00Z"
        },
        "Image": "sha256:a4fdfd462add8e63749aa08ff0044b13d342a042965f1ec6744586cda10dfce9",
        "ResolvConfPath": "/var/lib/docker/containers/88cdf6fb0881eb11e75444835037f9f0bc93d4bb6d01d633e5794c2475d4eb35/resolv.conf",
        "HostnamePath": "/var/lib/docker/containers/88cdf6fb0881eb11e75444835037f9f0bc93d4bb6d01d633e5794c2475d4eb35/hostname",
        "HostsPath": "/var/lib/docker/containers/88cdf6fb0881eb11e75444835037f9f0bc93d4bb6d01d633e5794c2475d4eb35/hosts",
        "LogPath": "/var/lib/docker/containers/88cdf6fb0881eb11e75444835037f9f0bc93d4bb6d01d633e5794c2475d4eb35/88cdf6fb0881eb11e75444835037f9f0bc93d4bb6d01d633e5794c2475d4eb35-json.log",
        "Name": "/mysql_5.7_3306",
        "RestartCount": 0,
        "Driver": "overlay2",
        "Platform": "linux",
        "MountLabel": "",
        "ProcessLabel": "",
        "AppArmorProfile": "docker-default",
        "ExecIDs": null,
        "HostConfig": {
            "Binds": [
                "/home/ubuntu/.nitro/databases/mysql/setup.sql:/docker-entrypoint-initdb.d/setup.sql",
                "/home/ubuntu/.nitro/databases/mysql/conf.d/5/:/etc/mysql/conf.d",
                "mysql_5.7_3306:/var/lib/mysql"
            ],
            "ContainerIDFile": "",
            "LogConfig": {
                "Type": "json-file",
                "Config": {}
            },
            "NetworkMode": "default",
            "PortBindings": {
                "3306/tcp": [
                    {
                        "HostIp": "",
                        "HostPort": "3306"
                    }
                ]
            },
            "RestartPolicy": {
                "Name": "always",
                "MaximumRetryCount": 0
            },
            "AutoRemove": false,
            "VolumeDriver": "",
            "VolumesFrom": null,
            "CapAdd": null,
            "CapDrop": null,
            "Capabilities": null,
            "Dns": [],
            "DnsOptions": [],
            "DnsSearch": [],
            "ExtraHosts": null,
            "GroupAdd": null,
            "IpcMode": "private",
            "Cgroup": "",
            "Links": null,
            "OomScoreAdj": 0,
            "PidMode": "",
            "Privileged": false,
            "PublishAllPorts": false,
            "ReadonlyRootfs": false,
            "SecurityOpt": null,
            "UTSMode": "",
            "UsernsMode": "",
            "ShmSize": 67108864,
            "Runtime": "runc",
            "ConsoleSize": [
                0,
                0
            ],
            "Isolation": "",
            "CpuShares": 0,
            "Memory": 0,
            "NanoCpus": 0,
            "CgroupParent": "",
            "BlkioWeight": 0,
            "BlkioWeightDevice": [],
            "BlkioDeviceReadBps": null,
            "BlkioDeviceWriteBps": null,
            "BlkioDeviceReadIOps": null,
            "BlkioDeviceWriteIOps": null,
            "CpuPeriod": 0,
            "CpuQuota": 0,
            "CpuRealtimePeriod": 0,
            "CpuRealtimeRuntime": 0,
            "CpusetCpus": "",
            "CpusetMems": "",
            "Devices": [],
            "DeviceCgroupRules": null,
            "DeviceRequests": null,
            "KernelMemory": 0,
            "KernelMemoryTCP": 0,
            "MemoryReservation": 0,
            "MemorySwap": 0,
            "MemorySwappiness": null,
            "OomKillDisable": false,
            "PidsLimit": null,
            "Ulimits": null,
            "CpuCount": 0,
            "CpuPercent": 0,
            "IOMaximumIOps": 0,
            "IOMaximumBandwidth": 0,
            "MaskedPaths": [
                "/proc/asound",
                "/proc/acpi",
                "/proc/kcore",
                "/proc/keys",
                "/proc/latency_stats",
                "/proc/timer_list",
                "/proc/timer_stats",
                "/proc/sched_debug",
                "/proc/scsi",
                "/sys/firmware"
            ],
            "ReadonlyPaths": [
                "/proc/bus",
                "/proc/fs",
                "/proc/irq",
                "/proc/sys",
                "/proc/sysrq-trigger"
            ]
        },
        "GraphDriver": {
            "Data": {
                "LowerDir": "/var/lib/docker/overlay2/c4c02461637f10b61d09246f4a150702957e7cd215ade7db43849372b4625936-init/diff:/var/lib/docker/overlay2/c3d238dc5f7e4e7dfcf18eb21c18325a0ed2585da4bd6a5be31672003edebe10/diff:/var/lib/docker/overlay2/69d7d44ef51dacaef54eab22c47f0a372c0f8947bc5f4761e5503ca369345676/diff:/var/lib/docker/overlay2/fbb2c0c2a7016b3ad11442db4bddc93dd8909fba368f7de0aa9fbfd2d2092529/diff:/var/lib/docker/overlay2/586110d7a62b92921a37ef6957d94e8b8024cae5c2f73f40ada498842f829107/diff:/var/lib/docker/overlay2/f190ac246f8534416e81cbc42519f5d7a02e47444f4c9ca3edf768edec1e4db7/diff:/var/lib/docker/overlay2/8ca47e89baec8e65c7ee5be624fe6779300adac342d6547d050f3ccd1d1f3a7b/diff:/var/lib/docker/overlay2/4da17b5b9e7753b43c59aac60fef5fcedabad87cf76c34bae918aebbd99f3a28/diff:/var/lib/docker/overlay2/6a92a969ae88e1c1cd91ba1d40bfb921b7e48c6daaed4bd3f7b7f048f4cc6264/diff:/var/lib/docker/overlay2/f17d639a2c2cda307a536bf7b3b3c26c31b9991ed5fd752cec413b4a919472e2/diff:/var/lib/docker/overlay2/1c20863b03f3a2f3104bdf3a59d46abb515b0d981b52a61e1ed9922d5d6ec1d3/diff:/var/lib/docker/overlay2/a94fd615c57fbd14749d7357005786009fd9aa432c411bd288925f20f8573de8/diff",
                "MergedDir": "/var/lib/docker/overlay2/c4c02461637f10b61d09246f4a150702957e7cd215ade7db43849372b4625936/merged",
                "UpperDir": "/var/lib/docker/overlay2/c4c02461637f10b61d09246f4a150702957e7cd215ade7db43849372b4625936/diff",
                "WorkDir": "/var/lib/docker/overlay2/c4c02461637f10b61d09246f4a150702957e7cd215ade7db43849372b4625936/work"
            },
            "Name": "overlay2"
        },
        "Mounts": [
            {
                "Type": "bind",
                "Source": "/home/ubuntu/.nitro/databases/mysql/setup.sql",
                "Destination": "/docker-entrypoint-initdb.d/setup.sql",
                "Mode": "",
                "RW": true,
                "Propagation": "rprivate"
            },
            {
                "Type": "bind",
                "Source": "/home/ubuntu/.nitro/databases/mysql/conf.d/5",
                "Destination": "/etc/mysql/conf.d",
                "Mode": "",
                "RW": true,
                "Propagation": "rprivate"
            },
            {
                "Type": "volume",
                "Name": "mysql_5.7_3306",
                "Source": "/var/lib/docker/volumes/mysql_5.7_3306/_data",
                "Destination": "/var/lib/mysql",
                "Driver": "local",
                "Mode": "z",
                "RW": true,
                "Propagation": ""
            }
        ],
        "Config": {
            "Hostname": "88cdf6fb0881",
            "Domainname": "",
            "User": "",
            "AttachStdin": false,
            "AttachStdout": false,
            "AttachStderr": false,
            "ExposedPorts": {
                "3306/tcp": {},
                "33060/tcp": {}
            },
            "Tty": false,
            "OpenStdin": false,
            "StdinOnce": false,
            "Env": [
                "MYSQL_ROOT_PASSWORD=nitro",
                "MYSQL_DATABASE=nitro",
                "MYSQL_USER=nitro",
                "MYSQL_PASSWORD=nitro",
                "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
                "GOSU_VERSION=1.12",
                "MYSQL_MAJOR=5.7",
                "MYSQL_VERSION=5.7.30-1debian10"
            ],
            "Cmd": [
                "mysqld"
            ],
            "Image": "mysql:5.7",
            "Volumes": {
                "/var/lib/mysql": {}
            },
            "WorkingDir": "",
            "Entrypoint": [
                "docker-entrypoint.sh"
            ],
            "OnBuild": null,
            "Labels": {}
        },
        "NetworkSettings": {
            "Bridge": "",
            "SandboxID": "2e9990abeca1578298afa32d71027f38500c50f427961248ddc914918746a93a",
            "HairpinMode": false,
            "LinkLocalIPv6Address": "",
            "LinkLocalIPv6PrefixLen": 0,
            "Ports": {
                "3306/tcp": [
                    {
                        "HostIp": "0.0.0.0",
                        "HostPort": "3306"
                    }
                ],
                "33060/tcp": null
            },
            "SandboxKey": "/var/run/docker/netns/2e9990abeca1",
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "EndpointID": "579e676b4d20843359b3188a59eea2021ebe6781799c00704c3c5161c8e5f2dd",
            "Gateway": "172.17.0.1",
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "172.17.0.2",
            "IPPrefixLen": 16,
            "IPv6Gateway": "",
            "MacAddress": "02:42:ac:11:00:02",
            "Networks": {
                "bridge": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": null,
                    "NetworkID": "c281660c60868ac9615e2373a289a7e1a00b15bbff70bccfe4175b1720a27b04",
                    "EndpointID": "579e676b4d20843359b3188a59eea2021ebe6781799c00704c3c5161c8e5f2dd",
                    "Gateway": "172.17.0.1",
                    "IPAddress": "172.17.0.2",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:11:00:02",
                    "DriverOpts": null
                }
            }
        }
    }
]

[angrybrad]

Was able to work around this in Craft itself and fix will be included in the next release.

To get the fix early, change your craftcms/cms requirement in composer.json to:

"require": {
  "craftcms/cms": "dev-develop#eec596dcea3beaeedf4da10128aaee9b2b325d04",
  "...": "..."
}

Then run composer update.

[brandonkelly]

Craft 3.4.28 is out now with this fix.

[RyanRoberts]

I believe I'm getting the same problem as this with Craft 3.5.2 and Nitro beta 10. Here's the error log:

[-][1][5bfc5nf5mt7q99rhrt1mcrqob6][error][yii\base\Exception] yii\base\Exception: Could not create backup: The shell command "mysqldump --defaults-extra-file="/home/ubuntu/sites/client/storage/runtime/temp/my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --default-character-set=utf8 --set-charset --triggers --no-tablespaces --single-transaction --no-data --result-file="/home/ubuntu/sites/client/storage/backups/client-rentals--2020-08-10-110337--v3.5.2.sql" client && mysqldump --defaults-extra-file="/home/ubuntu/sites/client/storage/runtime/temp/my.cnf" --add-drop-table --comments --create-options --dump-date --no-autocommit --routines --default-character-set=utf8 --set-charset --triggers --no-tablespaces --no-create-info --ignore-table=client.assetindexdata --ignore-table=client.assettransformindex --ignore-table=client.sessions --ignore-table=client.templatecaches --ignore-table=client.templatecachequeries --ignore-table=client.templatecacheelements --ignore-table=client.cache --ignore-table=client.templatecachecriteria client >> "/home/ubuntu/sites/client/storage/backups/client-rentals--2020-08-10-110337--v3.5.2.sql"" failed with exit code 127: sh: 1: mysqldump: not found in /home/ubuntu/sites/client/vendor/craftcms/cms/src/controllers/UtilitiesController.php:377
Stack trace:
#0 [internal function]: craft\controllers\UtilitiesController->actionDbBackupPerformAction()
#1 /home/ubuntu/sites/client/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#2 /home/ubuntu/sites/client/vendor/yiisoft/yii2/base/Controller.php(180): yii\base\InlineAction->runWithParams()
#3 /home/ubuntu/sites/client/vendor/craftcms/cms/src/web/Controller.php(181): yii\base\Controller->runAction()
#4 /home/ubuntu/sites/client/vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction()
#5 /home/ubuntu/sites/client/vendor/craftcms/cms/src/web/Application.php(294): yii\base\Module->runAction()
#6 /home/ubuntu/sites/client/vendor/craftcms/cms/src/web/Application.php(597): craft\web\Application->runAction()
#7 /home/ubuntu/sites/client/vendor/craftcms/cms/src/web/Application.php(273): craft\web\Application->_processActionRequest()
#8 /home/ubuntu/sites/client/vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest()
#9 /home/ubuntu/sites/client/public/index.php(22): yii\base\Application->run()
#10 {main}

Can't backup from the Craft Admin or CLI, but as above nitro db backup works.

[angrybrad]

@RyanRoberts Can you create this as a new issue on the Nitro repo?