Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread Simon Slavin 

On 22 Sep 2011, at 6:28pm, neelakanta reddy wrote:

> The following is the result od pragma integrity check:
> 
> sqlite> PRAGMA integrity_check;
> *** in database main ***
> Main freelist: 38 of 38 pages missing from overflow list starting at 0
> On tree page 608 cell 13: Rowid 1309 out of order (min less than parent min
> of 1315)
> Page 943 is never used

Are you using any PRAGMAs in apps which manipulate the database ?  For instance 
'PRAGMA synchronous = 0'.

Are there any crashes or power-losses involved in getting the database to this 
stage ?

Are you checking the result values returned by every SQLite operation, from the 
time you create the database to the point where 'PRAGMA integrity_check' shows 
something wrong ?

If you do the same operations with the SQLite shell tool, do you get the same 
errors at the same point ?  You can do this by simply putting all the commands 
in a text file and using '.read SQLCommands.txt' in the SQLite shell tool.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread neelakanta reddy 
The following is the result od pragma integrity check:

sqlite> PRAGMA integrity_check;
*** in database main ***
Main freelist: 38 of 38 pages missing from overflow list starting at 0
On tree page 608 cell 13: Rowid 1309 out of order (min less than parent min
of 1315)
Page 943 is never used
Page 944 is never used
Page 945 is never used
Page 946 is never used
Page 947 is never used
Page 948 is never used
Page 949 is never used
Page 950 is never used
Page 951 is never used
Page 952 is never used
Page 953 is never used
Page 954 is never used
Page 955 is never used
Page 956 is never used
Page 957 is never used
Page 958 is never used
Page 959 is never used
Page 960 is never used
Page 961 is never used
Page 962 is never used
Page 963 is never used
Page 964 is never used
Page 965 is never used
Page 966 is never used
Page 967 is never used
Page 968 is never used
Page 970 is never used
Page 971 is never used
Page 972 is never used
Page 973 is never used
Page 974 is never used
Page 975 is never used
Page 976 is never used
Page 977 is never used
Page 978 is never used
Page 979 is never used
Page 980 is never used
Page 981 is never used
Page 982 is never used
Page 983 is never used
Page 984 is never used
Page 985 is never used
rowid 1309 missing from index objects_idx
rowid 1310 missing from index objects_idx
rowid 1311 missing from index objects_idx
rowid 1312 missing from index objects_idx
rowid 1313 missing from index objects_idx
rowid 1314 missing from index objects_idx
rowid 1315 missing from index objects_idx
rowid 1316 missing from index objects_idx
rowid 1317 missing from index objects_idx
rowid 1318 missing from index objects_idx
rowid 1319 missing from index objects_idx
rowid 1320 missing from index objects_idx
rowid 1321 missing from index objects_idx
rowid 1322 missing from index objects_idx
wrong # of entries in index objects_idx
Error: database disk image is malformed
sqlite>

Neelakanta
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread Richard Hipp 
On Thu, Sep 22, 2011 at 11:55 AM, neelakanta reddy <
reddy.neelaka...@gmail.com> wrote:

> The following is observed
>

What does this show:

PRAGMA integrity_check;

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread neelakanta reddy 
The following is observed

sqlite> SELECT * FROM objects WHERE obj_id = 1309;
1309|155|entryId=62,tableId=CPGMembersTable,CSId=1|0
sqlite>

If i try to select it from different column here i can see two rows having
same obj_ids

sqlite> SELECT * FROM objects WHERE class_id=155;
1086|155|entryId=0,tableId=CPIdTable,CSId=1|0
1087|155|entryId=1,tableId=CPGMembersTable,CSId=1|0
1088|155|entryId=1,tableId=CPGNamesTable,CSId=1|0
1089|155|entryId=1,tableId=CPIdTable,CSId=1|0
1090|155|entryId=1,tableId=HWCTable,CSId=1|0
1091|155|entryId=1,tableId=NETable,CSId=1|0
1092|155|entryId=10,tableId=CPGMembersTable,CSId=1|0
1093|155|entryId=10,tableId=CPGNamesTable,CSId=1|0
1094|155|entryId=10,tableId=CPIdTable,CSId=1|0
1095|155|entryId=10,tableId=HWCTable,CSId=1|0
1096|155|entryId=100,tableId=CPGMembersTable,CSId=1|0
1097|155|entryId=1001,tableId=CPIdTable,CSId=1|0
1098|155|entryId=101,tableId=CPGMembersTable,CSId=1|0
1099|155|entryId=102,tableId=CPGMembersTable,CSId=1|0
1100|155|entryId=103,tableId=CPGMembersTable,CSId=1|0
1101|155|entryId=104,tableId=CPGMembersTable,CSId=1|0
1102|155|entryId=105,tableId=CPGMembersTable,CSId=1|0
1103|155|entryId=106,tableId=CPGMembersTable,CSId=1|0
1104|155|entryId=107,tableId=CPGMembersTable,CSId=1|0
1105|155|entryId=108,tableId=CPGMembersTable,CSId=1|0
1106|155|entryId=109,tableId=CPGMembersTable,CSId=1|0
1107|155|entryId=11,tableId=CPGMembersTable,CSId=1|0
1108|155|entryId=11,tableId=CPGNamesTable,CSId=1|0
1109|155|entryId=11,tableId=CPIdTable,CSId=1|0
1110|155|entryId=11,tableId=HWCTable,CSId=1|0
|155|entryId=110,tableId=CPGMembersTable,CSId=1|0
1112|155|entryId=111,tableId=CPGMembersTable,CSId=1|0
1113|155|entryId=112,tableId=CPGMembersTable,CSId=1|0
1114|155|entryId=113,tableId=CPGMembersTable,CSId=1|0
1115|155|entryId=114,tableId=CPGMembersTable,CSId=1|0
1116|155|entryId=115,tableId=CPGMembersTable,CSId=1|0
1117|155|entryId=116,tableId=CPGMembersTable,CSId=1|0
1118|155|entryId=117,tableId=CPGMembersTable,CSId=1|0
1119|155|entryId=118,tableId=CPGMembersTable,CSId=1|0
1120|155|entryId=119,tableId=CPGMembersTable,CSId=1|0
1121|155|entryId=12,tableId=CPGMembersTable,CSId=1|0
1122|155|entryId=12,tableId=CPGNamesTable,CSId=1|0
1123|155|entryId=12,tableId=CPIdTable,CSId=1|0
1124|155|entryId=120,tableId=CPGMembersTable,CSId=1|0
1125|155|entryId=121,tableId=CPGMembersTable,CSId=1|0
1126|155|entryId=122,tableId=CPGMembersTable,CSId=1|0
1127|155|entryId=123,tableId=CPGMembersTable,CSId=1|0
1128|155|entryId=124,tableId=CPGMembersTable,CSId=1|0
1129|155|entryId=125,tableId=CPGMembersTable,CSId=1|0
1130|155|entryId=126,tableId=CPGMembersTable,CSId=1|0
1131|155|entryId=127,tableId=CPGMembersTable,CSId=1|0
1132|155|entryId=128,tableId=CPGMembersTable,CSId=1|0
1133|155|entryId=129,tableId=CPGMembersTable,CSId=1|0
1134|155|entryId=13,tableId=CPGMembersTable,CSId=1|0
1135|155|entryId=13,tableId=CPGNamesTable,CSId=1|0
1136|155|entryId=13,tableId=CPIdTable,CSId=1|0
1137|155|entryId=130,tableId=CPGMembersTable,CSId=1|0
1138|155|entryId=131,tableId=CPGMembersTable,CSId=1|0
1139|155|entryId=132,tableId=CPGMembersTable,CSId=1|0
1140|155|entryId=133,tableId=CPGMembersTable,CSId=1|0
1141|155|entryId=134,tableId=CPGMembersTable,CSId=1|0
1142|155|entryId=135,tableId=CPGMembersTable,CSId=1|0
1143|155|entryId=136,tableId=CPGMembersTable,CSId=1|0
1144|155|entryId=137,tableId=CPGMembersTable,CSId=1|0
1145|155|entryId=138,tableId=CPGMembersTable,CSId=1|0
1146|155|entryId=139,tableId=CPGMembersTable,CSId=1|0
1147|155|entryId=14,tableId=CPGMembersTable,CSId=1|0
1148|155|entryId=14,tableId=CPGNamesTable,CSId=1|0
1149|155|entryId=14,tableId=CPIdTable,CSId=1|0
1150|155|entryId=140,tableId=CPGMembersTable,CSId=1|0
1151|155|entryId=141,tableId=CPGMembersTable,CSId=1|0
1152|155|entryId=142,tableId=CPGMembersTable,CSId=1|0
1153|155|entryId=143,tableId=CPGMembersTable,CSId=1|0
1154|155|entryId=144,tableId=CPGMembersTable,CSId=1|0
1155|155|entryId=145,tableId=CPGMembersTable,CSId=1|0
1156|155|entryId=146,tableId=CPGMembersTable,CSId=1|0
1157|155|entryId=147,tableId=CPGMembersTable,CSId=1|0
1158|155|entryId=148,tableId=CPGMembersTable,CSId=1|0
1159|155|entryId=149,tableId=CPGMembersTable,CSId=1|0
1160|155|entryId=15,tableId=CPGMembersTable,CSId=1|0
1161|155|entryId=15,tableId=CPGNamesTable,CSId=1|0
1162|155|entryId=15,tableId=CPIdTable,CSId=1|0
1163|155|entryId=150,tableId=CPGMembersTable,CSId=1|0
1164|155|entryId=151,tableId=CPGMembersTable,CSId=1|0
1165|155|entryId=152,tableId=CPGMembersTable,CSId=1|0
1166|155|entryId=153,tableId=CPGMembersTable,CSId=1|0
1167|155|entryId=154,tableId=CPGMembersTable,CSId=1|0
1168|155|entryId=155,tableId=CPGMembersTable,CSId=1|0
1169|155|entryId=156,tableId=CPGMembersTable,CSId=1|0
1170|155|entryId=157,tableId=CPGMembersTable,CSId=1|0
1171|155|entryId=158,tableId=CPGMembersTable,CSId=1|0
1172|155|entryId=159,tableId=CPGMembersTable,CSId=1|0
1173|155|entryId=16,tableId=CPGMembersTable,CSId=1|0
1174|155|entryId=16,tableId=CPGNamesTable,CSId=1|0
1175|155|entryId=16,tableId=CPIdTable,CSId=1|0

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread Simon Slavin 

On 22 Sep 2011, at 1:26pm, neelakanta reddy wrote:

> Here the obj_id which is primary is having duplicate primary value entries
> 
> INSERT INTO "objects"
> VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0);
> 
> can some one please tell me why the primary key is coming twice

How do you know that the second INSERT statement is working ?  Please show the 
result of

SELECT * FROM objects WHERE obj_id = 1309

Also, I tested it myself:

SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE objects (obj_id integer primary key, class_id integer, dn 
text,
   ...> last_ccb integer);
sqlite> INSERT INTO "objects"
   ...> VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0);
sqlite> INSERT INTO "objects"
   ...> VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0);
Error: PRIMARY KEY must be unique

Cannot reproduce using version 3.7.5.  Please try using just those three 
statements so we can tell what kind of bug you have.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread neelakanta reddy 
here the Duplication means the object table obj_id which is a primary key i
duplicated twice

Here my question is for primary key (obj_id) , we are getting two entries
for the same primary id which must be unique

since the database is large i am pasting the db bump output


CREATE TABLE objects (obj_id integer primary key, class_id integer, dn text,
last_ccb integer);
INSERT INTO "objects"
VALUES(1,127,'APG43L_DDISKPATH=ACS_ACA_DATA,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(2,127,'APG43L_DDISKPATH=ACS_ALEC_DATA,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(3,127,'APG43L_DDISKPATH=ACS_CHB_DATA,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(4,127,'APG43L_DDISKPATH=ACS_DATA,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(5,127,'APG43L_DDISKPATH=ACS_DATA_BASE,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(6,127,'APG43L_DDISKPATH=ACS_LOGS,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(7,127,'APG43L_DDISKPATH=ACS_LOGS_BASE,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(8,127,'APG43L_DDISKPATH=ACS_MTZ_DATA,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(9,127,'APG43L_DDISKPATH=ACS_RTR_DATA,safApp=safImmService',0);
  |
  |
  |


INSERT INTO "objects"
VALUES(1304,155,'entryId=6,tableId=CPIdTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1305,155,'entryId=6,tableId=HWCTable,CSId=1',0);
INSERT INTO "objects" VALUES(1306,155,'entryId=6,tableId=NETable,CSId=1',0);
INSERT INTO "objects"
VALUES(1307,155,'entryId=60,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1308,155,'entryId=61,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1310,155,'entryId=63,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1311,155,'entryId=64,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1312,155,'entryId=65,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1313,155,'entryId=66,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1314,155,'entryId=67,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1315,155,'entryId=68,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1310,118,'externalMediaFunctionMId=1,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(1311,115,'fileOutputId=1,statisticalCounterMId=1,safApp=safImmService',0);
INSERT INTO "objects"
VALUES(1312,114,'filterId=1,ldapId=1,ldapAuthenticationMethodId=1',0);


Here the obj_id which is primary is having duplicate primary value entries

INSERT INTO "objects"
VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0);
INSERT INTO "objects"
VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0);

can some one please tell me why the primary key is coming twice

-Neel.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread Stephan Beal 
On Thu, Sep 22, 2011 at 2:04 PM, Jay A. Kreibich  wrote:

>  The PK (first column).  The CREATE TABLE showed it as an INTEGER
>  PRIMARY KEY


Ah, indeed. i skipped that and looked at the unique index :/.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread Jay A. Kreibich 
On Thu, Sep 22, 2011 at 01:46:25PM +0200, Stephan Beal scratched on the wall:
> On Thu, Sep 22, 2011 at 1:37 PM, neelakanta reddy <
> reddy.neelaka...@gmail.com> wrote:
> 
> > INSERT INTO "objects"
> > VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0);
> > <===

> > INSERT INTO "objects"
> > VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0);
> > <==

> Where are they duplicated? The output you provide does not show any
> duplicates (all have a unique 3rd column value).

  The PK (first column).  The CREATE TABLE showed it as an INTEGER
  PRIMARY KEY, meaning that, if this happened as shown, the database
  would be corrupt.

> i don't see any dupes in your inserts and you didn't present any 
> SELECT data which shows duplicates.

  Yes, please.  Frequently when questions like this come up, the
  SELECTs don't actually show a problem.

  How are you inserting these?  Your own code?  Are you carefully
  checking all return codes?  Do you actually know the dups are ending
  up in the database?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

2011-09-22 Thread Stephan Beal 
On Thu, Sep 22, 2011 at 1:37 PM, neelakanta reddy <
reddy.neelaka...@gmail.com> wrote:

> INSERT INTO "objects"
> VALUES(1307,155,'entryId=60,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1308,155,'entryId=61,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0);
> <===
> INSERT INTO "objects"
> VALUES(1310,155,'entryId=63,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1311,155,'entryId=64,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1312,155,'entryId=65,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1313,155,'entryId=66,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1314,155,'entryId=67,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1315,155,'entryId=68,tableId=CPGMembersTable,CSId=1',0);
> INSERT INTO "objects"
> VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0);
> <==
> INSERT INTO "objects"
> VALUES(1310,118,'externalMediaFunctionMId=1,safApp=safImmService',0);
> INSERT INTO "objects"
>
> VALUES(1311,115,'fileOutputId=1,statisticalCounterMId=1,safApp=safImmService',0);
> INSERT INTO "objects"
> VALUES(1312,114,'filterId=1,ldapId=1,ldapAuthenticationMethodId=1',0);
> INSERT INTO "objects" VALUES(1313,111,'fmAlarmModelId=CW,fmId=1',0);
>
>
> The following entries pointed by the arrow are duplicated.
>

Where are they duplicated? The output you provide does not show any
duplicates (all have a unique 3rd column value).

 ...

> Here the dn name is unique but still i am seeing duplicate dn names.
>

Please show us the duplicates.

i don't see any dupes in your inserts and you didn't present any SELECT data
which shows duplicates.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users