Brace yourselves for an inception of database (please, don't ask why we are doing this ^^):
Our application has 2 SQLite databases (let's call them Cache
and LocalRepository
). These two databases are encrypted (here is the connection string; Data Source=%PATH_TO_FILE%;Password=%PWD%
). For some reasons, we store the Cache
database into the LocalRepository
. And for some other reasons, we are synchronizing the LocalRepository
with a MySQL database (lets call it GlobalRepository
).
As long as the application is extracting the Cache
database from the LocalRepository
, I can recreate the file and connect to it with success. But if the application updates the LocalRepository
with the data from the GlobalRepository
, I get the following error:
database disk image is malformed
The three databases are mapped through Entity Framework 6 and here is how everything is defined:
LocalRepository definition (SQLite):
CREATE TABLE `LocalRepository` (
[..]
, `CacheData` BLOB NOT NULL
[..]
);
public partial class LocalRepository
{
[..]
public byte[] CacheData { get; set; }
[..]
}
GlobalRepository definition (MySQL):
CREATE TABLE `schema`.`GlobalRepository` (
[..]
`CacheData` BLOB NOT NULL,
[..]
);
public partial class GlobalRepository
{
[..]
public byte[] CacheData { get; set; }
[..]
}
Saving to LocalRepository:
var cacheBytes = File.ReadAllBytes(cacheDatabaseFilePath);
var localRep = new LocalRepository
{
[..]
CacheData = cacheBytes,
[..]
};
localRepositoryContext.Add(localRep);
localRepositoryContext.SaveChanges();
Pushing to GlobalRepository:
var localRep.Status = Status.Pushed;
var globalRep = new GlobalRepository
{
[..]
CacheData = localRep.CacheData,
Status = localRep.Status,
[..]
};
globalRepositoryContext.Add(localRep);
var saving = new Task[]
{
globalRepositoryContext.SaveChangesAsync(),
localRepositoryContext.SaveChangesAsync()
};
await Task.WhenAll(saving);
Updating from GlobalRepository:
var globalRep = globalRepositoryContext.Find(id);
var localRep = new LocalRepository
{
[..]
CacheData = globalRep.CacheData,
[..]
};
localRepositoryContext.Add(localRep);
localRepositoryContext.SaveChanges();
Recreating the Cache:
File.WriteAllBytes(cacheFilePath, localRep.CacheData);
To summarize: The last operation copies to the disk altered bytes only when the CacheData comes from the GlobalRepository
. And indeed, the recreated Cache
is very small comparing to the original file (64 KB vs 1,7MB).
Where the bytes could be altered? As it is systematic, I guess the issue is related to the way MySQL stores byte comparing to SQLite.