SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
iwilson · 2011-11-23 16:39

SQLite is driving me insane... My current error is that
"BackQuery.Open" crashing on the second iteration of the loop. This is
the only procedure in my program that deals with BackDB and it's
queries. I also had to fix a "you cannot perform a transaction within
a transaction" by closing the database and opening it again right
after, even though the only query dealing with it has already been
closed... This doesn't seem proper to me.

Anyways, I'm at a complete loss for why it's crashing with "Library
out of sequence" on the BackQuery.Open when it runs a second time,
everything has been closed and the database reopened.

EDIT: Also, as you can see, I've commented out some chunks of code to
simplify it and still there is an issue. Only BackQuery is dealing
with the database ATM. Also the function MoveToHeadend always returns
true.

Code: [Select]procedure THeadendThread.Execute;
var
  filename   : String;
  testBool   : Boolean;
begin

  while(NOT Terminated) do begin

    if(NOT BackDB.Connected) then
       BackDB.Open;

    BackQuery.SQL.Clear;
    BackQuery.SQL.Add('SELECT * FROM Calls;');
    BackQuery.Open;
    while(NOT BackQuery.EOF) do begin
      filename := BackQuery.FieldByName('filename').AsString;
      if(MoveToHeadend(BackQuery.FieldByName('master').AsInteger,
BackQuery.FieldByName('station').AsInteger,
BackQuery.FieldByName('accept_time').AsString,
BackQuery.FieldByName('end_time').AsString, filename)) then begin
        //BackDelQuery.SQL.Add('DELETE FROM Calls WHERE
filename='+q+filename+q+';');
      end else begin
        BackQuery.Close;
        BackDB.Close;
        break;
      end;
      BackQuery.Next;
    end;
    BackQuery.Close;
    BackDB.Close;
    testBool := BackQuery.Active;
    if(BackDelQuery.SQL.Count > 0 ) then begin
     { if(NOT BackDB.Connected) then
         BackDB.Open;
      BackDelQuery.ExecSQL;
      BackDelTransaction.Commit;
      BackDelQuery.Close;
      BackDelQuery.SQL.Clear;        }
    end;
    //ftpClient.Free;
    BackDB.Close;
    Sleep(1000);
    //Sleep(600000)
  end;
end;

----------------------------------------------------------------------
BigChimp · 2011-11-23 17:02

Don't know (haven't programmed with sqlite yet), but maybe sqlite
needs explicit transactions for select queries as well... i.e. you
need to commit the transaction after your read and do another
.starttransaction to get a new transaction running for your next
statement...

In the first part I don't see any mention of transactions, maybe the
connection object sets up an implicit transaction. Then when you
define an explicit transaction when doing the delete, SQLite starts
complaining.

I'd set up some assertions, e.g.
Code: [Select]assert(BackDB.Open=true, 'BackDB should be open but is
not.');to find out what's going on.

Also: don't know if SQlite supports batched SQL statements!??!?!
Code: [Select]        //BackDelQuery.SQL.Add('DELETE FROM Calls WHERE
filename='+q+filename+q+';');

Within the loop before the break you seem to have a redundant
BackQuery.Close, BackDB.Close but that shouldn't matter, should it?

----------------------------------------------------------------------
iwilson · 2011-11-23 17:36

There is redundancy there as I've been messing with it for a while, it
makes no effect with them removed.

I wasn't sure if SQLite supported batch statements either thats why I
commented it out entirely. I can easily get around it with a loop in
the future, but I have more fundamental issues right now.

I have verified that BackDB is Open just before that line so I'm still
stumped.

----------------------------------------------------------------------
ludob · 2011-11-23 17:40

Some more questions:
-if you move the code from a thread to the main program, do you still
have the same problem?
-what fpc version are you using?
QuoteAlso the function MoveToHeadend always returns true.-what is this
function doing?

----------------------------------------------------------------------
iwilson · 2011-11-23 17:59

I ended up simplifying the loop to this:

Code: [Select]    while(true) do begin

    if(NOT BackDB.Connected) then
       BackDB.Open;
    BackQuery.SQL.Clear;
    BackQuery.SQL.Add('SELECT * FROM Calls;');
    BackQuery.Open;
    BackQuery.First;
    while(NOT BackQuery.EOF) do begin
       BackQuery.Next;
    end;
    BackQuery.Close;
    BackDB.Close;
    if(BackDelQuery.SQL.Count > 0 ) then begin
    end;
    //ftpClient.Free;
    BackDB.Close;
    Sleep(1000);
    //Sleep(600000)
  end;
That seems to work fine.

This is just a thread to move any sound files over FTP to my headend
server and their SQL entries as well, and then remove them when
successful. They are only ever on the backend if for whatever the
reason the headend was down upon initially trying to transfer. The
code in it's entirety:

Code: [Select]unit headend_upload;

{$mode objfpc}{$H+}

interface

uses
  pthreads, Classes, SysUtils, mysql50conn,sqldb, blcksock,
     synautil,
     synaip,
     synsock, FTPSend, sqlite3conn;

type
   THeadendThread = Class (TThread)
   private
     q               : String;
     Query           : String;
     HeadDB          : TMySQL50Connection;
     HeadQuery       : TSQLQuery;
     HeadTransaction : TSQLTransaction;
     BackDB          : TSQLite3Connection;
     BackQuery       : TSQLQuery;
     BackDelQuery    : TSQLQuery;
     BackTransaction : TSQLTransaction;
     BackDelTransaction : TSQLTransaction;
     ftpClient       : TFTPSend;
     Good            : Boolean;
     Running         : Boolean;
   protected
     procedure Execute; Override;
   public
     {Filename        : String;
     Master          : ShortInt;
     Station         : ShortInt;
     AcceptTime      : String;
     EndTime         : String; }
     Dir             : String;
     procedure InitialMove(Master, Station : Integer; AcceptTime,
EndTime, Filename : String);
     function MoveToHeadend(Master, Station : Integer; AcceptTime,
EndTime, Filename : String) : Boolean;
     constructor Create;
   end;

implementation

constructor THeadendThread.Create;
begin
  FreeOnTerminate := True;
  ftpClient := TFTPSend.Create;
  ftpClient.TargetHost := '192.168.101.73';
  ftpClient.TargetPort := '21';

  q := Chr(39);

  HeadDB := TMySQL50Connection.Create(nil);
  HeadDB.Hostname := '127.0.0.1';
  HeadDB.DatabaseName := 'db';
  HeadDB.UserName := 'root';
  HeadDB.Password := '4444';
  HeadQuery := TSQLQuery.Create(nil);
  HeadTransaction := TSQLTransaction.Create(nil);
  HeadQuery.DataBase := HeadDB;
  HeadTransaction.Database := HeadDB;
  HeadTransaction.Active := False;
  HeadQuery.Transaction := HeadTransaction;

  BackDB := TSQLite3Connection.Create(nil);
  BackDB.Hostname := 'localhost';
  BackDB.DatabaseName := 'voip';
  BackQuery := TSQLQuery.Create(nil);
  BackDelQuery := TSQLQuery.Create(nil);
  BackTransaction := TSQLTransaction.Create(nil);
  BackDelTransaction := TSQLTransaction.Create(nil);
  BackQuery.DataBase := BackDB;
  BackDelQuery.DataBase := BackDB;
  BackDelTransaction.Database := BackDB;
  BackDelTransaction.Active := False;
  BackTransaction.Database := BackDB;
  BackTransaction.Active := false;
  BackQuery.Transaction := BackTransaction;
  BackDelQuery.Transaction := BackDelTransaction;

  inherited  Create(true);
end;
function THeadendThread.MoveToHeadend(Master, Station : Integer;
AcceptTime, EndTime, Filename : String) : Boolean;
begin
   ftpClient.DirectFileName := dir + filename;
   ftpClient.DirectFile := true;
   if(ftpClient.Login) then begin  //succesful connection to FTP
server
      try
        if(NOT HeadDB.Connected) then
           HeadDB.Open;
      except
        result := false;
        Exit;
      end;
      if(ftpClient.StoreFile(filename,false)) then begin
        Query := 'INSERT INTO Calls (accept_time, end_time, filename,
master, station) VALUES ('+q+AcceptTime+q+','+q+EndTime+q+','+
        q+filename+q+','+IntToStr(Master)+','+IntToStr(Station)+');';

        HeadQuery.SQL.Add(Query);
        HeadQuery.ExecSQL;
        HeadTransaction.Commit;
        HeadQuery.Close;
        HeadQuery.SQL.Clear;
        HeadDB.Close;
        Running := False;

        result := true;
        Exit;
      end;
   end;
   result := false;
end;
procedure THeadEndThread.InitialMove(Master, Station : Integer;
AcceptTime, EndTime, Filename : String);
begin
  if(NOT MoveToHeadEnd(Master,Station,AcceptTime,EndTime,Filename))
then begin
        Query := 'INSERT INTO Calls (accept_time, end_time, filename,
master, station) VALUES ('+q+AcceptTime+q+','+q+EndTime+q+','+
      q+filename+q+','+IntToStr(Master)+','+IntToStr(Station)+');';
      if(NOT BackDB.Connected) then
         BackDB.Open;
      BackDelQuery.SQL.Add(Query);
      BackDelQuery.ExecSQL;
      BackDelTransaction.Commit;
      BackDelQuery.Close;
      BackDelQuery.SQL.Clear;
      BackDB.Close;
  end;
end;

procedure THeadendThread.Execute;
var
  filename   : String;
  testBool   : Boolean;
begin

  while(NOT Terminated) do begin

    if(NOT BackDB.Connected) then
       BackDB.Open;
    TESTBOOl := BackDB.Connected;
    BackQuery.SQL.Clear;
    BackQuery.SQL.Add('SELECT * FROM Calls;');
    BackQuery.Open;
    BackQuery.First;
    while(NOT BackQuery.EOF) do begin
      filename := BackQuery.FieldByName('filename').AsString;
      if(MoveToHeadend(BackQuery.FieldByName('master').AsInteger,
BackQuery.FieldByName('station').AsInteger,
BackQuery.FieldByName('accept_time').AsString,
BackQuery.FieldByName('end_time').AsString, filename)) then begin
        //BackDelQuery.SQL.Add('DELETE FROM Calls WHERE
filename='+q+filename+q+';');
      end else begin
        BackQuery.Close;
        BackDB.Close;
        break;
      end;
      BackQuery.Next;
    end;
    BackQuery.Close;
    BackDB.Close;
    testBool := BackQuery.Active;
    if(BackDelQuery.SQL.Count > 0 ) then begin
     { if(NOT BackDB.Connected) then
         BackDB.Open;
      BackDelQuery.ExecSQL;
      BackDelTransaction.Commit;
      BackDelQuery.Close;
      BackDelQuery.SQL.Clear;        }
    end;
    //ftpClient.Free;
    BackDB.Close;
    Sleep(1000);
    //Sleep(600000)
  end;
end;

end.

Thanks again guys. Also, apologies, I know the code needs to be
cleaned up a bit.

----------------------------------------------------------------------
iwilson · 2011-11-23 18:26

Commenting out the call to MoveToHeadend makes it loop fine without
crashing. I may be even more confused now. HeadDB is a seperate
database and SQL altogether and it's also not running when the query
is trying to Open.

----------------------------------------------------------------------
ludob · 2011-11-23 18:47

So if I understand you correctly, the problem disappears when
MoveToHeadend is removed.

A potential problem I see is that MySQL is normally not thread safe
and you initialise the mysql library in the main thread.
THeadendThread.Create still runs in the main thread. It would be
better to move the db initialisation into the beginning of
THeadendThread.Execute.

----------------------------------------------------------------------
iwilson · 2011-11-23 18:51

Yes, that is correct.

Unfortunately moving all of that into the beginning of the execute
thread changes nothing.

Another bit of info is that when I removed all the SQL stuff out of
MoveToHeadend other than the try statement (which just calls an Open
to HeadDB if it's not already opened) it still crashed. When I also
commented out the small try statement it didn't.

I noticed the possibility of HeadDB being open still if the function
fails and set HeadDB to close (if it's open) just before the
BackQuery.Open (the crash) and alas no different. The try statement is
fairly simple so other than closing the HeadDB I'm out of ideas.

----------------------------------------------------------------------
ludob · 2011-11-23 20:17

So as soon as you open the Mysql connection, it fails.

Have you tried to run this from the main loop instead of a thread?
Also, is there another mysql connection in the main or another thread?

Perhaps not linked to this problem but I would recommend to open the
connections in the beginning of Execute and close them at the end of
the execute loop. Opening and closing is a lot of overhead. Use
transaction.CommitRetaining where needed.

----------------------------------------------------------------------
iwilson · 2011-11-24 13:55

I haven't tried this specficially within the main...simply it wouldn't
work or function the way I want it to.

The main program is just a few sockets recording over RTP, I need this
thread to do the transferring over without interrupting the sockets
(ftp can take a bit).

There is no SQL in the main program. Something is very screwy and I
guess I may have to lose the controls and build my own

Thanks for your help

----------------------------------------------------------------------
iwilson · 2011-11-24 14:02

It's early and I forgot to add that I've tried creating the HeadendSQL
stuff within MoveToHeadend and then freeing it within itself as well,
so the object never exists when MoveToHeadend is done yet there is no
difference... Simply SQL connecting once upsets SQLite

----------------------------------------------------------------------
iwilson · 2011-11-24 14:04

I have just tried Opening HeadDB in the Execute BEFORE the loop beigns
(so it is open the entire time) and removed all the closes for HeadDB
and any transactions and query actions.

The loop runs fine the first time, but still crashes on the second
iteration... So very strange

----------------------------------------------------------------------
iwilson · 2011-11-24 14:08

Sorry for so many posts in a row.

I lied last post, I forgot I still had:

if(HeadDB.Connected) then
   HeadDB.Close;

At the start of the loop. This led it to crash after the second
iteration.
Breakdown:

SQLCreate
SQLiteCreate
HeadDB.Open
Loop starts:
HeadDB.Close
rest of loop <<
Loop Ends

but it only crashes the second time, even though HeadDB was opened and
closed before the first run of the loop.

Now, when I removed the HeadDB close entirely, it loops over and over
no problem.. However, this won't work for me as these programs will be
running 24/7 and Headend is bound to drop at some point and there can
be no crashes. I seem to have narrowed down the problem at least,
although no less confused

----------------------------------------------------------------------
iwilson · 2011-11-24 14:19

Ludob did you mean to say create and free the objects within the Loop?

I create the objects now (SQL and SQLITE) at the beginning of each
loop and free them entirely at the end... This seems to work fine, but
still unnecessary in my mind, but at this point I'll take anything.
Thanks again

----------------------------------------------------------------------
ludob · 2011-11-24 14:57

QuoteLudob did you mean to say create and free the objects within the
Loop?No, definitely not. Create and open connections before the
Execute loop and close and destroy after the Execute loop.
QuoteHowever, this won't work for me as these programs will be running
24/7 and Headend is bound to drop at some point and there can be no
crashes. When the db goes down, opening the connection will also fail
with an exception. So in any case you'll have to catch the exception
whether you keep the connection always open or re-open for each loop.
The location where the exception is raised will be different but the
principle is the same.
QuoteI haven't tried this specficially within the main...simply it
wouldn't work or function the way I want it to.It is only a matter of
eliminating possible causes of the problem. Mysql has problems with
multi-threading. So if the routine functions in the main thread, the
problem is thread related.

----------------------------------------------------------------------
BigChimp · 2011-11-24 15:04

Without much experience I can theorize with the best of them. Is there
any chance that those database close and open operations are
asynchronous - if the loop is tight the close might not have finished
and the db can't be opened again?

And have I understood correctly that you've isolated problem
completely to the sqlite part - no mysql involvement?

This must be immensely frustrating.

I'd suggest writing up a minimal test case that shows the same
behaviour if you have the time - you can submit it here & get it
checked by people like Ludo and/or upload it to the bug tracker.

Alternatively, you might switch from sqlite to Firebird embedded.
Works well on Windows for me (small scale though), might work well on
Linux as well.

Good luck!

----------------------------------------------------------------------
ludob · 2011-11-24 15:19

QuoteAnd have I understood correctly that you've isolated problem
completely to the sqlite part - no mysql involvement?From what I
gather the crash in sqlite only occurs when (re-)opening the mysql
connection. Reason why mysql threading is high on my suspect list. A
wrong pointer type of issue.

----------------------------------------------------------------------
ludob · 2011-11-24 16:17

I also notices that procedure InitialMove and  function MoveToHeadend
are declared public. InitialMove isn't called at all in the unit
headend_upload. Are they called from another thread?

----------------------------------------------------------------------
iwilson · 2011-11-24 19:12

Pretty busy at work and this bug kept me behind so I've had to move on
to the rest currently and use this temporary fix, however I will bring
the project home tonight and hash it out as BigChimp suggested. I want
to get to the bottom of this and if it's a bug then so be it.

InitialMove is the only one to be public, MoveToHeadend is only run
internally in the thread... As I said, the code is a bit of a
preliminary draft mess.

Appreciate all the support on these forums very much