Correcting a malformed Database [sql-script]

[mj10777]

I would like to share a small sql-script that assists in repairing a malformed Database.

I have no idea what caused the malformation, but suddenly errors such as :

- 'Unable to fetch list of tables: malformed database schema'

where turning up.

A quick google search of: 'sqlite3 correction malformed database'

- returned a solution that worked correctly

Being a friend of sql-scripts, I adapted the solution to be used in a more general way and have added it to my 'sql_howto_commands' directory

- in case it is needed again one day [better than searching for the panic button]

dump.malformed_database.sql:

---------------------------

-- source:

-- http://froebe.net/blog/2015/05/27/error-sqlite-database-is-malformed-solved/

---------------------------

-- ERROR 1: Unable to fetch list of tables: malformed database schema (2017-01-09T05:49:28.553Z) - no such table: street_segments_1920

-- listing of table-rows shows something similar to the results of : SELECT * FROM sqlite_master WHERE type='table';

---------------------------
-- Create a Sql-Dump of the malformed database:

-- sqlite3 20170108.berlin_admin_geometries.malformed.db < correct.malformed_database.sql

---------------------------
-- Restore the malformed Database from the Sql-Dump:

-- sqlite3 20170108.berlin_admin_geometries.corrected.db < dump.malformed_database.sql

---------------------------

.mode insert

.output dump.malformed_database.sql

.dump

.exit

---------------------------

Checking the result in QGIS showed the Database as it was before that last change was made.

Mark

[a.fu...@lqt.it]

Hi Mark,

I personally never encountered this "Unable to fetch list of tables:
malformed database schema" error, but the elegant and straightforward
solution suggested by you seems to be really useful just in case
such a major disaster should ever happen.

thanks for sharing,
Sandro