1 // Copyright 2013 The Chromium Authors
2 // Use of this source code is governed by a BSD-style license that can be
3 // found in the LICENSE file.
5 #include "sql/recovery.h"
15 #include "base/bind.h"
16 #include "base/check_op.h"
17 #include "base/dcheck_is_on.h"
18 #include "base/files/file_path.h"
19 #include "base/format_macros.h"
20 #include "base/logging.h"
21 #include "base/notreached.h"
22 #include "base/strings/string_util.h"
23 #include "base/strings/stringprintf.h"
24 #include "base/types/pass_key.h"
25 #include "sql/database.h"
26 #include "sql/recover_module/module.h"
27 #include "sql/statement.h"
28 #include "third_party/sqlite/sqlite3.h"
33 std::unique_ptr<Recovery> Recovery::Begin(Database* database,
34 const base::FilePath& db_path) {
35 // Recovery is likely to be used in error handling. Since recovery changes
36 // the state of the handle, protect against multiple layers attempting the
38 if (!database->is_open()) {
39 // Warn about API mis-use.
40 DCHECK(database->poisoned(InternalApiToken()))
41 << "Illegal to recover with closed Database";
45 // Using `new` to access a non-public constructor
46 std::unique_ptr<Recovery> recovery(new Recovery(database));
47 if (!recovery->Init(db_path)) {
48 // TODO(shess): Should Init() failure result in Raze()?
49 recovery->Shutdown(POISON);
57 bool Recovery::Recovered(std::unique_ptr<Recovery> r) {
62 void Recovery::Unrecoverable(std::unique_ptr<Recovery> r) {
64 // ~Recovery() will RAZE_AND_POISON.
68 void Recovery::Rollback(std::unique_ptr<Recovery> r) {
69 // TODO(shess): Crash / crash and dump?
73 Recovery::Recovery(Database* connection)
76 .exclusive_locking = false,
77 .page_size = db_->page_size(),
78 // The interface to the recovery module is a virtual table.
79 .enable_virtual_tables_discouraged = true,
81 // Files with I/O errors cannot be safely memory-mapped.
82 recover_db_.set_mmap_disabled();
84 // TODO(shess): This may not handle cases where the default page
85 // size is used, but the default has changed. I do not think this
86 // has ever happened. This could be handled by using "PRAGMA
87 // page_size", at the cost of potential additional failure cases.
90 Recovery::~Recovery() {
91 Shutdown(RAZE_AND_POISON);
94 bool Recovery::Init(const base::FilePath& db_path) {
96 // set_error_callback() will DCHECK if the database already has an error
97 // callback. The recovery process is likely to result in SQLite errors, and
98 // those shouldn't get surfaced to any callback.
99 db_->set_error_callback(base::BindRepeating(
100 [](int sqlite_error_code, sql::Statement* statement) {}));
102 // Undo the set_error_callback() above. We only used it for its DCHECK
104 db_->reset_error_callback();
105 #endif // DCHECK_IS_ON()
107 // Break any outstanding transactions on the original database to
108 // prevent deadlocks reading through the attached version.
109 // TODO(shess): A client may legitimately wish to recover from
110 // within the transaction context, because it would potentially
111 // preserve any in-flight changes. Unfortunately, any attach-based
112 // system could not handle that. A system which manually queried
113 // one database and stored to the other possibly could, but would be
115 db_->RollbackAllTransactions();
117 // Disable exclusive locking mode so that the attached database can
118 // access things. The locking_mode change is not active until the
119 // next database access, so immediately force an access. Enabling
120 // writable_schema allows processing through certain kinds of
122 // TODO(shess): It would be better to just close the handle, but it
123 // is necessary for the final backup which rewrites things. It
124 // might be reasonable to close then re-open the handle.
125 std::ignore = db_->Execute("PRAGMA writable_schema=1");
126 std::ignore = db_->Execute("PRAGMA locking_mode=NORMAL");
127 std::ignore = db_->Execute("SELECT COUNT(*) FROM sqlite_schema");
129 // TODO(shess): If this is a common failure case, it might be
130 // possible to fall back to a memory database. But it probably
131 // implies that the SQLite tmpdir logic is busted, which could cause
132 // a variety of other random issues in our code.
133 if (!recover_db_.OpenTemporary(base::PassKey<Recovery>()))
136 // Enable the recover virtual table for this connection.
137 int rc = EnableRecoveryExtension(&recover_db_, InternalApiToken());
138 if (rc != SQLITE_OK) {
139 LOG(ERROR) << "Failed to initialize recover module: "
140 << recover_db_.GetErrorMessage();
144 // Turn on |SQLITE_RecoveryMode| for the handle, which allows
145 // reading certain broken databases.
146 if (!recover_db_.Execute("PRAGMA writable_schema=1"))
149 if (!recover_db_.AttachDatabase(db_path, "corrupt", InternalApiToken()))
155 bool Recovery::Backup() {
157 CHECK(recover_db_.is_open());
159 // TODO(shess): Some of the failure cases here may need further
160 // exploration. Just as elsewhere, persistent problems probably
161 // need to be razed, while anything which might succeed on a future
162 // run probably should be allowed to try. But since Raze() uses the
163 // same approach, even that wouldn't work when this code fails.
165 // The documentation for the backup system indicate a relatively
166 // small number of errors are expected:
167 // SQLITE_BUSY - cannot lock the destination database. This should
168 // only happen if someone has another handle to the
169 // database, Chromium generally doesn't do that.
170 // SQLITE_LOCKED - someone locked the source database. Should be
171 // impossible (perhaps anti-virus could?).
172 // SQLITE_READONLY - destination is read-only.
173 // SQLITE_IOERR - since source database is temporary, probably
174 // indicates that the destination contains blocks
175 // throwing errors, or gross filesystem errors.
176 // SQLITE_NOMEM - out of memory, should be transient.
178 // AFAICT, SQLITE_BUSY and SQLITE_NOMEM could perhaps be considered
179 // transient, with SQLITE_LOCKED being unclear.
181 // SQLITE_READONLY and SQLITE_IOERR are probably persistent, with a
182 // strong chance that Raze() would not resolve them. If Delete()
183 // deletes the database file, the code could then re-open the file
184 // and attempt the backup again.
186 // For now, this code attempts a best effort.
188 // Backup the original db from the recovered db.
189 const char* kMain = "main";
190 sqlite3_backup* backup =
191 sqlite3_backup_init(db_->db(InternalApiToken()), kMain,
192 recover_db_.db(InternalApiToken()), kMain);
194 // Error code is in the destination database handle.
195 LOG(ERROR) << "sqlite3_backup_init() failed: "
196 << sqlite3_errmsg(db_->db(InternalApiToken()));
201 // -1 backs up the entire database.
202 int rc = sqlite3_backup_step(backup, -1);
203 int pages = sqlite3_backup_pagecount(backup);
204 // TODO(shess): sqlite3_backup_finish() appears to allow returning a
205 // different value from sqlite3_backup_step(). Circle back and
206 // figure out if that can usefully inform the decision of whether to
208 sqlite3_backup_finish(backup);
211 if (rc != SQLITE_DONE) {
212 LOG(ERROR) << "sqlite3_backup_step() failed: "
213 << sqlite3_errmsg(db_->db(InternalApiToken()));
216 // The destination database was locked. Give up, but leave the data
217 // in place. Maybe it won't be locked next time.
218 if (rc == SQLITE_BUSY || rc == SQLITE_LOCKED) {
223 // Running out of memory should be transient, retry later.
224 if (rc == SQLITE_NOMEM) {
229 // TODO(shess): For now, leave the original database alone. Some errors should
230 // probably route to RAZE_AND_POISON.
231 if (rc != SQLITE_DONE) {
236 // Clean up the recovery db, and terminate the main database
242 void Recovery::Shutdown(Recovery::Disposition raze) {
247 if (raze == RAZE_AND_POISON) {
249 } else if (raze == POISON) {
255 bool Recovery::AutoRecoverTable(const char* table_name,
256 size_t* rows_recovered) {
257 // Query the info for the recovered table in database [main].
259 base::StringPrintf("PRAGMA main.table_info(%s)", table_name));
260 Statement s(db()->GetUniqueStatement(query.c_str()));
262 // The columns of the recover virtual table.
263 std::vector<std::string> create_column_decls;
265 // The columns to select from the recover virtual table when copying
266 // to the recovered table.
267 std::vector<std::string> insert_columns;
269 // If PRIMARY KEY is a single INTEGER column, then it is an alias
270 // for ROWID. The primary key can be compound, so this can only be
271 // determined after processing all column data and tracking what is
272 // seen. |pk_column_count| counts the columns in the primary key.
273 // |rowid_decl| stores the ROWID version of the last INTEGER column
274 // seen, which is at |rowid_ofs| in |create_column_decls|.
275 size_t pk_column_count = 0;
276 size_t rowid_ofs = 0; // Only valid if rowid_decl is set.
277 std::string rowid_decl; // ROWID version of column |rowid_ofs|.
280 const std::string column_name(s.ColumnString(1));
281 const std::string column_type(s.ColumnString(2));
282 const ColumnType default_type = s.GetColumnType(4);
283 const bool default_is_null = (default_type == ColumnType::kNull);
284 const int pk_column = s.ColumnInt(5);
286 // http://www.sqlite.org/pragma.html#pragma_table_info documents column 5 as
287 // the 1-based index of the column in the primary key, otherwise 0.
291 // Construct column declaration as "name type [optional constraint]".
292 std::string column_decl = column_name;
294 // SQLite's affinity detection is documented at:
295 // http://www.sqlite.org/datatype3.html#affname
296 // The gist of it is that CHAR, TEXT, and INT use substring matches.
297 // TODO(shess): It would be nice to unit test the type handling,
298 // but it is not obvious to me how to write a test which would
299 // fail appropriately when something was broken. It would have to
300 // somehow use data which would allow detecting the various type
301 // coercions which happen. If STRICT could be enabled, type
302 // mismatches could be detected by which rows are filtered.
303 if (column_type.find("INT") != std::string::npos) {
304 if (pk_column == 1) {
305 rowid_ofs = create_column_decls.size();
306 rowid_decl = column_name + " ROWID";
308 column_decl += " INTEGER";
309 } else if (column_type.find("CHAR") != std::string::npos ||
310 column_type.find("TEXT") != std::string::npos) {
311 column_decl += " TEXT";
312 } else if (column_type == "BLOB") {
313 column_decl += " BLOB";
314 } else if (column_type.find("DOUB") != std::string::npos) {
315 column_decl += " FLOAT";
317 // TODO(shess): AFAICT, there remain:
318 // - contains("CLOB") -> TEXT
319 // - contains("REAL") -> FLOAT
320 // - contains("FLOA") -> FLOAT
321 // - other -> "NUMERIC"
322 // Just code those in as they come up.
323 NOTREACHED() << " Unsupported type " << column_type;
327 create_column_decls.push_back(column_decl);
329 // Per the NOTE in the header file, convert NULL values to the
330 // DEFAULT. All columns could be IFNULL(column_name,default), but
331 // the NULL case would require special handling either way.
332 if (default_is_null) {
333 insert_columns.push_back(column_name);
335 // The default value appears to be pre-quoted, as if it is
336 // literally from the sqlite_schema CREATE statement.
337 std::string default_value = s.ColumnString(4);
338 insert_columns.push_back(base::StringPrintf(
339 "IFNULL(%s,%s)", column_name.c_str(), default_value.c_str()));
343 // Receiving no column information implies that the table doesn't exist.
344 if (create_column_decls.empty()) {
348 // If the PRIMARY KEY was a single INTEGER column, convert it to ROWID.
349 if (pk_column_count == 1 && !rowid_decl.empty())
350 create_column_decls[rowid_ofs] = rowid_decl;
352 std::string recover_create(base::StringPrintf(
353 "CREATE VIRTUAL TABLE temp.recover_%s USING recover(corrupt.%s, %s)",
356 base::JoinString(create_column_decls, ",").c_str()));
358 // INSERT OR IGNORE means that it will drop rows resulting from constraint
359 // violations. INSERT OR REPLACE only handles UNIQUE constraint violations.
360 std::string recover_insert(base::StringPrintf(
361 "INSERT OR IGNORE INTO main.%s SELECT %s FROM temp.recover_%s",
363 base::JoinString(insert_columns, ",").c_str(),
366 std::string recover_drop(base::StringPrintf(
367 "DROP TABLE temp.recover_%s", table_name));
369 if (!db()->Execute(recover_create.c_str()))
372 if (!db()->Execute(recover_insert.c_str())) {
373 std::ignore = db()->Execute(recover_drop.c_str());
377 *rows_recovered = db()->GetLastChangeCount();
379 // TODO(shess): Is leaving the recover table around a breaker?
380 return db()->Execute(recover_drop.c_str());
383 bool Recovery::SetupMeta() {
385 static const char kCreateSql[] =
386 "CREATE VIRTUAL TABLE temp.recover_meta USING recover("
389 "value ANY" // Whatever is stored.
392 return db()->Execute(kCreateSql);
395 bool Recovery::GetMetaVersionNumber(int* version) {
397 // TODO(shess): DCHECK(db()->DoesTableExist("temp.recover_meta"));
398 // Unfortunately, DoesTableExist() queries sqlite_schema, not
399 // sqlite_temp_master.
401 static const char kVersionSql[] =
402 "SELECT value FROM temp.recover_meta WHERE key = 'version'";
403 sql::Statement recovery_version(db()->GetUniqueStatement(kVersionSql));
404 if (!recovery_version.Step())
407 *version = recovery_version.ColumnInt(0);
413 // Collect statements from [corrupt.sqlite_schema.sql] which start with |prefix|
414 // (which should be a valid SQL string ending with the space before a table
415 // name), then apply the statements to [main]. Skip any table named
416 // 'sqlite_sequence', as that table is created on demand by SQLite if any tables
417 // use AUTOINCREMENT.
419 // Returns |true| if all of the matching items were created in the main
420 // database. Returns |false| if an item fails on creation, or if the corrupt
421 // database schema cannot be queried.
422 bool SchemaCopyHelper(Database* db, const char* prefix) {
423 const size_t prefix_len = strlen(prefix);
424 DCHECK_EQ(' ', prefix[prefix_len-1]);
426 sql::Statement s(db->GetUniqueStatement(
427 "SELECT DISTINCT sql FROM corrupt.sqlite_schema "
428 "WHERE name<>'sqlite_sequence'"));
430 std::string sql = s.ColumnString(0);
432 // Skip statements that don't start with |prefix|.
433 if (sql.compare(0, prefix_len, prefix) != 0)
436 sql.insert(prefix_len, "main.");
437 if (!db->Execute(sql.c_str()))
440 return s.Succeeded();
445 // This method is derived from SQLite's vacuum.c. VACUUM operates very
446 // similarily, creating a new database, populating the schema, then copying the
449 // TODO(shess): This conservatively uses Rollback() rather than Unrecoverable().
450 // With Rollback(), it is expected that the database will continue to generate
451 // errors. Change the failure cases to Unrecoverable().
454 std::unique_ptr<Recovery> Recovery::BeginRecoverDatabase(
456 const base::FilePath& db_path) {
457 std::unique_ptr<sql::Recovery> recovery = sql::Recovery::Begin(db, db_path);
459 // Close the underlying sqlite* handle. Windows does not allow deleting
460 // open files, and all platforms block opening a second sqlite3* handle
461 // against a database when exclusive locking is set.
464 // When this code was written, histograms showed that most failures happened
465 // while attaching a corrupt database. In this case, a large proportion of
466 // attachment failures were SQLITE_NOTADB.
468 // We currently only delete the database in that specific failure case.
471 if (!probe_db.OpenInMemory() ||
472 probe_db.AttachDatabase(db_path, "corrupt", InternalApiToken()) ||
473 probe_db.GetErrorCode() != SQLITE_NOTADB) {
478 // The database has invalid data in the SQLite header, so it is almost
479 // certainly not recoverable without manual intervention (and likely not
480 // recoverable _with_ manual intervention). Clear away the broken database.
481 if (!sql::Database::Delete(db_path))
484 // Windows deletion is complicated by file scanners and malware - sometimes
485 // Delete() appears to succeed, even though the file remains. The following
486 // attempts to track if this happens often enough to cause concern.
489 if (!probe_db.Open(db_path))
492 if (!probe_db.Execute("PRAGMA auto_vacuum"))
496 // The rest of the recovery code could be run on the re-opened database, but
497 // the database is empty, so there would be no point.
502 // This code silently fails to recover fts3 virtual tables. At this time no
503 // browser database contain fts3 tables. Just to be safe, complain loudly if
504 // the database contains virtual tables.
506 // fts3 has an [x_segdir] table containing a column [end_block INTEGER]. But
507 // it actually stores either an integer or a text containing a pair of
508 // integers separated by a space. AutoRecoverTable() trusts the INTEGER tag
509 // when setting up the recover vtable, so those rows get dropped. Setting
510 // that column to ANY may work.
512 sql::Statement s(db->GetUniqueStatement(
513 "SELECT 1 FROM sqlite_schema WHERE sql LIKE 'CREATE VIRTUAL TABLE %'"));
514 DCHECK(!s.Step()) << "Recovery of virtual tables not supported";
518 // TODO(shess): vacuum.c turns off checks and foreign keys.
520 // TODO(shess): vacuum.c turns synchronous=OFF for the target. I do not fully
521 // understand this, as the temporary db should not have a journal file at all.
522 // Perhaps it does in case of cache spill?
524 // Copy table schema from [corrupt] to [main].
525 if (!SchemaCopyHelper(recovery->db(), "CREATE TABLE ") ||
526 !SchemaCopyHelper(recovery->db(), "CREATE INDEX ") ||
527 !SchemaCopyHelper(recovery->db(), "CREATE UNIQUE INDEX ")) {
528 // No RecordRecoveryEvent() here because SchemaCopyHelper() already did.
529 Recovery::Rollback(std::move(recovery));
533 // Run auto-recover against each table, skipping the sequence table. This is
534 // necessary because table recovery can create the sequence table as a side
535 // effect, so recovering that table inline could lead to duplicate data.
537 sql::Statement s(recovery->db()->GetUniqueStatement(
538 "SELECT name FROM sqlite_schema WHERE sql LIKE 'CREATE TABLE %' "
539 "AND name!='sqlite_sequence'"));
541 const std::string name = s.ColumnString(0);
542 size_t rows_recovered;
543 if (!recovery->AutoRecoverTable(name.c_str(), &rows_recovered)) {
544 Recovery::Rollback(std::move(recovery));
548 if (!s.Succeeded()) {
549 Recovery::Rollback(std::move(recovery));
554 // Overwrite any sequences created.
555 if (recovery->db()->DoesTableExist("corrupt.sqlite_sequence")) {
556 std::ignore = recovery->db()->Execute("DELETE FROM main.sqlite_sequence");
557 size_t rows_recovered;
558 if (!recovery->AutoRecoverTable("sqlite_sequence", &rows_recovered)) {
559 Recovery::Rollback(std::move(recovery));
564 // Copy triggers and views directly to sqlite_schema. Any tables they refer
565 // to should already exist.
566 static const char kCreateMetaItemsSql[] =
567 "INSERT INTO main.sqlite_schema "
568 "SELECT type, name, tbl_name, rootpage, sql "
569 "FROM corrupt.sqlite_schema WHERE type='view' OR type='trigger'";
570 if (!recovery->db()->Execute(kCreateMetaItemsSql)) {
571 Recovery::Rollback(std::move(recovery));
578 void Recovery::RecoverDatabase(Database* db, const base::FilePath& db_path) {
579 std::unique_ptr<sql::Recovery> recovery = BeginRecoverDatabase(db, db_path);
582 std::ignore = Recovery::Recovered(std::move(recovery));
585 void Recovery::RecoverDatabaseWithMetaVersion(Database* db,
586 const base::FilePath& db_path) {
587 std::unique_ptr<sql::Recovery> recovery = BeginRecoverDatabase(db, db_path);
592 if (!recovery->SetupMeta() || !recovery->GetMetaVersionNumber(&version)) {
593 sql::Recovery::Unrecoverable(std::move(recovery));
597 std::ignore = Recovery::Recovered(std::move(recovery));
601 bool Recovery::ShouldRecover(int extended_error) {
602 // Trim extended error codes.
603 int error = extended_error & 0xFF;
606 // SQLITE_NOTADB happens if the SQLite header is broken. Some earlier
607 // versions of SQLite return this where other versions return
608 // SQLITE_CORRUPT, which is a recoverable case. Later versions only
609 // return this error only in unrecoverable cases, in which case recovery
610 // will fail with no changes to the database, so there's no harm in
611 // attempting recovery in this case.
615 // SQLITE_CORRUPT generally means that the database is readable as a
616 // SQLite database, but some inconsistency has been detected by SQLite.
617 // In many cases the inconsistency is relatively trivial, such as if an
618 // index refers to a row which was deleted, in which case most or even all
619 // of the data can be recovered. This can also be reported if parts of
620 // the file have been overwritten with garbage data, in which recovery
621 // should be able to recover partial data.
624 // TODO(shess): Possible future options for automated fixing:
625 // - SQLITE_CANTOPEN - delete the broken symlink or directory.
626 // - SQLITE_PERM - permissions could be fixed.
627 // - SQLITE_READONLY - permissions could be fixed.
628 // - SQLITE_IOERR - rewrite using new blocks.
629 // - SQLITE_FULL - recover in memory and rewrite subset of data.
637 int Recovery::EnableRecoveryExtension(Database* db, InternalApiToken) {
638 return sql::recover::RegisterRecoverExtension(db->db(InternalApiToken()));