ZConnection - force a complete reload of database libraries

[aehimself]

Continuing the slightly offtopic http://zeoslib.sourceforge.net/viewtopi ... 40&t=97224 I managed to force longer SQL operations in a worker thread and added a cancel option to call TerminateThread on it. Seems to be working so far.
My only concern is that - as expected - this leaves lots of objects unfreed and leaks memory.

Can I somehow ask TZConnection to unload all libraries and load them again? I'm wondering if this would yield some positive results.

[Fr0sT]

When you call WinAPI TerminateThread, you just halt the Execute method but nothing prevents thread destructor or any other method from running. So just dispose all objects you created there.

[aehimself]

When you call WinAPI TerminateThread, you just halt the Execute method but nothing prevents thread destructor or any other method from running. So just dispose all objects you created there.

Unfortunately it's not that easy The worker thread allocates nothing, it just receives a method pointer to execute. After aborting I'm immediately calling ZConnection.Disconnect to force the connection to close, cleaning up any half-states.

For example, if I call TerminateThread during a '.Connect' operation, these are leaked:

An unexpected memory leak has occurred. The unexpected small block leaks are:

9 - 24 bytes: Unknown x 6
25 - 40 bytes: TList x 1, Unknown x 91
41 - 56 bytes: TZOracleDriver x 1, TZNativeLibraryLoader x 2, TCriticalSection x 2, TZDefaultIdentifierConvertor x 1, TZUnmodifiableCollection x 4, Unknown x 60
57 - 72 bytes: TZOracleDatabaseInfo x 1, TZCollection x 7, Unknown x 2
73 - 88 bytes: TZHashMap x 2, TZDriverManager x 1, Unknown x 1
89 - 104 bytes: TZClientVariantManager x 1, TZURL x 1
105 - 120 bytes: Unknown x 1
121 - 136 bytes: TZURLStringList x 1
137 - 152 bytes: TZAnyValue x 2
201 - 216 bytes: Unknown x 1
233 - 248 bytes: TZOracleConnection x 1
313 - 344 bytes: Unknown x 1
345 - 376 bytes: Unknown x 1
473 - 520 bytes: TZOracle9iPlainDriver x 2
617 - 664 bytes: TZOracleDatabaseMetadata x 1

The sizes of unexpected leaked medium and large blocks are: 3112, 3112

Terminating .Open sometimes seems not to leak anything.

[Fr0sT]

Ah, I thought you're talking about threaded BLOB loading only
Well, terminating a thread is always a very bad practice and should be used only as extreme measure. I'd recommend trying API functions for that, f.ex. for MySQL functions like mysql_kill() / mysql_query() with KILL stmt; connect-stage could be controlled with timeouts or even mysql_real_connect_nonblocking() but the latter must be first supported by Zeos.

[aehimself]

connect-stage could be controlled with timeouts or even mysql_real_connect_nonblocking()

The trick here is that I can pass any method pointer, let it be ZConnection.Connect, TZMetaData.Open or TZQuery.ExecSQL. The solution must include but not exclusive to .Connect and should work on all protocols. As according to my knowledge there is no such thing, especially since Zeos's design is asynchronous.
I am aware that TerminateThread is kind of a last-resort, this is what I could come up with which fulfills all the above requirements (I'm open to suggestions, though). Plus it seems to be working nicely. Apart from the leak

So, is there a command which forces a ZConnection to unload all libraries for me?

[Fr0sT]

Well, cancelling a current operation should exist in some form for all drivers. Cancelling a connect is more complicated though... Anyway I don't think you'll achieve what you want by unloading libs. There could exist some internally allocated variables which won't be disposed on thread termination so they will leak.
Anyway it's easier for you to examine sources in src\plain\ZPlainLoader.pas and src\plain\ZPlainDriver.pas and probably do what you want yourself.

[marsupilami]

Hello aehimself,

Zeos simply isn't suited for that kind of use (yet). Asume an operation trhat is programmed like this:

Code: Select all

MyStringList := TStringList.Create;
try
  // do something lengthy on MyStringList
finally
  FreeAndNil(MyStringList);
end;

If you simply terminate the thread, while it is in the try .. finally block and doing something lengthy, you have no chance of freeing MyStringList. And while we usually don't use TStringList a lot as a local object, other kinds of memory do get allocated like this. So in that case you are liable to get memory leaks in any case - simply because there is no chance of freeing MyStringList because there is no reference to it anymore.

If you want to be able to abort operations you need to extend the driver to be able to do that. And you need to do the thread synchronization for that kind of thing. Something like this could work:

• stop execution of the currentlx working thread - set it to get not executed anymore
• call the database operation that is necessary for cancelling the currently running operation.
• make sure, the stopped thread can get the information that the operation was cancelled intentionally
• let the thread continue its operation, so it can free any memory that needs to be freed.
• make sure, Zeos behaves in a decent way when it expects a result set but doesn't get one.

Some weeks ago I had a discussion with EgonHugeist about some kind of an asynchronous dataset. I came to the conclusion that TDataSet isn't made to be used in an asynchronous way. There are too many things that are bound to happen when TDataSet.Open is called - like generation of FieldDefas and the like...

Best regards,

Jan

[aehimself]

I had some thought about calling TerminateThread in a middle of a Try ... Finally ... End block a couple of day ago and it did start to bother me. I'll start to look into the sources @Fr0st pointed me to and will try to somehow achieve what I'd like to do.

@Jan,
Your approach is very interesting, I'll definitely play around with it! Just one concern: I have a thread which simply calls a method, like:

Code: Select all

Procedure Execute;
Begin
 If Assigned(_proc) Then _proc;
End;

If I pause this with threadobj.Suspended := True; and then resume - will this actually suspend the connection / opening the dataset; basically stop the dataflow so I can somehow "inject" my cancel request? As we are working synchronously I suspect yes, but I personally never tried this kind of intervention before.

[Fr0sT]

Suspending a thread from outside is deprecated and non-reliable. I'm sure this kind of stuff should not go to prod/release versions.

[aehimself]

So the good thing is that database systems already seem to support this: Oracle has OCIBreak(), MySQL has mysql_kill and DBLib (FreeTDS?) has dbcancel.

As https://vrogier.github.io/ocilib/doc/ht ... abort.html mentions:
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.

Unfortunately though, it is marked as "unused API" in the ZPlainOracleDriver.pas

Code: Select all

    @OracleAPI.OCIBreak           := GetAddress('OCIBreak');

As the code is there I simply can try to uncomment it. My only question is - how can I call these methods?

Code: Select all

function TZMySQLPlainDriver.Kill(mysql: PMYSQL; Pid: LongInt): Integer;
begin
  Result := mysql_kill(mysql, Pid);
end;

I'm thinking on something like this:

Code: Select all

 If ZConnection1.DbcDriver = IZMySQLPlainDriver Then (ZConnection1.DbcDriver As IZMySQLPlainDriver).Kill

...but I'm on the wrong track here, as it does not seem to compile

[Fr0sT]

Yep. FB has cancelling as well though I've never used it. As for the fact the functions were commented out, I guess nobody have touched them until this time . I think this should be a general method implemented for all drivers just like PingServer. But for now you can implement the method as you like for testing purposes.

My only question is - how can I call these methods?

There's GetInstance method to retrieve an object from an interface. You can check its usage in sources.

[aehimself]

I think this should be a general method implemented for all drivers just like PingServer.

This was exactly my thought. I started to check the implementation of PingServer to get an idea on how to proceed

There's GetInstance method to retrieve an object from an interface. You can check its usage in sources.

Guess I need 7.3 for that...? I can not see any references of .GetInstance in 7.2.6 only in 7.3.

*sigh* time to upgrade again ^^

[aehimself]

No luck until now. After upgrading to 7.3, I exported the property of TZMySQLConnection in ZDbcMySql.pas:

Code: Select all

    Property Handle: PMYSQL Read FHandle;

Then, I uncommented the mysql_kill functions in ZPlainMySqlDriver.pas. Test code looks like this:

Code: Select all

Var
 a: TZMySQLPlainDriver;
begin
 a := ZConnection1.DbcConnection.GetIZPlainDriver.GetInstance As TZMySQLPlainDriver;
 a.mysql_kill((ZConnection1.DbcConnection As TZMySQLConnection).Handle, 0);
// a.mysql_query((ZConnection1.DbcConnection As TZMySQLConnection).Handle, 'KILL QUERY');
end;

The connection is lost with the MySQL server aborting everything, but I guess it's because of the access violations in libmysql.dll...

[Fr0sT]

I've little knowledge on MySQL but pid hardly could be 0, I guess you should first get the right value with mysql_thread_id() (according to docs). Moreover the function returns a result, maybe it could shed some light.

[aehimself]

I know, it was only a test And - surprise, surprise - changing using libmariadb.dll instead of libmysql.dll does the job nicely (God, I can not express how much I started to dislike Oracle products!). No more access violations, and returns an error "Thead with PID 0 not found" or something similar.

This is how far I got yesterday, plus I saw the warning that mysql_kill is obsolate and will be removed. I still need to test the new (commented) version, but I don't really know how it will work as I must run it from a completely separate context (a new connection, effectively).

Not there yet, but getting close. Once I have a working idea on MySQL I'll start to convert it to a method of ZConnection and add the Oracle version. First of all, I'll need some proper version control set up...