Re: [sqlite] sqlite3 delete does not delete everything?
On Mar 11, 2010, at 12:18 PM, Skand wrote: >> >> Run REINDEX on your database. >> >> Get SQLite 3.6.23 and use that in place of 3.3.6 moving forward. >> > > REINDEX throws following error: > > SQL error: indexed columns are not unique > > Is there a graceful way to fix this? Run sqlite3 olddatabase >file.txt Then edit file.txt to remove the duplicate entries. Then: sqlite3 newdatabase Can you hypothesize the cause for this > error given that the schema specifies that the primary key consists > of all 3 > columns in the database? http://www.sqlite.org/lockingv3.html#how_to_corrupt D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
D. Richard Hipp wrote: > > > On Mar 11, 2010, at 11:42 AM, Skand wrote: > >> >> >> D. Richard Hipp wrote: >>> >>> >>> On Mar 11, 2010, at 11:13 AM, Skand wrote: >>> >>> sqlite> select count(*) from ip_domain_table where ttl < 99 ; 1605343 >>> >>> What does "PRAGMA integrity_check" show you at this point? >>> >>> >> >> The integrity check shows 395 lines similar to: >> "rowid 16422938 missing from index sqlite_autoindex_ip_domain_table_1" >> >> What does it mean? How can I fix this? I am running sqlite3 version >> 3.3.6. > > > > Run REINDEX on your database. > > Get SQLite 3.6.23 and use that in place of 3.3.6 moving forward. > REINDEX throws following error: SQL error: indexed columns are not unique Is there a graceful way to fix this? Can you hypothesize the cause for this error given that the schema specifies that the primary key consists of all 3 columns in the database? Thanks a lot. -- View this message in context: http://old.nabble.com/sqlite3-delete-does-not-delete-everything--tp27865654p27866565.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
On Mar 11, 2010, at 11:42 AM, Skand wrote: > > > D. Richard Hipp wrote: >> >> >> On Mar 11, 2010, at 11:13 AM, Skand wrote: >> >> >>> >>> sqlite> select count(*) from ip_domain_table where ttl < >>> 99 ; >>> >>> 1605343 >> >> What does "PRAGMA integrity_check" show you at this point? >> >> > > The integrity check shows 395 lines similar to: > "rowid 16422938 missing from index sqlite_autoindex_ip_domain_table_1" > > What does it mean? How can I fix this? I am running sqlite3 version > 3.3.6. Run REINDEX on your database. Get SQLite 3.6.23 and use that in place of 3.3.6 moving forward. > > Another followup question: Subsequent deletes after the first one, > keeps > removing partial entries with every run. > > sqlite> pragma cache_size=10; delete from ip_domain_table where > ttl < > 99; > sqlite> select count(*) from ip_domain_table where ttl < 99; > 258 > sqlite> pragma cache_size=10; delete from ip_domain_table where > ttl < > 99; > sqlite> select count(*) from ip_domain_table where ttl < 99; > 142 > > -- > View this message in context: > http://old.nabble.com/sqlite3-delete-does-not-delete-everything--tp27865654p27866031.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
On 11 Mar 2010, at 4:44pm, Skand wrote: > sqlite> SELECT ttl,typeof(ttl) FROM ip_domain_table WHERE ttl < 99 > LIMIT 10 > ...> ; > 1266895620|integer Okay, that's not the problem, but your response to Richard's post does betray the problem and should attract a useful response. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
Simon Slavin-3 wrote: > > > >> sqlite> select * from ip_domain_table where ttl < 99 limit 1; >> 107.35.138.41|127.2.0.2|1266895619 > > Although you have defined your column type as INTEGER, it's possible that > you have some values in that column which are of other types. Even the > record you show may have been put into the file with '1266895619' > representing a TEXT value. I believe that a text value would not satisfy > your WHERE clause in a consistent manner. > > Can you try something like > > SELECT ttl,typeof(ttl) FROM ip_domain_table WHERE ttl < 99 LIMIT > 10 > > sqlite> SELECT ttl,typeof(ttl) FROM ip_domain_table WHERE ttl < 99 LIMIT 10 ...> ; 1266895620|integer 1266895620|integer 1266895620|integer 1266895639|integer 1266895639|integer 1266895638|integer 1266895638|integer 1266895653|integer 1266895654|integer 1266895653|integer -- View this message in context: http://old.nabble.com/sqlite3-delete-does-not-delete-everything--tp27865654p27866048.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
D. Richard Hipp wrote: > > > On Mar 11, 2010, at 11:13 AM, Skand wrote: > > >> >> sqlite> select count(*) from ip_domain_table where ttl < 99 ; >> >> 1605343 > > What does "PRAGMA integrity_check" show you at this point? > > The integrity check shows 395 lines similar to: "rowid 16422938 missing from index sqlite_autoindex_ip_domain_table_1" What does it mean? How can I fix this? I am running sqlite3 version 3.3.6. Another followup question: Subsequent deletes after the first one, keeps removing partial entries with every run. sqlite> pragma cache_size=10; delete from ip_domain_table where ttl < 99; sqlite> select count(*) from ip_domain_table where ttl < 99; 258 sqlite> pragma cache_size=10; delete from ip_domain_table where ttl < 99; sqlite> select count(*) from ip_domain_table where ttl < 99; 142 -- View this message in context: http://old.nabble.com/sqlite3-delete-does-not-delete-everything--tp27865654p27866031.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
On 11 Mar 2010, at 4:13pm, Skand wrote: > sqlite> select count(*) from ip_domain_table where ttl < 99 ; > > 258 > > sqlite> select * from ip_domain_table where ttl < 99 limit 1; > 107.35.138.41|127.2.0.2|1266895619 Although you have defined your column type as INTEGER, it's possible that you have some values in that column which are of other types. Even the record you show may have been put into the file with '1266895619' representing a TEXT value. I believe that a text value would not satisfy your WHERE clause in a consistent manner. Can you try something like SELECT ttl,typeof(ttl) FROM ip_domain_table WHERE ttl < 99 LIMIT 10 Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
On Mar 11, 2010, at 11:13 AM, Skand wrote: > > Hi Folks, > > Whats going on here? I would expect the following delete to delete > everything under 99. > > sqlite> .schema > > CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl > INTEGER, > PRIMARY KEY(ip_domain, answer, ttl)); > > sqlite> select count(*) from ip_domain_table where ttl < 99 ; > > 1605343 What does "PRAGMA integrity_check" show you at this point? > > sqlite> pragma cache_size=10; delete from ip_domain_table where > ttl < > 99; > > sqlite> select count(*) from ip_domain_table where ttl < 99 ; > > 258 > > sqlite> select * from ip_domain_table where ttl < 99 limit 1; > 107.35.138.41|127.2.0.2|1266895619 > > The first "select" shows that there are 1605343 entries which have > ttl below > 99. So after the following delete, shouldn't the number of > entries > go down to 0? If the TTL corresponding to these entries were > something else, > why should they be counted for in the select in the first place? The > delta > of entries between the two selects should be 0. > > Do I have some fundamental misunderstanding about how sqlite stores > values > in database? > > > > -- > View this message in context: > http://old.nabble.com/sqlite3-delete-does-not-delete-everything--tp27865654p27865654.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 delete does not delete everything?
On Mar 11, 2010, at 11:13 PM, Skand wrote: > > Hi Folks, > > Whats going on here? I would expect the following delete to delete > everything under 99. > > sqlite> .schema > > CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl > INTEGER, > PRIMARY KEY(ip_domain, answer, ttl)); > > sqlite> select count(*) from ip_domain_table where ttl < 99 ; > > 1605343 > > sqlite> pragma cache_size=10; delete from ip_domain_table where > ttl < > 99; > > sqlite> select count(*) from ip_domain_table where ttl < 99 ; > > 258 > > sqlite> select * from ip_domain_table where ttl < 99 limit 1; > 107.35.138.41|127.2.0.2|1266895619 > > The first "select" shows that there are 1605343 entries which have > ttl below > 99. So after the following delete, shouldn't the number of > entries > go down to 0? If the TTL corresponding to these entries were > something else, > why should they be counted for in the select in the first place? The > delta > of entries between the two selects should be 0. > > Do I have some fundamental misunderstanding about how sqlite stores > values > in database? Seems strange to me too. What version of SQLite? Does running "PRAGMA integrity_check" reveal any problems with the database file? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 delete does not delete everything?
Hi Folks, Whats going on here? I would expect the following delete to delete everything under 99. sqlite> .schema CREATE TABLE ip_domain_table (ip_domain TEXT, answer TEXT, ttl INTEGER, PRIMARY KEY(ip_domain, answer, ttl)); sqlite> select count(*) from ip_domain_table where ttl < 99 ; 1605343 sqlite> pragma cache_size=10; delete from ip_domain_table where ttl < 99; sqlite> select count(*) from ip_domain_table where ttl < 99 ; 258 sqlite> select * from ip_domain_table where ttl < 99 limit 1; 107.35.138.41|127.2.0.2|1266895619 The first "select" shows that there are 1605343 entries which have ttl below 99. So after the following delete, shouldn't the number of entries go down to 0? If the TTL corresponding to these entries were something else, why should they be counted for in the select in the first place? The delta of entries between the two selects should be 0. Do I have some fundamental misunderstanding about how sqlite stores values in database? -- View this message in context: http://old.nabble.com/sqlite3-delete-does-not-delete-everything--tp27865654p27865654.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
