Database malformed after 6000 Inserts?

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Database malformed after 6000 Inserts?

Werner Kleiner-2
Hello,
a program written in C# makes inserts from an SQL script into a sqlite db.
We now saw that the database will be malformed after 6000 records.

Is there a limitation with huge inserts?
What could be the problem?

regards
Werner
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database malformed after 6000 Inserts?

Rob Willett
We've done inserts of tens of thousand at a time, we may well have done
hundreds of thousands in one single transaction. I've no doubt other
people do even larger transactions.

I would assume the problem lies elsewhere.

What error message are you getting? Whats the OS, the environment, disk,
pragma settings?

Rob

On 4 Oct 2016, at 11:11, Werner Kleiner wrote:

> Hello,
> a program written in C# makes inserts from an SQL script into a sqlite
> db.
> We now saw that the database will be malformed after 6000 records.
>
> Is there a limitation with huge inserts?
> What could be the problem?
>
> regards
> Werner
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database malformed after 6000 Inserts?

Jim Borden
In reply to this post by Werner Kleiner-2
I had a problem similar to this before. What is the threading model for access to the database and how is the native library compiled and configured?

Jim Borden
(Sent from a mobile device)

> On 4 Oct 2016, at 19:12, Werner Kleiner <[hidden email]> wrote:
>
> Hello,
> a program written in C# makes inserts from an SQL script into a sqlite db.
> We now saw that the database will be malformed after 6000 records.
>
> Is there a limitation with huge inserts?
> What could be the problem?
>
> regards
> Werner
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database malformed after 6000 Inserts?

Werner Kleiner-2
Thanks for help.
Hopefully I give you the correct answer, because a collegue has
written the C# program.
We had no problems with inserts in the past, but now if we have
records about 6000 inserts we get the errors.

The OS is Server 2012, there are no pragma settings (but collegue has
also used with pragma settings)
It is a single thread with "synchronize full"
We use the System.Data.SQLite.dll with version 1.0.89.0 from 12.12.2013

hope this is what you mean

2016-10-04 13:48 GMT+02:00 Jim Borden <[hidden email]>:

> I had a problem similar to this before. What is the threading model for access to the database and how is the native library compiled and configured?
>
> Jim Borden
> (Sent from a mobile device)
>
>> On 4 Oct 2016, at 19:12, Werner Kleiner <[hidden email]> wrote:
>>
>> Hello,
>> a program written in C# makes inserts from an SQL script into a sqlite db.
>> We now saw that the database will be malformed after 6000 records.
>>
>> Is there a limitation with huge inserts?
>> What could be the problem?
>>
>> regards
>> Werner
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database malformed after 6000 Inserts?

Rob Willett
We don't use Windows Server or System.Data.SQLite so can't comment.

I'd be astonished if its Sqlite itself thats at fault here.

Rob

On 4 Oct 2016, at 13:24, Werner Kleiner wrote:

> Thanks for help.
> Hopefully I give you the correct answer, because a collegue has
> written the C# program.
> We had no problems with inserts in the past, but now if we have
> records about 6000 inserts we get the errors.
>
> The OS is Server 2012, there are no pragma settings (but collegue has
> also used with pragma settings)
> It is a single thread with "synchronize full"
> We use the System.Data.SQLite.dll with version 1.0.89.0 from
> 12.12.2013
>
> hope this is what you mean
>
> 2016-10-04 13:48 GMT+02:00 Jim Borden <[hidden email]>:
>> I had a problem similar to this before. What is the threading model
>> for access to the database and how is the native library compiled and
>> configured?
>>
>> Jim Borden
>> (Sent from a mobile device)
>>
>>> On 4 Oct 2016, at 19:12, Werner Kleiner <[hidden email]>
>>> wrote:
>>>
>>> Hello,
>>> a program written in C# makes inserts from an SQL script into a
>>> sqlite db.
>>> We now saw that the database will be malformed after 6000 records.
>>>
>>> Is there a limitation with huge inserts?
>>> What could be the problem?
>>>
>>> regards
>>> Werner
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database malformed after 6000 Inserts?

Chris Locke
In reply to this post by Werner Kleiner-2
Are you accessing the database across a network, or is local on the server?
Are you using a transaction, or are these individual inserts?
I'm using system.data.sqlite.dll for a file backup program and that happily
rattles through 800,000 inserts (continuously) without issues.
Its worth noting that v103 was recently released, so the v89 you're using
is fairly old.  Thats not the cause of the problem, but it may not be
helping.

Thanks,
Chris
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database malformed after 6000 Inserts?

Simon Slavin-3
In reply to this post by Werner Kleiner-2

On 4 Oct 2016, at 1:24pm, Werner Kleiner <[hidden email]> wrote:

> Hopefully I give you the correct answer, because a collegue has
> written the C# program.
> We had no problems with inserts in the past, but now if we have
> records about 6000 inserts we get the errors.
>
> The OS is Server 2012, there are no pragma settings (but collegue has
> also used with pragma settings)
> It is a single thread with "synchronize full"

I'm not going to claim that there are no bugs in SQLite since some of the traffic on this list is about fixing bugs in SQLite.  However, a random bug which corrupts the database is extremely unlikely.  Can I ask you to read this page

<https://www.sqlite.org/howtocorrupt.html>

and tell us if you're doing any of those things, or anything close to one of those things ?  There are numerous things you could be doing which would corrupt the database and it would help us narrow down the cause.  My suspicions in this case lead me to section 3.2, for example, but you know your code better than we do.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database malformed after 6000 Inserts?

Roger Binns
In reply to this post by Werner Kleiner-2
On 04/10/16 03:11, Werner Kleiner wrote:
> ... after 6000 records.
>
> Is there a limitation with huge inserts?

While there may be "Lite" in the name, SQLite copes very well with
"huge" stuff.

That means many many gigabytes in database sizes, many many millions of
rows, up to 2GB per row etc.  6,000 records probably fits in caches, and
isn't remotely close to huge as far as SQLite is concerned.

Roger



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (188 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Database malformed after 6000 Inserts?

Simon Slavin-3

On 4 Oct 2016, at 6:30pm, Roger Binns <[hidden email]> wrote:

> On 04/10/16 03:11, Werner Kleiner wrote:
>> ... after 6000 records.
>>
>> Is there a limitation with huge inserts?
>
> While there may be "Lite" in the name, SQLite copes very well with
> "huge" stuff.
>
> That means many many gigabytes in database sizes, many many millions of
> rows, up to 2GB per row etc.  6,000 records probably fits in caches, and
> isn't remotely close to huge as far as SQLite is concerned.

But note also that you can mess yourself up.  So if you think you're going to speed things up by setting journal_mode = MEMORY, and you have 8GB of memory free, and you do a transaction which takes up 40GB then you have only yourself to blame.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users