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:
- Export the DB to a dump file of SQL statements.
- delete the old database
- 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!