Ad Widget

Collapse

Sqlite database malformed

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • oct
    Junior Member
    • Dec 2009
    • 22

    #1

    Sqlite database malformed

    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
    Senior Member
    Zabbix Certified Trainer
    Zabbix Certified SpecialistZabbix Certified Professional
    • Oct 2005
    • 3112

    #2
    assuming proxy has write permissions to the database file directory, just delete the corrupted one and restart the proxy
    Last edited by richlv; 04-06-2012, 15:49.
    Zabbix 3.0 Network Monitoring book

    Comment

    • oct
      Junior Member
      • Dec 2009
      • 22

      #3
      Easy, and worked!

      Thanks, richlv

      Comment

      • luisfernando
        Junior Member
        • Jan 2012
        • 4

        #4
        Originally posted by richlv
        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
        Last edited by luisfernando; 04-09-2012, 15:40.

        Comment

        • richlv
          Senior Member
          Zabbix Certified Trainer
          Zabbix Certified SpecialistZabbix Certified Professional
          • Oct 2005
          • 3112

          #5
          Originally posted by luisfernando
          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
          Zabbix 3.0 Network Monitoring book

          Comment

          • luisfernando
            Junior Member
            • Jan 2012
            • 4

            #6
            Originally posted by richlv
            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.

            Comment

            • richlv
              Senior Member
              Zabbix Certified Trainer
              Zabbix Certified SpecialistZabbix Certified Professional
              • Oct 2005
              • 3112

              #7
              Originally posted by luisfernando
              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.
              Zabbix 3.0 Network Monitoring book

              Comment

              • luisfernando
                Junior Member
                • Jan 2012
                • 4

                #8
                Originally posted by richlv
                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


                Code:
                # 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"?

                Comment

                • richlv
                  Senior Member
                  Zabbix Certified Trainer
                  Zabbix Certified SpecialistZabbix Certified Professional
                  • Oct 2005
                  • 3112

                  #9
                  Originally posted by luisfernando
                  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 ?
                  Zabbix 3.0 Network Monitoring book

                  Comment

                  • luisfernando
                    Junior Member
                    • Jan 2012
                    • 4

                    #10
                    Originally posted by richlv
                    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.

                    Code:
                      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.

                    Comment

                    • richlv
                      Senior Member
                      Zabbix Certified Trainer
                      Zabbix Certified SpecialistZabbix Certified Professional
                      • Oct 2005
                      • 3112

                      #11
                      Originally posted by luisfernando
                      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) ?
                      Zabbix 3.0 Network Monitoring book

                      Comment

                      • dholder
                        Junior Member
                        • May 2013
                        • 1

                        #12
                        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.

                        Comment

                        Working...