1175 Script continues after BeginTransaction/Commit
Moderator: Gurus
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
1175 Script continues after BeginTransaction/Commit
Script code after a BeginTransaction/Commit block continues before the commit is finished.
This is creating errors on new transactions and or new reads contain data not yet updated.
Please halt script execution until commit is complete
or
add a method to check if commit is still running
[EDIT]
If the script does
UpdateArtist
UpdateAlbum
UpdateDB
7 times
The next transaction I have to click retry between 20-30 times for it to go through (which is straight after the commit)
[EDIT AGAIN]
Even a way to automatically retry the transaction untill it stops erroring?
This is creating errors on new transactions and or new reads contain data not yet updated.
Please halt script execution until commit is complete
or
add a method to check if commit is still running
[EDIT]
If the script does
UpdateArtist
UpdateAlbum
UpdateDB
7 times
The next transaction I have to click retry between 20-30 times for it to go through (which is straight after the commit)
[EDIT AGAIN]
Even a way to automatically retry the transaction untill it stops erroring?
Do you have the code?
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!
All My Scripts
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!
All My Scripts
I solved my transaction problems by putting a commit before every begintransaction, along with the closing commit.
so
commit (to close out any other pending transactions)
begin transaction (start of your transaction)
commit (close your transaction)
this is how last.fm nodes has been working for 2-3 months
so
commit (to close out any other pending transactions)
begin transaction (start of your transaction)
commit (close your transaction)
this is how last.fm nodes has been working for 2-3 months
New script: Last.FM Node Now with DJ Mode!
Last.fm + MediaMonkey = Scrobbler DJ!
Tag with MusicBrainz ~ Get Album Art!
Tweak the Monkey! ~ My Scripts Page
Last.fm + MediaMonkey = Scrobbler DJ!
Tag with MusicBrainz ~ Get Album Art!
Tweak the Monkey! ~ My Scripts Page
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
Although attaching/detaching a database is unusual in a script it is a perfect example as detaching a database while a transaction is in progress will always create an error while it is a bit hit and miss to try and read/write a row that has/has not been committed yet.
The normal example would be refreshing a web control after the commit, you may or may not read the updated data
i.e. I don't know of any way to find out if the transaction/commit has actually completed.
The timer example here is ridicuclous for only 8 tracks
I have done some testing and even for 1 track I have to wait a minimum of 20secs.
Over the 20 secs I have set it to 3 secs per track so for 8 tracks it is 24 secs
As this is the end of the script 24 secs isn't too bad but if I want to use the updated data again I have to wait 24+ secs before updating a web control etc.
I haven't tested it with more tracks yet, it maybe only .005 secs per track after the minimum 20 secs?
Ideally there needs to be a command to make MM stop executing the script and show an hourglass until the commit has finished.
A Timer would work if there is a way to check if a transaction is still active.
The normal example would be refreshing a web control after the commit, you may or may not read the updated data
i.e. I don't know of any way to find out if the transaction/commit has actually completed.
The timer example here is ridicuclous for only 8 tracks
I have done some testing and even for 1 track I have to wait a minimum of 20secs.
Over the 20 secs I have set it to 3 secs per track so for 8 tracks it is 24 secs
As this is the end of the script 24 secs isn't too bad but if I want to use the updated data again I have to wait 24+ secs before updating a web control etc.
I haven't tested it with more tracks yet, it maybe only .005 secs per track after the minimum 20 secs?
Ideally there needs to be a command to make MM stop executing the script and show an hourglass until the commit has finished.
Code: Select all
SDB.Database.Commit
SDB.Database.WaitForCommit
Code: Select all
SDB.Database.IsRunningTransaction
Code: Select all
SDB.DataBase.ExecSQL("ATTACH " & Chr(34) & CopyFileName & Chr(34) & " AS ExportDB")
Total = SDB.Database.OpenSQL("SELECT COUNT(*) FROM ExportDB.Songs").ValueByIndex(0)
SDB.Database.BeginTransaction
Set iter = SDB.DataBase.OpenSQL("SELECT * FROM ExportDB.Songs")
Do While Not iter.EOF
Set iter2 = SDB.Database.QuerySongs("Songs.ID=" & iter.StringByName("ID"))
Do While Not iter2.EOF
Set objSongData = iter2.Item
iter2.Next
Loop
Set iter2 = Nothing
If Not objSongData Is Nothing Then
If objSongData.ID = iter.ValueByName("ID") Then
' Update Stuff
Else
' Something Wrong
End If
Else
' New Track
End If
If UpdateArtist = True Then
objSongData.UpdateArtist
End If
If UpdateAlbum = True Then
objSongData.UpdateAlbum
End If
objSongData.UpdateDB
iter.Next
Loop
Set iter = Nothing
SDB.Database.Commit
WaitTime = Total*3000
If WaitTime < 20000 Then
WaitTime = 20000
End If
Set Tmr = SDB.CreateTimer(WaitTime)
Script.RegisterEvent Tmr, "OnTimer", "TestTimer"
End Sub
Sub TestTimer(Tmr)
SDB.DataBase.ExecSQL("DETACH ExportDB")
Script.UnregisterEvents Tmr
End Sub
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
Additional Info
The only Error Number I can catch using ON ERROR is 450 of which the description isn't "Database Transaction in Progress"
I think because MM is trapping the error there is nothing I can do in the script to make it retry after 5 secs or something.
Of course for other examples it will not produce an error (unless you try to write it again) just non updated data
The only Error Number I can catch using ON ERROR is 450 of which the description isn't "Database Transaction in Progress"
I think because MM is trapping the error there is nothing I can do in the script to make it retry after 5 secs or something.
Of course for other examples it will not produce an error (unless you try to write it again) just non updated data
I have trouble to understand what you are trying to do but can't you:
1. Attach your Database do all the sql stuff
2. Update your Database
3. Detach your database
4. Update MM
1. Attach your Database do all the sql stuff
2. Update your Database
3. Detach your database
4. Update MM
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!
All My Scripts
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!
All My Scripts
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
Even though the name is ExportDB its actually an Import process
1. Attach your database
2. begin transaction
3. update MM from your database
4. commit
5. detach your database
But your example will fail also
1. Attach your Database do all the sql stuff
2. begin transastion
3. Update your Database
4. Commit
5. Detach your database <----- fail
6. begin transaction
7. Update MM
8. commit
1. Attach your database
2. begin transaction
3. update MM from your database
4. commit
5. detach your database
But your example will fail also
1. Attach your Database do all the sql stuff
2. begin transastion
3. Update your Database
4. Commit
5. Detach your database <----- fail
6. begin transaction
7. Update MM
8. commit
Then I really don't know...
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!
All My Scripts
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!
All My Scripts
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
Below is some correspondance in the SQLite users mailing list.
Basically I believe it confirms that MM allows scripts to continue even when the script has executed a commit.
"The commit is finished when it returns to your program."
Unfortunatly it is returning to MM not the script.
This is a serious bug as scripts expect the commit to be finalised before the next line is executed.
There is no suitable workaround scripters can use to avoid this problem which creates inconsistent errors and needless code checking.
Basically I believe it confirms that MM allows scripts to continue even when the script has executed a commit.
"The commit is finished when it returns to your program."
Unfortunatly it is returning to MM not the script.
This is a serious bug as scripts expect the commit to be finalised before the next line is executed.
There is no suitable workaround scripters can use to avoid this problem which creates inconsistent errors and needless code checking.
Thanks for the clarification.
The error I am receiving must then be produced by the application rather than just reporting an SQLite error.
Or
The application is taking control of the commit wait time, allowing my script to continue.
Or more correct perhaps; the script commands are passed to the application which allows the script to continue producing the DETACH error.
*****
There was a problem querying the database:
Error executing SQL statement "DETACH ExportDB " : SQL logic error or missing database (1,1) Cancel, Retry, Ignore
****
The error is ambiguous at best but I believe the second scenario is what is happening given the consistent time before clicking "Retry" is accepted without error.
I have posted the information received so far to the developers however, I'm not expecting the application to be corrected/changed in a hurry as ATTACH/DETACH are unusual events in user scripts.
So the question remains:
Is there a way to check for open transactions / locks from the command line?
-----Original Message-----
From: sqlite-users-bounces@sqlite.org [mailto:sqlite-users-bounces@sqlite.org] On Behalf Of Dennis Cote
Sent: Tuesday, 27 May 2008 11:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Checking for open transactions attach/detach database
MoDementia wrote:
> The database file is ready to (copy) Detach as soon as the commit completes.
>
Yes.
> This is the problem.
> Detach: "This statement will fail if SQLite is in the middle of a
> transaction."
This is true. This is why you must commit your transaction before you can detach.
> If I commit 10k row updates I cannot detach the database until it is
> finished.
>
The database (SQLite) will be finished all its processing by the time it returns to your program after executing the commit statement.
Attach
Begin
Loop to insert many rows
Commit
Detach
> I need to be able to check some sort of table entry that will be clear
> once the commit is finished.
The commit is finished when it returns to your program.
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
I think I have a solution for now
While a transaction is in progress SQLite creates a file called
DatabaseName-journal
In the same directory that the database is in
i.e.
MM.DB-journal
or in my case
ExportDB-journal
A loop checking for the existance of this file will halt script execution until the transaction is completed i.e. all new entries written.
While a transaction is in progress SQLite creates a file called
DatabaseName-journal
In the same directory that the database is in
i.e.
MM.DB-journal
or in my case
ExportDB-journal
A loop checking for the existance of this file will halt script execution until the transaction is completed i.e. all new entries written.
Code: Select all
SDB.Database.Commit
Do While 1=1
SDB.ProcessMessages
If FSO.FileExists(DATABASEPATHNAME & "-journal") Then
SDB.Tools.Sleep(1000)
Else
Exit Do
End If
Loop
SDB.ProcessMessages
SDB.DataBase.ExecSQL("DETACH ExportDB")
Last edited by MoDementia on Tue May 27, 2008 8:10 pm, edited 1 time in total.
that is good code for an infinite loop
I suggest putting some kind of counter in there with an error dialog or log msg to indicate the loop timed out while waiting for db commit.
you might also want to include a sdb.processmessages command so that MM can update the UI and stuff while your looping.
I suggest putting some kind of counter in there with an error dialog or log msg to indicate the loop timed out while waiting for db commit.
you might also want to include a sdb.processmessages command so that MM can update the UI and stuff while your looping.
New script: Last.FM Node Now with DJ Mode!
Last.fm + MediaMonkey = Scrobbler DJ!
Tag with MusicBrainz ~ Get Album Art!
Tweak the Monkey! ~ My Scripts Page
Last.fm + MediaMonkey = Scrobbler DJ!
Tag with MusicBrainz ~ Get Album Art!
Tweak the Monkey! ~ My Scripts Page
Re: 1175 Script continues after BeginTransaction/Commit
I've searched high and low for an answer to similar woes, but I can't quite *see* what is happening inside MM (or SQLite) to cause the errors that my particular hangup.
I'd like to see a working example of ForceCommit. When my app throws a particular type of DB error - 'Error executing SQL statement "COMMIT" : SQL logic error or missing database' when doing a <songlist>.UpdateAll() - trying to either Commit or ForceCommit (yes this is after a BeginTransaction) just does nothing but cause the process to hang indefinitely. I can trap for the exception itself, but recovery is impossible, after which the database is in a locked state.
I do wonder if in some sense the "SQL logic error" is being caused by the above reported "bug"/feature of SQLite (it only happens SOME of the time). Quite honestly at this point I would simply be deliriously happy to have a Rollback statement... unless that is part of what ForceCommit is suppose to accomplish.
I'd like to see a working example of ForceCommit. When my app throws a particular type of DB error - 'Error executing SQL statement "COMMIT" : SQL logic error or missing database' when doing a <songlist>.UpdateAll() - trying to either Commit or ForceCommit (yes this is after a BeginTransaction) just does nothing but cause the process to hang indefinitely. I can trap for the exception itself, but recovery is impossible, after which the database is in a locked state.
I do wonder if in some sense the "SQL logic error" is being caused by the above reported "bug"/feature of SQLite (it only happens SOME of the time). Quite honestly at this point I would simply be deliriously happy to have a Rollback statement... unless that is part of what ForceCommit is suppose to accomplish.