Unexpected behaviour: granting privileges to the runtime user as opposed to the supplied MySQL user

[lchski]

Command ran: tribble --user MYSQL_USER --password MYSQL_USER_PASSWORD create_db (subbing MYSQL_USER and MYSQL_USER_PASSWORD for actual values).

Expected behaviour: database would be created.

Actual behaviour: SQLAlchemy error 1133, "Can't find any matching row in the user table") [SQL: 'GRANT ALL PRIVILEGES ON spending.* to lucas@localhost;'

---

It looks like the create_db command grants privileges for the created table to the runtime user as opposed to the user provided by the --user flag (relevant line of code). I’d expect it to use the value of the --user flag as opposed to the runtime user. (On my machine, I’m using the root MySQL user as opposed to one named with my user account, so I’d expect it to grant privileges for the spending table to the root user that I pass in, rather than my runtime username.)

If this is expected behaviour, to be granting permissions to the runtime username instead of the MySQL username, we should document that. Otherwise, we should modify the behaviour. I also don’t altogether understand the distinction between the user provided in --user and the one provided in --runtime-user; clarifying that could clear this up.

[JasonMWhite]

The intended behaviour is:

• --user defines the user that will be used to login to the database. Under normal conditions, this would be the user's local username, e.g. the output of whoami.
• When creating the database for the first time, the user --user jason doesn't exist in the database. Instead, the user root is usually the administrative user. The --runtime-user argument is meant to define & permit the user jason to query/modify the tables in the spending schema

Here's how I ran it:

tribble --user root --password my_password create_db
tribble init_db

This logged in as root, created the database spending (the default schema name), created the user jason (the result of whoami), and gave jason full permissions to the spending schema.
The second command logged in as jason (the default), and created the necessary tables in the spending schema.

I added some additional context to the command help last night, e.g. tribble create_db --help. Also added some documentation to tribble --help to point users to the fact that you can ask for help on subcommands like that.

Suggestions?

[lchski]

Interesting! When I run tribble --user root --password [password] create_db, it gives me this error: 1133, "Can't find any matching row in the user table") [SQL: 'GRANT ALL PRIVILEGES ON spending.* to lucas@localhost;'

Looks like the step where it creates the lucas user (or whatever the value of --runtime-user is) isn’t happening.

[JasonMWhite]

That's great feedback - likely I created the user manually at some point and forgot to script it! I'll look into this ^^

[xander-miller]

I created the xander manually MySQL before I started. It used the existing xander user to create the database. I didn't get the error Lucas is getting so I think your assessment of the bug is correct, Jason.

[lchski]

Looking at the script, I think that’s right. The only mention of the runtime_user is in the GRANT ALL PRIVILEGES line, nothing to create it.