programmatically execute mysql commands

[mohit2152sharma]

Pretty much the title, is it possible to execute mysql commands programmatically. I think pscale shell database branch should do that but when I try to run a command, it throws an error saying, shell command is only possible in interactive mode.

[mscoutermarsh]

Try setting env var PSCALE_ALLOW_NONINTERACTIVE_SHELL=1 and should allow you to run it.

[mohit2152sharma]

thanks a lot, this solves it. But now I get different error, saying that database and branch do not exist in the org, even though they do exist and I am able to run the same command normally.

[mscoutermarsh]

I believe you'll need to authenticate with a service token. Which you can create by going to your organization settings. And adding connect_branch or connect_production_branch permissions for the database.

Set the following env vars once you have the token.

PLANETSCALE_SERVICE_TOKEN_ID
PLANETSCALE_SERVICE_TOKEN

[mohit2152sharma]

Gave all the permissions and set up the enviroment variables as well, although I am passing them in the command itself using --service-token and --serivce-token-id.

Here's how I was doing:

1. I have a file with multiple commands
2. I read the file and split into individual commands
3. I create a command pscale shell db branch --service-token $serviceToken --service-token-id $serviceTokenId <<EOF\n{the multiline command}\nEOF
4. I then run a subprocess.run() command and pass the above command to it.

Anyway, now I am doing differently, using mysql-connector-python and executing command using this module and it's running successfully. I can close this comment, but would still like to know, I am doing anything wrong.

[mscoutermarsh]

I believe you tried this, but just to confirm. The command works when run directly? Not from python?

Are you getting the same error when the service token is passed?

[mohit2152sharma]

Yes, it works. see for example:

❯ pscale shell saral develop --service-token $PLANETSCALE_SERVICE_TOKEN --service-token-id $PLANETSCALE_SERVICE_TOKEN_ID <<EOF
show databases;
EOF
+--------------------+
| Database           |
+--------------------+
| saral              |
| information_schema |
| mysql              |
| sys                |
| performance_schema |
+--------------------+

[disco-infinex]

How is the PSCALE_ALLOW_NONINTERACTIVE_SHELL env var not mentioned anywhere in the docs, error message or blog post? The cli error message basically denies non-interactive use is even possible:

$ pscale shell db_name main < some_raw_commands.sql
Error: pscale shell only works in interactive mode