ERROR: SQLite database is malformed – SOLVED

Every once in a while if SQLiteyou’re making heavy use of a SQLite database, as with any other DBMS, it will become corrupted. Usually this is due to bugs within the DBMS itself and sometimes hardware failure.

I had a SQLite database go corrupt when an application crashed. Normally I would have a backup to go to but in this case I didn’t because it was a new application that I was in the process of setting up.

Attempting to list tables in the database using sqlite3 resulted in the following message:

Error: near line 1: database disk image is malformed

In SQLite, the consistency checker is pragma integrity_check

This pragma does an integrity check of the entire database. The integrity_check pragma looks for out-of-order records, missing pages, malformed records, missing index entries, and UNIQUE and NOT NULL constraint errors. If the integrity_check pragma finds problems, strings are returned (as multiple rows with a single column per row) which describe the problems. Pragma integrity_check will return at most N errors before the analysis quits, with N defaulting to 100. If pragma integrity_check finds no errors, a single row with the value ‘ok’ is returned.

PRAGMA integrity_check does not find FOREIGN KEY errors. Use the PRAGMA foreign_key_check command for to find errors in FOREIGN KEY constraints.

sqlite> pragma integrity_check;
Error: database disk image is malformed

Yeah, it’s effectively a boolean response. OK if all is good else “database disk image is malformed”. Not very helpful IMO.

So what to do? Obviously, reverting to an earlier backup is preferred but if you remember, I didn’t have one and I didn’t want to redo all that work.

We need to export the schema and data and create a new database, import the schema and data. Simple:

sqlite> .mode insert
sqlite> .output mydb_export.sql
sqlite> .dump
sqlite> .exit

The .output sets the name of the file that the output of following commands to mydb_export.sql.
The .dump will export the database in SQL format.

Now, move the mydb.db file to mydb.db.original.

mv mydb.db mydb.db.original

We are almost done. Let’s create the database and load the exported schema & data:

sqlite3 mydb.db < mydb_export.sql

We have one last step. If the database had indexes, and all databases should have indexes, you will want to update the statistics for the indexes:

sqlite> analyze;
sqlite> .exit
Share Button

Thievery in Flossmoor, Illinois School District 161.. Where your money is really going to


Please email the Flossmoor School District 161 School Board and let them know what you think!

Share Button

WordPress, MySQL and a bit of corruption caused the tags & categories to disappear

Sometime early this afternoon, the categories & tags of my blog disappeared. After a little bit of investigation (I was working quite heavily at the time), I discovered that the wp_term_taxonomy table in MySQL contained minor corruption. I ran repair table ‘wp_term_taxonomy’ and it was corrected.

Share Button

Duplicate rows in sysusers??

If you’re like me, you’ve run into lots of different issues with the Sybase ASE DBMS over the years. Today, Drew Montgomery looked at what happens when the 12.5.x -> 12.5.4 upgrade goes bad:

If you receive the 8419 error on sysusers after applying ASE 12.5.4 (or any other upgrade for that matter) such as the following:

01:00000:00472:2007/12/10 14:46:03.86 server Error: 8419, Severity: 20, State: 3
01:00000:00472:2007/12/10 14:46:03.86 server Could not find index descriptor for objid 10, indid 3 in dbid 13.
01:00000:00472:2007/12/10 14:46:03.88 kernel ************************************
01:00000:00472:2007/12/10 14:46:03.88 kernel SQL causing error : select * from sysusers
01:00000:00472:2007/12/10 14:46:03.88 kernel ************************************
01:00000:00472:2007/12/10 14:46:03.88 server SQL Text: select * from sysusers
01:00000:00472:2007/12/10 14:46:03.89 kernel curdb = 13 tempdb = 9 pstat = 0x10000
01:00000:00472:2007/12/10 14:46:03.89 kernel lasterror = 8419 preverror = 0 transtate = 1

Fear not because Drew has you covered with a simple script that will fix the problem. Note that it involves a dump and load of the affected database but you could just restart the ASE server if you don’t mind the down time:

sp_configure "allow updates", 1
go
use db_with_8419
go

select * into tempdb..temp_sysusers from sysusers
go

select uid, cnt= count(*) 
  into tempdb..temp_uid 
  from tempdb..temp_sysusers 
 group by uid 
 having count(*) > 1
go

select s.uid, s.suid
  into tempdb..temp_fixuid 
  from tempdb..temp_sysusers s, tempdb..temp_uid t
 where s.uid = t.uid
go

select uid, min(suid) suid
  into tempdb..temp_rmuid
  from tempdb..temp_fixuid
 group by uid
go

delete tempdb..temp_fixuid
  from tempdb..temp_fixuid f, tempdb..temp_rmuid r
 where f.uid = r.uid
   and f.suid = r.suid
go

alter table tempdb..temp_fixuid add cntr int identity
go

declare @max_uid int
select @max_uid = max(uid) 
  from tempdb..temp_sysusers
 where suid > 16300

update sysusers
   set uid = @max_uid + cntr
  from sysusers s, tempdb..temp_fixuid f
 where s.uid = f.uid
   and s.suid = f.suid
go

use master
go

dump database db_with_8419 to "compress::N::/DumpLocation/dumpfilename.dmp"
go
load database db_with_8419 from "compress::/DumpLocation/dumpfilename.dmp"
go
online database db_with_8419
go

sp_configure "allow updates", 0
go

Bau-jen Liu over at Sybase came up with the following method but it does require rebooting ASE twice:

sp_configure "allow updates", 1 
go 
update ..sysobjects 
set sysstat = 115, type = "U" 
where name = "sysusers" 
go 

Shutdown ASE, reboot, and log back in as a user with sa_role.

use dbname
go 

create unique index ncsysusers2 on sysusers(uid) 
go 

The above CREATE INDEX command may fail due to the presence of duplicate key rows in sysusers. If this occurs, recreate the index as non-unique first:

create index ncsysusers2 on sysusers(uid) 
go 

Now identify any duplicates that may exist in sysusers:

select uid, name, count(*) 
from sysusers 
group by uid 
having count(*) > 1 
go 

Remove any duplicates found using the DELETE command and try the CREATE INDEX again:

create unique index ncsysusers2 on sysusers(uid) 
go 

Now, clean up after yourself:

update ..sysobjects 
set sysstat = 113, type = "S" 
where name = "sysusers" 
go 

sp_configure "allow updates", 0 
go

Sybase has created bug # 298620 for this issue.

Share Button

Finding suspect indexes in Sybase ASE

Problem: ASE does not use an index on a table because it is marked suspect

Index id 2 on table id 864003078 cannot be used in the optimization of a query as it is SUSPECT. Please have the SA run DBCC REINDEX on the specified table.
Index id 2 on table id 864003078 cannot be used in the optimization of a query as it is SUSPECT. Please have the SA run DBCC REINDEX on the specified table.
Index id 2 cannot be used to access table id 864003078 as it is SUSPECT. Please have the SA run the DBCC REINDEX command on the specified table.

Solution:  Use sp_indsuspect to identify the suspect indexes in a database and run dbcc reindex(<tablename>) tables.

dbcc reindex can only be executed on a single table at a time in a database so if time is short, you may be better off dropping and creating the indexes in parallel.

Share Button