Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Gary Weaver]

On Sat, Aug 17, 2013 at 11:47 AM, Keith Medcalf  wrote:

> > On 16.08.2013 18:03, Gary Weaver wrote:
> > You're right concurrency is not outlandish but from the viewpoint of
> > sqlite it was therefore this answer and the link.
>
> Hold on here.
>
> What do you mean "it works in OS X" and "not in Ubuntu VM in Travis"?
>
> The issue is not the concurrency in SQLite, but rather that the Operating
> System is broken because the "concurrency" of the SQLite library does *not*
> change -- the code is exactly the same -- the change is the OS.  You need
> to look at what is unsupported or non-functional in the "Ubuntu VM in
> Travis" since that is the root of the problem, and has nothing to do with
> SQLite itself.
>
>
I appreciate everyone's comments, but please read the entire thread. I was
pointed by a few people to pages in which SQLite documentation indicates
that to, on its own, does not handle concurrent writes. I've also relayed
everything I've learned about the environment here in the thread, and have
relayed it to the TravisCI team in the hope that they might be able to
track it down or get Blue Box's assistance in tracking it down.

SQLite has been very useful to me in testing. Yes, I was only surprised to
learn that it was as mature as it is and not meant to handle concurrent
writes, but I'm much less surprised now that I know that it relies on the
filesystem to handle locks properly and that concurrent writes typically
work for non-networked filesystems, which is what I experienced in OS X.

I spent time today doing some research on the environment in which the
issue occurred, and have asked for additional assistance from the TravisCI
team to help track this down, but I'm just doing this for the purpose of
helping other SQLite users, because at this point, it is not affecting me
much. As I'm sure you all understand, there are a lot of projects having
continuous integration testing on Travis using SQLite. I don't think that
mentioning the problem or pursuing a fix is a bad thing.

> the Operating System is broken

I think that is a little harsh. It just might be having some issues with
posix locks (maybe- not yet proven).

> You need to look at what is unsupported or non-functional in the "Ubuntu
VM in Travis" since that is the root of the problem, and has nothing to do
with SQLite itself.

If you'd read the thread, you'd have seen that I already started that.

Please everyone- lay off. Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Keith Medcalf]

> On 16.08.2013 18:03, Gary Weaver wrote:
> > Thanks to you and Richard for the links and info. Concurrency is not
> outlandish to expect, especially when it works in OS X and just not in
> the Ubuntu vm in Travis, but I now understand that these are expected
> problems.
> > ___
> >
> You're right concurrency is not outlandish but from the viewpoint of
> sqlite it was therefore this answer and the link.

Hold on here.

What do you mean "it works in OS X" and "not in Ubuntu VM in Travis"?

The issue is not the concurrency in SQLite, but rather that the Operating 
System is broken because the "concurrency" of the SQLite library does *not* 
change -- the code is exactly the same -- the change is the OS.  You need to 
look at what is unsupported or non-functional in the "Ubuntu VM in Travis" 
since that is the root of the problem, and has nothing to do with SQLite itself.




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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Gary Weaver]

Yuriy,

Thanks for following up. Henrik of TravisCI had said that "We do not use
network filesystems by default. We do use ramfs for some databases, but I
don't think SQLite would use ramfs by default unless you put the database
in an odd location. I'm also fairly sure we don't disable locks in any way."

I did a little more research by running some commands in the vm during
testing, and here is more info about the environment and filesystem it is
running in:

$ sqlite3 -version
3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e

$ uname -r
2.6.32-042stab061.2

$ cat /proc/version
Linux version 2.6.32-042stab061.2 (root@rh6-build-x64) (gcc version 4.4.6
20120305 (Red Hat 4.4.6-4) (GCC) ) #1 SMP Fri Aug 24 09:07:21 MSK 2012

$ lsb_release -a
Distributor ID: Ubuntu
Description: Ubuntu 12.04.2 LTS
Release: 12.04
Codename: precise

So it looks like an Ubuntu 12.04.2 LTS VM running on a Blue Box server
which is running Redhat 4.4.6-4.

(note: the home dir it is running in is within the / mount.)

$ cat /proc/mounts
/dev/simfs / simfs rw,relatime 0 0
...

$ mount
/vz/private/6062841 on / type simfs (rw)
...

More info (very limited) on the Blue Box hosting:
"Blue Box leverages an assortment of open source technologies including
OpenStack, EMC Razor and Opscode’s Chef alongside Blue Box intellectual
property and technologies.": https://bluebox.net/press-releases/travis-ci
https://bluebox.net/technology
http://redhat.sys-con.com/node/2586422
OpenStack storage (assuming that is what they use?):
http://www.openstack.org/software/openstack-storage/
If using OpenStack, more info on build:
http://www.turnkeylinux.org/blog/announcing-openstack-builds

I don't help manage TravisCI, so I'd have to relay questions about anything
else.



On Sat, Aug 17, 2013 at 12:55 AM, Yuriy Kaminskiy  wrote:

> Gary Weaver wrote:
> > On Aug 15, 2013, at 3:47 PM, ibrahim 
> wrote:
> >
> >> On 15.08.2013 21:39, Gary Weaver wrote:
> >>> SQLite varies between file is encrypted/not a DB errors and database
> disk image is malformed. It would seem consistent with SQLite not handling
> concurrent processing in this particular environment or with the version of
> SQLite since 3.7.7 is fine in OS X.
> >>>
> >>> Is there anything that stands out as something that would keep 30
> processes from being able to concurrently insert into the same tables?
> >> 30 processes being able to concurrently insert into the same tables ???
> >
> > Thanks to you and Richard for the links and info. Concurrency is not
> outlandish to expect, especially when it works in OS X and just not in the
> Ubuntu vm in Travis, but I now understand that these are expected problems.
>
> There are *two* problems above, one is "expected", and one is not.
>
> 1) SQLite won't execute any database modification concurrently, they will
> be
> serialized internally (or return SQLITE_BUSY error, depending on
> timeouts). That
> is, executing requests concurrently won't be any faster than executing them
> consequently from single connection. And those locks are not fine-grained
> - they
> are database-wide, not table or page-wide. And this one is "expected
> problem".
>
> 2) But database corruption is certainly an UNEXPECTED problem: no matter
> how
> many processes are trying to access same database, this is not expected to
> corrupt database. If database was corrupted as result of concurrent access,
> likely reasons are: FS bug, especially with file locking (e.g. many
> networking
> FS [nfs, cifs, etc] are known to be buggy/broken with locking; and anything
> FUSE-based is likely also problematic), other kernel/drivers bugs,
> malfunctioning hardware. Of course, "SQLite bug" is also possible, but very
> unlikely (sqlite is extensively tested in various scenarios, including
> concurrent access).
> If you use any "unusual" filesystem, that would be the prime suspect. E.g.
> if
> your VM allows transparent access to part of host fs, and you can open
> connections to same database on host fs from several guests, that's recipe
> for
> disaster.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[ibrahim]

On 16.08.2013 18:03, Gary Weaver wrote:

Thanks to you and Richard for the links and info. Concurrency is not outlandish 
to expect, especially when it works in OS X and just not in the Ubuntu vm in 
Travis, but I now understand that these are expected problems.
___

You're right concurrency is not outlandish but from the viewpoint of 
sqlite it was therefore this answer and the link.


On the other side most database systems also use serialized access to 
the underlying filesystem while always exclusivly locking the whole 
file, disk aso. The magic behind concurrently accessing the database in 
those systems is to provide a client/server level where only the server 
process has real access to the underlying physical storage while the 
clients only have concurrent access to the server as a process. The fact 
that most filesystem implementations especialy the network file 
protocols are buggy and locking methods on OS es have those many 
problems is proof enough that you need a client server modell if you 
really want to support concurrent access.  Sqlite on the other side is 
for most applications a database engine library and an appreciated way 
to access large amounts of data in a flexible way.


Concurrency is not outlandish but I for myself prefer that sqlite is a 
simple library which at least assures that many applications trying to 
access the same file can do this in a serialized way without corrupting 
the file. And thats not a simple thing to achieve and get freely.


If you are interested on client server protocols wrapped around sqlite 
there are a few and even contributed on the website.


It was not my intention to blame your statement but it was surprising to 
hear it for sqlite.


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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Yuriy Kaminskiy]

Gary Weaver wrote:
> On Aug 15, 2013, at 3:47 PM, ibrahim  wrote:
> 
>> On 15.08.2013 21:39, Gary Weaver wrote:
>>> SQLite varies between file is encrypted/not a DB errors and database disk 
>>> image is malformed. It would seem consistent with SQLite not handling 
>>> concurrent processing in this particular environment or with the version of 
>>> SQLite since 3.7.7 is fine in OS X.
>>>
>>> Is there anything that stands out as something that would keep 30 processes 
>>> from being able to concurrently insert into the same tables?
>> 30 processes being able to concurrently insert into the same tables ???
> 
> Thanks to you and Richard for the links and info. Concurrency is not 
> outlandish to expect, especially when it works in OS X and just not in the 
> Ubuntu vm in Travis, but I now understand that these are expected problems.

There are *two* problems above, one is "expected", and one is not.

1) SQLite won't execute any database modification concurrently, they will be
serialized internally (or return SQLITE_BUSY error, depending on timeouts). That
is, executing requests concurrently won't be any faster than executing them
consequently from single connection. And those locks are not fine-grained - they
are database-wide, not table or page-wide. And this one is "expected problem".

2) But database corruption is certainly an UNEXPECTED problem: no matter how
many processes are trying to access same database, this is not expected to
corrupt database. If database was corrupted as result of concurrent access,
likely reasons are: FS bug, especially with file locking (e.g. many networking
FS [nfs, cifs, etc] are known to be buggy/broken with locking; and anything
FUSE-based is likely also problematic), other kernel/drivers bugs,
malfunctioning hardware. Of course, "SQLite bug" is also possible, but very
unlikely (sqlite is extensively tested in various scenarios, including
concurrent access).
If you use any "unusual" filesystem, that would be the prime suspect. E.g. if
your VM allows transparent access to part of host fs, and you can open
connections to same database on host fs from several guests, that's recipe for
disaster.

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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Simon Slavin]

On 16 Aug 2013, at 5:03pm, Gary Weaver  wrote:

> Thanks to you and Richard for the links and info. Concurrency is not 
> outlandish to expect, especially when it works in OS X and just not in the 
> Ubuntu vm in Travis, but I now understand that these are expected problems.

Worth noting that concurrency works just fine in SQLite if all that accesses 
are done by directly talking to the hard disk and not using an NFS.  In other 
words you have to do it the way the big server/client DBMSes do it -- MySQL and 
Progress --  you don't have lots of computers opening the a database file on a 
remote hard disk, you run processes on the server computer itself and only 
those try to open the database file.

This is what happens with the, I'm sure, millions of web-facing database 
applications which use SQLite as a back end.  You request database action using 
HTTPS (either through JavaScript or perhaps by using a utility like 'curl' on 
your computer).  The server spawns an httpd process which does the actual file 
access and returns the results in the HTTPS connection.  That's what you get 
when you write a PHP backend.

SQLite's API automatically takes care of serialising concurrent requests, using 
an algorithm like exponential back-off.  It works fine.  But it works because 
all those processes are running on the same computer opening a SQLite database 
on its own hard disk, rather than remote computers trying to read and write 
that file using a Network File System.  You have promoted the network traffic 
to the level of API request rather than file reading and writing.

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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Gary Weaver]

On Aug 15, 2013, at 3:47 PM, ibrahim  wrote:

> On 15.08.2013 21:39, Gary Weaver wrote:
>> SQLite varies between file is encrypted/not a DB errors and database disk 
>> image is malformed. It would seem consistent with SQLite not handling 
>> concurrent processing in this particular environment or with the version of 
>> SQLite since 3.7.7 is fine in OS X.
>> 
>> Is there anything that stands out as something that would keep 30 processes 
>> from being able to concurrently insert into the same tables?
> 30 processes being able to concurrently insert into the same tables ???

Thanks to you and Richard for the links and info. Concurrency is not outlandish 
to expect, especially when it works in OS X and just not in the Ubuntu vm in 
Travis, but I now understand that these are expected problems.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[ibrahim]

On 15.08.2013 21:39, Gary Weaver wrote:

SQLite varies between file is encrypted/not a DB errors and database disk image 
is malformed. It would seem consistent with SQLite not handling concurrent 
processing in this particular environment or with the version of SQLite since 
3.7.7 is fine in OS X.

Is there anything that stands out as something that would keep 30 processes 
from being able to concurrently insert into the same tables?

30 processes being able to concurrently insert into the same tables ???

I suggest you should read this


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


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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Richard Hipp]

On Fri, Aug 16, 2013 at 6:58 AM, Gary Weaver  wrote:

>
> I'm going to stop on this for now, because it sounds like SQLite was never
> intended to support concurrent writes to a file DB from what you said, so
> there is no use trying to debug an use case that isn't supported. I am a
> little surprised about this behavior, so I would think others might run
> into it also, but I understand there are limitations and scope to any
> project.
>
>
Sorry it didn't work out for you.  The trade-offs of SQLite are well-known
and are discussed here:  http://www.sqlite.org/whentouse.html

-- 
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] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Gary Weaver]

On Thu, Aug 15, 2013 at 4:20 PM, Richard Hipp  wrote:

> On Thu, Aug 15, 2013 at 3:39 PM, Gary Weaver 
> wrote:
>
> >
> > Is there anything that stands out as something that would keep 30
> > processes from being able to concurrently insert into the same tables?
> >
>
> Yes.  SQLite does not (and has never) supported that.  Multiple processes
> can read from the same database at the same time, but only one can be
> writing at any given time.  Multiple writers have to take turns.
>
> SQLite serializes the writers using posix advisory locks (on unix) by
> default and unless the application has done something unusual to disable
> the locking.  But posix advisory locks are sometimes broken on network
> filesystems.  Is the database being stored on a network filesystem?
>
>
Richard, their response was, "We do not use network filesystems by default.
We do use ramfs for some databases, but I don't think SQLite would use
ramfs by default unless you put the database in an odd location. I'm also
fairly sure we don't disable locks in any way."

I'm going to stop on this for now, because it sounds like SQLite was never
intended to support concurrent writes to a file DB from what you said, so
there is no use trying to debug an use case that isn't supported. I am a
little surprised about this behavior, so I would think others might run
into it also, but I understand there are limitations and scope to any
project.

-Gary



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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Richard Hipp]

On Thu, Aug 15, 2013 at 3:39 PM, Gary Weaver  wrote:

>
> Is there anything that stands out as something that would keep 30
> processes from being able to concurrently insert into the same tables?
>

Yes.  SQLite does not (and has never) supported that.  Multiple processes
can read from the same database at the same time, but only one can be
writing at any given time.  Multiple writers have to take turns.

SQLite serializes the writers using posix advisory locks (on unix) by
default and unless the application has done something unusual to disable
the locking.  But posix advisory locks are sometimes broken on network
filesystems.  Is the database being stored on a network filesystem?

-- 
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] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Gary Weaver]

On Aug 15, 2013, at 3:20 PM, Richard Hipp  wrote:

> On Thu, Aug 15, 2013 at 3:10 PM, Gary Weaver  wrote:
> 
>> SQLite DB (file) corruption in travisci vm with 3.7.9 (latest they had
>> available). Link to logs showing errors, code to reproduce here:
>> https://github.com/travis-ci/travis-ci/issues/1334
>> 
>> Is there something in config, etc. that would help?
> 
> What makes you believe this is an SQLite problem, and not a problem
> somewhere else in Travis-CI that SQLite just happens to be stumbling over?

Richard, thanks for your response. If there is a problem in the file IO it 
seems like it would have caused problems for them before now. They do a lot. :)

SQLite varies between file is encrypted/not a DB errors and database disk image 
is malformed. It would seem consistent with SQLite not handling concurrent 
processing in this particular environment or with the version of SQLite since 
3.7.7 is fine in OS X.

Is there anything that stands out as something that would keep 30 processes 
from being able to concurrently insert into the same tables?

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

Re: [sqlite] Concurrent inserts -> DB corruption in travisci vm with sqlite 3.7.9

[Richard Hipp]

On Thu, Aug 15, 2013 at 3:10 PM, Gary Weaver  wrote:

> SQLite DB (file) corruption in travisci vm with 3.7.9 (latest they had
> available). Link to logs showing errors, code to reproduce here:
> https://github.com/travis-ci/travis-ci/issues/1334
>
> Is there something in config, etc. that would help?
>

What makes you believe this is an SQLite problem, and not a problem
somewhere else in Travis-CI that SQLite just happens to be stumbling over?



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