~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

122

123

<h1 align=center>How To Corrupt An SQLite Database File</h1>

124

125

<p>An SQLite database is highly resistant to corruption.

126

If an application crash, or an operating-system crash, or even

127

a power failure occurs in the middle of a transaction, the partially

128

written transaction should be automatically rolled back the next time

129

the database file is accessed. The recovery process is fully

130

automatic and does not require any action on the part of the user

131

or the application.

132

</p>

133

134

<p>Though SQLite is resistant to database corruption, it is not immune.

135

This document describes the various ways that an SQLite database might

136

go corrupt.</p>

137

138

<h2>1.0 File overwrite by a rogue thread or process</h2>

139

140

<p>SQLite database files are ordinary disk files.

141

That means that any process can open the file and

142

overwrite it with garbage. There is nothing that the SQLite

143

library can do to defend against this.</p>

144

145

<h3>1.1 Continuing to use a file descriptor after it has been closed</h3>

146

147

<p>We have seen cases where a file descriptor was open on a log file,

148

then that file descriptor was closed and reopened on an SQLite database.

149

Later, some other thread continued to write log information into the

150

old file descriptor, not realizing that the log file had been closed

151

already. But because the file descriptor had been reopened by SQLite,

152

the information that was intended to go into the log file ended up

153

overwriting parts of the SQLite database, leading to corruption of the

154

database.</p>

155

156

<h3>1.2 Backup or restore while a transaction is active</h3>

157

158

<p>Systems that run automatic backups in the background might try to

159

make a backup copy of an SQLite database file while it is in the middle

160

of a transaction. The backup copy then might contain some old and some

161

new content, and thus be corrupt.</p>

162

163

<p>The best approach to make reliable backup copies of an SQLite database

164

is to make use of the <a href="backup.html">backup API</a> that is part of the SQLite library.

165

Failing that, it is safe to make a copy of an SQLite database file as long

166

as there are no transactions in progress by any process. If the previous

167

transaction failed, then it is important that any rollback journal

168

(the <tt>*-journal</tt> file) or write-ahead log (the <tt>*-wal</tt> file)

169

be copied together with the database file itself.</p>

170

171

172

173

<h3>1.3 Deleting a hot journal</h3>

174

175

<p>SQLite normally stores all content in a single disk file. However,

176

while performing a transaction, information necessary to roll back that

177

transaction following a crash or power failure is stored in auxiliary

178

journal files. These journal files have the same name as the

179

original database file with the addition

180

of <tt>-journal</tt> or <tt>-wal</tt> suffix.</p>

181

182

<p>SQLite must see the journal files in order to recover from a crash

183

or power failure. If the journal files are moved, deleted, or renamed

184

after a crash or power failure, then automatic recovery will not work

185

and the database may go corrupt.</p>

186

187

<p>Another manifestation of this problem is

188

<a href="shortnames.html#db83corrupt">database corruption caused by inconsistent use of 8+3 filenames</a>.</p>

189

190

<h2>2.0 File locking problems</h2>

191

192

<p>SQLite uses file locks on the database file, and on the

193

<a href="wal.html">write-ahead log</a> or <a href="wal.html">WAL</a> file, to coordinate access between concurrent

194

processes. Without coordination, two threads or processes might try

195

to make incompatible changes to a database file at the same time,

196

resulting in database corruption.</p>

197

198

<h3>2.1 Filesystems with broken or missing lock implementations</h3>

199

200

<p>SQLite depends on the underlying filesystem to do locking as the

201

documentation says it will. But some filesystems contain bugs in their

202

locking logic such that the locks do not always behave as advertised.

203

This is especially true of network filesystems and NFS in particular.

204

If SQLite is used on a filesystem where the locking primitives contain

205

bugs, and if two or more threads or processes try to access the same

206

database at the same time, then database corruption might result.</p>

207

208

209

210

<h3>2.2 Posix advisory locks canceled by a separate thread doing close()</h3>

211

212

<p>The default locking mechanism used by SQLite on unix platforms is

213

POSIX advisory locking. Unfortunately, POSIX advisory locking has design

214

quirks that make it prone to misuse and failure. In particular, any

215

thread in the same process with a file descriptor that is holding a POSIX

216

advisory lock can override that lock using a different file descriptor.

217

One particularly pernicious problem is that the <tt>close()</tt> system

218

call will cancel all POSIX advisory locks on the same file for all

219

threads and all file descriptors in the process.</p>

220

221

<p>So, for example, suppose a multi-thread process has

222

two or more threads with separate SQLite database connections to the

223

same database file. Then a third thread comes along and wants to read

224

something out of that same database file on its own, without using the

225

SQLite library.

226

The third thread does an <tt>open()</tt>, a <tt>read()</tt> and then

227

a <tt>close()</tt>.

228

One would think this would be harmless.

229

But the <tt>close()</tt> system call caused the

230

locks held on the database by all the other threads to be dropped. Those

231

other threads have no way of knowing that their locks have just been

232

trashed (POSIX does not provide any mechanism to determine this) and so

233

they keep on running under the assumption that their locks are still valid.

234

This can lead to two or more threads or processes trying to write to the

235

database at the same time, resulting in database corruption.</p>

236

237

<p>Note that it is perfectly safe for two or more threads to access the

238

same SQLite database file using the SQLite library. The unix drivers for

239

SQLite know about the POSIX advisory locking quirks and work around them.

240

This problem only arises when a thread tries to bypass the SQLite library

241

and read the database file directly.</p>

242

243

<h4>2.2.1 Multiple copies of SQLite linked into the same application</h4>

244

245

<p>As pointed out in the previous paragraph, SQLite takes steps to work

246

around the quirks of POSIX advisory locking. Part of that work-around involves

247

keeping a global list (mutex protected) of open SQLite database files.

248

But, if multiple copies of SQLite are linked into the same application,

249

then there will be multiple instances of this global list.

250

Database connections opened using one copy of the SQLite library

251

will be unaware of database connections opened using the other copy,

252

and will be unable to work around the POSIX advisory locking quirks.

253

A <tt>close()</tt> operation on one connection might unknowingly

254

clear the locks on a different database connection, leading to database

255

corruption.</p>

256

257

<p>The scenario above sounds far-fetched.

258

But the SQLite developers are aware of at

259

least one commercial product that was release

260

with exactly this bug. The vendor came to the SQLite developers seeking

261

help in tracking down some infrequent database corruption issues they were

262

seeing on Linux and Mac. The problem was eventually traced to the

263

fact that the application was linking against two separate copies of SQLite.

264

The solution was to change the application build procedures to link against

265

just one copy of SQLite instead of two.</p>

266

267

<h3>2.3 Two processes using different locking protocols</h3>

268

269

<p>The default locking mechanism used by SQLite on unix platforms is

270

POSIX advisory locking, but there are other options. By selecting an

271

alternative <a href="c3ref/vfs.html">sqlite3_vfs</a> using the <a href="c3ref/open.html">sqlite3_open_v2()</a> interface, an

272

application can make use of other locking protocols that might be more

273

appropriate to certain filesystems. For example, dot-file locking might

274

be select for use in an application that has to run on an NFS filesystem

275

that does not support POSIX advisory locking.</p>

276

277

<p>It is important that all connections to the same database file use

278

the same locking protocol.

279

If one application is using POSIX advisory locks and another application

280

is using dot-file locking, then the two applications will not see each

281

others locks and will not be able to coordinate database access, possibly

282

leading to database corruption.</p>

283

284

<h2>3.0 Failure to sync</h2>

285

286

<p>In order to guarantee that database files are always consistent, SQLite

287

will occasionally ask the operating system to flush all pending writes to

288

persistent storage then wait for that flush to complete. This is

289

accomplished using the <tt>fsync()</tt> system call under unix and

290

<tt>FlushFileBuffers()</tt> under windows. We call this flush of

291

pending writes a "sync".</p>

292

293

<p>Actually, if one is only concerned with atomic and consistent writes and

294

is willing to forego durable writes, the sync operation does not need

295

to wait until the content is completely stored on persistent media. Instead,

296

the sync operation can be thought of as an I/O barrier. As long as all

297

writes that occur before the sync are completed before any write that happens

298

after the sync, no database corruption will occur. If sync is operating as

299

an I/O barrier and not as a true sync, then a power failure or system crash

300

might cause one or more previously committed transactions to roll back

301

(in violation of the "durable" property of "ACID") but the database will at

302

least continue to be consistent, and that is what most people care about.</p>

303

304

<h3>3.1 Disk drives that do not honor sync requests</h3>

305

306

<p>Unfortunately, most consumer-grade mass storage devices lie about

307

syncing. Disk drives will report that content is safely on persistent

308

media as soon as it reaches the track buffer and before actually being

309

written to oxide. This makes the disk drives seem to operate faster

310

(which is vitally important to the manufacturer so that they can show

311

good benchmark numbers in trade magazines). And in fairness, the lie

312

normally causes no harm, as long as there is no power loss or hard reset

313

prior to the track buffer actually being written to oxide. But if a

314

power loss or hard reset does occur, and if that results in content that

315

was written after a sync reaching oxide while content written before

316

the sync is still in a track buffer, then database corruption can occur.</p>

317

318

<p>USB flash memory sticks seem to be especially pernicious liars

319

regarding sync requests. One can easily see this by committing a large

320

transaction to an SQLite database on a USB memory stick. The COMMIT

321

command will return relatively quickly, indicating that the memory stick

322

has told the operating system and the operating system has told SQLite that

323

all content is safely in persistent storage, and yet the LED on the end

324

of the memory stick will continue flashing for several more seconds.

325

Pulling out the memory stick while the LED is still flashing will frequently

326

result in database corruption.</p>

327

328

<p>Note that SQLite must believe whatever the operating system and hardware

329

tell it about the status of sync requests. There is no way for SQLite to

330

detect that either is lying and that writes might be occurring out-of-order.

331

However, SQLite in <a href="wal.html">WAL mode</a> is far more forgiving of

332

out-of-order writes than in the default rollback journal modes. In WAL

333

mode, the only time that a failed sync operation can cause database corruption

334

is during a <a href="wal.html#ckpt">checkpoint</a> operation. A sync failure during a COMMIT might

335

result in loss of durability but not in a corrupt database file. Hence,

336

one line of defense against database corruption due to failed sync operations

337

is to use SQLite in WAL mode and to checkpoint as infrequently as possible.</p>

338

339

<h3>3.2 Disabling sync using PRAGMAs</h3>

340

341

<p>The sync operations that SQLite performs to help ensure integrity

342

can be disabled at run-time using the <a href="pragma.html#pragma_synchronous">synchronous pragma</a>. By setting

343

PRAGMA synchronous=OFF, all sync operations are omitted. This makes

344

SQLite seem to run faster, but it also allows the operating system to freely

345

reorder writes, which could result in database corruption if a power failure

346

or hard reset occurs prior to all content reaching persistent storage.</p>

347

348

<p>For maximum reliability and for robustness against database corruption,

349

SQLite should always be run with its default synchronous setting of FULL.</p>

350

351

352

353

<h2>4.0 Disk Drive and Flash Memory Failures</h2>

354

355

<p>An SQLite database can become corrupt if the file content changes

356

due to a disk drive or flash memory failure. It is very rare, but disks

357

will occasionally flip a bit in the middle of a sector.</p>

358

359

<h3>4.1 Non-powersafe flash memory controllers</h3>

360

361

<p>We are told that in some flash memory controllers the wear-leveling logic

362

can cause random filesystem damage if power is interrupted during a write.

363

This can manifest, for example, as random changes in the middle of a file

364

that was not even open at the time of the power loss. So, for example,

365

a device would be writing content into an MP3 file in flash memory when a

366

power loss occurs, and that could result in an SQLite database being

367

corrupted even though the database as not even in use at the time of the

368

power loss.</p>

369

370

371

372

<h3>4.2 Fake capacity USB sticks</h3>

373

374

<p>There are many fraudulent USB sticks in circulation that report to have

375

a high capacity (ex: 8GB) but are really only capable of storing a much

376

smaller amount (ex: 1GB). Attempts to write on these devices will

377

often result in unrelated files being overwritten. Any use of a fraudulent

378

flash memory device can easily lead to database corruption, therefore.

379

Internet searches such as "fake capacity usb" will turn up lots of

380

disturbing information about this problem.

381

382

<h2>5.0 Memory corruption</h2>

383

384

<p>SQLite is a C-library that runs in the same address space as the

385

application that it serves. That means that stray pointers, buffer

386

overruns, heap corruption, or other malfunctions in application can

387

corrupt internal SQLite data structure and ultimately result in a

388

corrupt database file. Normally these kinds of problems manifest themselves

389

as segfaults prior to any database corruption occurring, but there have

390

been instances where application code errors have caused SQLite to

391

malfunction subtly so as to corrupt the database file rather than

392

panicking.</p>

393

394

<h2>6.0 Other operating system problems</h2>

395

396

<p>Sometimes operating systems will exhibit non-standard behavior which

397

can lead to problems. Sometimes this non-standard behavior is deliberate,

398

and sometimes it is a mistake in the implementation. But in any event,

399

if the operating performs differently from they way SQLite expects it to

400

perform, the possibility of database corruption exists.</p>

401

402

<h3>6.1 Linux Threads</h3>

403

404

<p>Some older versions of Linux used the LinuxThreads library for thread

405

support. LinuxThreads is similar to Pthreads, but is subtly different

406

with respect to handling of POSIX advisory locks. SQLite versions

407

2.2.3 through 3.6.23 recognized that LinuxThreads where being used at

408

runtime and took appropriate action to work around the non-standard

409

behavior of LinuxThreads. But most modern Linux implementations make

410

use of the newer, and correct, NPTL implementation of Pthreads. Beginning

411

with SQLite version 3.7.0, the use of NPTL is assumed. No checks are

412

made. Hence, recent versions of SQLite will subtly malfunction and may

413

corrupt database files if used in multi-threaded application that run

414

on older linux systems that make use of LinuxThreads.</p>

415

416

<h3>6.2 Failures of mmap() on QNX</h3>

417

418

<p>There exists some subtle problem with mmap() on QNX such that making

419

a second mmap() call against the a single file descriptor can cause

420

the memory obtained from the first mmap() call to be zeroed. SQLite on

421

unix uses mmap() to create a shared memory region for transaction

422

coordination in <a href="wal.html">WAL mode</a>, and it will call mmap() multiple times

423

for large transactions. The QNX mmap() has been demonstrated to corrupt

424

database file under that scenario. QNX engineers are aware of this problem

425

and are working on a solution; the problem may have already been fixed by

426

the time you read this.</p>

427

428

<h2>7.0 Bugs in SQLite</h2>

429

430

<p>SQLite is <a href="testing.html">very carefully tested</a> to help ensure that it is

431

as bug-free as possible. Among the many tests that are carried out for

432

every SQLite version are tests that simulate power failures, I/O errors,

433

and out-of-memory (OOM) errors and verify that no database corrupt occurs

434

during any of these events. SQLite is also field-proven with approximately

435

two billion active deployments with no serious problems.</p>

436

437

<p>Nevertheless, no software is 100% perfect. There have been a few

438

historical bugs in SQLite (now fixed) that could cause database corruption.

439

And there may be yet a few more that remain undiscovered. Because of the

440

extensive testing and widespread use of SQLite, bugs that result in

441

database corruption tend to be very obscure. The likelihood

442

of an application encountering an SQLite bug is small. To illustrate this,

443

an account is given below

444

of all database-corruption bugs found in SQLite during the

445

two-year period from 2009-04-01 to 2011-04-01.

446

This account should give the reader an intuitive sense of the

447

kinds of bugs in SQLite that manage to slip through testing procedures

448

and make it into a release.</p>

449

450

451

<h3>7.1 False corruption reports due to database shrinkage</h3>

452

453

<p>If a database is written by SQLite version 3.7.0 or later and then

454

written again by SQLite version 3.6.23 or earlier in such a way as to

455

make the size of the database file decrease, then the next time that

456

SQLite version 3.7.0 access the database file, it might report that the

457

database file is corrupt. The database file is not really corrupt, however.

458

Version 3.7.0 was simply begin overly zealous in its corruption detection.</p>

459

460

<p>The problem was fixed on 2011-02-20. The fix first appears in

461

SQLite version 3.7.6.</p>

462

463

<h3>7.2 Corruption follow switches between rollback and WAL modes</h3>

464

465

<p>Repeatedly switching an SQLite database in and out of <a href="wal.html">WAL mode</a>

466

and running the <a href="lang_vacuum.html">VACUUM</a> command in between switches, in one process or

467

thread, can cause another process or thread that has the database file

468

open to miss the fact that the database has changed. That second process

469

or thread might then try to modify the database using a stale cache and

470

cause database corruption.</p>

471

472

<p>This problem was discovered during internal testing and has never been

473

observed in the wild. The problem was fixed on 2011-01-27 and in version

474

3.7.5.</p>

475

476

<h3>7.3 I/O while obtaining a lock leads to corruption</h3>

477

478

<p>If the operating system returns an I/O error while attempting to obtain

479

a certain lock on shared memory in <a href="wal.html">WAL mode</a> then SQLite might fail

480

to reset its cache,

481

which could lead to database corruption if subsequent writes are attempted.</p>

482

483

<p>Note that this problem only occurs if the attempt to acquire the lock

484

resulted in an I/O error. If the lock is simply not granted (because some

485

other thread or process is already holding a conflicting lock) then no

486

corruption will ever occur. We are not aware of any operating systems that

487

will fail with an I/O error while attempting to get a file lock on shared

488

memory. So this is a theoretical problem rather than a real problem.

489

Needless to say, this problem has never been observed in the wild. The

490

problem was discovered while doing stress testing of SQLite in a test

491

harness that simulates I/O errors.</p>

492

493

<p>This problem was fixed on 2010-09-20 for SQLite version 3.7.3.</p>

494

495

<h3>7.4 Database pages leak from the free page list</h3>

496

497

<p>When content is deleted from an SQLite database, pages that are no

498

longer used are added to a free list and are reused to hold content

499

added but subsequent inserts. A bug in SQLite that was present in

500

version 3.6.16 through 3.7.2 might cause pages to go missing out of

501

the free list when <a href="pragma.html#pragma_incremental_vacuum">incremental_vacuum</a> was used. This would not cause

502

data loss. But it would result in the database file being larger than

503

necessary. And it would cause the <a href="pragma.html#pragma_integrity_check">integrity_check pragma</a> to report

504

pages missing from the free list.</p>

505

506

<p>This problem was fixed on 2010-08-23 for SQLite version 3.7.2.</p>

507

508

<h3>7.5 Corruption following alternating writes from 3.6 and 3.7.</h3>

509

510

<p>SQLite version 3.7.0 introduced a number of new enhancements to

511

the SQLite database file format (such as but not limited to <a href="wal.html">WAL</a>).

512

The 3.7.0 release was a shake-out release for these new features.

513

We expected to find problems and were not disappointed.</p>

514

515

<p>If a database were originally created using SQLite version 3.7.0,

516

then written by SQLite version 3.6.23.1 such that the size of the database

517

file increased, then written again by SQLite version 3.7.0, the database

518

file could go corrupt.</p>

519

520

<p>This problem was fixed on 2010-08-04 for SQLite version 3.7.1.</p>

521