Serious problem: "sqlite3.DatabaseError: database disk image is malformed"

[Tomasz Lewicki]

I have the following problem. Today, at around 1:30 p.m. local time, Weewx stopped working - the data sent by rsync to the website are not refreshing. After restarting Weewx, I see:

pi@pihole-dom:~ $ sudo systemctl status weewx.service
× weewx.service - WeeWX
     Loaded: loaded (/lib/systemd/system/weewx.service; enabled; preset: enabled)
     Active: failed (Result: signal) since Thu 2026-01-01 16:14:29 CET; 32min ago
   Duration: 7min 15.568s
       Docs: https://weewx.com/docs
    Process: 6937 ExecStart=weewxd /etc/weewx/weewx.conf (code=killed, signal=KILL)
   Main PID: 6937 (code=killed, signal=KILL)
        CPU: 2min 29.856s
       
I checked the log, where I only save messages from the ERROR level. The log is about 150 MB in size, which in itself is not normal. Inside, there are lots of messages:

gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: Evaluation of template /etc/weewx/skins/neowx-material/year.html.tmpl failed with exception '<class>
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: **** Ignoring template /etc/weewx/skins/neowx-material/year.html.tmpl
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: **** Reason: database disk image is malformed
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****  Traceback (most recent call last):
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "/usr/share/weewx/weewx/cheetahgenerator.py", line 334, in generate
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****      unicode_string = compiled_template.respond()
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "_etc_weewx_skins_neowx_material_year_html_tmpl.py", line 1130, in respond
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "/usr/share/weewx/weewx/tags.py", line 508, in __getattr__
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****      vh = self._do_query()
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****           ^^^^^^^^^^^^^^^^
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "/usr/share/weewx/weewx/tags.py", line 494, in _do_query
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****      result = weewx.xtypes.get_aggregate(self.obs_type, self.timespan,
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "/usr/share/weewx/weewx/xtypes.py", line 136, in get_aggregate
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****      return xtype.get_aggregate(obs_type, timespan, aggregate_type, db_manager,
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "/usr/share/weewx/weewx/xtypes.py", line 372, in get_aggregate
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****      row = db_manager.getSql(select_stmt)
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "/usr/share/weewx/weewx/manager.py", line 597, in getSql
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****      _cursor.execute(sql, sqlargs)
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "/usr/share/weewx/weedb/sqlite.py", line 38, in guarded_fn
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****      return fn(*args, **kwargs)
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****             ^^^^^^^^^^^^^^^^^^^
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****    File "/usr/share/weewx/weedb/sqlite.py", line 233, in execute
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****      return sqlite3.Cursor.execute(self, *args, **kwargs)
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
gru 31 14:50:59 pihole-dom weewxd[968]: ERROR weewx.cheetahgenerator: ****  sqlite3.DatabaseError: database disk image is malformed

This one worried me:

sqlite3.DatabaseError: database disk image is malformed

I looked at the summary for the year and saw a single, very high peak on the pressure graph with an absurd value of approx. 2e+184 for August 15. All other parameters (temperature, etc.) look normal. The summary for the month also looks normal. I looked at the database, but neither on that day nor on any other day did the pressure values exceed the norm. So I decided to rebuild the summaries. Since Weewx and the database are on Raspberry Pi, I moved the weewx.sdb file to my laptop and, using the instructions from https://www.weewx.com/docs/5.1/utilities/weectl-database/, I first did the following:

stalker@e6540:~$ weectl database drop-daily
Using configuration file /etc/weewx/weewx.conf
Proceeding will delete all your daily summaries from database 'weewx.sdb'
Are you sure you want to proceed (y/n)? y
Daily summary tables dropped from database 'weewx.sdb' in 0.06 seconds

And then:

stalker@e6540:~$ weectl database rebuild-daily
Using configuration file /etc/weewx/weewx.conf
All daily summaries will be rebuilt.
Rebuild the daily summaries in the database 'weewx.sdb' (y/n)? y
Rebuilding daily summaries in database 'weewx.sdb' ...
Traceback (most recent call last):025-08-03 22:30:00 CEST (1754253000)
  File "/usr/share/weewx/weectl.py", line 75, in <module>
    main()
    ~~~~^^
  File "/usr/share/weewx/weectl.py", line 67, in main
    namespace.func(namespace)
    ~~~~~~~~~~~~~~^^^^^^^^^^^
  File "/usr/share/weewx/weectllib/__init__.py", line 90, in dispatch
    namespace.action_func(config_dict, namespace)
    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/weewx/weectllib/database_cmd.py", line 328, in rebuild_daily
    weectllib.database_actions.rebuild_daily(config_dict,
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
                                             date=namespace.date,
                                             ^^^^^^^^^^^^^^^^^^^^
    ...<3 lines>...
                                             dry_run=namespace.dry_run,
                                             ^^^^^^^^^^^^^^^^^^^^^^^^^^
                                             no_confirm=namespace.yes)
                                             ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/weewx/weectllib/database_actions.py", line 139, in rebuild_daily
    nrecs, ndays = dbm.backfill_day_summary(start_d=from_d,
                   ~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^
                                            stop_d=to_d,
                                            ^^^^^^^^^^^^
                                            trans_days=20)
                                            ^^^^^^^^^^^^^^
  File "/usr/share/weewx/weewx/manager.py", line 1331, in backfill_day_summary
    for rec in self.genBatchRecords(start_batch_ts, stop_batch_ts):
               ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/weewx/weewx/manager.py", line 536, in genBatchRecords
    for row in self.genBatchRows(startstamp, stopstamp):
               ~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/share/weewx/weewx/manager.py", line 518, in genBatchRows
    for row in gen:
               ^^^
sqlite3.DatabaseError: database disk image is malformed

Note that in the Traceback message (most recent call last):025-08-03 22:30:00 CEST (1754253000), the year is written as 025 instead of 2025, but the Julian date converts correctly to 2025-08-03 20:30:00 UT. However, moving along the graph in the year summary, August 3 looks normal, and the peak is August 15.

The question is simple: how to fix the database?

[michael.k...@gmx.at]

Restore the most recent working backup. 

[Tomasz Lewicki]

Thanks for the advice, but I don't know what to restore. Every night, I make a copy to a remote server. So downloading the database from the remote server won't help. Anyway, I don't know when the error occurred and why this particular day is “broken” in the year-end summary.

Does anyone have a more constructive suggestion?

[jterr...@gmail.com]

According to the weewx documentation, the sqlite databse ( a single file) is located in "SQLITE_ROOT" - see https://weewx.com/docs/5.2/usersguide/where/ 

In your case, the database file should probably be /var/lib/weewx/weewx.sdb

Stop weewx, replace the database file with the one of your backup , and restart weewx   - assuming that you have a backup with a valid database file..

[vince]

His suggestion was plenty constructive. If you don’t have a working backup that means you don’t have a good enough backup solution. There were a couple long threads here a while back about how to verify db and what a good backup solution looks like.

Verify your db looks like a db then verify it. 

# file weewx.sdb

weewx.sdb: SQLite 3.x database, last written using SQLite version 3026000

# echo "pragma integrity_check" | sqlite3 weewx.sdb

ok

If it fails you might need to dump and restore the archive table year by year until you determine where something wrong is located.

Why did it happen ? Usually bad power to the pi or a failing SD card…

[Tomasz Lewicki]

@vince, And this is precisely a constructive suggestion - a recommendation that I can use to check something. The result of this check is as follows:

stalker@e6540:~$ file weewx.sdb
weewx.sdb: SQLite 3.x database, last written using SQLite version 3040001, file counter 205338, database pages 13895, cookie 0x236, schema 4, UTF-8, version-valid-for 205338

stalker@e6540:~$ echo "pragma integrity_check" | sqlite3 weewx.sdb
*** in database main ***
Tree 2 page 10667 cell 2: Rowid 1754505600 out of order
Fragmentation of 266 bytes reported as 0 on page 10667
NULL value in archive.usUnits
NULL value in archive.interval
row 132843 missing from index sqlite_autoindex_archive_1

database disk image is malformed

Now at least I know where to look. The question is whether (and if so, how) it can be fixed without creating the entire database.

Of course, there is still the option of creating a database from scratch using CSV files from the station panel, but that's a last resort. I would prefer to just fix the existing database, if possible.

PS. I also suspect the SD card in the RPi.

[Tomasz Lewicki]

I've already figured it out. @vince's answer got me on the right track - thank you!

I had empty values (NULL) in line 132843. I tried to delete it, but the database test still showed that it was corrupted. I found this thread on Stack Overflow: https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database and in it the command sqlite3 weewx.sdb ".dump" | sqlite3 weewx-new.sdb

Unfortunately:

stalker@e6540:~$ sqlite3 weewx.sdb ".dump" | sqlite3 weewx-new.sdb
Runtime error near line 132846: NOT NULL constraint failed: archive.usUnits (19)

But in the same thread, there was also a link to another one: https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database/57872238#57872238 and the .recover function specified in it worked:

stalker@e6540:~$ sqlite3 weewx.sdb ".recover" | sqlite3 weewx-new.sdb
stalker@e6540:~$ echo "pragma integrity_check" | sqlite3 weewx-new.sdb
ok

I copied the weewx-new.sdb file to the Raspberry Pi, made a copy of the damaged database, renamed weewx-new.sdb to weewx.sdb, started Weewx, and everything started working :)

[michael.k...@gmx.at]

Tomasz, I was assuming you were aware of how you persist your data and know where the database file is located, because your post wasn't like a total WeWX noobs post would read like.

If your database file is corrupted, there may be many reasons, a very common to me personally was using RPi's. Regardless of the storage type, over the years I had problems with alle generations of RPis, and I've been using SD-cards, USB Flash drives, SSDs, ... Anyway, if you don't have a recent copy of your weewx.sdb, you might not be able to recover your data from the existing image. You could try to export the database als inserts and see if there is a sane output up until the crash - if it works at all.

sqlite weewx.sdb .dump > weewx.sdb.sql

[Tomasz Lewicki]

@michael: It seems that our replies have crossed paths. As I wrote, I make copies, and so far there have been no problems. Even if I had copies of the database from the entire previous month, day by day, the error "popped up" in August, and I would have nothing to restore the database from. Fortunately, my backup is the station panel, from which I can import data in CSV format.

However, I managed to repair the database with the command sqlite3 weewx.sdb ".recover" | sqlite3 weewx-new.sdb - unfortunately, the .dump method did not work.