Push/Pull/Fetch while sql-server is running

[styledev]

It would be amazing if we can push/pull/fetch from the command line while the dolt sql-server is running.

[timsehn]

Work around for this is

dolt sql -q "call dolt_push()"
dolt sql -q "call dolt_pull()"
dolt sql -q "call dolt_fetch()"

[ricardoreiter]

@timsehn doesn't work in case of a hosted dolt instance as you can't pass the remoteapi creds

[macneale4]

@ricardoreiter - I've been running up against the authentication issues of these methods, and I believe there is an inherent security concern which needs to be addressed before we tackle these methods. The issue is pretty fundamental to how we address this, and I believe getting this working securely in hosted is a heavy lift.

The first step to implementing these functions will actually disable the use of these CLI commands for any invocation which is off host - which doesn't actually address your concern. The first iteration of these methods will enable the CLI to run seamlessly when there is a local server running - with the caveat that the DOLT_REMOTE_PASSWORD used will be from the environment of the local server. This isn't a security risk because the CLI and the sql-server are run with the same admin permissions.

[timsehn]

More explicitly.

Before doing this make sure you are dolt logined to DoltHub so you have write permissions to your database.

Here is the repro:

1. Created this on DoltHub: https://www.dolthub.com/repositories/timsehn/test_push_pull_running_server/data/main

2. Clone it locally and start a SQL server in one shell:

$ dolt clone timsehn/test_push_pull_running_server
cloning https://doltremoteapi.dolthub.com/timsehn/test_push_pull_running_server
$ cd test_push_pull_running_server 
$ dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"|S="/tmp/mysql.sock"

3. Try to dolt pull from the command line and sql interface:

$ dolt pull
database locked by another sql-server; either clone the database to run a second server, or delete the '/Users/timsehn/dolthub/dolt/test_push_pull_running_server/.dolt/sql-server.lock' if no other sql-servers are active
$ dolt sql -q "call dolt_pull()"
+--------------+-----------+
| fast_forward | conflicts |
+--------------+-----------+
| 1            | 0         |
+--------------+-----------+

Get lock file error using the CLI but using sql interface works. The CLI should work.

4. Try dolt push from the command line and sql interface:

$ dolt push                                       
Everything up-to-date

With no updates the first push succeeds from the command line because it doesn't write a new manifest.

5. Make an update and try and push it.

$ dolt sql -q "insert into tablename values (0, 'foo')"

$ dolt commit -am "Inserted values"
commit 1573cvkgml1d24k1t8c8ho8h2rrgrqpv (HEAD -> main) 
Author: timsehn <tim@dolthub.com>
Date:  Mon Sep 11 12:02:47 -0700 2023

        Inserted values

$ dolt push                                            
Uploaded 832 B of 832 B @ 6.73 kB/s.
error: push failed
cause: unknown push error; cannot update manifest: database is read only
$ dolt sql -q "call dolt_push()"                       
+---------+
| success |
+---------+
| 1       |
+---------+

$

Fails from the command line dolt push because it has a concurrency problem updating the manifest. This should work. At the very least it should fail with the same lock file error as dolt pull. Push from the SQL interface succeeds as intended.

[timsehn]

We should fix the DoltHub case first because it is easier, then make it work with Hosted Dolt or other remotes.

[stephkyou]

Resolved by the following PRs:

• Fetch: migrates fetch to use sql queries #6652
• Pull: migrate dolt pull to use sql queries #6774
• Push: migrate dolt push to use sql queries #6738