Sqlite database malformed

[oct]

Hello,

One of our Zabbix proxy stopped working yesterday. After reviewing the logs, I foun the following error on zabbix-proxy.log:

829:20120601:175928.219 [Z3005] query failed: [0] database disk image is malformed [delete from proxy_history where id<60145428 and (clock<1338544703 or (id<=60145348 and clock<1338377218))]

How can I recreate the database? It's a Zabbix proxy 1.8.10 with Sqlite3.

Thanks

Oct

[richlv]

assuming proxy has write permissions to the database file directory, just delete the corrupted one and restart the proxy

[oct]

Easy, and worked!

Thanks, richlv

[luisfernando]

assuming proxy has write permissions to the database file directory, just delete the corrupted one and restart the proxy

Just for the record, I've been through this issue and unfortunately richlv's approach didn't work for me.

Zabbix kept saying there was no such table xxx:

query failed: [0] no such table: ids
query failed: [0] no such table: proxy_dhistory
query failed: [0] no such table: hosts

I had to create a dump of the corrupted sqllite database, import it to a new one and restart zabbix_proxy.

# /etc/init.d/zabbix-proxy stop
# echo .dump | sqlite3 /var/lib/zabbix/zabbix.db > zabbix.sql
# rm -f /var/lib/zabbix/zabbix.db
# sqlite3 -init zabbix.sql /var/lib/zabbix/zabbix.db
# /etc/init.d/zabbix-proxy start

[richlv]

Just for the record, I've been through this issue and unfortunately richlv's approach didn't work for me.

you probably created an empty file, which you should have not done

[luisfernando]

you probably created an empty file, which you should have not done

You mean the .sql file? No, it wasn't empty.

Actually it had all the sql statements to create the database and its tables. As said, it was a dump of the corrupted sqllite database.

Zabbix proxy is working just fine now.

By the way, the proxy here is in passive mode.

[richlv]

You mean the .sql file? No, it wasn't empty.

Actually it had all the sql statements to create the database and its tables. As said, it was a dump of the corrupted sqllite database.

uh, that's absolutely wrong
sqlite db is not a collection of sql statements.
you should either have no such file, or create a proper sqlite database and populate it with the schema.

[luisfernando]

uh, that's absolutely wrong
sqlite db is not a collection of sql statements.
you should either have no such file, or create a proper sqlite database and populate it with the schema.

Honestly, I don't know exactly how a sqllite database works. What I know is that I generated the below dump from a corrupted sqllite database file and imported it to a new/fresh/clean database, which in turn made zabbix-proxy startable again.

This is part of the dump generated from the corrupted sqlite database with the command echo .dump | sqlite3 /var/lib/zabbix/zabbix.db > zabbix.sql

# head zabbix.sql 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE slideshows ( slideshowid bigint DEFAULT '0' NOT NULL, name varchar(255) DEFAULT '' NOT NULL, delay integer DEFAULT '0' NOT NULL, PRIMARY KEY (slideshowid));
CREATE TABLE slides ( slideid bigint DEFAULT '0' NOT NULL, slideshowid bigint DEFAULT '0' NOT NULL, screenid bigint DEFAULT '0' NOT NULL, step integer DEFAULT '0' NOT NULL, delay integer DEFAULT '0' NOT NULL, PRIMARY KEY (slideid));
CREATE TABLE drules ( druleid bigint DEFAULT '0' NOT NULL, proxy_hostid bigint DEFAULT '0' NOT NULL, name varchar(255) DEFAULT '' NOT NULL, iprange varchar(255) DEFAULT '' NOT NULL, delay integer DEFAULT '0' NOT NULL, nextcheck integer DEFAULT '0' NOT NULL, status integer DEFAULT '0' NOT NULL, unique_dcheckid bigint DEFAULT '0' NOT NULL, PRIMARY KEY (druleid));
CREATE TABLE dchecks ( dcheckid bigint DEFAULT '0' NOT NULL, druleid bigint DEFAULT '0' NOT NULL, type integer DEFAULT '0' NOT NULL, key_ varchar(255) DEFAULT '0' NOT NULL, snmp_community varchar(255) DEFAULT '0' NOT NULL, ports varchar(255) DEFAULT '0' NOT NULL, snmpv3_securityname varchar(64) DEFAULT '' NOT NULL, snmpv3_securitylevel integer DEFAULT '0' NOT NULL, snmpv3_authpassphrase varchar(64) DEFAULT '' NOT NULL, snmpv3_privpassphrase varchar(64) DEFAULT '' NOT NULL, PRIMARY KEY (dcheckid));
CREATE TABLE dhosts ( dhostid bigint DEFAULT '0' NOT NULL, druleid bigint DEFAULT '0' NOT NULL, status integer DEFAULT '0' NOT NULL, lastup integer DEFAULT '0' NOT NULL, lastdown integer DEFAULT '0' NOT NULL, PRIMARY KEY (dhostid));
CREATE TABLE dservices ( dserviceid bigint DEFAULT '0' NOT NULL, dhostid bigint DEFAULT '0' NOT NULL, type integer DEFAULT '0' NOT NULL, key_ varchar(255) DEFAULT '0' NOT NULL, value varchar(255) DEFAULT '0' NOT NULL, port integer DEFAULT '0' NOT NULL, status integer DEFAULT '0' NOT NULL, lastup integer DEFAULT '0' NOT NULL, lastdown integer DEFAULT '0' NOT NULL, dcheckid bigint DEFAULT '0' NOT NULL, ip varchar(39) DEFAULT '' NOT NULL, PRIMARY KEY (dserviceid));
CREATE TABLE ids ( nodeid integer DEFAULT '0' NOT NULL, table_name varchar(64) DEFAULT '' NOT NULL, field_name varchar(64) DEFAULT '' NOT NULL, nextid bigint DEFAULT '0' NOT NULL, PRIMARY KEY (nodeid,table_name,field_name));
INSERT INTO "ids" VALUES(0,'proxy_autoreg_host','autoreg_host_lastid',103);
...
...
...
...
...

If these are not SQL statements, then I no longer know what SQL statement stands for.

Again, I just wanted to add a solution that worked for me in order to help other people going through a similar issue. Moreover, I don't wanna be rude, but if it worked, how can I be "absolutely wrong"?

[richlv]

This is part of the dump generated from the corrupted sqlite database with the command echo .dump | sqlite3 /var/lib/zabbix/zabbix.db > zabbix.sql

oh, but that's different. the resulting file was a proper sqlite database, not just a file with sqlite statements

nevertheless, starting zabbix proxy without the database should have worked - do you remember what was the error message ?

[luisfernando]

oh, but that's different. the resulting file was a proper sqlite database, not just a file with sqlite statements

nevertheless, starting zabbix proxy without the database should have worked - do you remember what was the error message ?

Yes, I do. I mentioned it on a previous message.

When I removed the corrupted database and started zabbix-proxy, the logs kept saying there was no such table a, b and c.

  7123:20120904:095050.104 Starting Zabbix Proxy (passive) [node1_proxy]. Zabbix 1.8.14 (revision 28454).
  7123:20120904:095050.104 **** Enabled features ****
  7123:20120904:095050.104 SNMP monitoring:       YES
  7123:20120904:095050.104 IPMI monitoring:       YES
  7123:20120904:095050.104 ODBC:                  YES
  7123:20120904:095050.104 SSH2 support:          YES
  7123:20120904:095050.104 IPv6 support:          YES
  7123:20120904:095050.104 **************************
  7123:20120904:095050.105 [Z3005] query failed: [0] no such table: items [select i.itemid,i.hostid,h.proxy_hostid,i.type,i.data_type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.snmp_port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.delay_flex,i.trapper_hosts,i.logtimefmt,i.params,i.status,i.authtype,i.username,i.password,i.publickey,i.privatekey from items i,hosts h where i.hostid=h.hostid and h.status in (0) and i.status in (0,3)]
  7123:20120904:095050.105 [Z3005] query failed: [0] no such table: hosts [select hostid,proxy_hostid,host,useip,ip,dns,port,useipmi,ipmi_ip,ipmi_port,ipmi_authtype,ipmi_privilege,ipmi_username,ipmi_password,maintenance_status,maintenance_type,maintenance_from,errors_from,available,disable_until,snmp_errors_from,snmp_available,snmp_disable_until,ipmi_errors_from,ipmi_available,ipmi_disable_until,status from hosts where status in (0,5,6)]
  7123:20120904:095050.107 proxy #0 started [main process]
  7140:20120904:095050.107 proxy #16 started [history syncer #2]
  7136:20120904:095050.108 proxy #12 started [icmp pinger #1]
  7132:20120904:095050.108 proxy #8 started [trapper #2]
  7137:20120904:095050.113 proxy #13 started [housekeeper #1]
  7137:20120904:095050.113 Executing housekeeper
  7137:20120904:095050.113 [Z3005] query failed: [0] no such table: ids [select nextid from ids where table_name='proxy_history' and field_name='history_lastid']
  7137:20120904:095050.114 [Z3005] query failed: [0] no such table: ids [select nextid from ids where table_name='proxy_dhistory' and field_name='dhistory_lastid']
  7137:20120904:095050.114 [Z3005] query failed: [0] no such table: ids [select nextid from ids where table_name='proxy_autoreg_host' and field_name='autoreg_host_lastid']
  7137:20120904:095050.114 Deleted 0 records from history [0.000154 seconds]
  7139:20120904:095050.114 proxy #15 started [history syncer #1]
  7134:20120904:095050.114 proxy #10 started [trapper #4]
  7141:20120904:095050.114 proxy #17 started [history syncer #3]
  7135:20120904:095050.114 proxy #11 started [trapper #5]
  7142:20120904:095050.114 proxy #18 started [history syncer #4]
  7131:20120904:095050.114 proxy #7 started [trapper #1]
  7133:20120904:095050.120 proxy #9 started [trapper #3]
  7127:20120904:095050.130 proxy #3 started [poller #3]
  7128:20120904:095050.135 proxy #4 started [poller #4]
  7126:20120904:095050.143 proxy #2 started [poller #2]
  7125:20120904:095050.146 proxy #1 started [poller #1]
  7138:20120904:095050.150 proxy #14 started [discoverer #1]
  7138:20120904:095050.150 [Z3005] query failed: [0] no such table: drules [select druleid,iprange,name,unique_dcheckid from drules where proxy_hostid=0 and status=0 and (nextcheck<=1346763050 or nextcheck>1346763050+delay) and druleid%1=0]
  7138:20120904:095050.150 [Z3005] query failed: [0] no such table: drules [select count(*),min(nextcheck) from drules where proxy_hostid=0 and status=0 and druleid%1=0]
  7129:20120904:095050.151 proxy #5 started [poller #5]
  7130:20120904:095050.155 proxy #6 started [unreachable poller #1]
  7133:20120904:095051.518 [Z3005] query failed: [0] no such table: hosts [select hostid,available,error,snmp_available,snmp_error,ipmi_available,ipmi_error from hosts]
  7134:20120904:095052.593 [Z3005] query failed: [0] no such table: ids [select nextid from ids where table_name='proxy_history' and field_name='history_lastid']

It looks like it did not rebuild the db for some reason.

By the way, the zabbix user has/had full permissions on the db directory.

[richlv]

When I removed the corrupted database and started zabbix-proxy, the logs kept saying there was no such table a, b and c.

interesting. is this something you can reproduce (if you can play with any of those proxies at this time) ?

[dholder]

I ran into the same issue

I also encountered the malformed database issue and tried deleting the sqlite DB. After restarting the proxy I ran into the same issue with error messages indicating that various tables did not exist.

It seems to be an issue of performing the procedure in the correct order. First stop the zabbix proxy, then delete the database and finally start the proxy again.

If you just delete the database file then try to restart the proxy you will run into the issue described in earlier posts.