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

Optimizing the Firefox (SQLite databases)

Most people that want to speed up Firefox know about the various settings in about:config but many forget that Firefox uses SQLite databases that can become cluttered.

In your home directory ($HOME on *nix/Mac and %APPDIR% on Windows), run sqlite3 with vacuum, reindex and analyze on eche of the *.sqlite databases. Don’t have sqlite3? No problem, get it from the SQLite Download page or your distribution’s package manager (e.g. apt-get install sqlite3)


cd ${HOME}/.mozilla/firefox/

for profile in *.default; do
cd ${profile}

for db in *.sqlite; do 
echo Processing $db
sqlite3 "${db}" < 

That wasn’t hard was it? 🙂

Share Button

Andrew E. Bruno: Database Design with Dia

Back in December Diaof 2007, Andrew E. Bruno wrote an excellent article on how to use the open source program Dia to design databases.  He used the resultant design to create a MySQL database but the same principles could be used to create just about any relational database from the design.  Dia is available for Linux, *nix, and Windows.

Database Design with Dia

In this post I’m going to give a quick how-to on creating database schemas with a wonderful tool called Dia. I’ve often found having a nice visual representation of a database to be quite helpful but can’t stand keeping it up to date. As soon as you add a new column or change the design around you end up having to sync your visual diagram with your SQL files. It’s tedious having to manage the various SQL for building the database and this can be a larger pain when trying to support different database platforms each with their own SQL syntax. So before you create your next database read on and see how Dia can make your life a bit easier.

Dia is a program for creating diagrams and for this exercise we’ll be creating UML diagrams from within Dia. We’re also going to use a perl script called tedia2sql which will transform our Dia files directly to SQL for our target database. What’s also nice about creating database schemas this way is that you can generate SQL for multiple target databases without the maintenance overhead.

read more….

Share Button

FW: Migration Migraines

Adrian Bridgwater
Adrian Bridgwater

Migration migraines: the top seven DBA data headaches

Posted by Adrian Bridgwater

Once or twice a year I get to work with an excellent DBA pal from Illinois called Jason Froebe who describes himself as a, “Perlmonger capable of speaking fluent munchkin.” His personal blog is called Ramblings of a Geek, but I keep telling him he should rename it “Froebe’s Frontal Lobe”.

read more at ZDnet…

The work that Adrian mentions is a last minute article for the International Sybase User’s Group that I wrote and he edited.  The benchmarking article that many people are waiting for is being held up by several groups giving their approval for the release of the material.

As always, it is a real pleasure to work with Adrian 🙂

Share Button

Borderline Scamming being done by software companies

The latest craze from software vendors to companies is to charge for each and every core a machine has regardless of whether or not you’re going to use it.

Get this, if you want to buy a production license for your database/middleware/web server, the vendor (starts with an “S”) wants you to send them the hardware specs of the box.  If you tell them it is a Dell superduper server with 8 quad core CPUs and 96GBytes of RAM but you only will be using a single core for the database and devoting the rest to the middleware/webserver, you STILL have to pay the vendor for all 32 cores (8 CPUs X 4 cores).  Your software license costs is now 32 times MORE what you should have to pay IMHO.

Lots of software companies are now doing this anti-customer practice just to beef up their short term revenues.

What makes them think that you won’t go to another vendor?

  1. Their competitors are probably doing the same sales tactic
  2. They have you by the family jewels, vendor lock in, and it will cost you far too much $$$ to migrate
  3. They think that you’re too stupid and/or timid to call their bluff
  4. FUD that is spread by well meaning and well known folk that don’t know jack about the open source alternatives

Who the hell do they think they are?

Share Button

It’s here! The very first issue of MyDatabases!

MyDatabases Volume 1 Issue 1 (July 2008)

MyDatabases Volume 1 Issue 1

Geek Spotlight:  Michael Peppler
Sybase ASE on Ubuntu 8.04
Multicore Processors
VMware Virtualization
Logical Process Manager

Share Button

Native support of SQLite in Sybase PowerBuilder and PowerDesigner?

What I would love to see is native support for SQLite.

It is the most installed embedded database on the planet hands down.  Don’t believe me?  You know that Firefox web browser, Thunderbird newsreader, most Adobe products, Miro, etc all have it embedded?  – reference

We use it extensively at work as:

  1. staging for mass data imports/exports/conversions
  2. local application ‘cache’ for large data sets
  3. projects that don’t require all the features of Sybase ASE (or Oracle for that matter)

One of the best features is that the database itself in platform independent… copy the db on to AIX from your Windows box … then on to your old Amiga … then on to your windows mobile device.  Getting the point?  🙂

I really wish Sybase would make it so that the Sybase ASE databases were truly platform and character set/sort order independent… but that is in another dream 😉

Not a blurb in the PowerDesigner/PowerBuilder manuals or anything

Share Button

Multicore processors and Sybase ASE: Jeff Tallman

When T V S Murty asked on the sybase-l mailing list about Sybase ASE, multicores and Sybase licensing, the discussion quickly drilled down to whether or not multicores were beneficial to Sybase ASE and database software in general. Jeff Tallman, of Sybase fame, described in detail how Sybase ASE and multicore processors relate to each other.

From: Jeff Tallman <tallmanATsybaseZeDOTcom>
Subject: [sybase-l] – RE: Multicore processors and ASE

As always a lot depends on the application profile.   Something to consider for any multicore processor are factors:

  1. The number of FPU units per chip (FPU = Floating Point Unit)
  2. The number and capacity (in IOPS) of IO processors per chip
  3. The type of chip multi-threading

With respect to #1, most DBMS (at least the commercial ones) use statistics for query optimization – so while the actual query processing doesn’t use a lot of FPU instructions (assuming a minimum of float datatypes, etc.).  Each query requires a pretty good smack of the FPU time to do the floating point math on the stats.  The impact of this could be lessened by doing statement caching or fully prepared statements…or other means at reducing the optimizer load.

The second problem is one of capacity vs. bandwidth.   All network and disk IO obviously need to use the IO processor.   With 4 dual core chips, usually, you have 4 IO processors.

With a single chip with 8 cores, it is likely that you will have only a single IO processor.   The single IO processor has 8 cores all making requests.  The number of IO operations per second it can handle becomes a real key factor in the box’s scalability.

The chip multi-threading is an interesting issue as there are ~3 different flavors today:

  1. Intel’s Hyperthreading (no longer implemented on XEON and I don’t think implemented at all anymore)
  2. Sun’s Chip Multi-Threading (CMT)
  3. IBM’s SMT

Some instructions require multiple cycles to complete due to they are waiting on a fetch from main memory or whatever.   The thread/process of execution typically blocks in these cases, resulting in a fairly idle core.   By making use of this idle time, CMT or SMT can increase the throughput overall — ignoring HT as it was fairly ineffective at this – and appears to have been dropped by Intel lately.

The question that comes up is how do you manage the threading?  Do you do a form of timeslicing (i.e. when you suspend on process that is blocked on a call, do you let the one that replaced it run for a certain length of time or until it blocks before returning back to the original) or do you do an interrupt based/preemptive mechanism in which when the blocked call returns, that you suspend the other thread?   Both have advantages and disadvantages, and do allow more engines than cores.

However, it may also mean tuning ASE to be more reactive, such as reducing the ‘runnable process search count’.  You also need to be careful that engines running on CMT’s don’t get woken back up on another core (especially if the L2 cache is split between the cores) as well as other considerations.

A rule of thumb to think about is that if you have a multi-core CPU that supports chip threading, if you have a lengthy list of SPIDs in a ‘runnable’ state, enabling extra engines on the threads will likely help.   If you don’t – i.e. you are IO bound – that it probably won’t help.

Currently, Sun uses a timeslicing mechanism that is more along the lines of ASE’s SPID management – and as a consequence, it shows scalability when the various tasks do a lot of blocking calls such as fetches from main memory.  It does have the detrimental effect of only providing a percentage of cpu time to the ASE engine (i.e. 25% when 4 threads per core).   The more parallelism is used within your application, such as higher numbers of concurrent users in ASE, the more it can be distributed across the engines.

You have to be careful as net engine affinity and short query’s (i.e. DML).  They can have a negative impact, which may be controllable using engine groups.   Overall, a cpu-intensive/cpu bound application can benefit from the Sun CMT implementation.  An IO bound application does not.

Share Button

The first issue of “My Databases” coming next week!

As many of you know, I’ve been working on a free magazine regarding various database systems (dbms) called My Databases.  I hope to have multiple authors in future issues covering all sorts of open source and proprietary databases.

I should have the first issue done Sunday night.  I’m using OpenOffice, Scribus, Gimp, and Inkscape.

Share Button

Google Video: Introduction to SQLite

We are currently using SQLite in production for some smaller projects.

Share Button