Add tutor [dev|local|k8s] do sqlshell command

[kdmccormick]

Background

In devstack, one could get a MySQL database prompt by:

• entering make mysql-shell and then entering mysql at the container shell prompt.
• entering make dev.dbshell to jump directly in.

or a MongoDB prompt by:

• entering make mongo-shell and then entering mongo at the container shell prompt.

In Tutor, the equivalent command for MySQL is:

tutor dev exec mysql mysql --user="$(tutor config printvalue MYSQL_ROOT_USERNAME)" --password="$(tutor config printvalue MYSQL_ROOT_PASSWORD)"

and the equivalent command for Mongo is:

tutor dev exec mongodb mongo

We should make these commands simpler. Or, at the very least, we should document them.

Tasks

• Wait unil the pluggable jobs interface is implemented.
• Implement and document a new job (name suggestion: sqlshell) using the new CLI_DO_COMMANDS filter. The job should execute, in the mysql service:

  mysql --user='{{ MYSQL_ROOT_USERNAME }}' \
        --password='{{ MYSQL_ROOT_PASSWORD }}' \
        --host='{{ MYSQL_HOST }}' \
        --port='{{ MYSQL_PORT }}'
  

• Test that the new job works in all three modes: tutor [dev|local|k8s] do sqlshell.
• Document the job as a common local deployment task.

[kdmccormick]

@rgraber FYI

[kdmccormick]

@regisb This one's ready for your eyes now.

[regisb]

Let me think out loud here:

• run should be preferred over exec because there is no guarantee that the mysql container may be running. For instance, if the platform was not started.

• Actually, even run may be problematic in the case when the user decided to host mysql separately (RUN_MYSQL=false). In this situation, there isn't even a "mysql" container declared in docker-compose.yml, so tutor dev run mysql would fail.

• Thus, there is no solution that works in the general case...

• ... but I agree that it would be very convenient to have a simple "local/dev/k8s mysqlrootshell" command.

• Currently, the only image that comes with a mysql client is the "mysql" image. The "openedx" image does not ship with mysql-client. Thus ./manage.py lms dbshell fails.

• If we added the "mysql-client" package to the "openedx" image we could access a db shell as root by running:

  tutor local run lms mysql --user=$(tutor config printvalue MYSQL_ROOT_USERNAME) --password=$(tutor config printvalue MYSQL_ROOT_PASSWORD) --host=$(tutor config printvalue MYSQL_HOST) --port=$(tutor config printvalue MYSQL_PORT)
  

Thus I suggest the following solution:

1. Add the "mysql-client" to the "openedx" image.
2. Add "local/dev/local dbshell" commands that would alias to the command above.

[kdmccormick]

@regisb I like that solution. I updated the acceptance criteria, can you take a look and let me know if it still looks good?

[regisb]

I thought about this again, and I changed my mind about the recommended solution. The problem with a dedicated tutor local/dev/k8s dbshell command is that it's not convenient to add ad-hoc commands both to compose.py and k8s.py. Well, it's certainly possible, but every extra command requires quite a bit of work. Also, it's not really possible to use this approach in plugins.

An alternative that I would like to suggest is to add a dbshell script to the openedx Docker image. To do that, add a dbshell file to tutor/templates/build/openedx/bin/. The file will be copied at build-time and made executable. It should contain the mysql command described above. In order to make it work, we would still have to install the mysql-client in the openedx Docker image. That way, users would be able to open a root MySQL shell by running tutor local run lms dbshell or tutor k8s exec lms dbshell.

Alternatives that I've considered:

1. Creating a dedicated dbshell compose service, accessible with tutor local run dbshell: this will not work on Kubernetes.
2. Create a pluggable tasks interface: this would be the "right" solution but it requires quite a bit more work.
3. Running tutor local run ./manage.py lms shell: this works, but is actually a bit different than what we need, as it does not open a MySQL shell with root access.

EDIT: the year is 2023, we have pluggable tasks, and (2) is the right way to implement this.

[kdmccormick]

this would be the "right" solution but it requires quite a bit more work.

If you feel that pluggable tasks is the right way to solve it, then let's do that! We have at least two other reasons to build the task interface, anyway.

I'll consider this ticket as blocked by #75.

[kdmccormick]

@regisb I updated this issue now that we have do-jobs. Can you check that the tasks look right to you?

I'm also curious if you have a pref between do dbshell and do sqlshell. For what it's worth, I was going to make a follow-up ticket for do mongoshell as well.

[kdmccormick]

fyi @Carlos-Muniz , since you might be the one to pick this up.

[regisb]

Can you check that the tasks look right to you?

They do. I think that we should add a --db option to connect to a database by default. By default, the mysql client should not connect to a database, so the end user will have to run use openedx or something similar.

I prefer "sqlshell" because it's clearer than "dbshell".

[kdmccormick]

🎉