EldoS | Feel safer!

Software components for data protection, secure storage and transfer

SSH Tunnel MySQL

Also by EldoS: Rethync
The cross-platform framework that simplifies synchronizing data between mobile and desktop applications and servers and cloud storages
#11832
Posted: 11/27/2009 08:17:13
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

Quote
Innokentiy Ivanov wrote:
I have performed some checks and found out that newer versions of MySQL driver (I started from 5.0 one) cooperate with SBB fine even if used within the main thread of the application.
Thanks for taking the time to check that. Yet I kind of slightly doubt that driver theory. Both MicroOlap and DBISAM TDatabase components are written in pure VCL only. They do not use any drivers at all. Earlier I was able to re-produce this 'does not work' situation with both of these databases.
Quote

a) turn SynchronizeGUI property off. This will make the component fire events in non-synchronized mode, however, you have to implement the handlers correctly
(modifying GUI objects from a non-synchronized code may cause certain problems, so the handlers must not access the form and the controls contained in it),
I tested with SynchronizeGUI=False, but this had no effect. The original SSH tunnel Demo Form still logs everything to it's own Form, and shows there messages like "SSH Connection established" etc. But my MySQL connection itself does not open or work.
Quote

b) just do not handle events of TElSSHLocalPortForwarding component.

I do not know what exactly does this mean. All the time I have used the original SSH Demo Form as is, and I do not add there any of my own events or anything.
I just open that Demo form from my own application, click the SSH tunnel open. Then leave the SSH tunnel Form visible and open and return to my own MySQL data Form.

I do not think that I am able to solve this myself. SSH tunnel works with Databases when SSH tunnel is opened in separate app, but does not work if I try to integrate SSH in to my own app.

You had that quite messy old SSH Database demo. Could it be possible for you to write a new simple SSH demo? It does not have to be with MySQL but with any database. And this time use the modern TElSSHLocalPortForwarding components, instead of instantiating everything in code.

Thanks.
-Sanna
#11840
Posted: 11/30/2009 02:32:00
by Ken Ivanov (EldoS Corp.)

Please find attached a simple SSHBlackbox + MicroOLAP sample based on high-level forwarding components.


[ Download ]
#11853
Posted: 12/01/2009 07:01:58
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

Quote
Innokentiy Ivanov wrote:
Please find attached a simple SSHBlackbox + MicroOLAP sample based on high-level forwarding components.
Phew, now there's finally some light at the end of the SSH tunnel:) Thanks! Now this integrated tunneling thing started to work. All inside the same executable.

This all has taken me so much time in general that I had to check what exactly was the reason for the earlier unsuccesfullness.

So the differences to the original TElSSHLocalPortForwarding demo with


-Also the new, working demo still has the SynchronizeGUI = True. So that was property not the reason.

-And also nothing to do with MySQL drivers nor Cygwin library at all.

-But then, the original demo had these four TElSSHLocalPortForwarding events and the new demo does not have them:
OnConnectionOpen
OnConnectionClose
OnConnectionChange
OnConnectionError


I did add those events back one event at a time. I discovered that the other three events do not harm anything, but the OnConnectionChange event seems to be the show stopper. If you use it, and that event runs the original demo's code, then the DB connection fails.

It was quite difficult to find this. The OnConnectionChange event works as a charm, if you run the SSH tunnel within separate executable. But once you combine SSH functionality to your own app, and try to use the OnConnectionChange event, then the DB connection fails.

I'm not sure if this exactly was the reason, of course. The real technical reasons may well have names like "deadlocks, as blocking SQL request.." etc.

Also, maybe one could put some lightweight code inside OnConnectionChange event and the connection would still work.

Perhaps someone else discovers more details about it. Now I'll have to try to get my own apps further.

Thanks for finding the solution.
-Sanna
#11860
Posted: 12/01/2009 11:26:38
by Ken Ivanov (EldoS Corp.)

That's exactly what I have written in one of my previous posts. You should either remove OnConnection* event handlers (leaving SynchronizeGUI on) or switch SynchronizeGUI to false and perform the appropriate GUI synchronization yourself.

I will try to explain why it does happen this way.

1) You set Query.Active := true in the main thread of your application (SSH tunnel is already up and running),
2) the database component attempts to execute the query. I should note that the database component uses blocking calls, i.e. the Query.Active := true call does not return until all the requested data have been retrieved from the database server. Thus the main thread of your application is busy until the result of the query is received from the database server (you can notice this by the behavior of the main form, which does not respond to mouse or button clicks, does not redraw itself etc.),
3) Once TElSSHLocalPortForwarding accepts the incoming connection from the database component, it is happy to report this fact via the OnConnectionOpen event. Besides, during the lifetime of the tunnel it can also fire OnConnectionChange/OnConnectionClose/OnConnectionError events. If SynchronizeGUI is true, each of these calls is enveloped into a Synchronize() procedure call, which tries to fire the event from within the main thread of the application. But (!) the latter is busy waiting for the database component to finish the query. So we have the deadlock here: the synchronized event waits for the database component to finish, and the database component cannot finish as the forwarding needs the OnConnection* event to return to proceed with SQL request tunneling.

According to the explained above, either removing the OnConnection* handlers or setting SynchronizeGUI to false will resolve this deadlock.
#11867
Posted: 12/02/2009 05:01:12
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

Quote
Innokentiy Ivanov wrote:
That's exactly what I have written in one of my previous posts. You should either remove OnConnection* event handlers (leaving SynchronizeGUI on) or switch SynchronizeGUI to false and perform the appropriate GUI synchronization yourself.
I try not to waste anybody's time and that's why I always try to do all the tests exactly as asked. Also then I tried that one right away:
Quote
San P wrote:
I tested with SynchronizeGUI=False, but this had no effect. The original SSH tunnel Demo Form still logs everything to it's own Form, and shows there messages like "SSH Connection established" etc.

Now I'll say this again, to me it looks like SynchronizeGUI=False
would have no effect. You should be able to see that yourself if you add this line to the event handler of that working demo:
Code
procedure TfrmMain.ForwardingConnectionOpen(Sender: TObject;
  Conn: TElSSHForwardedConnection);
begin
  Label1.Caption := 'New secure channel opened';
end;

Then if you try to turn SynchronizeGUI=False, the DB application still always fails. The only working option really is not to have OnConnectionOpen event there at all.
Quote

If SynchronizeGUI is true, each of these calls is enveloped into a Synchronize() procedure call, which tries to fire the event from within the main thread of the application. But (!) the latter is busy waiting for the database component to finish the query. So we have the deadlock here: the synchronized event waits for the database component to finish, and the database component cannot finish as the forwarding needs the OnConnection* event to return to proceed with SQL request tunneling.
That explanation of SynchronizeGUI's behaviour and effects probably is explained somewhere on the manuals, or on Eldos site? At least to me that info had been useful in the first hand.
Quote

According to the explained above, either removing the OnConnection* handlers or setting SynchronizeGUI to false will resolve this deadlock.
Maybe you should try that test above, and we'll see if we agree or disagree with this specific thing:)

I general, I am happy that the solution was found and now SSH tunnel works. My intention is not to try to find guilty one or anything. I just want to ensure I have understood things right, and I may well have understood something wrong here.
San P
#11868
Posted: 12/02/2009 10:37:36
by Ken Ivanov (EldoS Corp.)

I did not say that just setting SynchronizeGUI to false will get rid of the deadlock:
Quote
a) turn SynchronizeGUI property off. This will make the component fire events in non-synchronized mode, however, you have to implement the handlers correctly (modifying GUI objects from a non-synchronized code may cause certain problems, so the handlers must not access the form and the controls contained in it)


The attempt to change the caption of the Label1 control from another thread while the main thread is busy waiting for Query.Active to return leads to the deadlock similar to the one I described above.
#11875
Posted: 12/03/2009 07:53:15
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

Quote
Innokentiy Ivanov wrote:
a) turn SynchronizeGUI property off. This will make the component fire events in non-synchronized mode, however,

Yes, but what exactly is then the use for those non-synchronized events anyway? The OnConenctionOpen event can not run in sensible way internally, within the SSH thread alone, while the logic always needs the input from the Main thread.

Currently TElSSHLocalPortForwarding comonent runs the code within OnConenctionOpen event always, no matter if the connection was opened or not. You could have this kind of code within that event:
Code
procedure TfrmMain.ForwardingConnectionOpen(Sender: TObject;
  Conn: TElSSHForwardedConnection);
var
  S:String;
begin
  S := 'Hey, everybody within this thread!!!  
   No matter if that ForwardingConnection actually
   was ever opened, we will execute the code
   within this event handler anyway.';
  FSomeImportantGlobalTimeVariable := DateTimeToStr(Now);
  FSomeOtherVariable := 'Put some strange and confusing values
  here also, as like the Connection was opened succesfully.';
end;

I understand there are several needs of those internal routines. But they should somehow be kept separate from these MainThread related events. And once the developer turns the SynchronizeGUI =False, then those events that are MainThread related, they should not be executed.
Not all the TElSSHForwardedConnection events are this much deadlock dangerous. Maybe only OnConnectionChange and OnConnectionOpen events should have that kind of treatment.

Quote
The attempt to change the caption of the Label1 control from another thread while the main thread is busy waiting for Query.

Ehen the property SynchronizeGUI =False, then an extremely clever Component should skip all those GUI stuff that point outside the SSH thread. But that kind of logic is quite difficult, or probably impossible to write into a SSH Component.
Maybe just give some new and better describing name for the current SynchronizeGUI property

In my example code above there are only thread's interal variables referenced. But while the code in OnConenctionOpen launches always, then I think also that can lead to misbehaviour.

I have spent four weeks with this SSH component and this problem. Yet I am not sure if these thoughts have any validity or not.
San P
#11878
Posted: 12/03/2009 12:06:59
by Ken Ivanov (EldoS Corp.)

Quote
Yes, but what exactly is then the use for those non-synchronized events anyway? The OnConenctionOpen event can not run in sensible way internally, within the SSH thread alone, while the logic always needs the input from the Main thread.

Not sure if I understand you right. The purpose of OnConnection* events is the notification about something being happening with the tunneled connection. In certain use cases there is a need to execute those events within the main thread of the application. To remove the task of synchronization from the shoulders of the user code we added the SynchronizeGUI property. This property can be safely turned off if there is no need in such synchronization.

Quote
Currently TElSSHLocalPortForwarding comonent runs the code within OnConenctionOpen event always, no matter if the connection was opened or not. You could have this kind of code within that event:

OnConnectionOpen is fired at the very start of the data transfer through the tunneled connection (once the incoming socket connection has been accepted, to be exact). It may be so -- I cannot claim this without taking a look at the code of the database components -- that the database component performs a single message queue processing before sending SQL request to server. This may cause OnConnectionOpen event synchronization/form access to succeed. However, the further synchronized events still will block.

Quote
I understand there are several needs of those internal routines. But they should somehow be kept separate from these MainThread related events. And once the developer turns the SynchronizeGUI =False, then those events that are MainThread related, they should not be executed.

In general case, none of the TElSSHLocalPortForwarding events are "MainThread-related" (as you name them). All the events either are synchronized with the main thread, or are not. In your particular case the OnConnection* events lead to deadlocks, while the others work fine. Under other circumstances the list of events causing deadlock will differ. So the solution is simple here: if you do not need the event to be executed in your particular case, just do not handle it.

Quote
Ehen the property SynchronizeGUI =False, then an extremely clever Component should skip all those GUI stuff that point outside the SSH thread. But that kind of logic is quite difficult, or probably impossible to write into a SSH Component.

Not sure if this would be the right behavior. The component may work in its own thread and do not deal with GUI at all -- all the events can fire safely under such circumstances. Besides, I do not think that the component should be "extremely clever". Instead, it should be predictable. Simple SSH components are quite predictable -- they either synchronize all the events (SynchronizeGUI is on), or allow all the events to fire in their own threads (SynchronizeGUI is off). Depending on the exact task of the user code (and third-party components being used), the user is free to choose the appropriate synchronization mode.
#11879
Posted: 12/04/2009 05:08:46
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

Quote
The purpose of OnConnection* events is the notification about something being happening with the tunneled connection. In certain use cases there is a need to execute those events within the main thread of the application.

Of course, in some cases those are very handy events for the application developer, to report something about the connection to the end user or so. But those couple of havoc events seem not to have any special meaning for the SSH component itself. The code within these events is run always, no matter if the forwarding connection was actually opened or not. For Component's internal needs there could maybe be simple private procedures to run the code for the component's internal needs or maybe private OnChange event. But those should kept separate from the published events, as they can so easily cause those dead locks.
Quote
To remove the task of synchronization from the shoulders of the user code we added the SynchronizeGUI property. This property can be safely turned off if there is no need in such synchronization.
And this is very close to what I think also. Once you turn that SynchronizeGUI=False, then it should turn SSH tunnel to act as the old Putty SSH tunnel. Not to care about anything around itself. Or at least when SSH component is out of GUI events then it should not paint itself to a corner, going to dead lock.
Quote
So the solution is simple here: if you do not need the event to be executed in your particular case, just do not handle it.
Yes, we both know this now after this marathon thread:) After going through all the possible driver related causes etc.
At the very first phase 4 weeks ago I wanted to avoid this. First write a bunch of my own code, and then start asking why this and that does not work in there. That's why I took one of your original standard simple SSH demos, and didn't change anything in there. I carefully used that Demo Form and explained what it did and what it did not with my DB application. If that miserable OnConnectionOpen event and a couple of others had not been in that demo, or if you had given exactly the simple advice above earlier, we both had spent several, several hours of time.

Now the SSH tunnel works and it's good. It looks like we at our side are going to be married with Eldos SSH Component for quite a while. So I'm only trying to understand what's going under the hood and get things clear and predictable in the future. All the thoughts I have brought here are from the application developer's view and standpoint. There's no way I would be able to write whole SSH tunnel Component myself now.
Quote
Simple SSH components are quite predictable -- they either synchronize all the events (SynchronizeGUI is on), or allow all the events to fire in their own threads (SynchronizeGUI is off).
As an Application Developer, when the property SynchronizeGUI =False, then the predictable, understandable and clear result to me would be that with this setting the application developer's own code should never be able to put also the SSH Tunnel Component to it's knees, in to a dead lock.

Just my thoughts. Yet no immediate need to change anything within SSH Tunnel Component now. As I now know how to write around those havocs and our app and tunnel seems to run quite OK now. Thanks for all the advices and comments.
San P
#11882
Posted: 12/04/2009 10:33:25
by Ken Ivanov (EldoS Corp.)

Quote
As an Application Developer, when the property SynchronizeGUI =False, then the predictable, understandable and clear result to me would be that with this setting the application developer's own code should never be able to put also the SSH Tunnel Component to it's knees, in to a dead lock.

I will try to conclude the topic by answering the above statement.

SSH forwarding classes "know" nothing about the third-party components that use them for tunneling higher-layer data. Neither they know whether those components run in the main or dedicated thread and whether they use synchronous or asynchronous model of execution. Without knowing these facts it is difficult to consider which events should be synchronized (or run at all) and which shouldn't. Thus SSH components go in a different way -- they propose two types of event invocation: synchronized (suitable for simple GUI applications that use asynchronous third-party components within the main thread of execution) and non-synchronized (universal, but requiring certain care from the user code). As the user knows the specifics of work of the third-party components he uses, he is able to choose the correct event invocation type and implement the event handlers in the way not causing the locking. However, I understand that this task is not trivial without knowing the details of work of simple forwarding components I explained in the above messages.

We would like to thank you for interesting thoughts and suggestions. I think we need to summarize the above conversation in a kind of how-to article and make it available in the SecureBlackbox knowledgebase.
Also by EldoS: CallbackDisk
Create virtual disks backed by memory or custom location, expose disk images as disks and more.

Reply

Statistics

Topic viewed 7412 times

Number of guests: 1, registered members: 0, in total hidden: 0




|

Back to top

As of July 15, 2016 EldoS Corporation will operate as a division of /n software inc. For more information, please read the announcement.

Got it!