Using dolt sql with a running dolt sql-server

[post-no-preference]

For the rates repository, I can do the following:

$ dolt sql -q "insert into us_treasury values ('1990-01-01', null, null, null, null, null, null, null, null, null, null, null, null);"

I used to be able to do the following (-r can be supplied or not; same error):

$ dolt sql-server -u user -r
$ dolt sql -q "insert into us_treasury values ('1990-01-01', null, null, null, null, null, null, null, null, null, null, null, null);"

This now returns:

error on line 1 for query insert into us_treasury values ('1990-01-01', null, null, null, null, null, null, null, null, null, null, null, null): not authorized
not authorized

dolt version is 0.40.19

[zachmu]

We recently made it illegal to modify a database with a running server via the CLI -- you have to stop the server for any CLI commands that might update state to proceed. It seems like this is a bad error message here. @max-hoffman can you take a look?

We could improve this experience by making dolt sql detect the presence of a running server and connect to it automatically, rather than refusing to process DML statements. Can you give us an idea what it is about your workflow that makes this pattern necessary?

[post-no-preference]

The workflow is, from a script:

$ dolt pull
$ dolt sql -q "insert ..."
$ dolt add table
$ dolt commit table -m "message"
$ dolt push

It's done this way rather than going through some mysql connector as the script still needs to pull, commit, push, etc. which seem better done via dolt commands?

I think dolt sql-client is fine to use, but I am not sure if I can just supply a query for it to execute?

[zachmu]

I don't think dolt sql-client works that way, no. You would need to connect to the server with something like the mysql command line.

All of those commands are not going work work if a server is running, using a recent dolt.

In the short term, we can give you some setting to let you make changes from the CLI anyway, with the understanding that this may be unsafe / may not be reflected in query results until restart. The other option is for you to rewrite your tooling to issue SQL statements, e.g. CALL DOLT_COMMIT(...)

In the longer term, we need to make these CLI commands automatically adapt to the presence of a running server and behave identically to running their equivalent SQL statements on a server.

[post-no-preference]

Is there CALL DOLT_PULL() CALL DOLT_PUSH()?

[post-no-preference]

Also, I take it this sort of model will apply to dolt table import too?

[max-hoffman]

Working on a fix that will give better "error read only" messages in general, and in this case a more specific source for the read only mode.

[zachmu]

Yes, most CLI commands are also available as stored procedures (sometimes with limitations)

https://docs.dolthub.com/sql-reference/version-control/dolt-sql-procedures

dolt table import is actually implemented internally as a LOAD DATA statement already.

[max-hoffman]

@post-no-preference this PR #3920 will update the error message for your query:

 ~/D/d/d/rates> dolt sql -q "insert into us_treasury values ('1990-01-01', null, null, null, null, null, null, null, null, null, null, null, null);"
error on line 1 for query insert into us_treasury values ('1990-01-01', null, null, null, null, null, null, null, null, null, null, null, null): database is locked to writes
database is locked to writes

More work will be needed to expose the specific write lock path to the engine, but hopefully this is more useful than the not authorized error before.

[post-no-preference]

Ok. I think I will refactor my code to make use of the stored procedures and do all of my dolt interaction through dolt sql-server. Thanks all.