TR: Database base disk is malformed issue

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

TR: Database base disk is malformed issue

Jean-François RIGHI
 

Hello everyone,

Following this Issue we would like to rollback from the last version (1.0.105.2) to the version 1.0.66.
We’re going to drop the special index wich are not compatibles with this version.
But we would like to know if both version are compatible (structures,syntaxe, etc) or we have to dump/recreate  the DB while the rollback . ?

Ticket created for this question https://system.data.sqlite.org/index.html/tktview?name=5683ab0fab
Ticket created for malformed DB issues : https://system.data.sqlite.org/index.html/tktview?name=ec17f31f2e
Many thanks in  advance

PS : We found  out the version realeasing the specials indexes (1.0.99)



http://www.synelia.fr/
Jean-François RIGHI
Service Étude et Développement

+33(0)3 27 08 08 08
mailto:[hidden email]

3595 route de Tournai - C.S. 20009 - 59501 DOUAI Cedex France
________________________________________
        Pour toutes demandes d’assistance: mailto:[hidden email] ou +33(0)3 27 08 08 26
De : Jean-François RIGHI
Envoyé : mardi 3 octobre 2017 09:14
À : mailto:[hidden email]
Objet : Database base disk is malformed issue

Hello everyone,

Following this ticket issue, we were advised to  submit it  by email,
https://system.data.sqlite.org/index.html/tktview?name=ec17f31f2e

Basicaly we  got 1 issue and 2 questions

1) We were working with an old version of System.data.sqlite.dll (1.0.66) wich was prety stable. But we needed to work with concat index (CREATE INDEX IDX ON TABLE_1 (FIELD1 || FIELD2) to upgrade performance. Since we changed the dll we often got a
Database base disk is malformed issue. We can repair the base via an sql dump of the base. The issue itself is never exactly the same : we can have a duplicate  TRIGGER or TABLE or sometimes INSERT INTO with non valide data.
That's crazy. We tried to change the DROP / CREATE code. Catchall exception and close connections... But the issue is still here and worst of all it happend randomly !
⇒ More informations :
.The old version of SQLite we used : 1.0.66.0 and the new one : 1.0.105.2
.Almost users have a windows 10 OS
.We use visual studio 2010 (SP1) with the .net  Framework 3.5 (x86)
.The DB is encrypted with the sqlite encryption algorithm
.The DB is around 200Mo (depending of users)
.The DB is saved in the users computers
       . We tried to set :   AddHandler AppDomain.CurrentDomain.UnhandledException, AddressOf CatchAllHandler wich try to close the connection (in case of)
. We don’t have mutlithread connection as in this link (https://system.data.sqlite.org/index.html/tktview?name=ee2f11285e) -> so we did not modify WAL connection settings
             
       As soon as the the DB is corrupted we use this method to repair it :
       
       sqlite3 mydb.db
       sqlite> .mode insert
       sqlite> .output dump_all.sql
       sqlite> .dump
       sqlite> .exit
       
       move mydb.db mydb-corrupt.db
       
       sqlite3 mydb.db
       sqlite> .read dump_all.sql
       sqlite> .exit
       
With this method we can see what happend and we often have this kind of issue : Table XXX  already exists -> and indeed in the Dump_all.sql there is twice the : « CREATE TABLE  XXX » , this Table was created at runtime with
- DROP IF EXISTS XXX
- CREATE TABLE XXX
We tried a new method like this  (in production since yesterday -> no feedbacks yet)
- CREATE IF NOT EXISTS
- DELETE

As told before we also have twice CREATE TRIGGER issue in Corrupted DB.

DOES SOMEONE HAVE ANY IDEA how what is hapenning ? =)

(We are going to upload a corrupted DB on a ftp if needed and i can answer to any question while office hours (GMT+1))

2) we also like to know when the index concatenation feature has been released ? (to try with the oldest version of System.data.sqlite.dll compatible) (

3) An finally , Is there a way to get the old version of dll witout having to recompile the source code ?

Many Many Many thanks !!(we're getting nuts so help will be really great ^^)
Cheers
Regards

 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: TR: Database base disk is malformed issue

Joe Mistachkin-3

Jean-François RIGHI wrote:
>
> 1) We were working with an old version of System.data.sqlite.dll (1.0.66)
> wich was prety stable. But we needed to work with concat index (CREATE
> INDEX IDX ON TABLE_1 (FIELD1 || FIELD2) to upgrade performance. Since we
> changed the dll we often got a Database base disk is malformed issue. We
> can repair the base via an sql dump of the base. The issue itself is never
> exactly the same : we can have a duplicate  TRIGGER or TABLE or sometimes
> INSERT INTO with non valide data.
>

Are you able to share the full schema for the database?

--
Joe Mistachkin @ https://urn.to/r/mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users