SQLite Fix Error – Database Disk Image Is Malformed

SQLite3 Fix Error – Database Disk Image Is Malformed

We will be using SQLite Browser, so download it here if you don’t have it.  It’s pretty easy to fix it, just follow the steps below

  1. Load up your corrupted database into SQLite Browser,
  2. Run the integrity check  under Tools > Integrity Check .The images below shows the steps and the result (error) I got
    Run Integrity Check 0
    Run Integrity Check 0
    Run Integrity Check 1
    Run Integrity Check 1

    Run Integrity Check 2 Results
    Run Integrity Check 2 Results
  3. If it returns ‘OK’, it means your database is not corrupted. If it returns anything else, you will need to fix your database. Follow through with this guide.
  4. Export your database to any format

    SQLite Browser Export
    SQLite Browser Export
  5. Import your database back to .sqlite3 (or any format you which)
    SQLite Browser Import 0
    SQLite Browser Import 0

    SQLite Browser Import 1
    SQLite Browser Import 1
  6. If you run the integrity check once more, it should return ‘OK’

If you face any other issues, let me know in the comments below and I’ll try my best to help you out

Enjoyed the content ? Share it with your friends !

PHP SQLite3 optimize large amount of inserts

PHP SQLite3 optimize large amount of inserts

One of the things many people forget to do is to start and end a transaction yourself instead of letting the internal SQLite3 do it.

If a transaction is not detected by SQLite, it will start a new transaction for each insert you do. As the numbers scale up, the time taken for the entire set of insert queries exponentially increases.

To start and end your own transaction, it is as simple as using the code below (remember to modify it for your own use)

// START TRANSACTION
$this->db->exec('BEGIN;'); 

// Prepare query
$stmt = $this->db->prepare($insert_query);

// Bind parameters to statement variables
$stmt->bindParam(':field', $field_value);

// Loop execute
foreach ($records as $record) {
    $field_value = $record['field'];
    $stmt->execute();	
}			

// END TRANSACTION
$this->db->exec('COMMIT;'); // END TRANSACTION

 

Enjoyed the content ? Share it with your friends !