Restore mysql fails

[mariovitale1979]

I have a local openedx installation (Palm edition) and using your plugin I am able to do a backup of Mysql/Mongodb and Caddy.
When I then try to restore it following your instructions, the mysql won't restore.

docker compose -f /home/ubuntu/.local/share/tutor/env/local/docker-compose.yml -f /home/ubuntu/.local/share/tutor/env/local/docker-compose.prod.yml --project-name tutor_local -f /home/ubuntu/.local/share/tutor/env/local/docker-compose.jobs.yml run --rm backup-job sh -e -c 'python restore_services.py --date=2023-09-13'
[+] Creating 4/0
 ✔ Container tutor_local-caddy-1        Running                                                                                                                                                                                                                        0.0s 
 ✔ Container tutor_local-mysql-1        Running                                                                                                                                                                                                                        0.0s 
 ✔ Container tutor_local-permissions-1  Created                                                                                                                                                                                                                        0.0s 
 ✔ Container tutor_local-mongodb-1      Running                                                                                                                                                                                                                        0.0s 
[+] Running 1/1
 ✔ Container tutor_local-permissions-1  Started                                                                                                                                                                                                                        0.4s 
2023-09-13 09:54:30,295 INFO Extracting archive /data/backup/backup.2023-09-13.tar.xz to /data
2023-09-13 09:54:32,250 INFO Complete. /data is 190286838 bytes.
2023-09-13 09:54:32,250 INFO Restoring MySQL databases on mysql:3306 from /data/mysql_dump.sql
ERROR 3552 (HY000) at line 25: Access to system schema 'mysql' is rejected.
Traceback (most recent call last):
  File "restore_services.py", line 248, in <module>
    main()
  File "/s3/venv/lib/python3.8/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/s3/venv/lib/python3.8/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/s3/venv/lib/python3.8/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/s3/venv/lib/python3.8/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "restore_services.py", line 240, in main
    restore_mysql()
  File "restore_services.py", line 50, in restore_mysql
    check_call(cmd,
  File "/usr/local/lib/python3.8/subprocess.py", line 364, in check_call
    raise CalledProcessError(retcode, cmd)
subprocess.CalledProcessError: Command 'mysql --host=mysql --port=3306 --user=root --password="xxxx"' returned non-zero exit status 1.
Error: Command failed with status 1: docker compose -f /home/ubuntu/.local/share/tutor/env/local/docker-compose.yml -f /home/ubuntu/.local/share/tutor/env/local/docker-compose.prod.yml --project-name tutor_local -f /home/ubuntu/.local/share/tutor/env/local/docker-compose.jobs.yml run --rm backup-job sh -e -c python restore_services.py --date=2023-09-13

When investigating further, problems seems related to the mysqldump itself that can't be restored. When trying to restore the mysqldump manually, I get this error.

ERROR 3552 (HY000) at line 25: Access to system schema 'mysql' is rejected.
--> this I could solve by following instructions found here but I wonder if that is really ok ?

But then I get next error:

ERROR 3105 (HY000) at line 218: The value specified for generated column 'default_value' in table 'engine_cost' is not allowed.

So there is an error when trying to recover generated columns but this is now getting out of my league and worrying that the mysqldump needs to be altered to be able to restore it again. I guess that can't be the intention of this plugin who should be plug&play right.
The instructions for using your plugin seems straightforward so no idea what I might be doing wrong.

[fghaas]

Hi! Thanks for reporting this. Here's a workaround for you: assuming that you haven't created any users or set privileges and passwords in your MySQL database manually (you shouldn't), you can add this bit to your Tutor configuration, then after tutor config save run a fresh backup and restore.

BACKUP_MYSQL_DATABASES:
  - openedx

(Add other databases except mysql to this list if you are running any plugins that create another database, such as tutor-contrib-webhook-receiver.)

This way, the mysql database is not included in the backup, and consequently, no attempt is made to overwrite it on restore.

We should probably make this the default, now that people are moving to Palm and with it, to MySQL 8. Any MySQL user accounts and privileges are managed by init jobs in Tutor anyway, so the mysql database shouldn't really contain anything that's necessary to restore.

Please let us know if this workaround gets you moving again. Thanks!

[mariovitale1979]

That worked like a charm !!!!
Thanks for the quick support !

[fghaas]

Good to know, but let's not close this just yet — like I said, what I recommended you do was a workaround, not a fix. :)

[mariovitale1979]

hmm, maybe I was too fast confirming.
I don't know if this is expected but after I restored the backup on a different openedx instance, I see that the courses are there but I lost some settings like the 'indigo' theme, the OAuth configuration I had configured.
Is this expected?

[fghaas]

@mrtmm What do you think, should we make [openedx] the default for BACKUP_MYSQL_DATABASES?

• We shouldn't need to back up the mysql database, for reasons discussed above.
• If people are running on AWS RDS/Aurora, we know that including the mysql already broke things for MySQL 5.7, so anyone using that would have needed to exclude mysql from BACKUP_MYSQL_DATABASES for a while (see docs: Small README updates #44).

So, it probably makes more sense to default to backing up just the openedx database.

The only situation in which this is problematic is if people are running with databases other than openedx that they do want backed up. If they rely on the current default behavior of mysqldump --all-databases, and forget to subsequently set BACKUP_MYSQL_DATABASES to a list that includes all the databases they want backed up, they'd lose some backup data that they are presumably interested in.

[fghaas]

@mariovitale1979 OAuth settings are in the openedx database, so they would be included in the backup. However, they are keyed to the site configuration so if you restore your database to a different Open edX instance than where you backed it up (with a different site name, presumably), then it would be expected that you "lose" them. (Which is reasonable, because your OAuth callbacks wouldn't work anyway.) In other words, that's a wholly different issue compared to what you initially reported — and it does sound like the behaviour you describe is by design.

[mariovitale1979]

ok, makes sense. Thanks for clarifying. So the plugin's purpose is mainly made for backup/restoring of 1 openedx instance. Not for transferring everything to a different instance.

[fghaas]

You can totally transfer everything to an exactly identically configured instance, as you would if you ran a production Open edX instance in one location and another, non-production one that's running in a different location, so that you can cut over from one instance to the other by, say, a DNS update (or by transferring the IP address behind your DNS name, using a dynamic routing protocol). You can even automate this via scheduled backup and restore; see the README for details.

But backing up one database and trying to drop it into a completely different site is something you can't expect to work, without additional modifications, in any Django application. That's not a limitation of Open edX specifically, nor of Tutor, and certainly not of this plugin.

[mrtmm]

@mrtmm What do you think, should we make [openedx] the default for BACKUP_MYSQL_DATABASES?

Or maybe, since we know not having it defined can be problematic in various cases, we can make it a required setting? So anyone who is not using it yet will get a message to set it? So there is no risk of someone silently losing some data from their backups.

[fghaas]

Hmmm at the risk of asking a very silly question, how does one do that, in a way that doesn't break Tutor's expectations? The way I read the cookiecutter, I think we can

• set a variable with a default (even if that default is None/null, the empty string, an empty list, etc.),
• set a variable with a "unique" value, such as a generated password,
• set an override for an existing Tutor configuration value.

But setting a variable with no default that the user must set (that is, a "required" setting) — is that a thing that a plugin is "allowed" (expected) to do?

[mrtmm]

Okay, I was thinking of the "unique" option (previously named "add") and when doing something like:

config = {
    "unique": {
        "MYSQL_DATABASES": "{{ MYSQL_DATABASES }}",
    },
    "defaults": {
         ...
    }

the setting requires itself basically, if you don't have BACKUP_MYSQL_DATABASES defined, when running tutor config save you should see an error like: "Error: Missing configuration value: 'MYSQL_DATABASES' is undefined"

But setting a variable with no default that the user must set (that is, a "required" setting) — is that a thing that a plugin is "allowed" (expected) to do?

Well that I am not sure about, it was my interpretation that the "unique" option was suitable for this but now that I read the cookiecutter example again I see that it suggests that a default value should be set.

So on second thought, I believe "unique" would still be the right place for this setting because I believe the "settings that don't have a reasonable default for all users" bit applies here; who's also running ecommerce or plugins with a database, we don't know. In addition in that case the setting will be added to config.yml so folks who haven't defined it yet should notice it. But what should be the default value either the empty list or openedx only I am still conflicted but I would trust your judgement on that.

[fghaas]

There is another option that we have here, which is to retain the default for BACKUP_MYSQL_DATABASES being [], but modify the plugin's behavior in that case.

Right, now, we do this:

tutor-contrib-backup/tutorbackup/templates/backup/build/backup/backup_services.py

Lines 53 to 54 in 1192464

	else:
	databases_statement = "--all-databases"

What we could also do is that rather than set --all-databases, we enumerate the databases first, then exclude mysql from that enumeration, and then back up all others. That way we'd catch notes, ecommerce, etc. if someone runs those.

If we did that, and

• someone has already set BACKUP_MYSQL_DATABASES, the plugin already does (and continues to) backup the databases the user wants.
• someone hasn't set BACKUP_MYSQL_DATABASES and manages their all their database users, passwords, and privileges with Tutor (as they should), then the backup includes all databases that matter.
• someone hasn't set BACKUP_MYSQL_DATABASES and they manually created or set users, privileges, or passwords, they would be in trouble. But they probably are anyway, because in a Tutor environment you really aren't supposed to do that.

[mrtmm]

What we could also do is that rather than set --all-databases, we enumerate the databases first, then exclude mysql from that enumeration, and then back up all others. That way we'd catch notes, ecommerce, etc. if someone runs those.

That sounds quite reasonable to me :)

[borgdrone7]

Hi! Thanks for reporting this. Here's a workaround for you: assuming that you haven't created any users or set privileges and passwords in your MySQL database manually (you shouldn't), you can add this bit to your Tutor configuration, then after tutor config save run a fresh backup and restore.

BACKUP_MYSQL_DATABASES:
  - openedx

(Add other databases except mysql to this list if you are running any plugins that create another database, such as tutor-contrib-webhook-receiver.)

This way, the mysql database is not included in the backup, and consequently, no attempt is made to overwrite it on restore.

We should probably make this the default, now that people are moving to Palm and with it, to MySQL 8. Any MySQL user accounts and privileges are managed by init jobs in Tutor anyway, so the mysql database shouldn't really contain anything that's necessary to restore.

Please let us know if this workaround gets you moving again. Thanks!

Where do we actually set this BACKUP_MYSQL_DATABASES conf? In config.yml that is located in the root of tutor folder?

[fghaas]

Yes, and in addition you have to add the backup plugin to the PLUGINS list.

[dagg]

Hello @fghaas , and everyone else, I had the same problem with the 'mysql' schema when trying to restore.

I saw the whole conversation here and there are some very interesting solutions.

I have one suggestion though which I have implemented in a fork:
Get a list of all database schemas except from 'mysql' (and 'sys', 'performance_schema' and 'information_schema', I think those aren't needed as well but I might be wrong), and use that list of databases instead of all.
So, instead of '--all-databases' use '--databases {databases_list}'...

My changes in the code are here (just one line):

https://github.com/dagg/tutor-contrib-backup/blob/ca66844bded505e90d0176ce509b7fd3222aaadf/tutorbackup/templates/backup/build/backup/backup_services.py#L24

And here:

https://github.com/dagg/tutor-contrib-backup/blob/ca66844bded505e90d0176ce509b7fd3222aaadf/tutorbackup/templates/backup/build/backup/backup_services.py#L56-L80

Maybe they can be proven useful.
I didn't create a PR for it but if you want me to, just let me know.

~ ~ ~

Something else I would like to propose is the possibility to create the backup files/folders (before compressing them into the .tar.xz file) inside the /data/backup folder which is mounted and visible outside the container, instead of the /data folder which only exists inside the container.

My problem is that our implementation has a disk space problem with our huge data (about 90G for MySQL and MongoDB).
I think if I mount the $(tutor config printroot)/env/backup/ folder to another drive (or a network drive), this would solve our disk space problem while backing up the databases... Since I'm quite novice with docker, I cannot be sure if that will work, and also I cannot manage to make my changes to the backup above to work yet, but I'd love to know your thoughts on these suggestions.

[fghaas]

@dagg Yes please, do send a PR for that! I have a couple of questions/suggestions on your approach, but those are best discussed in a PR thread. Thanks for looking into this!

[dagg]

Thank you @fghaas the PR has been created!