`SQLITE_CORRUPT: database disk image is malformed` Migration Error from 1.23.15 to 2.0.0-beta

[corasaniti]

⚠️ Please verify that this question has NOT been raised before.

• I checked and didn't find similar issue

🛡️ Security Policy

• I agree to have read this project Security Policy

📝 Describe your problem

Migration Error

📝 Error Message(s) or Log

2024-10-29T22:13:44Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-08 [81.83%][45/45]
2024-10-29T22:13:44Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-09 [81.85%][45/45]
2024-10-29T22:13:44Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-10 [81.87%][45/45]
2024-10-29T22:13:44Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-11 [81.89%][45/45]
2024-10-29T22:13:45Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-12 [81.91%][45/45]
2024-10-29T22:13:45Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-13 [81.93%][45/45]
2024-10-29T22:13:45Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-14 [81.95%][45/45]
2024-10-29T22:13:46Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-15 [81.97%][45/45]
2024-10-29T22:13:46Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-16 [81.99%][45/45]
2024-10-29T22:13:46Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-17 [82.01%][45/45]
2024-10-29T22:13:46Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-18 [82.03%][45/45]
2024-10-29T22:13:47Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-19 [82.05%][45/45]
2024-10-29T22:13:47Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-20 [82.07%][45/45]
2024-10-29T22:13:47Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-21 [82.09%][45/45]
2024-10-29T22:13:47Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-22 [82.11%][45/45]
2024-10-29T22:13:48Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-23 [82.13%][45/45]
2024-10-29T22:13:48Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-24 [82.15%][45/45]
2024-10-29T22:13:48Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-25 [82.17%][45/45]
2024-10-29T22:13:48Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-26 [82.19%][45/45]
2024-10-29T22:13:49Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-27 [82.21%][45/45]
2024-10-29T22:13:49Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-28 [82.23%][45/45]
2024-10-29T22:13:49Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-29 [82.25%][45/45]
2024-10-29T22:13:49Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-30 [82.27%][45/45]
2024-10-29T22:13:49Z [DB] INFO: Clearing non-important heartbeats
2024-10-29T22:13:49Z [DB] INFO: Deleting non-important heartbeats for monitor 18
2024-10-29T22:17:53Z [DB] INFO: Deleting non-important heartbeats for monitor 19
2024-10-29T22:21:38Z [DB] INFO: Deleting non-important heartbeats for monitor 20
2024-10-29T22:25:28Z [DB] INFO: Deleting non-important heartbeats for monitor 21
2024-10-29T22:29:14Z [DB] INFO: Deleting non-important heartbeats for monitor 22
2024-10-29T22:33:05Z [DB] INFO: Deleting non-important heartbeats for monitor 23
2024-10-29T22:33:12Z [DB] ERROR: Database migration failed
2024-10-29T22:33:12Z [SERVER] ERROR: Failed to prepare your database:
                DELETE FROM heartbeat
                WHERE monitor_id = 23
                AND important = 0
                AND time < DATETIME('now', -24 || ' hours')
                AND id NOT IN (
                    SELECT id
                    FROM heartbeat
                    WHERE monitor_id = 23
                    ORDER BY time DESC
                    LIMIT 100
                )
             - SQLITE_CORRUPT: database disk image is malformed
pi@raspi5-work:~/docker/uptime-kuma $

Welcome to Uptime Kuma
Your Node.js version: 20.18.0
2024-10-29T22:36:31Z [SERVER] INFO: Env: production
2024-10-29T22:36:32Z [SERVER] INFO: Uptime Kuma Version: 2.0.0-beta.0
2024-10-29T22:36:32Z [SERVER] INFO: Loading modules
2024-10-29T22:36:33Z [SERVER] INFO: Creating express and socket.io instance
2024-10-29T22:36:33Z [SERVER] INFO: Server Type: HTTP
2024-10-29T22:36:33Z [SERVER] INFO: Data Dir: ./data/
2024-10-29T22:36:33Z [DB] INFO: Database Type: sqlite
2024-10-29T22:36:33Z [SERVER] INFO: Connected to the database
2024-10-29T22:36:33Z [DB] WARN: Aggregate table migration is already in progress, or it was interrupted
2024-10-29T22:36:33Z [DB] ERROR: Database migration failed
2024-10-29T22:36:33Z [SERVER] ERROR: Failed to prepare your database: Aggregate table migration is already in progress

🐻 Uptime-Kuma Version

1.23.15 to 2 beta

💻 Operating System and Arch

RaspianOS Bookworm based

🌐 Browser

Chrome and Opera

🖥️ Deployment Environment

• Runtime: Docker version 27.3.1
• Database: sqllite
• Filesystem used to store the database on:
• number of monitors: 44

[louislam]

Try to start again using your backup, and make sure the file system is not a network drive.

[corasaniti]

@louislam
No the backup is a local folder. If I restore the backup and use the tag 1.23.15 everything works fine.
Thanks in advance

[corasaniti]

@louislam
Have you news? Are you builded another beta, so can I retry?
Thanks

[CommanderStorm]

What louis was trying to ask for is if there are any things that might cause your DB to become corrupted (using 2 simultaneous instances, network drives, ...)

This is somewhat unlikely to be a bug in v2, at least I would not know how we would corrupt the db in the code path you were executing at the time..

=> could you just retry again?

[corasaniti]

What louis was trying to ask for is if there are any things that might cause your DB to become corrupted (using 2 simultaneous instances, network drives, ...)

I have replied in prev post. No no network drive but only local folder, no multi istances

This is somewhat unlikely to be a bug in v2, at least I would not know how we would corrupt the db in the code path you were executing at the time..

=> could you just retry again?

I try again this evening

Thanks

[by-cx]

I encountered similar problem. Also using local folder. No network drives.

web-1  | 2024-11-01T09:27:16Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-28 [7.78%][2/28]
web-1  | 2024-11-01T09:27:18Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-29 [7.80%][2/28]
web-1  | 2024-11-01T09:27:19Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-30 [7.82%][2/28]
web-1  | 2024-11-01T09:27:21Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-31 [7.84%][2/28]
web-1  | 2024-11-01T09:27:23Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-11-01 [7.86%][2/28]
web-1  | 2024-11-01T09:27:24Z [DB] ERROR: Database migration failed
web-1  | 2024-11-01T09:27:24Z [SERVER] ERROR: Failed to prepare your database: 
web-1  |                     SELECT status, ping, time
web-1  |                     FROM heartbeat
web-1  |                     WHERE monitor_id = 3
web-1  |                     AND DATE(time) = '2023-10-18'
web-1  |                     ORDER BY time ASC
web-1  |                  - SQLITE_CORRUPT: database disk image is malformed
web-1  | Welcome to Uptime Kuma
web-1  | Your Node.js version: 20.18.0

[corasaniti]

@louislam
I tried again and got the same error.
Only local folder. No network drives.
So it's a bug. For now I will no longer test the beta version
Thank you

2024-11-01T09:47:25Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-23 [82.13%][45/45]
2024-11-01T09:47:25Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-24 [82.15%][45/45]
2024-11-01T09:47:25Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-25 [82.17%][45/45]
2024-11-01T09:47:26Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-26 [82.19%][45/45]
2024-11-01T09:47:26Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-27 [82.21%][45/45]
2024-11-01T09:47:26Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-28 [82.23%][45/45]
2024-11-01T09:47:26Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-29 [82.25%][45/45]
2024-11-01T09:47:27Z [DB] INFO: [DON'T STOP] Migrating monitor data 67 - 2024-09-30 [82.27%][45/45]
2024-11-01T09:47:27Z [DB] INFO: Clearing non-important heartbeats
2024-11-01T09:47:27Z [DB] INFO: Deleting non-important heartbeats for monitor 18
2024-11-01T09:51:16Z [DB] INFO: Deleting non-important heartbeats for monitor 19
2024-11-01T09:55:08Z [DB] INFO: Deleting non-important heartbeats for monitor 20
2024-11-01T09:58:59Z [DB] INFO: Deleting non-important heartbeats for monitor 21
2024-11-01T10:02:48Z [DB] INFO: Deleting non-important heartbeats for monitor 22
2024-11-01T10:06:37Z [DB] INFO: Deleting non-important heartbeats for monitor 23
2024-11-01T10:06:46Z [DB] ERROR: Database migration failed
2024-11-01T10:06:46Z [SERVER] ERROR: Failed to prepare your database: 
                DELETE FROM heartbeat
                WHERE monitor_id = 23
                AND important = 0
                AND time < DATETIME('now', -24 || ' hours')
                AND id NOT IN (
                    SELECT id
                    FROM heartbeat
                    WHERE monitor_id = 23
                    ORDER BY time DESC
                    LIMIT 100
                )
             - SQLITE_CORRUPT: database disk image is malformed
pi@raspi5-work:~ $ 

[by-cx]

I tried again:

web-1  | 2024-11-01T10:00:39Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-24 [7.70%][2/28]
web-1  | 2024-11-01T10:00:41Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-25 [7.72%][2/28]
web-1  | 2024-11-01T10:00:42Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-26 [7.74%][2/28]
web-1  | 2024-11-01T10:00:44Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-27 [7.76%][2/28]
web-1  | 2024-11-01T10:00:45Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-28 [7.78%][2/28]
web-1  | 2024-11-01T10:00:47Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-29 [7.80%][2/28]
web-1  | 2024-11-01T10:00:48Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-30 [7.82%][2/28]
web-1  | 2024-11-01T10:00:50Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-10-31 [7.84%][2/28]
web-1  | 2024-11-01T10:00:52Z [DB] INFO: [DON'T STOP] Migrating monitor data 2 - 2024-11-01 [7.86%][2/28]
web-1  | 2024-11-01T10:00:54Z [DB] ERROR: Database migration failed
web-1  | 2024-11-01T10:00:54Z [SERVER] ERROR: Failed to prepare your database: 
web-1  |                     SELECT status, ping, time
web-1  |                     FROM heartbeat
web-1  |                     WHERE monitor_id = 3
web-1  |                     AND DATE(time) = '2023-10-18'
web-1  |                     ORDER BY time ASC
web-1  |                  - SQLITE_CORRUPT: database disk image is malformed

And both runs crashed at the same point, when the migration tool reached today of "monitor data 2".

[corasaniti]

@by-cx
your interruption happens very early at 7,86% I am able to get to 82,27%
In my opinion they are two different problems

[corasaniti]

@louislam

Unable to Create MariaDB Embedded Instance
After more than an hour I destroyed the container. I cannot create the MariaDB Database

2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...

PS;
in version 2 beta, can I expose 3306 port of Maria DB Embedded, so I can reache it, from outside container?
Thanks

[louislam]

I believe that you guys' SQLite was corrupted.

You can try to repair the SQLite database first.
https://stackoverflow.com/questions/5274202/sqlite3-database-or-disk-is-full-the-database-disk-image-is-malformed

PS: $DB_NAME = kuma.db

Again, don't forget to backup.

[corasaniti]

@louislam
after repair SQLlite DB

2024-11-01T17:09:59Z [SERVER] INFO: Env: production
2024-11-01T17:10:00Z [SERVER] INFO: Uptime Kuma Version: 2.0.0-beta.0
2024-11-01T17:10:00Z [SERVER] INFO: Loading modules
2024-11-01T17:10:00Z [SERVER] INFO: Creating express and socket.io instance
2024-11-01T17:10:00Z [SERVER] INFO: Server Type: HTTP
2024-11-01T17:10:01Z [SERVER] INFO: Data Dir: ./data/
2024-11-01T17:10:01Z [DB] INFO: Database Type: sqlite
2024-11-01T17:10:01Z [SERVER] ERROR: Failed to prepare your database: PRAGMA journal_mode = WAL - SQLITE_CORRUPT: malformed database schema (0)
Welcome to Uptime Kuma
Your Node.js version: 20.18.0
2024-11-01T17:10:14Z [SERVER] INFO: Env: production
2024-11-01T17:10:15Z [SERVER] INFO: Uptime Kuma Version: 2.0.0-beta.0
2024-11-01T17:10:15Z [SERVER] INFO: Loading modules
2024-11-01T17:10:15Z [SERVER] INFO: Creating express and socket.io instance
2024-11-01T17:10:15Z [SERVER] INFO: Server Type: HTTP
2024-11-01T17:10:15Z [SERVER] INFO: Data Dir: ./data/
2024-11-01T17:10:15Z [DB] INFO: Database Type: sqlite
2024-11-01T17:10:16Z [SERVER] ERROR: Failed to prepare your database: PRAGMA journal_mode = WAL - SQLITE_CORRUPT: malformed database schema (0)
Welcome to Uptime Kuma
Your Node.js version: 20.18.0

What can you tell me about the startup error with MariaDB Embedded?
After more than an hour I destroyed the container. I cannot create the MariaDB Database

2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...
2024-11-01T12:36:15Z [MARIADB] INFO: Waiting for Embedded MariaDB to start...

In version 2 beta, can I expose 3306 port of Maria DB Embedded, so I can reache it, from outside container?

Thanks again

[by-cx]

@louislam It worked, thank you.

For future generations, I did this:

echo '.dump' | sqlite3 kuma.db | sqlite3 kuma.repaired.db
mv kuma.repaired.db kuma.db