Hi,
We're experiencing strange database corruptions using SQLite database (we
are using it for over three years). We are reporting this now because one of
databases got corrupted on less sensitive data which we can give you
for analysis and because we wanted to reject all other possibilities of
error on our side, we also separated SQLite library from the rest of
application to exclude memory management problems. But when problem occurred in
our experimental project which was using .NET Compact Framework it was sure
that there is something not right.
First of all, we are using SQLite 3.6.23.1 on Pocket PC 2003 SE/Windows
Mobile 5.0 - 6.5 platform. SQLite is compiled using eVC 4.0 SP4. Described
situation occurs mostly on newer versions of OS, but it may be hardware
dependent, because we have few types of devices on which this problem did
not occur, but on most of available devices this problem happens, so this
problem can include other developers using Windows Mobile and SQlite.
We were unable to create tests that would trigger this issue, but it mostly
happens when we synchronize data between server and mobile device.
Synchronization downloads from server SQLite database with updated data and
copy that data using single query per table. If table is big, then we use
limit and offset to copy in chunks. Downloaded database has similar
structure and identical settings (encoding, page size, etc.) Query looks
like that:
REPLACE INTO "main"."ProductPriceList" (
"Product_Id",
"PriceList_Code",
"NettoVal",
"Deleted")
SELECT
"BigSync_TH_SYNC"."ProductPriceList"."Product_Id",
"BigSync_TH_SYNC"."ProductPriceList"."PriceList_Code",
"BigSync_TH_SYNC"."ProductPriceList"."NettoVal",
"BigSync_TH_SYNC"."ProductPriceList"."Deleted"
FROM "BigSync_TH_SYNC"."ProductPriceList" *LIMIT 16536 OFFSET 0*;
In this case we are positive that this corruption occurred while executing
this query, because at the moment only synchronization modifies that table.
We don't know whether limit offset was applied, because we don't know how
big change was in database. (If synchronization is successful we remove
incoming database). Database also did not report any error, until something
else referenced that table.
This issue mostly takes place on this query:
REPLACE INTO "main"."Clients" (
"Id_Client",
"Code",
"ShortName",
"Name",
"Street",
"No",
"City",
"Region_Id",
"Latitude",
"Longitude",
"GPSData",
"Modified",
"Deleted",
...)
SELECT
"BigSync_TH_SYNC"."Clients"."Id_Client",
"BigSync_TH_SYNC"."Clients"."Code",
"BigSync_TH_SYNC"."Clients"."ShortName",
"BigSync_TH_SYNC"."Clients"."Name",
"BigSync_TH_SYNC"."Clients"."Street",
"BigSync_TH_SYNC"."Clients"."No",
"BigSync_TH_SYNC"."Clients"."City",
"main"."Clients"."Region_Id",
"main"."Clients"."Latitude",
"main"."Clients"."Longitude",
"main"."Clients"."GPSData",
"main"."Clients"."Modified",
"BigSync_TH_SYNC"."Clients"."Deleted",
...
FROM "BigSync_TH_SYNC"."Clients"
LEFT OUTER JOIN "main"."Clients" ON
"main"."Clients"."Id_Client"="BigSync_TH_SYNC"."Clients"."Id_Client";
Database in attachment has corrupted index on table ProductPriceList. Data
in other tables was wiped, because those are real client
data. Fortunately this did not change output of SQLite analyzer and
integrity check. (also in attachment).
Hope that you will be able to resolve our issue. I hope that information I
provided will be useful. If you have any questions please feel free to ask.
Cheers
PS. I'm also curious if there is any faster way to transfer data between
databases. On desktop this is not a problem, but on mobile if table Clients
has 5000 rows replace into takes from 1 to 6 minutes, depending on processor
and flash speed.
Analyzing table AbsenceReasons...
Analyzing table Absences...
Analyzing table Addresses...
Analyzing table Cache_Clients...
Analyzing table Cache_Group...
Analyzing table Cache_Product_Filter...
Analyzing table Cache_Promotions_Products...
Analyzing table ClientCategories...
Analyzing table ClientGroupDiscounts...
Analyzing table ClientGroups...
Analyzing table ClientHardware...
Analyzing table ClientLicences...
Analyzing table ClientPackages...
Analyzing table ClientPrices...
Analyzing table ClientProductDiscounts...
Analyzing table ClientPromotions...
Analyzing table ClientSpecialPrices...
Analyzing table ClientStandards...
Analyzing table Client_Tasks...
Analyzing table Clients...
Analyzing table ClientsCatalog...
Analyzing table Corporations...
Analyzing table DocumentTypes...
Analyzing table FreebeProducts...
Analyzing table GasUsage...
Analyzing table Groups...
Analyzing table GroupsGroups...
Analyzing table Hardware...
Analyzing table HardwareTypes...
Analyzing table InvoiceHeads...
Analyzing table InvoicePositions...
Analyzing table InvoiceTypes...
Analyzing table KP...
Analyzing table LastOrdered...
Analyzing table Licences...
Analyzing table MarketingAnkieta...
Analyzing table MarketingElementy...
Analyzing table Messages...
Analyzing table MessagesUsers...
Analyzing table OrderHeads...
Analyzing table OrderPositions...
Analyzing table PackageTypes...
Analyzing table PackageUnits...
Analyzing table PaymentTypes...
Analyzing table PollAnswers...
Analyzing table PollHeaders...
Analyzing table PollQuestions...
Analyzing table PollResultHeaders...
Analyzing table PollResults...
Analyzing table Producers...
Analyzing table ProductDiscounts...
Analyzing table ProductPriceList...
Analyzing table Product_Departments...
Analyzing table Product_Group...
Analyzing table Product_Warehouse...
Analyzing table Products...
Analyzing table PromotionPrices...
Analyzing table Promotions...
Analyzing table Reasons...
Analyzing table Regions...
Analyzing table Replacements...
Analyzing table Report_ClientSales...
Analyzing table Report_ClientSales_FV...
Analyzing table Report_Groups...
Analyzing table Report_Groups_FV...
Analyzing table Report_Products...
Analyzing table Report_Products_FV...
Analyzing table Report_Targets...
Analyzing table Report_Targets_FV...
Analyzing table RestorationHeads...
Analyzing table RestorationPositions...
Analyzing table Routes...
Analyzing table Settings...
Analyzing table ShelfProducts...
Analyzing table ShelfStates...
Analyzing table StandardGroups...
Analyzing table StandardProducts...
Analyzing table Standards...
Analyzing table SyncStamps...
Analyzing table SyncStats...
Analyzing table TargetElements...
Analyzing table Targets...
Analyzing table TaskExecuted...
Analyzing table TaskGroups...
Analyzing table Tasks...
Analyzing table UserData...
Analyzing table Users...
Analyzing table UsersAddresses...
Analyzing table UsersClients...
Analyzing table UsersRegions...
Analyzing table UsersTargets...
Analyzing table UsersTasks...
Analyzing table Visits...
Analyzing table Warehouse...
Analyzing table Wholesales...
Analyzing table WholesalesClients...
Analyzing table sqlite_master...
Analyzing index idx_AbsenceReasons_Deleted of table AbsenceReasons...
Analyzing index sqlite_autoindex_AbsenceReasons_1 of table AbsenceReasons...
Analyzing index idx_Absences_Deleted of table Absences...
Analyzing index sqlite_autoindex_Absences_1 of table Absences...
Analyzing index idx_Addresses_Deleted of table Addresses...
Analyzing index sqlite_autoindex_Addresses_1 of table Addresses...
Analyzing index idx_Cache_Group_Group_Id of table Cache_Group...
Analyzing index sqlite_autoindex_Cache_Product_Filter_1 of table
Cache_Product_Filter...
Analyzing index idx_Cache_Promotions_Products_Search of table
Cache_Promotions_Products...
Analyzing index sqlite_autoindex_Cache_Promotions_Products_1 of table
Cache_Promotions_Products...
Analyzing index idx_ClientCategories_Deleted of table ClientCategories...
Analyzing index sqlite_autoindex_ClientCategories_1 of table ClientCategories...
Analyzing index idx_ClientGroupDiscounts_Deleted of table
ClientGroupDiscounts...
Analyzing index sqlite_autoindex_ClientGroupDiscounts_1 of table
ClientGroupDiscounts...
Analyzing index idx_ClientGroups_Deleted of table ClientGroups...
Analyzing index sqlite_autoindex_ClientGroups_1 of table ClientGroups...
Analyzing index idx_ClientHardware_Deleted of table ClientHardware...
Analyzing index idx_ClientHardware_Hardware_Id of table ClientHardware...
Analyzing index sqlite_autoindex_ClientHardware_1 of table ClientHardware...
Analyzing index idx_ClientLicences_Client_Id_Licence_Id_DateFrom_DateTo of
table ClientLicences...
Analyzing index idx_ClientLicences_DateTo of table ClientLicences...
Analyzing index idx_ClientLicences_Deleted of table ClientLicences...
Analyzing index sqlite_autoindex_ClientLicences_1 of table ClientLicences...
Analyzing index idx_ClientPackages_Client_Id of table ClientPackages...
Analyzing index sqlite_autoindex_ClientPackages_1 of table ClientPackages...
Analyzing index idx_ClientPrices_Deleted of table ClientPrices...
Analyzing index sqlite_autoindex_ClientPrices_1 of table ClientPrices...
Analyzing index idx_ClientProductDiscounts_Deleted of table
ClientProductDiscounts...
Analyzing index sqlite_autoindex_ClientProductDiscounts_1 of table
ClientProductDiscounts...
Analyzing index idx_ClientPromotions_Client_Id_Address_Id of table
ClientPromotions...
Analyzing index idx_ClientPromotions_Deleted of table ClientPromotions...
Analyzing index sqlite_autoindex_ClientPromotions_1 of table ClientPromotions...
Analyzing index idx_ClientSpecialPrices_Client_Id of table
ClientSpecialPrices...
Analyzing index idx_ClientSpecialPrices_Client_Id_Product_Id of table
ClientSpecialPrices...
Analyzing index idx_ClientSpecialPrices_Deleted of table ClientSpecialPrices...
Analyzing index idx_ClientSpecialPrices_Product_Id of table
ClientSpecialPrices...
Analyzing index sqlite_autoindex_ClientSpecialPrices_1 of table
ClientSpecialPrices...
Analyzing index idx_ClientStandards_Deleted of table ClientStandards...
Analyzing index sqlite_autoindex_ClientStandards_1 of table ClientStandards...
Analyzing index idx_Client_Tasks_Deleted of table Client_Tasks...
Analyzing index sqlite_autoindex_Client_Tasks_1 of table Client_Tasks...
Analyzing index idx_Clients_ClientGroup_Id of table Clients...
Analyzing index idx_Clients_Name of table Clients...
Analyzing index idx_Clients_ShortName of table Clients...
Analyzing index sqlite_autoindex_Clients_1 of table Clients...
Analyzing index sqlite_autoindex_ClientsCatalog_1 of table ClientsCatalog...
Analyzing index idx_Corporations_Deleted of table Corporations...
Analyzing index sqlite_autoindex_Corporations_1 of table Corporations...
Analyzing index idx_DocumentTypes_Deleted of table DocumentTypes...
Analyzing index sqlite_autoindex_DocumentTypes_1 of table DocumentTypes...
Analyzing index idx_FreebeProducts_Deleted of table FreebeProducts...
Analyzing index sqlite_autoindex_FreebeProducts_1 of table FreebeProducts...
Analyzing index idx_GasUsage_Deleted of table GasUsage...
Analyzing index sqlite_autoindex_GasUsage_1 of table GasUsage...
Analyzing index idx_Groups_Asort of table Groups...
Analyzing index idx_Groups_Deleted of table Groups...
Analyzing index idx_Groups_Order of table Groups...
Analyzing index sqlite_autoindex_Groups_1 of table Groups...
Analyzing index idx_GroupsGroups_Deleted of table GroupsGroups...
Analyzing index idx_GroupsGroups_Group_Id of table GroupsGroups...
Analyzing index idx_GroupsGroups_Id_Group of table GroupsGroups...
Analyzing index sqlite_autoindex_GroupsGroups_1 of table GroupsGroups...
Analyzing index idx_Hardware_Deleted of table Hardware...
Analyzing index sqlite_autoindex_Hardware_1 of table Hardware...
Analyzing index idx_HardwareTypes_Deleted of table HardwareTypes...
Analyzing index sqlite_autoindex_HardwareTypes_1 of table HardwareTypes...
Analyzing index idx_InvoiceHeads_Address_Id of table InvoiceHeads...
Analyzing index idx_InvoiceHeads_Client_Id of table InvoiceHeads...
Analyzing index idx_InvoiceHeads_Deleted of table InvoiceHeads...
Analyzing index idx_InvoiceHeads_InvoiceTypes_Id of table InvoiceHeads...
Analyzing index sqlite_autoindex_InvoiceHeads_1 of table InvoiceHeads...
Analyzing index idx_InvoicePositions_Deleted of table InvoicePositions...
Analyzing index sqlite_autoindex_InvoicePositions_1 of table InvoicePositions...
Analyzing index idx_InvoiceTypes_Deleted of table InvoiceTypes...
Analyzing index sqlite_autoindex_InvoiceTypes_1 of table InvoiceTypes...
Analyzing index idx_KP_Deleted of table KP...
Analyzing index idx_KP_InvoiceHead_Id of table KP...
Analyzing index sqlite_autoindex_KP_1 of table KP...
Analyzing index idx_LastOrdered_Client_Id of table LastOrdered...
Analyzing index idx_LastOrdered_Deleted of table LastOrdered...
Analyzing index idx_LastOrdered_Product_Id_Client_Id of table LastOrdered...
Analyzing index sqlite_autoindex_LastOrdered_1 of table LastOrdered...
Analyzing index idx_Licences_Deleted of table Licences...
Analyzing index sqlite_autoindex_Licences_1 of table Licences...
Analyzing index idx_MarketingAnkieta_Deleted of table MarketingAnkieta...
Analyzing index sqlite_autoindex_MarketingAnkieta_1 of table MarketingAnkieta...
Analyzing index idx_MarketingElementy_Deleted of table MarketingElementy...
Analyzing index sqlite_autoindex_MarketingElementy_1 of table
MarketingElementy...
Analyzing index idx_Messages_Deleted of table Messages...
Analyzing index idx_Messages_User_Id of table Messages...
Analyzing index sqlite_autoindex_Messages_1 of table Messages...
Analyzing index idx_MessagesUsers_Deleted of table MessagesUsers...
Analyzing index idx_MessagesUsers_User_Id of table MessagesUsers...
Analyzing index sqlite_autoindex_MessagesUsers_1 of table MessagesUsers...
Analyzing index idx_OrderHeads_Approved of table OrderHeads...
Analyzing index idx_OrderHeads_Client_Id of table OrderHeads...
Analyzing index idx_OrderHeads_Deleted of table OrderHeads...
Analyzing index idx_OrderHeads_Modified of table OrderHeads...
Analyzing index idx_OrderHeads_OrderDate of table OrderHeads...
Analyzing index idx_OrderHeads_Visit_Id of table OrderHeads...
Analyzing index sqlite_autoindex_OrderHeads_1 of table OrderHeads...
Analyzing index idx_OrderPositions_Modified of table OrderPositions...
Analyzing index idx_OrderPositions_OrderHead_Id of table OrderPositions...
Analyzing index sqlite_autoindex_OrderPositions_1 of table OrderPositions...
Analyzing index idx_PackageTypes_Deleted of table PackageTypes...
Analyzing index sqlite_autoindex_PackageTypes_1 of table PackageTypes...
Analyzing index idx_PackageUnits_Deleted of table PackageUnits...
Analyzing index sqlite_autoindex_PackageUnits_1 of table PackageUnits...
Analyzing index idx_PaymentTypes_Deleted of table PaymentTypes...
Analyzing index sqlite_autoindex_PaymentTypes_1 of table PaymentTypes...
Analyzing index idx_PollAnswers_Deleted of table PollAnswers...
Analyzing index idx_PollAnswers_PollQuestion_Id of table PollAnswers...
Analyzing index sqlite_autoindex_PollAnswers_1 of table PollAnswers...
Analyzing index idx_PollHeaders_Deleted of table PollHeaders...
Analyzing index sqlite_autoindex_PollHeaders_1 of table PollHeaders...
Analyzing index idx_PollQuestions_Deleted of table PollQuestions...
Analyzing index idx_PollQuestions_PollHeader_Id of table PollQuestions...
Analyzing index idx_PollResultHeaders_PollHeader_Id of table PollQuestions...
Analyzing index sqlite_autoindex_PollQuestions_1 of table PollQuestions...
Analyzing index idx_PollResultHeaders_Deleted of table PollResultHeaders...
Analyzing index sqlite_autoindex_PollResultHeaders_1 of table
PollResultHeaders...
Analyzing index idx_PollResults_Deleted of table PollResults...
Analyzing index idx_PollResults_PollQuestion_Id of table PollResults...
Analyzing index idx_PollResults_PollResultHeader_Id of table PollResults...
Analyzing index sqlite_autoindex_PollResults_1 of table PollResults...
Analyzing index idx_Producers_Deleted of table Producers...
Analyzing index sqlite_autoindex_Producers_1 of table Producers...
Analyzing index idx_ProductDiscounts_Deleted of table ProductDiscounts...
Analyzing index sqlite_autoindex_ProductDiscounts_1 of table ProductDiscounts...
Analyzing index idx_ProductPriceList_Deleted of table ProductPriceList...
Analyzing index idx_ProductPriceList_PriceListCode of table ProductPriceList...
Analyzing index idx_ProductPriceList_PriceListCode_ProductId of table
ProductPriceList...
Analyzing index idx_ProductPriceList_ProductId of table ProductPriceList...
ERROR: SQLITE_CORRUPT
SQLITE_CORRUPT
while executing
"btree_next $csr"
("foreach" body line 29)
invoked from within
"foreach {name tbl_name rootpage} [db eval $sql] {
puts stderr "Analyzing index $name of table $tbl_name..."
# Code below traverses the index bein..."
*** in database main ***
On tree page 7825 cell 77: Child page depth differs
On tree page 7825 cell 78: Child page depth differs
On tree page 6496 cell 13: Rowid 1296 out of order (max larger than parent max
of 0)
On tree page 273 cell 1: Child page depth differs
On tree page 6481 cell 16: Rowid 49853 out of order (max larger than parent max
of 0)
On tree page 6488 cell 13: Rowid 1296 out of order (max larger than parent max
of 0)
On tree page 6489 cell 16: Rowid 49853 out of order (max larger than parent max
of 0)
On tree page 8865 cell 22: Rowid 51555 out of order (max larger than parent max
of 0)
Page 8512: btreeInitPage() returns error code 11
On tree page 8930 cell 13: Child page depth differs
On tree page 8930 cell 14: Child page depth differs
Page 8511: btreeInitPage() returns error code 11
On tree page 9461 cell 22: Child page depth differs
On tree page 9461 cell 23: Child page depth differs
On tree page 8861 cell 0: 2nd reference to page 7672
On tree page 8861 cell 1: 2nd reference to page 7900
On tree page 8861 cell 2: 2nd reference to page 9861
On tree page 8861 cell 3: 2nd reference to page 9125
On tree page 8861 cell 4: 2nd reference to page 6742
On tree page 8861 cell 5: 2nd reference to page 9623
On tree page 8861 cell 6: 2nd reference to page 8065
On tree page 8861 cell 7: 2nd reference to page 8364
On tree page 8861 cell 8: 2nd reference to page 10031
On tree page 8861 cell 9: 2nd reference to page 9868
On tree page 8861 cell 10: 2nd reference to page 8608
On tree page 8861 cell 11: 2nd reference to page 7674
On tree page 8861 cell 12: 2nd reference to page 7746
On tree page 8861 cell 13: 2nd reference to page 9642
On tree page 8861 cell 14: 2nd reference to page 9086
On tree page 8861 cell 15: 2nd reference to page 6747
On tree page 8861 cell 16: 2nd reference to page 6775
On tree page 8861 cell 17: 2nd reference to page 9678
On tree page 8861 cell 18: 2nd reference to page 7892
On tree page 8861 cell 19: 2nd reference to page 7920
On tree page 8861 cell 20: 2nd reference to page 8860
On tree page 8861 cell 21: 2nd reference to page 6789
On tree page 8861 cell 22: 2nd reference to page 8196
On tree page 8861 cell 23: 2nd reference to page 9817
On tree page 8861 cell 24: 2nd reference to page 8528
On tree page 8861 cell 25: 2nd reference to page 6797
On tree page 8861 cell 26: 2nd reference to page 8563
On tree page 8861 cell 27: 2nd reference to page 9277
On tree page 8861 cell 28: 2nd reference to page 9690
On tree page 8861 cell 29: 2nd reference to page 6809
On tree page 8861 cell 30: 2nd reference to page 8956
On tree page 8861 cell 31: 2nd reference to page 6872
On tree page 8861 cell 32: 2nd reference to page 6897
On tree page 8861 cell 33: 2nd reference to page 6898
On tree page 8861 cell 34: 2nd reference to page 9359
On tree page 8861 cell 35: 2nd reference to page 9975
On tree page 8861 cell 36: 2nd reference to page 6910
On tree page 8861 cell 37: 2nd reference to page 8251
On tree page 8861 cell 38: 2nd reference to page 8827
On tree page 8861 cell 39: 2nd reference to page 8331
On tree page 8861 cell 40: 2nd reference to page 8531
On tree page 8861 cell 41: 2nd reference to page 9591
On tree page 8861 cell 42: 2nd reference to page 10077
On page 8861 at right child: 2nd reference to page 8883
On tree page 270 cell 13: Child page depth differs
On tree page 270 cell 14: Child page depth differs
On tree page 6480 cell 13: Rowid 1296 out of order (max larger than parent max
of 0)
Page 6338 is never used
Page 6339 is never used
Page 6344 is never used
Page 6353 is never used
Page 6357 is never used
Page 6362 is never used
Page 6368 is never used
Page 6372 is never used
Page 6378 is never used
Page 6380 is never used
Page 6387 is never used
Page 6394 is never used
Page 6396 is never used
Page 6453 is never used
Page 7279 is never used
Page 7471 is never used
Page 7566 is never used
Page 7638 is never used
Page 7801 is never used
Page 7883 is never used
Page 8335 is never used
Page 8492 is never used
Page 8722 is never used
Page 8725 is never used
Page 8978 is never used
Page 9150 is never used
Page 9371 is never used
Page 9441 is never used
Page 9489 is never used
Page 9538 is never used
Page 9903 is never used
Page 10034 is never used
Page 10074 is never used
Page 10180 is never used
Page 17184 is never used
rowid 439 missing from index idx_ProductPriceList_PriceListCode_ProductId
rowid 439 missing from index sqlite_autoindex_ProductPriceList_1
Error: database disk image is malformed
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
