Local db get nuked when syncing from remote if dev server restarted

Open
#2,193 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 11 days

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
38/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
rust, sqlite

Research direction

Reproduce the issue with the Rust client using Builder::new_remote_replica, local.db, remote.db, and db.sync(), then restart both turso dev servers as described. Inspect the remote-replica sync path and verify that rerunning cargo run restores the users table and rows to local.db instead of leaving it empty.

Written by the indexing model from the issue text.

Description

Steps to reproduce:

  1. Run the dev server

    $ turso dev -f remote.db
    sqld listening on port 8080.
    Use the following URL to configure your libSQL client SDK for local development:
    
        http://127.0.0.1:8080
    
    By default, no auth token is required when sqld is running locally. If you want to require authentication, use --auth-jwt-key-file to specify a file containing the JWT key.
    
    Using database file remote.db.
    
  2. Create a rust client

    cargo new client
    cd client
    cargo add libsql -F remote,replication,sync
    cargo add tokio -F full
    cat <<"EOF" >src/main.rs
    use libsql::{Builder, Result};
    
    const CREATE_USERS_TABLE: &str = "\
    CREATE TABLE IF NOT EXISTS users (\
    id INTEGER PRIMARY KEY, \
    name TEXT UNIQUE NOT NULL, \
    email TEXT UNIQUE NOT NULL\
    )";
    const SELECT_USER: &str = "SELECT * FROM users WHERE name = ?1 AND email = ?2";
    const INSERT_USER: &str = "INSERT INTO users (name, email) VALUES (?1, ?2)";
    const SELECT_USERS: &str = "SELECT * FROM users";
    
    #[tokio::main]
    async fn main() -> Result<()> {
        let local_path = "local.db";
        // let local_path = ":memory:";
        let remote_url = "http://127.0.0.1:8080";
        let remote_auth = "";
        print!("Builder (path={local_path:?}, url={remote_url:?}, auth_token={remote_auth:?})... ");
        let db = Builder::new_remote_replica(
            local_path.to_string(),
            remote_url.to_string(),
            remote_auth.to_string(),
        )
        .build()
        .await?;
        println!("Done");
    
        print!("Syncing... ");
        let f = db.sync().await?;
        println!("{f:?}");
    
        print!("Connecting... ");
        let conn = db.connect()?;
        println!("{conn:?}");
    
        print!("Syncing... ");
        let f = db.sync().await?;
        println!("{f:?}");
    
        print!("Create users table (if doesn't already exist)... ");
        conn.execute(CREATE_USERS_TABLE, ()).await?;
        println!("Done");
    
        println!("Insert users...");
        for (name, email) in &[
            ("Alice", "[email protected]"),
            ("Bob", "[email protected]"),
            ("Charlie", "[email protected]"),
        ] {
            let mut rows = conn.query(SELECT_USER, (*name, *email)).await?;
            if let Some(row) = rows.next().await? {
                println!("* User {name:?} {email:?} already exists! `row = {row:?}`");
            } else {
                println!("* Inserting user {name:?} {email:?}");
                conn.execute(INSERT_USER, [*name, *email]).await?;
    
                print!("\n  Syncing... ");
                let f = db.sync().await?;
                println!("{f:?}\n");
            }
        }
    
        println!("Select all users...");
        let mut rows = conn.query(SELECT_USERS, ()).await?;
        print!("\n```json\n{{\"users\": [");
        let mut count = 0;
        while let Some(row) = rows.next().await? {
            count += 1;
    
            let id = row.get_value(0)?;
            let name = row.get_value(1)?;
            let email = row.get_value(2)?;
    
            let id = id.as_integer().unwrap();
            let name = name.as_text().unwrap();
            let email = email.as_text().unwrap();
    
            if count > 1 {
                print!(",");
            }
    
            print!("\n    {{\"id\": {id:?}, \"name\": {name:?}, \"email\": {email:?}}}");
        }
        println!("\n]}}\n```\n");
    
        Ok(())
    }
    EOF
    
  3. Build and run it:

    $ cargo run
    ...
    Builder (path="local.db", url="http://127.0.0.1:8080", auth_token="")... Done
    Syncing... Replicated { frame_no: Some(0), frames_synced: 1 }
    Connecting... Connection
    Syncing... Replicated { frame_no: Some(0), frames_synced: 0 }
    Create users table (if doesn't already exist)... Done
    Insert users...
    * Inserting user "Alice" "[email protected]"
    
      Syncing... Replicated { frame_no: Some(7), frames_synced: 0 }
    
    * Inserting user "Bob" "[email protected]"
    
      Syncing... Replicated { frame_no: Some(10), frames_synced: 0 }
    
    * Inserting user "Charlie" "[email protected]"
    
      Syncing... Replicated { frame_no: Some(13), frames_synced: 0 }
    
    Select all users...
    
    ```json
    {"users": [
        {"id": 1, "name": "Alice", "email": "[email protected]"},
        {"id": 2, "name": "Bob", "email": "[email protected]"},
        {"id": 3, "name": "Charlie", "email": "[email protected]"}
    ]}
    ```
    
  4. Check that it wrote to the remote db:

    $ turso db shell http://127.0.0.1:8080 .dump
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, email TEXT UNIQUE NOT NULL);
    INSERT INTO users VALUES(1,'Alice','[email protected]');
    INSERT INTO users VALUES(2,'Bob','[email protected]');
    INSERT INTO users VALUES(3,'Charlie','[email protected]');
    COMMIT;
    
  5. Check that it wrote to the local db:

    $ turso dev -f local.db -p 8081
    sqld listening on port 8081.
    Use the following URL to configure your libSQL client SDK for local development:
    
        http://127.0.0.1:8081
    
    By default, no auth token is required when sqld is running locally. If you want to require authentication, use --auth-jwt-key-file to specify a file containing the JWT key.
    
    Using database file local.db.
    
    $ turso db shell http://127.0.0.1:8081 .dump
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, email TEXT UNIQUE NOT NULL);
    INSERT INTO users VALUES(1,'Alice','[email protected]');
    INSERT INTO users VALUES(2,'Bob','[email protected]');
    INSERT INTO users VALUES(3,'Charlie','[email protected]');
    COMMIT;
    
  6. Ctrl+C the dev servers started in steps 1 and 4.

  7. Up arrow to restart the dev server and run turso db -f remote.db again.

  8. Confirm data persisted in remote: turso db shell http://127.0.0.1:8080 .dump

    $ turso db shell http://127.0.0.1:8080 .dump
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, email TEXT UNIQUE NOT NULL);
    INSERT INTO users VALUES(1,'Alice','[email protected]');
    INSERT INTO users VALUES(2,'Bob','[email protected]');
    INSERT INTO users VALUES(3,'Charlie','[email protected]');
    COMMIT;
    
  9. Rerun the client: cargo run

    $ cargo run
    ...
    Builder (path="local.db", url="http://127.0.0.1:8080", auth_token="")... Done
    Syncing... Replicated { frame_no: Some(0), frames_synced: 0 }
    Connecting... Connection
    Syncing... Replicated { frame_no: Some(0), frames_synced: 0 }
    Create users table (if doesn't already exist)... Done
    Insert users...
    Error: SqliteFailure(1, "no such table: users")
    

    Fails and says the users table doesn't exist.

  10. Check state of the local db:

    $ turso dev -f local.db -p 8081
    sqld listening on port 8081.
    Use the following URL to configure your libSQL client SDK for local development:
    
        http://127.0.0.1:8081
    
    By default, no auth token is required when sqld is running locally. If you want to require authentication, use --auth-jwt-key-file to specify a file containing the JWT key.
    
    Using database file local.db.
    
    $ turso db shell http://127.0.0.1:8081 .dump
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    COMMIT;
    

DATA IS GONE!

It's in the remote... but how can the local db sync it???

rm local.db* and rerunning cargo run doesn't work...

Any ideas?

Dominant language
C
Stars
17.3k
Forks
562
Avg merge
10d 15h
Merged PRs (30d)
1

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from tursodatabase/libsql

All issues in tursodatabase/libsql

Similar issues

More C issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.