ZConnection - force a complete reload of database libraries

[Fr0sT]

Congratulations! )))
The function could be deprecated but many people tend to stick to older versions (I've seen pretty much guys still using Firebird 1.5!) so in Zeos we'll have to support even the most ancient pieces of a mammoth's sh*t.

[aehimself]

Soooooooooooooooooooo..... it WORKS! By not sticking to mysql_kill, it even runs fine with libmysql.dll:

[14:36:20.462] Connection established, client version: 6.1.11, server version: 8.0.13, component version: 7.3.0-alpha
[14:36:21.322] MySQL thread ID: 25946
[14:36:21.525] Cancellation query is sent
[14:36:24.806] EZSQLException was raised while running the query with the message: SQL Error: Query execution was interrupted

The test code is:

Code: Select all

Procedure TForm1.Button2Click(Sender: TObject);
Var
 a: TZMySQLPlainDriver;
 mti: ULong;
 s: String;
Begin
 Try
  a := ZConnection1.DbcConnection.GetIZPlainDriver.GetInstance As TZMySQLPlainDriver;
  mti := a.mysql_thread_id((ZConnection1.DbcConnection As TZMySQLConnection).Handle);
  Log('MySQL thread ID: ' + mti.ToString);
  s := 'KILL QUERY ' + mti.ToString;
  ZConnection2.ExecuteDirect(s);
  Log('Query is sent');
 Except
  On E:Exception Do Log (E.ClassName + ' was raised while cancelling with the message: ' + E.Message);
 End;
End;

The backdraw is that I need a second connection to be able to execute the command. Is there a way in Zeos to clone a connection completely, or to create a secondary thread to the server? Creating a ZConnection object, copying the parameters, calling connect, sending the query and then freeing the connection smells a little bit

Edit:
I published an .AbortOperation method to ZConnection, and attempting the MySQL implementation like this:

Code: Select all

function TZMySQLConnection.AbortOperation: Boolean;
Var
 d2: TZMySQLPlainDriver;
 s: AnsiString;
 r: Integer;
 d2h: PMYSQL;
begin
 s := 'KILL QUERY ' + AnsiString(FPlainDriver.mysql_thread_id(FHandle).ToString);
 d2 := FPLainDriver.NewInstance As TZMySQLPlainDriver;
 GlobalCriticalSection.Enter;
 try
  d2h := d2.Init(nil); //is not threadsave!
 finally
  GlobalCriticalSection.Leave;
 end;
 Try
   if Closed or (FHandle = nil)
   then Result := False
   else Begin
        r := FPlainDriver.mysql_real_query(FHandle, Pointer(s), Length(s) + 1);
        Result := True;
        End;
 Finally
  d2.mysql_close(d2h);
  FreeAndNil(d2);
 End;
end;

This fails with an invalid pointer operation, as .NewInstance does not seem to completely recreate the plain driver. Libraries are not loaded, and mysql_init is unassigned. So... can I somehow clone the FPlainDriver to get a secondary thread to the server somehow?

[aehimself]

Getting closer. When using the advised (duh...) .GetInstance, I get the following result:

[18:16:51.245] Connection established, client version: 6.1.11, server version: 8.0.13, component version: 7.3.0-alpha
[18:17:26.838] Abort request was sent
[18:17:28.687] EAccessViolation was raised while running the query with the message: Access violation at address 6645B100 in module 'libmysql.dll'. Read of address 00000010

Query execution stops, but with a crash instead of the desired cancelled. Exception is raised in TZMySQL_Store_ResultSet.OpenCursor at this line:

FQueryHandle := FPlainDriver.mysql_store_result(FPMYSQL^);

I suspect that at the end of the .AbortOperation call the driver is getting freed (because of interfaces?) but this destroys the originating connection too.

Implementation is like this:

Code: Select all

function TZMySQLConnection.AbortOperation: Boolean;
Var
 d2: TZMySQLPlainDriver;
 s: AnsiString;
 d2h: PMYSQL;
 r: Integer;
begin
 d2 := FPLainDriver.GetInstance As TZMySQLPlainDriver;
 GlobalCriticalSection.Enter;
 try
  d2h := d2.Init(nil);
 Finally
  GlobalCriticalSection.Leave;
 End;
 s := 'KILL QUERY ' + AnsiString(FPlainDriver.mysql_thread_id(FHandle).ToString);
 if Closed or (FHandle = nil) then Result := False
   else Begin
        r := FPlainDriver.mysql_real_query(d2h, Pointer(s), Length(s) + 1);
        Result := r = 0;
        End;
 d2.mysql_close(d2h);
End;

I'll keep trying, getting too close to give up

[Fr0sT]

Hmm, have you tried using the same connection from within canceller thread? Of course it's always a bad practice but using another connection for cancelling seems an overhead to me. Anyway these functions were supposed to work somehow!
F.ex., FB function is able (and supposed to!) work with the same handles from different threads: https://firebirdsql.org/rlsnotesh/rnfb2 ... api-cnclop

[aehimself]

GOT IT TO WORK!!!!

Looks like this:

Code: Select all

Function TZMySQLConnection.AbortOperation: Boolean;
Var
 killquery: String;
 izc: IZConnection;
Begin
 If Closed Or (FHandle = Nil) Then Result := False
   Else Begin
        killquery := 'KILL QUERY ' + FPlainDriver.mysql_thread_id(FHandle).ToString;
        izc := DriverManager.GetConnection(DriverManager.ConstructURL('mysql', Self.HostName, Self.Database, Self.User, Self.Password, Self.Port, Self.GetParameters));
        Result := izc.CreateStatement.ExecuteUpdate(killquery) = 0;
        End;
End;

Result:

[20:05:19.880] Connection established, client version: 6.1.11, server version: 8.0.13, component version: 7.3.0-alpha
[20:05:21.187] Abort request was sent
[20:05:23.641] EZSQLException was raised while running the query with the message: SQL Error: Query execution was interrupted

Unfortunately the longest query takes about 3 seconds so I need to insert some insanely high amount of data as a test case to be sure that it actually ends before downloading the data.

I'll turn my attention to the Oracle driver now; just let me know if the above implementation can be optimized somehow.

As for a separate connection; Oracle seems to be love doing this:

Any call to OCI_Break() has to be done from a separate thread because the thread that has executed a long OCI call is waiting for its OCI call to complete.

[aehimself]

Oracle seems to be a little better and a little worse from this perspective. The library is parallel, so simply calling

Code: Select all

Result := (Self.GetIZPlainDriver As TZOraclePlainDriver).OCIBreak(FHandle, err) = 0;

yields the desired results. The bad thing? It only cancels connect operations.

For queries it needs OCIStmtFetch2. It's getting close to midnight so tomorrow I'll implement the status variable to decide which method to call.

I'm using Git at home and have no intentions of installing / configuring / learning subversion. If I manage to implement this, how can I send the modified files to you guys for review?

[Fr0sT]

Fantastic! ))

just let me know if the above implementation can be optimized somehow

Well, we still support D7 so type helpers are a no-go. For other things it's better to wait EgonHugeist

The bad thing? It only cancels connect operations.

This is quite usual, I guess we need two methods - CancelConnect and CancelQuery and probably general method Cancel that would check the current state and call appropriate cancel.

I'm using Git at home and have no intentions of installing / configuring / learning subversion. If I manage to implement this, how can I send the modified files to you guys for review?

No problem, just put your changes to Github/any other site, either full fork or just changed files. Git diffs are nicely processed by SVN too.

[aehimself]

Oracle version seems to be working fine during the .Open and .FetchAll methods too:

[09:54:35.445] Connection established, client version: 19.3.0, server version: 11.2.0, component version: 7.3.0-alpha
[09:54:35.680] Abort request was sent
[09:54:35.774] EZSQLException was raised while running the query with the message: SQL Error: OCI_ERROR: ORA-01013: user requested cancel of current operation
Code: 1013 SQL: FETCH ROW

[09:54:38.310] Connection established, client version: 19.3.0, server version: 11.2.0, component version: 7.3.0-alpha
[09:54:43.236] Abort request was sent
[09:54:43.298] EZSQLException was raised while running the query with the message: SQL Error: OCI_ERROR: ORA-01013: user requested cancel of current operation
Code: 1013 SQL: Read Large Object

I modified the implementations a little bit so now they look closer to the surrounding code.


I'm a paranoid person, I host my own remote git repositories with no access to the outside world So I attached the changed files from zeoslib-code-0-r6100-branches-testing-7.3 which I downloaded yesterday.

Let me know where I can see when / if it gets implemented

AbortOperation-code-0-r6100-branches-testing-7.3.7z

[aehimself]

For DBLib (Tested on MSSQL with FreeTDS):

AbortOperation-DBLib-code-0-r6100-branches-testing-7.3.7z

Most peaceful abortion ever! No exceptions raised, simply stops downloading records.

[12:06:00.667] Connection established, client version: 0.0.0, server version: 0.0.0, component version: 7.3.0-alpha
[12:06:04.951] Abort request was sent
[12:06:05.143] All done, 321 row(s) returned.

ntwdblib.dll seems waaaaaaaay too outdated as it throws an access violation if protocol is mssql and librarylocation is set to ntwdblib.dll:

[12:04:10.335] EAccessViolation was raised while running the query with the message: Access violation at address 7333F15A in module 'ntwdblib.dll'. Read of address 0437FFFF

[aehimself]

...just replace ZDbcMySql.pas line 663 from

Code: Select all

 killquery := 'KILL QUERY ' + FPlainDriver.mysql_thread_id(FHandle).ToString;

to

Code: Select all

 killquery := 'KILL QUERY ' + IntToStr(FPlainDriver.mysql_thread_id(FHandle));

[marsupilami]

Hello,

Let me know where I can see when / if it gets implemented

I just checked your changes in to Zeos 7.3. You can see it if you update your git repository from our SVN.

Most peaceful abortion ever! No exceptions raised, simply stops downloading records.

Maybe we should find a mechanism to raise an exception - maybe by setting a flag or something like that. So we can raise an exception to make all drivers behave similar.

ntwdblib.dll seems waaaaaaaay too outdated as it throws an access violation if protocol is mssql and librarylocation is set to ntwdblib.dll

ntwdblib is deprecated for Zeos 7.2. The mssql driver should be removed from Zeos 7.3 before we do a release. We will only be supporting freetds and Sybase in the future.

Best regards,

Jan

[aehimself]

I just checked your changes in to Zeos 7.3. You can see it if you update your git repository from our SVN.

Sounds good, thanks! Just keep in mind that I forgot to change the String helper and @Fr0st said that it's a no-go this way. See http://zeoslib.sourceforge.net/viewtopi ... 00#p136000.

Maybe we should find a mechanism to raise an exception - maybe by setting a flag or something like that. So we can raise an exception to make all drivers behave similar.

Yes, I was thinking about the same; however I really like the idea of this soft-cancel. It really makes things smooth, and you'll still get some downloaded records if you abort.

ntwdblib is deprecated for Zeos 7.2. The mssql driver should be removed from Zeos 7.3 before we do a release. We will only be supporting freetds and Sybase in the future.

Sounds just about right, in this case I'm not worried that I could not test it. The latest ntwdblib.dll I could find is 32 bit only and dated 2004 so it is time to let it go.

As far as I can see SourceForge supports Git access, but it explicitly has to be enabled on the repository first: https://sourceforge.net/p/forge/documentation/Git. I tried to clone Zeos but my Git said it's not published, so I'll stick to the .Zip download

[aehimself]

So I found the necessary API calls and implemented the .AbortOperation method for SQLite and PostgreSQL as well.

However, as I have no such databases available I could not test if they actually work so I left a warning in the source about them. I also corrected the .ToString to IntToStr in the MySQL implementation and added links I used for the implementation.

AbortOperation-PostgreSQL-SQLite-code-0-r6100-branches-testing-7.3.7z

P.s.: I also created a SourceForge account. If I manage to find out how to clone Zeos and enable Git access on it I can publish them there so you can just pull the changes. Hope it'll be easier that way.

[marsupilami]

I just checked your changes in to Zeos 7.3. You can see it if you update your git repository from our SVN.

Sounds good, thanks! Just keep in mind that I forgot to change the String helper and @Fr0st said that it's a no-go this way. See http://zeoslib.sourceforge.net/viewtopi ... 00#p136000.

I added your latest files. I hope, these contain all relevant changes? Could you please check that? I also created a separate branch in the SVN, to track your changes and to more easily be able to merge them back to the main 7.3 branch.

Maybe we should find a mechanism to raise an exception - maybe by setting a flag or something like that. So we can raise an exception to make all drivers behave similar.

Yes, I was thinking about the same; however I really like the idea of this soft-cancel. It really makes things smooth, and you'll still get some downloaded records if you abort.

We could add a separate connection level parameter to disable that exception on any chosen dblib connection?

As far as I can see SourceForge supports Git access, but it explicitly has to be enabled on the repository first: https://sourceforge.net/p/forge/documentation/Git. I tried to clone Zeos but my Git said it's not published, so I'll stick to the .Zip download

Unfortunately that documentation is only valid if we use a git repository For accessing the main repository you still can use git-svn. TortoiseGit also has support for git-svn included.

P.s.: I also created a SourceForge account. If I manage to find out how to clone Zeos and enable Git access on it I can publish them there so you can just pull the changes. Hope it'll be easier that way.

You could create a repository on your user account and mirror Zeos there. Unfortunately it seems that the import menu entry that is mentioned on the git help page doesn't exist there.

I currently try to crate a repository on github - and also on sourceforge. Unfortunately the caveats section of the git-svn manual suggests to keep them read only. So I don't know if they will be of any use.

[marsupilami]

So - as a test case I created two repositories that both seem to be a good copy ot the current Zeos repository:
https://sourceforge.net/u/marsupilami79/zeos/
https://github.com/marsupilami79/zeoslib

I assume, changes still have to be made against the original Zeos SVN repository. We will see, if these repositories can be of any help in terms of patches and pull requests and the like.