Database disk image is malformed,

[tuceturan]

Hello,

I'm using sqlite in my android app.When doing multi insert ı use ;
I use existing database in sqlite3

final _db = .sqlite3.open(fileDb.path);
var stmt = _db.prepare(...)
stmt.dispose();
like that but sometimes its throws error "while executing statement, database disk image is malformed"

[simolus3]

Are you continuing to use the existing database natively? If so, does that also report errors about malformed database images?

I think this might be a concurrency issue, but we're compiling sqlite with thread-safe options so it should be fine. I wonder if the native side might be using the database without thread safety, but it seems unlikely when using the default Android APIs.

[tuceturan]

what do you mean for " I wonder if the native side might be using the database without thread safety" sorry ı couldn't understand

[simolus3]

I don't think this is the source of the problem, but if two threads are using the same sqlite3 database and that database hasn't been configured to be used with thread safety (an option in sqlite3), that might corrupt the database. But again, this is unlikely the problem here.

Do you have a way to check whether you're also getting the malformed database errors natively, or are you only accessing the database in Dart now?

[tuceturan]

just accessting database in dart

[Den-creator]

have the similar issue

[simolus3]

@Den-creator Could you also describe your setup in a bit more detail (e.g. does this only happen on some platforms/operating systems, are you opening multiple connections to the same database file (even in different threads), did you notice anything else that seems to cause this problem or make it more likely)? At the moment I don't really have enough information to investigate this, so everything helps.

[Den-creator]

@simolus3 flutter app, drift: ^2.16.0, flutter: 3.19.3, Android 12, Pixel 3, one connection to data base, looks like it has happened after upgrade to flutter 3.16.9, but maybe it is coincidence.

AccelerometerDataDao.batchInsertFrom.error SqliteException(11): while executing statement, database disk image is malformed, database disk image is malformed (code 11).│ Causing statement: INSERT INTO "raw_accelerometer_table" ("raw_text") VALUES (?), parameters: accelerometer,VIVOSMART_5,2024-03-26T17:40:53.477,2024-03-26T17:41:08.762,-960,204,-352.

Raw Accelerometer Data Upload error: SqliteException(11): while executing statement, database disk image is malformed, database disk image is malformed (code 11).│ Causing statement: DELETE FROM "raw_accelerometer_table" WHERE "id" <= ?;, parameters: 282453

[bendangelo]

Hi, I've been able to reproduce this by using a sqlite db created by the Sequel gem: https://github.com/jeremyevans/sequel

Use the db file created like this:

DB = Sequel.connect('sqlite://blog.db')

DB.create_table :items do
  primary_key :id
  String :name
  Float :price
end

items = DB[:items] # Create a dataset

# Populate the table
items.insert(name: 'abc', price: rand * 100)
items.insert(name: 'def', price: rand * 100)
items.insert(name: 'ghi', price: rand * 100)

# Print out the number of records
puts "Item count: #{items.count}"

# Print out the average price
puts "The average price is: #{items.avg(:price)}"

You'll get the malformed error.

[simolus3]

I couldn't reproduce the error. I have this Gemfile.lock:

GEM
  remote: https://rubygems.org/
  specs:
    bigdecimal (3.1.8)
    mini_portile2 (2.8.7)
    sequel (5.84.0)
      bigdecimal
    sqlite3 (2.0.4)
      mini_portile2 (~> 2.8.0)
    sqlite3 (2.0.4-x86_64-linux-gnu)

PLATFORMS
  ruby
  x86_64-linux

DEPENDENCIES
  sequel
  sqlite3

BUNDLED WITH
   2.5.11

With that, your snippet (well, with a require 'sequel' at the top) generated a blog.db that I was able to open like this:

import 'package:sqlite3/sqlite3.dart';

void main() {
  final dbtest = sqlite3.open('/tmp/test/blog.db');
  print(dbtest.select('select * from items'));
}

[bendangelo]

I found the issue. It had to do with the left over -mal -shm files. Those have to be deleted.