[sqlite] Corrupted database

[R.Smith]

On 2015-03-16 09:49 PM, Dave Dyer wrote:
>> Do you still have a copy of the originally damaged Database? I believe a 
>> closer look to it will reveal more corruption than the assumed.
> I have the original database.  What other tests could I do to look for 
> evidence?
>
> It appears (so far) that the database I reconstructed from the dump (minus an 
> index request)
> contains all the original data, but that's hard to prove.

Yes but the dump doesn't provide automatic indices and row-ids etc. 
(some of which might be corrupt too). It is great that all the data is 
perfectly in tact, which means that the duplication problem ran into is 
likely the only problem that occurred.

>
>> I do however think you are on the right track with thinking that the 
>> duplicated transaction (or failure of rollback or program error or 
>> mishandling of a duplicate insert fail that cause the transaction to be 
>> either committed twice or not failed correctly or resubmitted without clear 
>> prior failure or success) is the culprit here, and I am fairly certain this 
>> can happen in the setup described above as per the 
>> http://www.sqlite.org/howtocorrupt.html
> It's likely in this case that the apple/microsoft cooperation on SMB is 
> ultimately at fault
> because it is breaking some guarantee that sqlite is depending on.  Their 
> file sharing is known
> to be crap. It's still worthwhile to try to get to the bottom of it as a 
> preliminary step to fixing it.

It isn't likely, it is almost guaranteed - but not because of the 
above-mentioned players as such - There simply is no actual networked 
system in existence (that isn't excruciatingly slow) that does 
file-locking correct via a network of any sort.

I will have to say very sadly that if you absolutely have to have a 
system where the DB file is not on the exact same machine (physical) 
than the client software (the bit using SQLite code), then SQLite is not 
a good choice - consider MySQL or Postgres perhaps. There was a crowd 
some time ago making an SQLite for client-server environments 
(SQLitening if memory serves), but I believe it's a commercial system.
See: http://www.sqlite.org/whentouse.html

[sqlite] Searching for interfacing info with Liberty Basic

[cmassar]

I've been using Liberty Basic 4.04 as a hobby language for about twelve
years now. In that time I never programmed anything that needed a real
database. Now I have that need and I would like to use sqlite because of its
many advantages.

I know enough about (relational) databases and how they work but not how to
use them from within LB.  I can't figure out what the DLL calls are like -
mostly which parameters, their sequence  and their definitions. Can anyone
help me to obtain this information? I would like to work directly in the
sqlite dll but if a wrapper is available I'll be satisfied if I can work
with indexes and foreign keys.

Thanks all in advance.

Cor

[sqlite] Corrupted database

[R.Smith]

On 2015-03-16 08:35 PM, Dave Dyer wrote:
> I have some addition evidence that there is an underlying problem,
> exacerbated by some failure in SMB file sharing.
>
> In this instance, there is a set of duplicated records that did not
> directly cause an indexing error, but which could have been created
> if a transaction failed (presumably due to a file i/o error), was
> incorrectly unwound, and then repeated.
>
> - Details -
>
> Using the sqlite3 tool, starting with the damaged database;
>   I dropped the indexes that had directly caused the complaint
>   queried to find the duplicated records
>   deleted the duplicated records
>   tried to recreate the indexes (expecting this would succeed).
>   It did not.  I got a "database is malformed" error.
>
> I take this as evidence that there was some actual damage to the
> database, not just cleanly duplicated records with a bad index.

This is unfortunately an assumption and not evidence of any sort. How do 
you know the database is "healthy" in total now that you have deleted a 
couple of records? Did you run an integrity check which passed before 
attempting to recreate the indices?


> I did a full dump of the original database, removed the bad index
> request, created a new database from the dump, repeated the duplicate
> record removal, and successfully created the index.
>
> This "fully repaired" database turned out to contain a duplicated set of 
> records which did not cause an indexing problem, but which should not have
> occurred, and was consistent with a duplicated transaction.  If this had
> been caused by a program error - ie; I really inserted the records twice,
> the database would not have been really damaged, and the shortcut repair I
> tried first would have succeeded.

Another assumption I'm afraid. If a transaction failed, the script might 
call for any amount of things based on your ON CONFLICT control 
settings.  If you are logging the DB activity and a rollback was logged 
that would get us closer to evidence.

Also, one has to assume that SQLite has full access and control over the 
file and locking mechanisms to assume that any damage must be due SQLite 
not rolling back a transaction correctly - which incidentally is 
directly proved to not be the case by the very next statement:

> In this case, the client is a mac running os 10.7.5, the file
> server is a PC running OS 8 server, and the sharing is via SMB

Do you still have a copy of the originally damaged Database? I believe a 
closer look to it will reveal more corruption than the assumed.

I do however think you are on the right track with thinking that the 
duplicated transaction (or failure of rollback or program error or 
mishandling of a duplicate insert fail that cause the transaction to be 
either committed twice or not failed correctly or resubmitted without 
clear prior failure or success) is the culprit here, and I am fairly 
certain this can happen in the setup described above as per the 
http://www.sqlite.org/howtocorrupt.html

[sqlite] Searching for interfacing info with Liberty Basic

[Drago, William @ CSG - NARDA-MITEQ]

And this:

http://www.libertybasicuniversity.com/lbnews/nl106/SQLite.htm

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of cmassar
> Sent: Monday, March 16, 2015 4:55 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Searching for interfacing info with Liberty Basic
>
> I've been using Liberty Basic 4.04 as a hobby language for about twelve
> years now. In that time I never programmed anything that needed a real
> database. Now I have that need and I would like to use sqlite because
> of its many advantages.
>
> I know enough about (relational) databases and how they work but not
> how to use them from within LB.  I can't figure out what the DLL calls
> are like - mostly which parameters, their sequence  and their
> definitions. Can anyone help me to obtain this information? I would
> like to work directly in the sqlite dll but if a wrapper is available
> I'll be satisfied if I can work with indexes and foreign keys.
>
> Thanks all in advance.
>
> Cor
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Searching for interfacing info with Liberty Basic

[Drago, William @ CSG - NARDA-MITEQ]

This is old, but may still be useful to you:

http://libertybasicuniversity.com/lbnews/nl105/sql.htm

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of cmassar
> Sent: Monday, March 16, 2015 4:55 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Searching for interfacing info with Liberty Basic
>
> I've been using Liberty Basic 4.04 as a hobby language for about twelve
> years now. In that time I never programmed anything that needed a real
> database. Now I have that need and I would like to use sqlite because
> of its many advantages.
>
> I know enough about (relational) databases and how they work but not
> how to use them from within LB.  I can't figure out what the DLL calls
> are like - mostly which parameters, their sequence  and their
> definitions. Can anyone help me to obtain this information? I would
> like to work directly in the sqlite dll but if a wrapper is available
> I'll be satisfied if I can work with indexes and foreign keys.
>
> Thanks all in advance.
>
> Cor
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Documentation typo

[Drago, William @ CSG - NARDA-MITEQ]

On this page:

http://www.sqlite.org/queryplanner.html

Under the heading  1.4 Multiple Result Rows  the word "of" is missing between 
the words "instead" and "peaches" in the third sentence.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] When to disambiguate column names in queries?

[Drago, William @ CSG - NARDA-MITEQ]

Ryan & Dave,

Thank you for the replies. It's nice to know my thinking is on the right track.

Regards,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of R.Smith
> Sent: Monday, March 16, 2015 1:26 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] When to disambiguate column names in queries?
>
>
> On 2015-03-16 06:16 PM, Drago, William @ CSG - NARDA-MITEQ wrote:
> > All,
> >
> > Some of my Select statements are pretty long and I'm starting to
> think it'd be a good idea to always include table names of columns
> instead of just when they are not unique. This would make the Select
> statements longer, but perhaps easier to understand if the reader knows
> where each column is from.
> >
> > Any thoughts on this? I realize something like this can be highly
> subjective, but I'm wondering if there's a generally accepted practice
> in the SQLite world.
>
> I won't try to imagine this applies to the SQLite or any other SQL
> world, but I've had good success (In terms of always understanding
> queries that needs to be referred back to after some months and
> legibility to others plus zero SQL engine confusion or incorrect
> queries) when using very short aliasing as a standard.
>
> I've started the practice out of necessity using MS SQL where automated
> queries by default append the table-name to every column which made it
> almost illegible and fills up the screen with nonsense text making the
> query hard to read from among the riffraff.  (People using MS Query via
> Excel might be familiar with this example).
>
> A typical Query offered by some designer might start out like this:
>
> SELECT PorMasterHeader.POrder, PorMasterHeader..EntryDate,
> PorMasterHeader.CLientCode, AprClients.ClientName, PorMaster.Lines,
> PorMasterDetail.LineNo, PorMasterDetail.StockCode,
> PorMasterDetail.Price, PorMasterDetail.OrderQty,
> (PorMasterDetail.OrderQty*PorMasterDetail.Price) AS TotCost
> FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PorMasterHeader,
> [SAPSystemCompanyAX].[dbo].[PorMaster] PorMaster,
> [SAPSystemCompanyAX].[dbo].[PorMasterDetail] PorMasterDetail,
> [SAPSystemCompanyAX].[dbo].[AprClients] AprClients
> WHERE PorMasterDetail.POrder=PorMasterHeader.POrder AND
> PorMaster.POrder=PorMasterHeader.POrder  AND
> PorMasterDetail.LineNo>1AND
> AprClients.ClientCode=PorMasterHeader.Client
> ORDER BY AprClients.ClientName, PorMasterHeader.POrder,
> PorMasterDetail.LineNo
>
>
>
> which is a wall-of-text mess...
> Making use of nothing more than short clear aliases and better
> indentation fixes it to read like this:
>
> SELECT PH.POrder, PH..EntryDate, PH.CLientCode, CL.ClientName,
> PM.Lines, PD.LineNo,
> PD.StockCode, PD.Price, PD.OrderQty, (PD.OrderQty*PD.Price)
> AS TotCost
>FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PH,
> [SAPSystemCompanyAX].[dbo].[PorMaster] PM,
> [SAPSystemCompanyAX].[dbo].[PorMasterDetail] PD,
> [SAPSystemCompanyAX].[dbo].[AprClients] CL
>   WHERE PD.POrder=PH.POrder  AND PM.POrder=PH.POrder AND
> PD.LineNo>1
> AND CL.ClientCode=PH.Client
>   ORDER BY CL.ClientName, PM.POrder, PD.LineNo
>
>
> which is legible to all, works always exactly (no column name is left
> to
> ambiguity) and easy to understand and is DB-Engine independent.
>
> The best part being, the more convoluted the query, the larger the
> space-saving.
>
> Note: It also helps to have a bit of convention in place, such as
> ALWAYS aliasing [AprClients] to CL which will make you "see" the
> meaning of a query faster over time - but be careful, it may happen
> that someone else aliases some other table to CL and if you don't check
> it, might end up spending hours chasing obscure bugs.
>
> Good luck,
> Ryan
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by 

[sqlite] When to disambiguate column names in queries?

[R.Smith]

On 2015-03-16 06:16 PM, Drago, William @ CSG - NARDA-MITEQ wrote:
> All,
>
> Some of my Select statements are pretty long and I'm starting to think it'd 
> be a good idea to always include table names of columns instead of just when 
> they are not unique. This would make the Select statements longer, but 
> perhaps easier to understand if the reader knows where each column is from.
>
> Any thoughts on this? I realize something like this can be highly subjective, 
> but I'm wondering if there's a generally accepted practice in the SQLite 
> world.

I won't try to imagine this applies to the SQLite or any other SQL 
world, but I've had good success (In terms of always understanding 
queries that needs to be referred back to after some months and 
legibility to others plus zero SQL engine confusion or incorrect 
queries) when using very short aliasing as a standard.

I've started the practice out of necessity using MS SQL where automated 
queries by default append the table-name to every column which made it 
almost illegible and fills up the screen with nonsense text making the 
query hard to read from among the riffraff.  (People using MS Query via 
Excel might be familiar with this example).

A typical Query offered by some designer might start out like this:

SELECT PorMasterHeader.POrder, PorMasterHeader..EntryDate,
PorMasterHeader.CLientCode, AprClients.ClientName, PorMaster.Lines,
PorMasterDetail.LineNo, PorMasterDetail.StockCode,
PorMasterDetail.Price, PorMasterDetail.OrderQty,
(PorMasterDetail.OrderQty*PorMasterDetail.Price) AS TotCost
FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PorMasterHeader,
[SAPSystemCompanyAX].[dbo].[PorMaster] PorMaster,
[SAPSystemCompanyAX].[dbo].[PorMasterDetail] PorMasterDetail,
[SAPSystemCompanyAX].[dbo].[AprClients] AprClients
WHERE PorMasterDetail.POrder=PorMasterHeader.POrder AND
PorMaster.POrder=PorMasterHeader.POrder  AND
PorMasterDetail.LineNo>1AND AprClients.ClientCode=PorMasterHeader.Client
ORDER BY AprClients.ClientName, PorMasterHeader.POrder,
PorMasterDetail.LineNo



which is a wall-of-text mess...
Making use of nothing more than short clear aliases and better 
indentation fixes it to read like this:

SELECT PH.POrder, PH..EntryDate, PH.CLientCode, CL.ClientName,
PM.Lines, PD.LineNo,
PD.StockCode, PD.Price, PD.OrderQty, (PD.OrderQty*PD.Price)
AS TotCost
   FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PH,
[SAPSystemCompanyAX].[dbo].[PorMaster] PM,
[SAPSystemCompanyAX].[dbo].[PorMasterDetail] PD,
[SAPSystemCompanyAX].[dbo].[AprClients] CL
  WHERE PD.POrder=PH.POrder  AND PM.POrder=PH.POrder AND PD.LineNo>1
AND CL.ClientCode=PH.Client
  ORDER BY CL.ClientName, PM.POrder, PD.LineNo


which is legible to all, works always exactly (no column name is left to 
ambiguity) and easy to understand and is DB-Engine independent.

The best part being, the more convoluted the query, the larger the 
space-saving.

Note: It also helps to have a bit of convention in place, such as ALWAYS 
aliasing [AprClients] to CL which will make you "see" the meaning of a 
query faster over time - but be careful, it may happen that someone else 
aliases some other table to CL and if you don't check it, might end up 
spending hours chasing obscure bugs.

Good luck,
Ryan

[sqlite] Corrupted database

[Simon Slavin]

On 16 Mar 2015, at 6:35pm, Dave Dyer  wrote:

> This "fully repaired" database turned out to contain a duplicated set of 
> records which did not cause an indexing problem, but which should not have
> occurred, and was consistent with a duplicated transaction.  If this had 
> been caused by a program error - ie; I really inserted the records twice, 
> the database would not have been really damaged, and the shortcut repair I
> tried first would have succeeded.

You are correct in what you have noticed but the explanation is simpler and 
well-known.  Because of the format in which SQLite keeps its indexes, you 
cannot always repair a corrupted index by deleting the rows which you think are 
corrupt.  Sometimes you need to delete other rows too (for example those 
immediately before and after a corrupt row) and you need detailed analysis of 
what's wrong with the index to know exactly what you need to delete.  If you do 
leave corrupt information in the index is can lead to additional corruption 
when you add more rows to those indexes.

The only safe thing to do is to drop the index and remake it.  Or do to 
something which does that (e.g. VACUUM).

Simon.

[sqlite] FAQ typo

[Richard Hipp]

On 3/16/15, O.Zolotov  wrote:
> Dear All,
> the FAQ's item 21 ( http://www.sqlite.org/faq.html ) has a typo. The
> sentences
>
> " Your can use PRAGMA integrity_check
> Your can use PRAGMA quick_check ..."
>
> are more likely to be
> " You can use PRAGMA integrity_check
> You can use PRAGMA quick_check ..."

Fixed.  Tnx.

>
> PS
> By the way, do I have a right to translate the FAQ into Russian?
>

Da.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Corrupted database

[Keith Medcalf]

>I will have to say very sadly that if you absolutely have to have a
>system where the DB file is not on the exact same machine (physical)
>than the client software (the bit using SQLite code), then SQLite is not
>a good choice - consider MySQL or Postgres perhaps. There was a crowd
>some time ago making an SQLite for client-server environments
>(SQLitening if memory serves), but I believe it's a commercial system.
>See: http://www.sqlite.org/whentouse.html

QNX also modifies SQLite (QDB) to work with their distributed interprocess 
messaging, thus all operations, even from remote nodes, occur against a local 
filesystem ... or at least that was the case last time I looked at it.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

[sqlite] When to disambiguate column names in queries?

[Keith Medcalf]

Personally, I'd format it like this to be the most readable:

  SELECT PH.POrderas POrder,
 PH.EntryDate as EntryDate,
 PH.ClientCodeas ClientCode,
 CL.ClientNameas ClientName,
 PM.Lines as Lines,
 PD.LineNoas LineNo,
 PD.StockCode aa StockCode,
 PD.Price as Price,
 PD.OrderQty  as OrderQty,
 PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader  as PH,
 SAPSystemCompanyAX.dbo.PorMasteras PM,
 SAPSystemCompanyAX.dbo.PorMasterDetail  as PD,
 SAPSystemCompanyAX.dbo.AprClients   as CL
   WHERE PD.POrder = PH.POrder
 AND PM.POrder = PH.POrder
 AND PD.LineNo > 1
 AND CL.ClientCode = PH.Client
ORDER BY ClientName, POrder, LineNo

or, if you prefer the JOIN syntax:

  SELECT PH.POrderas POrder,
 PH.EntryDate as EntryDate,
 PH.ClientCodeas ClientCode,
 CL.ClientNameas ClientName,
 PM.Lines as Lines,
 PD.LineNoas LineNo,
 PD.StockCode aa StockCode,
 PD.Price as Price,
 PD.OrderQty  as OrderQty,
 PD.OrderQty*PD.Price as TotCost
FROM SAPSystemCompanyAX.dbo.PorMasterHeader  as PH
JOIN SAPSystemCompanyAX.dbo.PorMasteras PM
  ON PM.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.PorMasterDetail  as PD
  ON PD.POrder = PH.POrder
JOIN SAPSystemCompanyAX.dbo.AprClients   as CL
  ON CL.ClientCode = PH.Client
   WHERE PD.LineNo > 1
ORDER BY ClientName, POrder, LineNo

I find gratuitous quoting of symbols somewhat obnoxious and distasteful, as 
well as the propensity to (include (extraneous) (brackets) just) for the (sake 
(of (adding (brackets.  I see that your original obviously-ms-generated 
query didn't generate its usual crapload of () -- or perhaps you removed them 
already :)

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of R.Smith
>Sent: Monday, 16 March, 2015 11:26
>To: sqlite-users at mailinglists.sqlite.org
>Subject: Re: [sqlite] When to disambiguate column names in queries?
>
>
>On 2015-03-16 06:16 PM, Drago, William @ CSG - NARDA-MITEQ wrote:
>> All,
>>
>> Some of my Select statements are pretty long and I'm starting to think
>it'd be a good idea to always include table names of columns instead of
>just when they are not unique. This would make the Select statements
>longer, but perhaps easier to understand if the reader knows where each
>column is from.
>>
>> Any thoughts on this? I realize something like this can be highly
>subjective, but I'm wondering if there's a generally accepted practice in
>the SQLite world.
>
>I won't try to imagine this applies to the SQLite or any other SQL
>world, but I've had good success (In terms of always understanding
>queries that needs to be referred back to after some months and
>legibility to others plus zero SQL engine confusion or incorrect
>queries) when using very short aliasing as a standard.
>
>I've started the practice out of necessity using MS SQL where automated
>queries by default append the table-name to every column which made it
>almost illegible and fills up the screen with nonsense text making the
>query hard to read from among the riffraff.  (People using MS Query via
>Excel might be familiar with this example).
>
>A typical Query offered by some designer might start out like this:
>
>SELECT PorMasterHeader.POrder, PorMasterHeader..EntryDate,
>PorMasterHeader.CLientCode, AprClients.ClientName, PorMaster.Lines,
>PorMasterDetail.LineNo, PorMasterDetail.StockCode,
>PorMasterDetail.Price, PorMasterDetail.OrderQty,
>(PorMasterDetail.OrderQty*PorMasterDetail.Price) AS TotCost
>FROM [SAPSystemCompanyAX].[dbo].[PorMasterHeader] PorMasterHeader,
>[SAPSystemCompanyAX].[dbo].[PorMaster] PorMaster,
>[SAPSystemCompanyAX].[dbo].[PorMasterDetail] PorMasterDetail,
>[SAPSystemCompanyAX].[dbo].[AprClients] AprClients
>WHERE PorMasterDetail.POrder=PorMasterHeader.POrder AND
>PorMaster.POrder=PorMasterHeader.POrder  AND
>PorMasterDetail.LineNo>1AND
>AprClients.ClientCode=PorMasterHeader.Client
>ORDER BY AprClients.ClientName, PorMasterHeader.POrder,
>PorMasterDetail.LineNo
>
>
>
>which is a wall-of-text mess...
>Making use of nothing more than short clear aliases and better
>indentation fixes it to read like this:
>
>SELECT PH.POrder, PH..EntryDate, PH.CLientCode, CL.ClientName,
>PM.Lines, PD.LineNo,
>PD.StockCode, PD.Price, PD.OrderQty, (PD.OrderQty*PD.Price)
>AS 

[sqlite] FAQ typo

[Jim Callahan]

Not personally familiar with Russian translation issue, but thanks for the
English language spell check should use "You" instead of "Your" in cited
sentences.

Jim Callahan
Orlando, FL

On Mon, Mar 16, 2015 at 5:19 PM, O.Zolotov  wrote:

> Dear All,
> the FAQ's item 21 ( http://www.sqlite.org/faq.html ) has a typo. The
> sentences
>
> " Your can use PRAGMA integrity_check
> Your can use PRAGMA quick_check ..."
>
> are more likely to be
> " You can use PRAGMA integrity_check
> You can use PRAGMA quick_check ..."
>
> PS
> By the way, do I have a right to translate the FAQ into Russian?
>
> Best Regards,
> Oleg V. Zolotov
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Paul]

>   On 3/16/15, Paul  wrote:
> 
> > Is  doc page is
> > outdated and 'synchronous' pragma is now set for each databse separately?
> 
> It has always been that way.  The documentation has recently been
> updated to clarify that point.  See
> http://www.sqlite.org/draft/pragma.html#pragma_synchronous for the
> documentation as it will appears in the next release.
> 

Thank you for clarification, Dr.Hipp, sadly I didn't know that.

But there is still one question left. Why when my usage is

(1) open database
(2) execute 'PRAGMA synchronous = OFF;'
(3) begin transaction
(4) do some inserts/deletes/updates
(5) commit transaction
(6) close database

not a single fsync() is being invoked. But when the usage is

(1) open database
(2) execute 'PRAGMA synchronous = OFF;'
(3) attach database X
(4) execute 'PRAGMA X.synchronous = OFF;'
(5) begin transaction
(6) do some inserts/deletes/updates
(7) commit transaction
(8) close database

I observe one fsync() for directory where 'main' database is located.
Why attaching database is causing this nasty fsync() during commit, even when 
both databases have fsync() disabled?
How can I avoid it, since even directory sync is an expensive operation?

Thanks,
Paul

[sqlite] When to disambiguate column names in queries?

[Darren Duncan]

If you design your database schemas such that, where possible, corresponding 
columns have the same names in all tables, and you do natural joins, the 
problem 
will basically go away. -- Darren Duncan

On 2015-03-16 9:16 AM, Drago, William @ CSG - NARDA-MITEQ wrote:
> All,
>
> Some of my Select statements are pretty long and I'm starting to think it'd 
> be a good idea to always include table names of columns instead of just when 
> they are not unique. This would make the Select statements longer, but 
> perhaps easier to understand if the reader knows where each column is from.
>
> Any thoughts on this? I realize something like this can be highly subjective, 
> but I'm wondering if there's a generally accepted practice in the SQLite 
> world.
>
> Thanks,
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
> attachments are solely for the use of the addressee and may contain 
> information that is privileged or confidential. Any disclosure, use or 
> distribution of the information contained herein is prohibited. In the event 
> this e-mail contains technical data within the definition of the 
> International Traffic in Arms Regulations or Export Administration 
> Regulations, it is subject to the export control laws of the U.S.Government. 
> The recipient should check this e-mail and any attachments for the presence 
> of viruses as L-3 does not accept any liability associated with the 
> transmission of this e-mail. If you have received this communication in 
> error, please notify the sender by reply e-mail and immediately delete this 
> message and any attachments.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] When to disambiguate column names in queries?

[Dave Wellman]

Hi Bill,

I'm not sure about the 'sqlite world', most of my sql work is done using a
different dbms, but my 'standard' is to always use alias names anytime I
have two or more tables in a SELECT.

Yes, it can make the sql longer and sometimes it is not strictly necessary
(as you say, only needed for column names  that are not unique), but I've
found that this makes large pieces of sql much easier to read -
particularly when you have to come back to them after a few months in order
to change something.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Drago,
William @ CSG - NARDA-MITEQ
Sent: 16 March 2015 16:17
To: General Discussion of SQLite Database
Subject: [sqlite] When to disambiguate column names in queries?

All,

Some of my Select statements are pretty long and I'm starting to think it'd
be a good idea to always include table names of columns instead of just when
they are not unique. This would make the Select statements longer, but
perhaps easier to understand if the reader knows where each column is from.

Any thoughts on this? I realize something like this can be highly
subjective, but I'm wondering if there's a generally accepted practice in
the SQLite world.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
attachments are solely for the use of the addressee and may contain
information that is privileged or confidential. Any disclosure, use or
distribution of the information contained herein is prohibited. In the event
this e-mail contains technical data within the definition of the
International Traffic in Arms Regulations or Export Administration
Regulations, it is subject to the export control laws of the U.S.Government.
The recipient should check this e-mail and any attachments for the presence
of viruses as L-3 does not accept any liability associated with the
transmission of this e-mail. If you have received this communication in
error, please notify the sender by reply e-mail and immediately delete this
message and any attachments.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation typo

[Richard Hipp]

On 3/16/15, Drago, William @ CSG - NARDA-MITEQ  
wrote:
> On this page:
>
> http://www.sqlite.org/queryplanner.html
>
> Under the heading  1.4 Multiple Result Rows  the word "of" is missing
> between the words "instead" and "peaches" in the third sentence.

Thanks for the report.  Fixed at
https://www.sqlite.org/docsrc/info/94ffc3e5cbf8c867 with the changes
already pushed up to the website.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Paul]

Hello, fellow developers. 

Recently we performed a lot of test with ZFS and Sqlite and long story short: 
we came up with a conclusion 
that it is safe to disable fsync() to keep database consistent (though not 
durable), even in case of power failure. 
So we decided to stck with? 'PRAGMA synchronous = OFF' since durability does 
not concerns us very much. 

But we have one specific use-case whe 'master' database is first opened and 
then 'slave' database is being attached. 
When some updates? are made to 'slave' and 'master' we still see fsync() being 
called for 'slave', its journal and 
their directory but not for 'master' nor for 'master's journal. 

We perform these steps: 

(1) open 'master' 
(2) execute 'PRAGMA synchronous = OFF;' 
(3) attach 'slave' 
(4) do updates 

and see fsync()s. 

Even when we insert 'PRAGMA synchronous = OFF;' after (3): 

(1) open 'master' 
(2) execute 'PRAGMA synchronous = OFF;' 
(3) attach 'slave' 
(3.1) execute 'PRAGMA synchronous = OFF;' 
(4) do updates 

nothing changes: 'slave', its journal and thier directory are still fsync()-ed. 

On the other hand if we change our steps to 

(1) open 'master' 
(2) execute 'PRAGMA synchronous = OFF;' 
(3) attach 'slave' 
(3.1) execute 'PRAGMA slave.synchronous = OFF;' 
(4) do updates 

i.e. explicitly specify 'slave' before synchronous pragma, both 'slave' and its 
journal stop being fsync()-ed. 
However there is still fsync() for directory that we would really like to 
avoid. 

So the questions are: 

Is  doc page is outdated 
and 'synchronous' pragma is now set for each databse separately? 

How to avoid fsync() for directory? 
I know that it is 'not safe'. But same can be said about not syncing journal 
and/or database file when 'PRAGMA synchronous = OFF;' is set. 
Is there a specific reason for keeping fsync() for directory? 

Seqlite version that we use is: sqlite3-3.8.7_1 

Thanks, 
Paul 

[sqlite] When to disambiguate column names in queries?

[Drago, William @ CSG - NARDA-MITEQ]

All,

Some of my Select statements are pretty long and I'm starting to think it'd be 
a good idea to always include table names of columns instead of just when they 
are not unique. This would make the Select statements longer, but perhaps 
easier to understand if the reader knows where each column is from.

Any thoughts on this? I realize something like this can be highly subjective, 
but I'm wondering if there's a generally accepted practice in the SQLite world.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Escaping strings in custom FTS tokenizer

[Xavier Snelgrove]

Hello all,

Another question: in my application I have a FTS4 virtual table that I am
writing a custom tokenizer for. I can't find any documentation on what
symbols are "safe" to include in the index, and which ones are not because
they have special meaning in an FTS query.

For example: if my tokenizer does not do case-folding, it's not safe for me
to store "AND" or "OR" directly as tokens, because they will become
un-queryable. Instead I'll need to in some way escape them ("\AND"?) both
when indexing and when querying so that I bypass the query parser.

Similarly I believe that the symbols "-", "!", "*", "(", ")" and the
double-quote itself are not safe for me to return from my custom tokenizer
without some form of escaping.

Is there a best practice here? My best-case scenario would be some way to
do a "raw" query on an FTS table where all symbols are interpreted as text
queries, and bypass the entire FTS "mini-language". I'm tempted to add a
custom new operator, e.g. RAWMATCH, to make this possible, but that seems
like a pretty heavy-weight solution.

Thanks,
  Xavier Snelgrove

Cofounder & CTO, Whirlscape Inc.
http://whirlscape.com
xavier at whirlscape.com

[sqlite] Corrupted database

[Richard Hipp]

On 3/16/15, Simon Slavin  wrote:
>
> Because of the format in which SQLite keeps its indexes, you
> cannot always repair a corrupted index by deleting the rows which you think
> are corrupt.  Sometimes you need to delete other rows too (for example those
> immediately before and after a corrupt row) and you need detailed analysis
> of what's wrong with the index to know exactly what you need to delete.

But you can always repair corruption in indexes using the "REINDEX" command.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] minor documentation flaw

[Wolfgang Enzinger]

In https://sqlite.org/lang_expr.html, the anchor



appears twice. Obviously the scond occurence should be



Wolfgang

[sqlite] sqlite3_release_memory when SQLITE_THREADSAFE=0

[Xavier Snelgrove]

Hi Simon,

Well in my case the linked threadsafe.html documentation won't be helpful,
because it looks like at runtime I can only downgrade as far as
multi-thread mode, not single-thread mode, and it's in single-thread mode
where I'm really seeing a significant performance advantage.

I think the performance improvement is significant enough that I'll forgo
the off-thread calls to sqlite3_release_memory. That was only rarely used
anyway. I'll add an on-thread memory release call, and hope it triggers in
time.

And I'll just trust that sqlite3_interrupt is safe to use, based on my
reading of the source.

  Xavier


On Sat, Mar 14, 2015 at 8:27 PM, Simon Slavin  wrote:

>
> On 13 Mar 2015, at 6:26pm, Xavier Snelgrove  wrote:
>
> > Are there any other options here?
>
> Does the last section of
>
> 
>
> help you in your situation ?  Alternatively you might be able to use
>
> 
>
> to manually maintain a mutex.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Xavier Snelgrove
Cofounder & CTO, Whirlscape Inc.
http://whirlscape.com
1 (416) 876-9427
xavier at whirlscape.com

[sqlite] Corrupted database

[Dave Dyer]

>
>Do you still have a copy of the originally damaged Database? I believe a 
>closer look to it will reveal more corruption than the assumed.

I have the original database.  What other tests could I do to look for 
evidence? 

It appears (so far) that the database I reconstructed from the dump (minus an 
index request)
contains all the original data, but that's hard to prove.


>I do however think you are on the right track with thinking that the 
>duplicated transaction (or failure of rollback or program error or mishandling 
>of a duplicate insert fail that cause the transaction to be either committed 
>twice or not failed correctly or resubmitted without clear prior failure or 
>success) is the culprit here, and I am fairly certain this can happen in the 
>setup described above as per the http://www.sqlite.org/howtocorrupt.html

It's likely in this case that the apple/microsoft cooperation on SMB is 
ultimately at fault
because it is breaking some guarantee that sqlite is depending on.  Their file 
sharing is known
to be crap. It's still worthwhile to try to get to the bottom of it as a 
preliminary step to fixing it.

[sqlite] Corrupted database

[Dave Dyer]

>
>Do you still have a copy of the originally damaged Database? I believe a 
>closer look to it will reveal more corruption than the assumed.

I have the original database.  What other tests could I do to look for 
evidence? 

It appears (so far) that the database I reconstructed from the dump (minus an 
index request)
contains all the original data, but that's hard to prove.


>I do however think you are on the right track with thinking that the 
>duplicated transaction (or failure of rollback or program error or mishandling 
>of a duplicate insert fail that cause the transaction to be either committed 
>twice or not failed correctly or resubmitted without clear prior failure or 
>success) is the culprit here, and I am fairly certain this can happen in the 
>setup described above as per the http://www.sqlite.org/howtocorrupt.html

It's likely in this case that the apple/microsoft cooperation on SMB is 
ultimately at fault
because it is breaking some guarantee that sqlite is depending on.  Their file 
sharing is known
to be crap. It's still worthwhile to try to get to the bottom of it as a 
preliminary step to fixing it.

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Richard Hipp]

On 3/16/15, Paul  wrote:
>  when the usage is
>
> (1) open database
> (2) execute 'PRAGMA synchronous = OFF;'
> (3) attach database X
> (4) execute 'PRAGMA X.synchronous = OFF;'
> (5) begin transaction
> (6) do some inserts/deletes/updates
> (7) commit transaction
> (8) close database
>
> I observe one fsync() for directory where 'main' database is located.
> Why attaching database is causing this nasty fsync() during commit, even
> when both databases have fsync() disabled?

Problem fixed in https://www.sqlite.org/src/info/018d7671402a0f81


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Corrupted database

[Dave Dyer]

>
>Do you still have a copy of the originally damaged Database? I believe a 
>closer look to it will reveal more corruption than the assumed.

I have the original database.  What other tests could I do to look for 
evidence? 

It appears (so far) that the database I reconstructed from the dump (minus an 
index request)
contains all the original data, but that's hard to prove.


>I do however think you are on the right track with thinking that the 
>duplicated transaction (or failure of rollback or program error or mishandling 
>of a duplicate insert fail that cause the transaction to be either committed 
>twice or not failed correctly or resubmitted without clear prior failure or 
>success) is the culprit here, and I am fairly certain this can happen in the 
>setup described above as per the http://www.sqlite.org/howtocorrupt.html

It's likely in this case that the apple/microsoft cooperation on SMB is 
ultimately at fault
because it is breaking some guarantee that sqlite is depending on.  Their file 
sharing is known
to be crap. It's still worthwhile to try to get to the bottom of it as a 
preliminary step to fixing it.

[sqlite] Corrupted database

[Dave Dyer]

>
>Do you still have a copy of the originally damaged Database? I believe a 
>closer look to it will reveal more corruption than the assumed.

I have the original database.  What other tests could I do to look for 
evidence? 

It appears (so far) that the database I reconstructed from the dump (minus an 
index request)
contains all the original data, but that's hard to prove.


>I do however think you are on the right track with thinking that the 
>duplicated transaction (or failure of rollback or program error or mishandling 
>of a duplicate insert fail that cause the transaction to be either committed 
>twice or not failed correctly or resubmitted without clear prior failure or 
>success) is the culprit here, and I am fairly certain this can happen in the 
>setup described above as per the http://www.sqlite.org/howtocorrupt.html

It's likely in this case that the apple/microsoft cooperation on SMB is 
ultimately at fault
because it is breaking some guarantee that sqlite is depending on.  Their file 
sharing is known
to be crap. It's still worthwhile to try to get to the bottom of it as a 
preliminary step to fixing it.

[sqlite] Corrupted database

[Dave Dyer]

>
>The only safe thing to do is to drop the index and remake it.  Or do to 
>something which does that (e.g. VACUUM).

As I said, the first step in my repair was to drop the offending index.

[sqlite] Corrupted database

[Dave Dyer]

>
>The only safe thing to do is to drop the index and remake it.  Or do to 
>something which does that (e.g. VACUUM).

As I said, the first step in my repair was to drop the offending index.

[sqlite] Corrupted database

[Dave Dyer]

I have some addition evidence that there is an underlying problem,
exacerbated by some failure in SMB file sharing.

In this instance, there is a set of duplicated records that did not
directly cause an indexing error, but which could have been created
if a transaction failed (presumably due to a file i/o error), was 
incorrectly unwound, and then repeated.

- Details -

Using the sqlite3 tool, starting with the damaged database;
 I dropped the indexes that had directly caused the complaint
 queried to find the duplicated records
 deleted the duplicated records
 tried to recreate the indexes (expecting this would succeed).  
 It did not.  I got a "database is malformed" error.

I take this as evidence that there was some actual damage to the
database, not just cleanly duplicated records with a bad index.

I did a full dump of the original database, removed the bad index
request, created a new database from the dump, repeated the duplicate 
record removal, and successfully created the index.  

This "fully repaired" database turned out to contain a duplicated set of 
records which did not cause an indexing problem, but which should not have
occurred, and was consistent with a duplicated transaction.  If this had 
been caused by a program error - ie; I really inserted the records twice, 
the database would not have been really damaged, and the shortcut repair I
tried first would have succeeded.

--

In this case, the client is a mac running os 10.7.5, the file
server is a PC running OS 8 server, and the sharing is via SMB

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Richard Hipp]

On 3/16/15, Paul  wrote:
>
>>   On 3/16/15, Paul  wrote:
>>
>> > Is  doc page is
>> > outdated and 'synchronous' pragma is now set for each databse
>> > separately?
>>
>> It has always been that way.  The documentation has recently been
>> updated to clarify that point.  See
>> http://www.sqlite.org/draft/pragma.html#pragma_synchronous for the
>> documentation as it will appears in the next release.
>>
>
> Thank you for clarification, Dr.Hipp, sadly I didn't know that.
>
> But there is still one question left
>

We are still working on that problem.  Patience, Grasshopper.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Is 'synchronous' pragma now being set on per database basis?

[Richard Hipp]

On 3/16/15, Paul  wrote:

> Is  doc page is
> outdated and 'synchronous' pragma is now set for each databse separately?

It has always been that way.  The documentation has recently been
updated to clarify that point.  See
http://www.sqlite.org/draft/pragma.html#pragma_synchronous for the
documentation as it will appears in the next release.


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Memory leak?

[Matthias Schmitt]

Hello,

sorry for the late answer. My weekend was full of events.

> On 13 Mar 2015, at 23:32, Gregory Moore  wrote:
> 
> Are you interacting directly with sqlite?  Or are you using a 3rd party API 
> such as FMDB?  I take it you are not using Core Data (which optionally can 
> use a sqlite database as a datastore.)

I am using FMDB. BTW, this was visible in the call stack I sent in my first 
mail.

> Are you using ARC?  

No. The first versions of my programs are more than 3 years old. At this time I 
made bad experiences with ARC, so I do not like ARC. 

> Have you implemented the -(void)dealloc method for all your Objective-C (or 
> Swift?) objects?

Yes.

> Are you willing to share some of your application code where it interacts 
> with the sqlite database?

No problem with that. Here is a minimum example, which leaks memory.

int numberOfRestos;

FMResultSet *rs = [db executeQuery:@"SELECT count() FROM restaurant"];
while ([rs next]) {
  numberOfRestos = [rs intForColumnIndex:0];
}
[rs close];

I have chosen this example, because it does not involve any complex parameter 
passing or result handling.
This code was working perfectly without memory leaks for years. Now this 
example is leaking two memory segments of ~4.5 KB each.

Interesting fact: I am getting the same memory leaks when I am compiling Sqlite 
from source or if I am using the precompiled library, which comes with the 
Xcode development environment.

Best regards

Matthias Schmitt

magic moving pixel s.a.
23, Avenue Grande-Duchesse Charlotte
L-3441 Dudelange
Luxembourg
Phone: +352 54 75 75
http://www.mmp.lu

[sqlite] BUG in 3.8.8.3 and pre-release: Wrong ERROR_DISK_FULL writing a blob on Windows

[javaj1...@elxala.com]

R.Smith wrote:
>
>
> On 2015-03-16 12:49 AM, javaj1811 at elxala.com wrote:
>> Hi Joe,
>>
>> good point, I've tested the fail scenario having the database located 
>> in a NTFS disk with the result of NO FAIL!!!
>> so the insertion was done without sqlite error.
>> now the problem seems to be reduced to Windows and FAT32 (and maybe 
>> FAT) drives
>> could you tell me why did you suspect about the drive type ?
>
> You do know what the "32" in FAT32 means right?  For all intents and 
> purposes, in a FAT32 system, that disk might actually be full. Maximum 
> size for a FAT32 disk used to be 32GB but there are some programs 
> available which can write a FAT32 allocation table with 512 byte 
> sectors up to about ~2TB - which I'm hoping is the case for you - but 
> more likely you are hitting the upper bound at 32GB. Even if you did 
> manage to extend the partition beyond 32GB, the maximum file size will 
> be 1 byte shy of 4GB. If you exceed any of these hard limits, you will 
> get a disk-full error (correctly because in a FAT32 universe, that 
> disk or file is in fact "Full").
>
> Btw, FAT32 must be the near worst file system ever used in history 
> (and I'm not blaming Microsoft, they had to try to come up with 
> "something" that could be backward compatible with FAT16 and provide 
> larger storage, but in normal use it is atrocious).  Is there a reason 
> you need to use it?
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Ok, the limit of 4GB for a file in FAT32 seems to be the cause of the 
problem, I didn't realize it since until now
I never needed such big files in my home computer and also get confused 
by the error message (definitively not disk full but file full).

answering your question
 >>> Is there a reason you need to use it?
The disk was already formatted with FAT32 when I bought it some years 
ago. And indeed is the most reliable
external drive disk that I have in the last years but now I've learned 
that is not appropiate for my big sqlite databases!

thank you for your feedback!

[sqlite] BUG in 3.8.8.3 and pre-release: Wrong ERROR_DISK_FULL writing a blob on Windows

[R.Smith]

On 2015-03-16 12:49 AM, javaj1811 at elxala.com wrote:
> Hi Joe,
>
> good point, I've tested the fail scenario having the database located 
> in a NTFS disk with the result of NO FAIL!!!
> so the insertion was done without sqlite error.
> now the problem seems to be reduced to Windows and FAT32 (and maybe 
> FAT) drives
> could you tell me why did you suspect about the drive type ?

You do know what the "32" in FAT32 means right?  For all intents and 
purposes, in a FAT32 system, that disk might actually be full. Maximum 
size for a FAT32 disk used to be 32GB but there are some programs 
available which can write a FAT32 allocation table with 512 byte sectors 
up to about ~2TB - which I'm hoping is the case for you - but more 
likely you are hitting the upper bound at 32GB. Even if you did manage 
to extend the partition beyond 32GB, the maximum file size will be 1 
byte shy of 4GB. If you exceed any of these hard limits, you will get a 
disk-full error (correctly because in a FAT32 universe, that disk or 
file is in fact "Full").

Btw, FAT32 must be the near worst file system ever used in history (and 
I'm not blaming Microsoft, they had to try to come up with "something" 
that could be backward compatible with FAT16 and provide larger storage, 
but in normal use it is atrocious).  Is there a reason you need to use it?

[sqlite] BUG in 3.8.8.3 and pre-release: Wrong ERROR_DISK_FULL writing a blob on Windows

[javaj1...@elxala.com]

javaj1811 at elxala.com wrote:
> Joe Mistachkin wrote:
>> What file system is being used on the drive in question (e.g. FAT, 
>> FAT32,
>> NTFS, etc)?
>>
>> -- 
>> Joe Mistachkin
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> FAT32 in the documented case, I can test it in a NTFS drive as well, I 
> need just the time to copy the database
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
Hi Joe,

good point, I've tested the fail scenario having the database located in 
a NTFS disk with the result of NO FAIL!!!
so the insertion was done without sqlite error.
now the problem seems to be reduced to Windows and FAT32 (and maybe FAT) 
drives
could you tell me why did you suspect about the drive type ?

[sqlite] BUG in 3.8.8.3 and pre-release: Wrong ERROR_DISK_FULL writing a blob on Windows

[javaj1...@elxala.com]

Joe Mistachkin wrote:
> What file system is being used on the drive in question (e.g. FAT, FAT32,
> NTFS, etc)?
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
FAT32 in the documented case, I can test it in a NTFS drive as well, I 
need just the time to copy the database