Usually when developing Drupal websites I use MySQL or MariaDB as my go to choice for a DBMS for any project. If my project is just a quick testbed to try a new idea or module sometimes I resort to serving static HTML from a folder using the RubyGem Serve, if I want to debug a small Drupal idea I usually resort to using the Drush runserver command and an Sqlite database for my drupal project. This way my entire project needs no system configuration! Recently in such a simple setup … I broke my database. Here is how I fixed it.

In using the runserver command throughout the day on and off and I suppose Ctrl+C (killing) the website off and on — somehow the underlying site.sqlite database I used for the site broke. I know it broke because when I tried to access the site after moving the project folder to a new system and running drush runserver, I got the following output:

$ drush runserver
HTTP server listening on 127.0.0.1, port 8888 (see http://127.0.0.1:8888/), serving site default...
PHP 5.5.9-1ubuntu4.14 Development Server started at Wed Nov 25 15:33:28 2015
Listening on http://127.0.0.1:8888
Document root is /home/dgurba/apps/projects/webdev/2015/ucsb/bits/revert_revision/www
Press Ctrl-C to quit.


[Wed Nov 25 15:34:17 2015] Watchdog: PDOException: SQLSTATE[HY000]: General error: 11 database disk image is malformed: INSERT INTO {search_index} (word, sid, type, score) VALUES (?, ?, ?, ?); Array
(
    [0] => dolore
    [1] => 728
    [2] => node
    [3] => 0.81924855491329
)
 in search_index() (line 720 of /home/dgurba/apps/projects/webdev/2015/ucsb/bits/revert_revision/www/modules/search/search.module). | severity: 3 | type: cron | uid: 0 | 127.0.0.1 | http://127.0.0.1:8888/ |  |

From this error message I can infer sqlite can open the file but upon trying to perform an INSERT the drupal search_index table the site barfed and PDO/Sqlite was unable to handle the request due to database corruption. This project is really not mission critical so I simply wanted to restore the database to working order. If this had been MySQL I likely would have tried to run an Optimize or Check Tables command to resolve the issue. But, I have never seen this issue before in SQLITE.

Not having a backup of this database … after much googling I came upon a solution, which is typical of any crashed database in a DBMS:

1. Export the DB to a dump file of SQL statements.
2. delete the old database
3. recreate from your dumpfile.

So I set about issuing the necessary commands to Sqlite3:

$sqlite3 site.sqilte
sqlite> .mode insert
sqlite> .output mydb_export.sql
sqlite> .dump
sqlite> .exit

The above exports the contents of my drupal sqlite database to a dumpfile called mydb_export. Then I simply replaced the broken database with a fresh copy from ingesting the dumpfile to a new database:

mv site.sqlite site.sqlite.original
sqlite3 site.sqlite < mydb_export.sql

Lastly, if our database contains indexes we should regenerate them:

$sqlite3 site.sqlite
sqlite> analyze;
sqlite> .exit

Then upon restarting my small project with drush runserver — the site was able to bootstrap and run fine with a consistent database. I hope you’ve found this small journey into the land of Sqlite useful!

Looking for quality web hosting? Look no further than Arvixe Web Hosting!