Réparer une base de données SqLite corrompue

sqlite

Aujourd'hui j'ai eu un souci avec une base de données SqLite qui me sert dans une application DotNet.

En effet, lorsque j'ai voulu insérer des données dans ma base de données SqLite, j'ai eu un message d'erreur m'indiquant que la base de données était malformée:

The database disk image is malformed

Pour réparer la base de données SqLite j'ai dû vérifier l'intégrité des données via la commande:

sqlite3.exe ma_base_de_donnee.s3db
pragma integrity_check;

Ensuite, il y a deux possibilités:

  • soit vous n'avez aucune erreur et dans ce cas il faut quitter sqlite3 via la commande "quit"
  • soit vous avez une multitude d'erreur et dans ce cas vous devez sauver la base de données (dumper la base de données) et la recharger dans une nouvelle base de données).

Dans mon cas la commande de vérification de l'intégrité des données m'a retourné:

sqlite> pragma integrity_check;
*** in database main ***
On tree page 1337470 cell 6: Rowid 87261 out of order (min less than parent min
of 89285)
On tree page 1331605 cell 79: 2nd reference to page 1337470
On tree page 1331605 cell 79: Child page depth differs
On tree page 1331605 cell 80: Child page depth differs
On tree page 1236289 cell 21: 2nd reference to page 1337472
On tree page 1236289 cell 21: Child page depth differs
On tree page 1236289 cell 22: Child page depth differs
On tree page 1337509 cell 6: Rowid 572766 out of order (min less than parent max
 of 1183107)
On tree page 1337476 cell 6: Rowid 1016553 out of order (max larger than parent
max of 0)
On tree page 1337489 cell 5: Rowid 1016608 out of order (max larger than parent
max of 0)
On tree page 1337502 cell 6: Rowid 572731 out of order (min less than parent min
 of 1016608)
On tree page 1337502 cell 6: Rowid 572737 out of order (max larger than parent m
ax of 0)
On tree page 1335400 cell 53: 2nd reference to page 1337473
On tree page 1335400 cell 53: Child page depth differs
On tree page 1337486 cell 6: Rowid 1016595 out of order (max larger than parent
max of 0)
On tree page 1335400 cell 54: Child page depth differs
On tree page 1337499 cell 6: Rowid 572717 out of order (min less than parent min
 of 1016595)
On tree page 1337499 cell 6: Rowid 572723 out of order (max larger than parent m
ax of 0)
On tree page 1337513 cell 6: Rowid 1318489 out of order (max larger than parent
max of 0)
Fragmentation of 9 bytes reported as 0 on page 1336786
Fragmentation of 9 bytes reported as 0 on page 1337511
On tree page 1337521 cell 6: Rowid 1318537 out of order (max larger than parent
max of 0)
On tree page 100743 cell 16: 2nd reference to page 1337483
On tree page 100743 cell 16: Child page depth differs
On tree page 100743 cell 17: Child page depth differs
On tree page 1337504 cell 6: Rowid 572744 out of order (max larger than parent m
ax of 0)
On tree page 1264401 cell 16: 2nd reference to page 1337469
On tree page 1264401 cell 16: Child page depth differs
On tree page 1264401 cell 17: Child page depth differs
On tree page 1337524 cell 34: 2nd reference to page 1337513
On tree page 1337524 cell 34: Child page depth differs
On tree page 1337524 cell 35: Child page depth differs
On tree page 1337524 cell 37: 2nd reference to page 1337517
On tree page 1337524 cell 37: Child page depth differs
On tree page 1337524 cell 38: 2nd reference to page 1337518
On tree page 1337524 cell 39: Child page depth differs
On tree page 1337524 cell 41: 2nd reference to page 1337521
On tree page 1337524 cell 41: Child page depth differs
On tree page 1337524 cell 42: Child page depth differs
On tree page 1336559 cell 91: 2nd reference to page 1337499
On tree page 1336559 cell 91: Child page depth differs
On tree page 1336559 cell 92: Child page depth differs
On tree page 1336559 cell 93: 2nd reference to page 1337502
On tree page 1336559 cell 93: Child page depth differs
On tree page 1336559 cell 94: 2nd reference to page 1337504
On tree page 1336559 cell 95: Child page depth differs
On page 1336559 at right child: 2nd reference to page 1337509
On tree page 1338044 cell 1: 2nd reference to page 1337511
On tree page 1338044 cell 1: Child page depth differs
On tree page 1338044 cell 2: Child page depth differs
On tree page 1338875 cell 10: 2nd reference to page 1337500
On tree page 1338875 cell 10: Child page depth differs
On tree page 1338875 cell 11: Child page depth differs
On tree page 1337234 cell 56: 2nd reference to page 1337471
On tree page 1337234 cell 56: Child page depth differs
On tree page 1337234 cell 57: Child page depth differs
On tree page 1337234 cell 59: 2nd reference to page 1337476
On tree page 1337234 cell 59: Child page depth differs
On tree page 1337234 cell 60: Child page depth differs
On tree page 1337234 cell 65: 2nd reference to page 1337486
On tree page 1337234 cell 65: Child page depth differs
On tree page 1337234 cell 66: Child page depth differs
On tree page 1337234 cell 67: 2nd reference to page 1337489
On tree page 1337234 cell 67: Child page depth differs
On tree page 1337234 cell 68: Child page depth differs
On tree page 1318070 cell 2: 2nd reference to page 1337469
On tree page 1318070 cell 2: Child page depth differs
On tree page 1318070 cell 3: Child page depth differs
On page 1253912 at right child: 2nd reference to page 1337480
On tree page 1331569 cell 57: 2nd reference to page 1337471
On tree page 1331569 cell 57: Child page depth differs
On tree page 1331569 cell 58: 2nd reference to page 1337485
On page 1331569 at right child: 2nd reference to page 1337498
On tree page 1330087 cell 58: 2nd reference to page 1337472
On tree page 1330087 cell 58: Child page depth differs
On tree page 1330087 cell 59: 2nd reference to page 1337487
On page 1330087 at right child: 2nd reference to page 1337501
On tree page 1212001 cell 36: 2nd reference to page 1337494
On tree page 1212001 cell 36: Child page depth differs
On tree page 1212001 cell 37: Child page depth differs
On tree page 1263070 cell 32: 2nd reference to page 1337477
On tree page 1263070 cell 32: Child page depth differs
On tree page 1263070 cell 33: Child page depth differs
Page 1336479 is never used
Page 1336481 is never used

J'ai donc dû corriger la base de données en utilisant la commande:

sqlite3.exe ma_base_de_donnee.s3db .dump | sqlite3.exe nouvelle_bdd.s3db

Ce qui m'a créé un nouveau fichier de database nommé "nouvelle_bdd.s3db" qui contient l'ensemble de mes données non corrompu.

Voilà j'espère que cette astuce vous aidera :)

Leave a Reply

Your email address will not be published. Required fields are marked *