SQLite problem

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
paulclaude
Posts: 98
Joined: Thu Mar 27, 2008 10:19 am

SQLite problem

Post by paulclaude » Thu Sep 10, 2015 2:43 pm

I've a SQLite Database (not protected) in a dropbox folder. If another Mac try to access it with a copy of my app when it's already open, the first app that open the database freeze.
So I've two important questions_

1) It's possible to "share" a SQLite database for multiple accesses from different computers (and how)?
2) How can I detect if a database on dropbox is already open to "stop" the user from trying to open it from another computer?

Thanks...
Last edited by paulclaude on Thu Sep 10, 2015 4:12 pm, edited 1 time in total.

MaxV
Posts: 1542
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: MySQL problem

Post by MaxV » Thu Sep 10, 2015 3:31 pm

MySQL database (not SQLite) must be accessed by a MySQL server.
The server and the mysql files must be unique and in the same PC.
The server has an IP address.
You connect to your MySQL server using the IP address with a mySQL client.
Usually there is a MySQL server and many MySQL clients.

The dropbox way will only create errors on MySQL files, because more than a MySQL server try to use the files.
There are many free hosting for small web and mysql servers around the world. :D

The other way is to use your PC as a public server, free service like dynamic dns give to your PC a fix address every time you turn on it. So you can use your PC like a server. Another solution is to ask a fix IP to your internet provider.
Livecode Wiki: http://livecode.wikia.com
My blog: http://livecodeitalia.blogspot.it
To post code use this: http://tinyurl.com/ogp6d5w

paulclaude
Posts: 98
Joined: Thu Mar 27, 2008 10:19 am

Re: MySQL problem

Post by paulclaude » Thu Sep 10, 2015 4:12 pm

Sorry, MaxV, that's my fault: it was an SQLite database, not MySQL, I will correct also the post subject!

Can you give me some suggestion about the SQLIte DB?

LCMark
Livecode Staff Member
Livecode Staff Member
Posts: 996
Joined: Thu Apr 11, 2013 11:27 am

Re: SQLite problem

Post by LCMark » Thu Sep 10, 2015 4:24 pm

@paulclaude: I must confess I did wonder whether you meant SQLite when I noticed your post.

Unfortunately, the situation is essentially the same - there's no way you could have multiple accessors to a file in a drop-box folder as there is no possible synchronization between the uses of it on the difference machines.

If you need multi-user access to an SQL database, then you should use an RDMBS like MySQL or PostgreSQL.

paulclaude
Posts: 98
Joined: Thu Mar 27, 2008 10:19 am

Re: SQLite problem

Post by paulclaude » Thu Sep 10, 2015 4:28 pm

I suspected this, LCMark , but my question then is how can I detect if a SQLite database on dropbox is already open to "stop" the user from trying to open it from another computer?

LCfraser
Livecode Staff Member
Livecode Staff Member
Posts: 71
Joined: Thu Nov 28, 2013 11:18 am
Contact:

Re: SQLite problem

Post by LCfraser » Thu Sep 10, 2015 6:06 pm

The usual way of doing "does some other program have this file open?" detection is to create a file called "<filename>.lock" and use the presence or absence of this file. This is especially common on Linux and Unix systems as they don't have a built-in mechanism for opening files while blocking access by others.

paulclaude
Posts: 98
Joined: Thu Mar 27, 2008 10:19 am

Re: SQLite problem

Post by paulclaude » Thu Sep 10, 2015 6:11 pm

I was hoping there was a SQLite command to ask if the database was already open or not...

LCfraser
Livecode Staff Member
Livecode Staff Member
Posts: 71
Joined: Thu Nov 28, 2013 11:18 am
Contact:

Re: SQLite problem

Post by LCfraser » Thu Sep 10, 2015 6:16 pm

https://www.sqlite.org/faq.html#q5

From the looks of this, SQLite uses the OS' built-in file locking mechanisms. I'd be very surprised if Dropbox provides these, though - multiple processes on a single system would work but I suspect that it won't work across multiple computers.

MaxV
Posts: 1542
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: SQLite problem

Post by MaxV » Fri Sep 11, 2015 9:08 am

Even if you could use sqlite as you wish, with dropbox there is a big issue about syncronizing time. Two users could modify their local DB at the same time, both users wouldn't receive any warning, creating conflicts.
Dropbox sync files every 1-2 seconds, it's too much.
I encountered sync problem with dropbox,it pop up a window message warning that there is a problem on some files.
Livecode Wiki: http://livecode.wikia.com
My blog: http://livecodeitalia.blogspot.it
To post code use this: http://tinyurl.com/ogp6d5w

paulclaude
Posts: 98
Joined: Thu Mar 27, 2008 10:19 am

Re: SQLite problem

Post by paulclaude » Fri Sep 11, 2015 9:54 am

It seems the only solution is to implement in my app a check to the internet connection, to avoid using local DB disconnected from dropbox, and then to create a file called "<filename>.lock" and use the presence or absence of this file to avoid users try to access simultaneously the DB.

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 279
Joined: Tue Apr 10, 2012 9:18 am

Re: SQLite problem

Post by ghettocottage » Fri Sep 11, 2015 3:51 pm

paulclaude wrote:It seems the only solution is to implement in my app a check to the internet connection, to avoid using local DB disconnected from dropbox, and then to create a file called "<filename>.lock" and use the presence or absence of this file to avoid users try to access simultaneously the DB.

...or switch to a MySQL server, which can handle multiple connections at once.

MaxV
Posts: 1542
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: SQLite problem

Post by MaxV » Mon Sep 14, 2015 11:31 am

paulclaude wrote:It seems the only solution is to implement in my app a check to the internet connection, to avoid using local DB disconnected from dropbox, and then to create a file called "<filename>.lock" and use the presence or absence of this file to avoid users try to access simultaneously the DB.
No, you can't; because:
Time 12:00
DB is unlocked on pc A and pc B

Time 12:01
user A lock DB creating .lock file
user B lock DB creating .lock file

Time 12:02
dropbox sync its folder: ERROR
Livecode Wiki: http://livecode.wikia.com
My blog: http://livecodeitalia.blogspot.it
To post code use this: http://tinyurl.com/ogp6d5w

zaxos
Posts: 211
Joined: Thu May 23, 2013 11:15 pm
Location: Greece

Re: SQLite problem

Post by zaxos » Mon Sep 14, 2015 1:37 pm

Why dont you switch over to MySQL? You will solve all of your problems.

http://www.000webhost.com/free-mysql-hosting Free web hosting service, including MySQL.
Knowledge is meant to be shared.

paulclaude
Posts: 98
Joined: Thu Mar 27, 2008 10:19 am

Re: SQLite problem

Post by paulclaude » Thu Sep 17, 2015 9:42 am

MaxV wrote:
paulclaude wrote:It seems the only solution is to implement in my app a check to the internet connection, to avoid using local DB disconnected from dropbox, and then to create a file called "<filename>.lock" and use the presence or absence of this file to avoid users try to access simultaneously the DB.
No, you can't; because:
Time 12:00
DB is unlocked on pc A and pc B

Time 12:01
user A lock DB creating .lock file
user B lock DB creating .lock file

Time 12:02
dropbox sync its folder: ERROR
Yes, I know this. There is a moment, a couple of seconds needed for the dropbox sync, when a conflict may occours if the DB access is simultaneous. I've made a file check where the user ID is written each time there is an access, and emptied at the end. An asyncronous double check of this file should avoid the above conflict. Anyway, I know that the only clean solution would be to switch to a MySQL DB.... (thanks zaxos)

rinzwind
Posts: 131
Joined: Tue May 01, 2012 10:44 am

Re: SQLite problem

Post by rinzwind » Sat Oct 03, 2015 2:02 pm

You can open an SQLite db more than once/concurrent usage. But it's just that Dropbox is not suitable for this because it has it's own sync mechanism and tries to be intelligent about it... which does not work with a shared db file. Dropbox does not work on a live copy. Each pc has its own copy of the file that is delta synced only once in a while. A normal modern file share (ie Windows XP/2003+) is suitable. Or maybe even webdav. But still.. lots of latency in comparison with a local file share...

FAQ:
(5) Can multiple applications or multiple instances of the same application access a single database file at the same time?

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.

Post Reply

Return to “Databases”