search

angelo-v / wordpress-backup

Simple Docker container that helps you backup and restore your WordPress blog.
80 stars 60 forks source link

Add --single-transaction --column-statistics=0 to avoid errors on mysqldump #28

Closed dybber closed 4 years ago

dybber commented 4 years ago

I've tried to setup wordpress-backup, but encountered two connection problems, which I've tried to address with this pull request.

First, I encountered an 1045: Access denied error, and the fix included in this pull request is to add a --single-transaction argument to mysqldump, as explained here: https://stackoverflow.com/questions/20059823/mysqldump-error-1045-access-denied-despite-correct-passwords-etc

Second, I encountered the error Unknown table 'COLUMN_STATISTICS' in information_schema (1109) during execution of mysqldump, which seems to be solvable by adding the argument --column-statistics=0 as explained here: https://serverfault.com/questions/912162/mysqldump-throws-unknown-table-column-statistics-in-information-schema-1109

angelo-v commented 4 years ago

Sounds reasonable, thanks for your contribution. Did you test a restore as well or do we need equivalent adjustments on that side?

dybber commented 4 years ago

I just tested, and the restore command doesn't seem to error for me.

I created a version here to test with: https://hub.docker.com/repository/docker/dybber/wordpress-backup/ (if anyone else gets these errors: don't use my image, I will probably not update it and perhaps delete it again)

angelo-v commented 4 years ago

Thanks for testing. I tried to reproduce the errors you described, but could not. Can you give me a hint in what context they occur? Specific mysql version / setup?

dybber commented 4 years ago

Hi, here's the docker-compose.yml file I'm using. As you can see, I'm currently using my version of wordpress-backup (dybber/wordpress-backup)

dybber commented 4 years ago

Regarding mysql version:

$ docker exec dataekspeditionerdk_db_1 mysql --version
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper
angelo-v commented 4 years ago

Cannot reproduce it with the official mysql image,also using version 5.7. What image are you using? Can you provide a docker-compose where I can reproduce the problem? (your comment mentions one, but does not link anything)

dybber commented 4 years ago

That's weird. I'm on a Red Hat Enterprise Linux 7, don't know if they pull images from a different source.

When I do docker image ls, this is the version of the image I've got:

REPOSITORY                                         TAG                 IMAGE ID            CREATED             SIZE
docker.io/mysql                                    5.7                 9cfcce23593a        3 weeks ago         448 MB

Here's my docker-compose.yml, sorry that I forgot the link previously: https://github.com/DatalogiForAlle/Dataekspeditioner.dk/blob/master/dataekspeditioner.dk/docker-compose.yml (requires a .env file to specify database name, user, and passwords)

angelo-v commented 4 years ago

weird I have the exact same image id. Will check further with your dockerfile, thanks. I think your PR is fine in general, but please allow me a few more days to fully understand the problem before merging. Since you have a workarround for now I hope this is fine for you.

dybber commented 4 years ago

Hi Angelo.

No rush, I’m on vacation the coming weeks, but will try to play around and test it again when I get back, and see if I can still replicate the behavior with your version of the image.

One thing I’d like to test is if it could be a permission issue on the backups folder or something like that, not related to the image.

Best regards, Martin

On Sat, 4 Jul 2020 at 10.08, Angelo Veltens notifications@github.com wrote:

weird I have the exact same image id. Will check further with your dockerfile, thanks. I think your PR is fine in general, but please allow me a few more days to fully understand the problem before merging. Since you have a workarround for now I hope this is fine for you.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/angelo-v/wordpress-backup/pull/28#issuecomment-653736805, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAANPJB2Q5GIKJYEIJHDEHDRZ3PO3ANCNFSM4OKTWHAA .

-- Martin Dybdal

angelo-v commented 4 years ago

I tried with your dockerfile (but my image) and still are not able to reproduce.

Can you check the image ID of aveltens/wordpress-backup that produces the described errors. I am using: 9be1671f915b, which is quite recent. Also check the version of the included mysqldump:

> docker exec -it <wordpress-backup-container> mysqldump --version
mysqldump  Ver 8.0.20-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
angelo-v commented 4 years ago

Ok, now I got it! I actually ran the backup against a fresh / empty installation. After creating a page / post and trying the backup then I got

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'foobar' AND TABLE_NAME = 'wp_commentmeta';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
angelo-v commented 4 years ago

Not sure about the cause of the other error, but since --single-transaction seems to be a good option anyway I will merge your PR now.