sqlx::migrate throws disk I/O error 1802

[arjtala]

Bug Description

Executing the program I get the following disk I/O error, only at the point of calling sqlx::migrate!. The actual SQL statements in the files (000_init.sql, 000_a.sql, 000_b.sql) work in Sqlite3 on their own, and I had previously used the rusqlite crate to execute the same statements which yielded the expected behavior, creating the db.sqlite file.

Right now only db.sqlite-shm and db.sqlite-wal are created before I see:

Error: Execute(Database(SqliteError { code: 1802, message: "disk I/O error" }))

Minimal Reproduction

repo/
\_ Cargo.toml
\_ src/
    \_ ...
\_ migrates/
    \_ 000_init.sql
    \_ 000_a.sql
    \_ 000_b.sql

000_init.sql

CREATE TABLE IF NOT EXISTS workouts_records (
  wid integer,
  ds integer,
  ts text,
  lat float,
  lng float,
  elevation float,
  heartrate integer,
  temperature integer,
  UNIQUE (wid, ds, ts)
);

000_a.sql

CREATE TABLE IF NOT EXISTS workouts (
  wid integer PRIMARY KEY UNIQUE NOT NULL,
  activity text,
  ds integer,
  record_locations text
);

000_b.sql

CREATE VIRTUAL TABLE workouts_geo
USING geopoly ();

Snippet

const PRAGMAS: [&str; 3] = [
    "PRAGMA TEMP_STORE = MEMORY;",
    "PRAGMA MMAP_SIZE = 30000000000;",
    "PRAGMA PAGE_SIZE = 4096;",
];

const DB_NAME: &'static str = "workouts.sqlite";
const TABLE: &'static str = "workouts";
const INSERT_SQL: &'static str = formatcp!(
    "INSERT OR IGNORE INTO {} (
    activity,
    ds,
    ts,
    lat,
    lng,
    elevation,
    heartrate,
    temperature
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?);",
    TABLE
);
const GEO_SQL: &'static str = formatcp!(
    "INSERT INTO {}_geo (
    activity,
    ds,
    _shape
    )  VALUES(?, ?, ?);",
    TABLE
);

pub async fn get_sqlite_pool(concurrency: usize) -> Result<Pool<Sqlite>, Box<dyn std::error::Error>> {
    let database_url: String = format!("sqlite://{}", DB_NAME);
    let pool_timeout: Duration = Duration::from_secs(30);
    let pool_max_connections: u32 = if concurrency == 1 {
        2
    } else {
        concurrency as u32
    };
    info!("Setting up configuration with {} concurrent connections", concurrency);
    let connection_options = SqliteConnectOptions::from_str(&database_url)?
        .create_if_missing(true)
        .journal_mode(SqliteJournalMode::Wal)
        .synchronous(SqliteSynchronous::Normal)
        .busy_timeout(pool_timeout);

    info!("Creating connection pool");
    let sqlite_pool: Pool<Sqlite> = SqlitePoolOptions::new()
        .max_connections(pool_max_connections)
        .idle_timeout(pool_timeout)
        .connect_with(connection_options)
        .await?;

    info!("Executing {} PRAGMA statements", PRAGMAS.len());
    join_all(
        PRAGMAS
            .into_iter()
            .map(|p| sqlx::query(p).execute(&sqlite_pool)),
    )
    .await;
    info!("Connection created");
    Ok(sqlite_pool)
}

pub async fn create_table(sqlite_pool: &Pool<Sqlite>) -> Result<(), Box<dyn std::error::Error>> {
    info!("Removing existing db file: {}", DB_NAME);
    let _ = fs::remove_file(DB_NAME);
    info!("Executing table creation");
    sqlx::migrate!().run(sqlite_pool).await?;
    Ok(())
}

Info

• SQLx version: 0.7.1
• SQLx features enabled: [ "runtime-tokio-rustls", "sqlite", "uuid", "chrono", "migrate" ]
• Database server and version: SQLite 3.42.0
• Operating system: macOS 14.0 23A5328b x86_64
• rustc --version: 1.73.0-nightly (also same error on stable 1.71.1)

[abonander]

You're deleting the database file after it has already been created with this line:

    let _ = fs::remove_file(DB_NAME);

You need to do this before you create the Pool.