Unfortunately i don't have access to lsof or fuser on the hosting machine i tried to make a cron job from cpanel but it returned that they don't exist (or at least i dont't have access to them...) so i can't use that... and i don't have any other access to the server no ssh or telnet or whatever just ftp...
> If you put in extra locking at the PHP level, then only your PHP will use > it. If something else is locking the database, then it will not help > unless it follows the same lock file protocol. now about that i was more asking if i have done it right more than will it work because i have never done locking... Unfortunately that didn't work the database is still locking And unfortunatelly the last two days it is loking more than ever it locks 4-5 times a day aside from the night lockings... So i modified the log in which i put the queries that are run to that form (i am 100% sure that every script uses that class for running queries so every query must be inside the log): before sqlite_query: IP:xxxxxxxxx time: xxxxxxx filename: xxxxxxxx.php username:xxxxxxx sql:xxxx and after sqlite_query: if sqlite_query returned false: not done: sql query..... else: done So the result log is tipically : sql..... done naturally in a multithreaded environment that is a web site they may mix like that: sql... sql... done done but i noticed that this case is very rare so naturally the suspicion laied on them so i got them in a file and started testing them on a small script on the site that basically lets you enter a query and run it in the database and there were no problems every query from the "suspects" was doing just fine doing what it is supposed to do and most importanly not locking the database... I got an idea please tell me is it possible this scenario: When the site was first uploaded to the hosting there was only PHP5 beta version and the hosting didn't had a server with installed PHP5 so they installed it for us and i suppose we were using a separate machine. At some point these problems began and i noticed that they have installed PHP 5.0.2 (to be honest i don't remember exactly if i have first noticed PHP 5.0.2 or first the problems began) so i suppose they have installed PHP 5.0.2 on a major machine and now the scripts are using this PHP throught NFS and i think i remember seeing somewhere that sqlite had some locking problems with NFS (and if i remember correctly it was not only sqlite...). So if the PHP is running the scripts throught NFS will be there locking problems ? Thank you. Hope someone will come up with some idea. On Fri, 26 Nov 2004 11:52:16 +0000 (GMT), Christian Smith <[EMAIL PROTECTED]> wrote: > On Wed, 24 Nov 2004, Alexander Jordanov wrote: > > >@Christian Smith > >> Please, reply to the list as well, as any information may be useful to > >> others. > >Sorry about that i'm new to the mailing lists... > >I'll try lsof i just wonder what it will return? Is it going to tell > >me that the file is locked by PHP or it will tel me also the script?? > >I suppose that it will be the first which is not very useful. > > > Well, at least you'll have a suspect. > > > >> You really need a test server to debug on! > >As i said i have a test server but nevar managed to lock the database > >on it (and that's the reason i've asked what could cause a lock if > >only i knew where to look...) if i could i would've already found what > >is causing the lock and probably fix it... or at least find a way to > >avoid it. > > > Does the test machine have the same configuration as the production > machine? Ideally, it should be a mirror, especially if you're trying to > reproduce a problem. > > If not the same, then it could be some extra process on the production > machine, which fuser or lsof will point the finger at. > > > >Please anybody else i am currently trying the suggestion of > >Ara.T.Howard i've answered to the mail list also but it seems he(she?) > >is not available until Monday "i'm out skiing! back the monday > >following turkey day.". So if anybody knows if what i've done is > >correct or not please tell me. Or if anyone has any other suggestions. > >It's urgent. Thank you. > > > If you put in extra locking at the PHP level, then only your PHP will use > it. If something else is locking the database, then it will not help > unless it follows the same lock file protocol. > > > > > > > > > > > >On Tue, 23 Nov 2004 16:28:12 +0000 (GMT), Christian Smith > ><[EMAIL PROTECTED]> wrote: > >> Please, reply to the list as well, as any information may be useful to > >> others. > >> > >> Inline... > >> > >> > >> > >> On Tue, 23 Nov 2004, Alexander Jordanov wrote: > >> > >> >First thank you for the fast reply > >> >Well i already have such a log that logs all the queries that are run > >> >by my php scripts and there was never a BEGIN TRANSACTION or COMMIT > >> >but you might be right for the activity because Saturday and Sunday i > >> >was constantly monitoring if the database is locked by running an > >> >UPDATE query about every hour and it didn't lock at all so i suppose > >> >i'll run a cron to run me one update query and will see if it helps > >> >Unfortunatelly the scripts are not locally (i have a local copy but > >> >never managed to lock it) > >> > >> > >> I wonder if the PHP backend is locking it? Not an area of expertise for > >> me, unfortunately. > >> > >> >What is lsof? > >> > >> lsof means "list open files". It's a free utility, shipped with most Linux > >> distros, and available for most UNIX platforms from source. Get it from: > >> > >> ftp://lsof.itap.purdue.edu/pub/tools/unix/lsof > >> > >> Typical output for a quiescent sqlite shell (interesting files only listed > >> below): > >> [EMAIL PROTECTED] csmith]$ lsof | grep sqlite > >> ... > >> sqlite 29546 csmith 3u REG 0,13 3072 2667735 > >> /home/csmith/tmp/db (netapp:/vol/home/csmith) > >> sqlite 29546 csmith 4u REG 8,6 0 115375 > >> /var/tmp/sqlite_RLChwRJBRKUaXLK (deleted) > >> > >> File 3 in this case is the main database file. File 4 is a temporary > >> database file, used, I think, for intermediate results. > >> > >> Creating a transaction in the shell: > >> [EMAIL PROTECTED] csmith]$ lsof | grep sqlite > >> ... > >> sqlite 29546 csmith 3uW REG 0,13 3072 2667735 > >> /home/csmith/tmp/db (netapp:/vol/home/csmith) > >> sqlite 29546 csmith 4uW REG 8,6 0 115375 > >> /var/tmp/sqlite_RLChwRJBRKUaXLK (deleted) > >> sqlite 29546 csmith 5u REG 0,13 20 2667736 > >> /home/csmith/tmp/db-journal (netapp:/vol/home/csmith) > >> sqlite 29546 csmith 7u REG 8,6 20 115380 > >> /var/tmp/sqlite_RLChwRJBRKUaXLK-journal (deleted) > >> > >> In the second listing, notice that file descriptors 3 and 4 have uW flags. > >> This means the files are open for update, and both have write locks on the > >> whole file (as a result of the open transaction.) Files 5 and 7 are the > >> rollback journals for the main and temporary databases respectively. > >> > >> > >> >Unfortunatelly I have no choise about how to unlock (at least until > >> >somebody tells me a better way to unlock it) because this site is the > >> >official site of the company I work in and we are shipping a new > >> >product these days and a lot of people will be visiting the site so it > >> >has to be running. > >> > >> > >> You really need a test server to debug on! > >> > >> > >> >What do you mean by "the copy is not atomic"? > >> > >> > >> Copying the file is not done in a single action relative to everything > >> else. The file can be updated while it is being copied, with the resulting > >> copy not being a consistent file. Consider: > >> > >> Copy Process Update Process > >> ------------ -------------- > >> Copy block 0 > >> Copy block 1 > >> Update block 1 > >> Update block 2 > >> Copy block 2 > >> ... > >> > >> At the end of the copy, the copy has the updated block 2, but not the > >> updated block 1. Corrupt database. > >> > >> > >> >Since the database has been locked for several hours and there is no > >> >script writing to it i thought it is safe enought (and that's the > >> >reason i keep a backup copy - if the copy becomes corrupted somehow) > >> >Thank you again i'll see if the constant activity will solve the things. > >> > >> > >> Christian > >> > >> > >> > >> > >> > > >> >On Tue, 23 Nov 2004 15:18:42 +0000 (GMT), Christian Smith > >> ><[EMAIL PROTECTED]> wrote: > >> >> My guess is that you have a script that opens a transaction, waits for > >> >> something to happen, updates the database, then commits the changes. > >> >> > >> >> This may be why you are seeing the locking at night when there is little > >> >> activity. Something may be beginning a transaction (thus locking the > >> >> database) and waiting patiently for some activity. During the day, the > >> >> constant activity ensures that there is little to no waiting between > >> >> opening a transaction and an update. > >> >> > >> >> I'd check your scripts. Log to a file when you create a transaction, > >> >> then > >> >> log when you update the database and commit the transaction. > >> >> > > > >> >> You may be able to check the lock condition of the file using lsof if > >> >> all > >> >> the scripts run locally. This will at least point you at the offending > >> >> script if any. > >> >> > >> >> Christian > >> >> > >> >> PS. I'd recommend NOT unlocking the file in the manner you describe! You > >> >> can corrupt the database, as the copy is not atomic. > >> >> > >> >> > >> >> > >> >> > >> >> On Tue, 23 Nov 2004, Alexander Jordanov wrote: > >> >> > >> >> >Hi i have an SQLite database file that gets locked from time to time > >> >> >and some parts of the site that uses the sqlite cannot be accesed. For > >> >> >what i know the SQLite database uses the OS locking mechanism. > >> >> >So my question is: Is there any way to unlock it (preferabbly by using > >> >> >PHP)? > >> >> >The most interesting part is that the file locks most often at night > >> >> >when there is almost no activity but is behaving very well during the > >> >> >day when there is a lot of activity - and that's the reason why i > >> >> >think it's not my script locking it otherwise it would lock mostly > >> >> >during the day when there is a lot of queries to the database (if it > >> >> >wasn't for that i would seek the problem in my scripts). I have made a > >> >> >log file for the queries that are run by the script and i see there > >> >> >just a few INSERT or UPDATE queries and a lot of SELECT queries and > >> >> >nothing that (at least i think so) could lock the database. > >> >> >And another question is: What could lock the database? I have succeded > >> >> >to lock it on linux when a timeout for the script occured (the script > >> >> >was just doing a lot of SELECT-s and very little INSERT or UPDATE -s) > >> >> >but i don't beleive that that is how the database gets locked because > >> >> >I had put a timeout to my scripts of 3600 s and it still gets locked > >> >> >and i don't beleive that any of my scripts will need that much time to > >> >> >finish (i don't remember doing something infinite anywhere and if i do > >> >> >then the guys from the hosting would've called me by now :) ) and > >> >> >especially the scipts that are being run around the time when the > >> >> >locking occurs. > >> >> >The current problems occured recently, before that the site was > >> >> >working very stable... > >> >> >Is it possible a corrupt database to have such a behaviour? > >> >> >Currently i unlock the database by a script i made that basically > >> >> >copies the file renames the old wan to '.bak' and renames the copy to > >> >> >the database file name, it works but i do not dare to make this > >> >> >automatic because if something happens during the copy i will lose the > >> >> >entire database... > >> >> >I almost forgot: SQLite version is 2.8.14, php is 5.0.2 on Apache > >> >> >release 10331100 API version 19990320 . > >> >> >I know that versions prior to 2.8.9 had some problems with locking > >> >> >under linux is it possible that thay are still there? > >> >> >Sorry if this is told too confusing the reason is that this problem is > >> >> >pretty urgent and i wanted to say everything i have tried and any > >> >> >information that may be relevant so i don't be asked for more > >> >> >information... > >> >> > > >> >> >Shortly: is there any way to unlock the database right there on the > >> >> >hosting server? and what could cause the locking? > >> >> > > >> >> >Please help me. Thank you > >> >> > > >> >> > >> >> -- > >> >> /"\ > >> >> \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > >> >> X - AGAINST MS ATTACHMENTS > >> >> / \ > >> >> > >> > > >> > >> -- > >> /"\ > >> > >> > >> \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > >> X - AGAINST MS ATTACHMENTS > >> / \ > >> > > > > -- > /"\ > > > \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > X - AGAINST MS ATTACHMENTS > / \ >
