Repairing Domoticz database...

Some error appearing in domoticz log concerning VACUUM:

2020-01-12 15:00:01.363 Error: SQL Query("VACUUM") : database disk image is malformed

First, stop domoticz:

$ sudo service domoticz.sh stop

Backup the database:

$ cp ./domoticz/domoticz.db ./domoticz/domoticz.db.bak

Method 1

$ sqlite3 ./domoticz/domoticz.db

sqlite> .output domo.sql 

sqlite> .dump

sqlite> .quit

Download domo.sql to your Windows PC,

Try to import domo.sql in Sqlite Browser ( https://sqlitebrowser.org/ ) ,

Note error messages if exists and cleanup manualy ( notepad++ ) domo.sql (duplicate inserts, etc...) until importation success.

When sql is successfully imported, save to "domoticz.db"

Upload to domoticz machine, 

$ rm ./domoticz/domoticz.db

$ cp ./domoticz.db ./domoticz/domoticz.db

$ sudo service domoticz.sh start

If no success then :

$ rm ./domoticz/domoticz.db

$ cp ./domoticz/domoticz.db.bak ./domoticz/domoticz.db

Method 2 (not worked for me)

Open database with sqlite3:

$ sqlite3 ./domoticz/domoticz.db

Check integrity:

sqlite> PRAGMA integrity_check;

*** in database main ***

On tree page 138 cell 50: Rowid 2287 out of order

row 252 missing from index ds_hduts_idx

wrong # of entries in index ds_hduts_idx

...

First reindex DeviceStatus:

sqlite> reindex ds_hduts_idx;

Check integrity again:

sqlite> PRAGMA integrity_check;

*** in database main ***

On tree page 138 cell 50: Rowid 2287 out of order

... Error: database disk image is malformed

Build a clean copy of database:

sqlite> .clone domoticz-clone.sqlite

DeviceStatus... done

LightingLog... done

SceneLog... done

Preferences... done

Rain... done

Rain_Calendar... done

Temperature... done

Temperature_Calendar... done

Timers... done

SetpointTimers... done

UV... done

UV_Calendar... done

Wind... done

Wind_Calendar... done

Meter... done

Meter_Calendar... done

MultiMeter... done

MultiMeter_Calendar... done

Notifications... done

Hardware... done

Users... done

LightSubDevices... done

Cameras... done

CamerasActiveDevices... done

DeviceToPlansMap... done

Plans... done

Scenes... done

SceneDevices... done

TimerPlans... done

SceneTimers... done

SharedDevices... done

EventMaster... done

EventRules... done

ZWaveNodes... done

WOLNodes... done

Percentage... done

Percentage_Calendar... done

Fan... done

Fan_Calendar... done

BackupLog... done

EnoceanSensors... done

FibaroLink... done

HttpLink... done

PushLink... done

GooglePubSubLink... done

UserVariables... done

Floorplans... done

CustomImages... done

MySensors... done

MySensorsVars... done

MySensorsChilds... done

ToonDevices... done

UserSessions... done

MobileDevices... done

sqlite_stat1... Error: object name reserved for internal use: sqlite_stat1

SQL: [CREATE TABLE sqlite_stat1(tbl,idx,stat)]

Error 1: no such table: sqlite_stat1 on [SELECT * FROM "sqlite_stat1"]

done

sqlite_autoindex_UserSessions_1... done

sqlite_autoindex_UserSessions_2... done

ds_hduts_idx... done

f_id_idx... done

f_id_date_idx... done

fc_id_idx... done

fc_id_date_idx... done

ll_id_idx... done

ll_id_date_idx... done

sl_id_idx... done

sl_id_date_idx... done

m_id_idx... done

m_id_date_idx... done

mc_id_idx... done

mc_id_date_idx... done

mm_id_idx... done

mm_id_date_idx... done

mmc_id_idx... done

mmc_id_date_idx... done

p_id_idx... done

p_id_date_idx... done

pc_id_idx... done

pc_id_date_idx... done

r_id_idx... done

r_id_date_idx... done

rc_id_idx... done

rc_id_date_idx... done

t_id_idx... done

t_id_date_idx... done

tc_id_idx... done

tc_id_date_idx... done

u_id_idx... done

u_id_date_idx... done

uv_id_idx... done

uv_id_date_idx... done

w_id_idx... done

w_id_date_idx... done

wc_id_idx... done

wc_id_date_idx... done

devicestatusupdate... done

deviceplantatusupdate... done

planordertrigger... done

scenesupdate... done

scenedevicesupdate... done

floorplanordertrigger... done

...

sqlite> PRAGMA user_version;

0  <----- TAKE NOTE OF THIS VALUE it may be different for you

sqlite> .exit

Open the database copy and correct:

$ sqlite3 domoticz-clone.sqlite

sqlite> PRAGMA integrity_check;

ok

sqlite> PRAGMA user_version = 0;  -- use the number you got from PRAGMA user_version; above

sqlite> PRAGMA journal_mode = truncate;

truncate

sqlite> PRAGMA page_size = 32768;

sqlite> VACUUM;

sqlite> PRAGMA journal_mode = wal;

wal

sqlite> .exit

Copy new cleaned database on domoticz database:

$ mv domoticz-clone.sqlite ./domoticz/domoticz.db

Finally start domoticz service:

$ sudo service domoticz.sh start

You can now check domoticz log with web ui...