Upgrade 0.92.2 -> 1.0.2 fails on SQLite: migration chain is not idempotent and breaks in 6 places

Bug Description

Title

Upgrade 0.92.2 -> 1.0.2 fails on SQLite: migration chain is not idempotent and breaks in 6 places

What happened

I upgraded a single-node deployment from v0.92.2 to v1.0.2. The meta store is SQLite (default, ZO_META_STORE is not set).

The service does not start. It panics in db init, then systemd restarts it in a loop.

openobserve[3223]: thread '<unnamed>' panicked at src/main.rs:175:17:
openobserve[3223]: db init failed: Execution Error: error returned from database: (code: 1) no such column: ""created_at""
openobserve[3223]: Error: backend job init failed, exiting

The standalone command openobserve upgrade-db fails with the same error.

Root cause

The migration runner reports DB_SCHEMA_VERSION mismatch : expected 77, found 64. It then applies migrations one by one. Several migrations assume a state that does not exist in a database that came from 0.92.2.

I ran the upgrade against a copy of the database and removed each blocker in turn. The chain stops at six different points.

Details per case:

1. In my database the table folders has these columns: id, org, folder_id, name, description, type. There is no created_at. The earlier migration m20241114_000001_create_folders_table creates the table without that column, and the next migration drops it unconditionally.

2. The row default already exists in organizations before the populate migration runs.

3. The meta table holds 8 rows with module = 'templates' and an empty value (length(value) = 0). Their keys are prebuilt_slack, prebuilt_msteams, prebuilt_pagerduty, prebuilt_discord, prebuilt_webhook, prebuilt_opsgenie, prebuilt_servicenow, prebuilt_email. The migration parses value as JSON and fails on the empty string.

4. The table system_settings and all of its indexes already exist.

5. The column org_id already exists in search_queue.

6. This one is created by the migration chain itself. The index service_streams_org_service_key_idx refers to a column service_key that does not exist in service_streams. This index is not present in the original 0.92.2 database. It appears only after the new migrations run. Any later DROP COLUMN on any table then fails, because SQLite validates all indexes.

Steps to reproduce

1. Run v0.92.2 with SQLite meta store until db_schema_version is 64.
2. Replace the binary with v1.0.2.
3. Start the service, or run openobserve upgrade-db.

What I expected

Migrations should be idempotent, or they should check the current state before they drop, create or insert. An upgrade from the previous release should not need manual SQL.

Workaround I used

I went back to v0.92.2. The database was not damaged by the failed attempts. Three migrations had been applied before the first failure, and they removed no data.

For anyone who wants to move forward manually, these steps let the chain continue, but they are not safe in general:

ALTER TABLE folders ADD COLUMN created_at bigint NOT NULL DEFAULT 0;
DELETE FROM organizations;
DELETE FROM meta WHERE module = 'templates' AND (value IS NULL OR length(value) = 0);
DROP TABLE IF EXISTS system_settings;

With those the runner reached 61 applied migrations out of the 77 expected schema version, then stopped at case 5 and 6.

Note

Related older reports with the same shape: #9829 (v0.20.3 -> v0.40.0 db init failed) and #11099 (DB_SCHEMA_VERSION mismatch). In #9829 the advice was to use MySQL. If SQLite is still a supported meta store, this upgrade path needs a fix.

Priority

P1 - High

Severity

Major

Related Story/Issue

No response

Is this a regression?

Yes

Steps to Reproduce (Optional)

No response

Environment Details

## Environment

- OpenObserve: from v0.92.2 to v1.0.2
- Meta store: SQLite (default), local disk, single node
- SQLite CLI used for inspection: 3.46.1
- OS: Debian GNU/Linux 13 (trixie), kernel 7.0.14-17
- Arch: x86_64, Intel Celeron J3160 (no AVX)
- Data volume: about 112 800 rows in `file_list`, 627 rows in `meta`, 301 streams

Which OpenObserve Functionalities Are Affected?

• Home & Login
• Logs / Metrics / Traces
• Dashboards
• Alerts
• Streams, Pipelines & Functions
• Ingestion
• Real User Monitoring (RUM)
• Reports
• IAM & Management
• UI / Styling
• Unknown / Other