New database corruption message: DatabaseError: malformed database schema

[Chris Auston]

possibly_quarantine() probably needs to be updated to catch this error from sqlite on a corrupted DB replica.

swift-container-info f2d5ef251ee3137f159284a6cfc49557.db
Traceback (most recent call last):
  File "/usr/local/bin/swift-container-info", line 32, in <module>
    print_info('container', *args, **vars(options))
  File "/usr/local/lib/python2.7/dist-packages/swift/cli/info.py", line 327, in print_info
    info = broker.get_info()
  File "/usr/local/lib/python2.7/dist-packages/swift/container/backend.py", line 500, in get_info
    self._commit_puts_stale_ok()
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 631, in _commit_puts_stale_ok
    self._commit_puts()
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 614, in _commit_puts
    self.merge_items(item_list)
  File "/usr/local/lib/python2.7/dist-packages/swift/container/backend.py", line 889, in merge_items
    with self.get() as conn:
  File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__
    return self.gen.next()
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 366, in get
    self.possibly_quarantine(*sys.exc_info())
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 338, in possibly_quarantine
    six.reraise(exc_type, exc_value, exc_traceback)
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 364, in get
    self.conn = get_db_connection(self.db_file, self.timeout)
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 194, in get_db_connection
    timeout=timeout)
swift.common.db.DatabaseConnectionError: DB connection error (./f2d5ef251ee3137f159284a6cfc49557.db, 25):
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 186, in get_db_connection
    cur.execute('PRAGMA synchronous = NORMAL')
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 133, in execute
    self.timeout, self.db_file, lambda: sqlite3.Cursor.execute(
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 71, in _db_timeout
    return call()
  File "/usr/local/lib/python2.7/dist-packages/swift/common/db.py", line 134, in <lambda>
    self, *args, **kwargs))
DatabaseError: malformed database schema (bf02358.bin) - near "application": syntax error

pip show swift
---
Name: swift
Version: 2.7.0.post11
Location: /usr/local/lib/python2.7/dist-packages
Requires: PyECLib, dnspython, netifaces, greenlet, xattr, six, eventlet, pastedeploy

dpkg -l libsqlite3-0
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name Version Architecture Description
+++-=========================================-=========================-=========================-========================================================================================
ii libsqlite3-0:amd64 3.7.13-1+deb7u3 amd64 SQLite 3 shared library

[Matthew Oliver (matt-0)]

Looking initially at the code, it does seem wont catch this anywhere, not in the broker (or db.py), the container-replicator (replicator or rpc replicator).

From the stack trace it does throw the DatabaseConnectionError, but we may need to add a new malformed string match.

Now to try and recreate it :)

[Matthew Oliver (matt-0)]

Looking online at forums, sometimes these malformed database schemas can be fixed up. But to do so involves dumping the schema, fixing it, and loading it back in using SQLite special PRAMAs set. And I don't think this is anything that we can automate.

I want to make sure there isn't a bug where and old schema from and older SQLite DB isn't comparable with newer.. but the version your running seems quite old, and I'm sure we would have seen much more if that was the case. Further there seems to be no bug reported for it.

So I think this is safe just to add to the possibly_quarantine to quarantine, as getting a correct version from another node would be much simpler then trying to fix it.

I'll send up an initial patch.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/405031

[clayg (clay-gerrard)]

Chris, would it be possible to share this corrupt database with me (or someone else in the swift/sqlite communities) so that we may be able to investigate how to create a similarly corrupted example database which we can use in our test suite?

[Matthew Oliver (matt-0)]

I managed to create one using these steps.

 - Grab or create a container (I used ipython and a ContainerBroker).
 - Open the db in sqlite:

  sqlite3 my_malfored_schema.db

 - Turn on editable schemas:

   PRAGMA writable_schema=ON

 - Update (put a syntax error in the existing schema)

   update sqlite_master set sql = 'CRREAT TABLE object (ROWID INTEGER name TEXT)' where type='table' and name='object';

 - for safe measure I turned off the updateable schemas:

   PRAGMA writable_schema=OFF

 And there you have it.

sqlite> select * from object;
Error: malformed database schema (object_delete_policy_stat) - no such table: main.object

Attached you'll find the one I currupted.

[Chris Auston (cfauston)]

Thanks for the quick response and detailed investigation! I'll try to test the patch against the corrupt DB we have. I cannot post our DB since it's from a production system.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/405031
Committed: https://git.openstack.org/cgit/openstack/swift/commit/?id=3bde14b5cf88272a43c0594a695fcbcd0690e957
Submitter: Jenkins
Branch: master

commit 3bde14b5cf88272a43c0594a695fcbcd0690e957
Author: Matthew Oliver <email address hidden>
Date: Thu Dec 1 09:46:53 2016 +1100

    Quarantine malformed database schema SQLite errors

    Currently if an sqlite3.DatabaseError is thrown when caused by
    a corrupted database schema, it get logged and the database is isn't
    quarantined.

    This patch adds the malformed database schema case to the list of
    SQLite errors in possibly_quarantine that will trigger the db to be
    quarantined.

    Also it improved the possibly_quarantined unit test to test all existing
    exceptions, and catches exceptions based on the real world except we use
    in code.

    Closes-Bug: #1646247

    Change-Id: Id9452c88f8394a2a910c34c69361442543aa206d

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/swift 2.12.0 release.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/newton
Review: https://review.openstack.org/416090

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/416090
Committed: https://git.openstack.org/cgit/openstack/swift/commit/?id=ea1ecf3d8b097a5d24fe81f2c5ee9ec390d41809
Submitter: Jenkins
Branch: stable/newton

commit ea1ecf3d8b097a5d24fe81f2c5ee9ec390d41809
Author: Matthew Oliver <email address hidden>
Date: Thu Dec 1 09:46:53 2016 +1100

    Quarantine malformed database schema SQLite errors

    Currently if an sqlite3.DatabaseError is thrown when caused by
    a corrupted database schema, it get logged and the database is isn't
    quarantined.

    This patch adds the malformed database schema case to the list of
    SQLite errors in possibly_quarantine that will trigger the db to be
    quarantined.

    Also it improved the possibly_quarantined unit test to test all existing
    exceptions, and catches exceptions based on the real world except we use
    in code.

    Closes-Bug: #1646247

    Change-Id: Id9452c88f8394a2a910c34c69361442543aa206d
    (cherry picked from commit 3bde14b)

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/swift 2.10.2 release.