EldoS | Feel safer!

Software components for data protection, secure storage and transfer

SSH Tunnel MySQL

Also by EldoS: RawDisk
Access locked and protected files in Windows, read and write disks and partitions and more.
#11593
Posted: 11/07/2009 10:39:13
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

I use the original demo LocalPortForwardDemo to open SSH tunnel and do port 3306 forwarding.

My own app uses http://www.microolap.com MicroOlap MySQL components. After opening the tunnel I start the app and it connects fine to the MySQL database through that localized port.

To combine the SSH tunnel to my app I took for starters the whole MainForm from the SSH tunnel demo, and integrated it in to my app.
In my test I open the SSH tunnel and leave it running and also that mentioned MainForm open in the background. SSH log looks like this:

Server key received
Authentication succeeded
SSH connection established


Then I click MySQL Connect -button on another Form. But this time I can't get the MySQL connect up any more. MySQL timeout fires after 15 second or so.

The log on SSH tunnel Form does not get updated during that 15 second wait time. Only after MySQL timeout tells the connection can't be opened there appears text again on SSH tunnel's Log:

New secure channel opened
Secure channel closed


Is it so that I have to add some threading to somewhere? So the SSH tunneling and MicroOlap data components both get the CPU time to do their task.

I was in an impression that SSH tunnel always runs within it's own thread, and that way it does get the CPU time it needs. But looks like at least the SSH Log updating does not run inside that thread, while the log screen does not get updated immediately.

I know here is some SSH beginner's mistake I have done.

Any ideas what I should try to tune? Is it SSH tunnel or maybe something within the MicroOLAP Database component's threading capabilities or something?
Thanks.
-Sanna
#11594
Posted: 11/07/2009 11:30:51
by Eugene Mayevski (EldoS Corp.)

The developers will answer you in details later (Saturday evening, you know ...), meanwhile I'll add a couple of remarks.

First of all, you keep the form in background, but do you create and maintain this form in the main thread. VCL requires that all VCL GUI is run in the main thread. Worker threads may not have forms.

Second (related) issue is that logging should be performed in the main form. If the event is fired from the secondary thread, Synchronize() method should be used to update GUI.

I don't have the sample at hand to look at it, but it is very likely that while copying the code from the sample you've missed one of the above, and this might cause problems you are having.

Yes, worker threads are created automatically for each forwarding tunnel.


Sincerely yours
Eugene Mayevski
#11600
Posted: 11/09/2009 01:11:49
by Ken Ivanov (EldoS Corp.)

1) There are two samples with the name of LocalPortForwardDemo available (the first one in SSHBlackbox\Client\LocalPortForwarding\ directory, another one in SSHBlackbox\Client\LocalPortForwarding\SimplePortForwarding\Local\ directory). We suggest you to use the second ("simple") one, as it is based on highly-optimized simple forwarding components,

2) Using client-side MySQL components in the main thread of the application may result in conflicts with other networking components used within that application (I assume this has something to do with the Cygwin library used by MySQL driver, but I didn't check that). So you MUST run your MySQL code in a separate thread to make it work with SBB.
#11608
Posted: 11/09/2009 07:03:31
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

Quote
Innokentiy Ivanov wrote:
We suggest you to use the second ("simple") one, as it is based on highly-optimized simple forwarding components,

Yes, we have tested with both of those. Even the more 'advanced' demo works, even though it's Log always shoes "Authentication type 2 failed" message. The pipe probably opens with the type 1 authentication instead.

Quote
(I assume this has something to do with the Cygwin library used by MySQL driver,

It's all Pascal, there are no MySQL libraries nor drivers included in the MicroOLAP package: http://www.microolap.com/products/connectivity/mysqldac/
"no MySQL libraries (libmysql.dll) are required on a client workstation;
100% native Delphi code; "

Quote
So you MUST run your MySQL code in a separate thread to make it work with SBB.

Hmm, we have used threads only for quite simple tasks, like opening some slow big Queries or updates or something. I was wondering how much extra trouble would that requirement bring?

If it means that we only have to open the MicrOLAP TDatabase component inside different thread, then that would be simple.
But if we somehow have to write all our application's Forms and all behaviour inside one or more threads, then it will turn too complicated in general.
-Sanna
#11612
Posted: 11/09/2009 10:33:06
by Ken Ivanov (EldoS Corp.)

Then this issue is likely to have a different reason. Up to now we have faced it with MySQL driver who hung up inside the blocking socket "connect" call while establishing connection to the listening SBB socket.

You do not have to move all your application's logic to a separate thread. The only code that should be moved there is the code that actually performs MySQL query.
#11634
Posted: 11/10/2009 12:54:29
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

Quote
Innokentiy Ivanov wrote:
Then this issue is likely to have a different reason. Up to now we have faced it with MySQL driver who hung up inside the blocking socket "connect" call while establishing connection to the listening SBB socket.

I do not think that this has anything to do specially with MySQL. I can reproduce the exact behaviour also with DBISAM databases, and in pure Windows environment.

- I installed Open SSH Server package http://sourceforge.net/projects/sshwindows/files/ to our server machine running XP-Professional.
- Put DBISAM database and the DBISAM server process running on that server machine.
- The Client app on another Win-XP machine uses TDBISAMDatabase, TDBISAMQuery etc. components only. Nothing related to MySQL.
- Again, when I used the original BlackBox SSH demo app to open the SSH tunnel and do the port forwarding. When SSH tunnel is run and opened within separate executable, the tunnel opens fine, and I can get connected to DBISAM database.
- If I integrate the SSH Demo Form to my application and try to launch the SSH tunnel from there, then SSH Form and its Log shows that the SSH tunnel was opened. But just like with the MySQL client also the DBISAM client will never get connected to the server.

Quote
You do not have to move all your application's logic to a separate thread. The only code that should be moved there is the code that actually performs MySQL query.

The problem is that our app is not an single Form, single Query application. In the minimum it will have at least 15-20 Forms, two Datamodules, several Reports etc. I have no idea how to put all that functionality inside some separate Thread.

I great deal of this app already exists, partly taken from older DBISAM projects. We bought the SSH Client to add secure tunneling capability to our open internet app. My current thought is that 'SSH Tunnel' more like separate component, and it should be easier to threadenize than whole DB applications. . Maybe put SSH Tunnel inside a DLL,somehow better arrange own thread to SSH Tunnel or something. Well, I am no Thread specialist at all.

It was no big surprise to me to discover that also DBISAM Tables behaved in the same way as MicroOLAP Tables and other data components. They both descend from standard Delphi TDataSet, and so it is predictable that they both behave in the same way when acting with SSH Tunnel.
TDBISAMDataSet = Class(TDataSet)
TmySQLDataSet = Class(TDataSet)


I wonder if there exists any demo application that would show its is possible to integrate SSH tunnel to any DB application. Some app that uses those usual TTables, DbGrids etc., the standard GUI development stuff.

I still have good hopes that there would be some simple solution.
-Sanna
#11637
Posted: 11/11/2009 00:35:50
by Ken Ivanov (EldoS Corp.)

Quote
If I integrate the SSH Demo Form to my application and try to launch the SSH tunnel from there, then SSH Form and its Log shows that the SSH tunnel was opened. But just like with the MySQL client also the DBISAM client will never get connected to the server.

In any case, the symptoms are the same -- the connect call performed from the main thread to the listening socket opened within the same application blocks forever. It is not an SBB issue, but a socket layer problem.

Quote
The problem is that our app is not an single Form, single Query application. In the minimum it will have at least 15-20 Forms, two Datamodules, several Reports etc. I have no idea how to put all that functionality inside some separate Thread.

You do not have to move all that functionality (forms, reports etc.) to a separate thread. The only code that must be executed in another thread is the code that establishes TCP connection to the database server (or to SBB components in the case of the forwarded channel), sends query and receives a response. All other components can be safely left in the main thread of the application.

Quote
I wonder if there exists any demo application that would show its is possible to integrate SSH tunnel to any DB application. Some app that uses those usual TTables, DbGrids etc., the standard GUI development stuff.

There is a dbExpressForwarding sample available at the %INSTALLDIR%\Samples\Delphi\SSHBlackbox\Client directory. Though it is based on low-level SSH components, the approach would be the same for simple forwarding components.
#11650
Posted: 11/11/2009 13:46:16
by San P (Standard support level)
Joined: 11/07/2009
Posts: 37

Quote
Innokentiy Ivanov wrote:
You do not have to move all that functionality (forms, reports etc.) to a separate thread. The only code that must be executed in another thread is the code that establishes TCP connection to the database server (or to SBB components in the case of the forwarded channel), sends query and receives a response

I tried in different way to dynamically create TDatabase component inside threads etc. No luck. That approach you suggested will not succeed with my current TCP/IP skills. I would have to take one or two weeks to start studying everything in there, and then the try to understand the VCL code of those Database components.

Quote

There is a dbExpressForwarding sample available at the %INSTALLDIR%\Samples\Delphi\SSHBlackbox\Client directory. Though it is based on low-level SSH components, the approach would be the same for simple forwarding components.

With my standard Indy-10 components I could not get that compiled. I saw there is some BlackBox README for Indy, but I did not take time to evaluate it.
That DbExpress demo also looks quite messy, not very much Component Oriented design and approach. Some parts even slightly look like those pitifull Threading code snippets I tried to write earlier this day:)

I still kind of think that it is the SSH Tunneling Component or functionality that should run within its own thread, and not to push that burden to Database components.
When BlackBox SSH Tunnel is run within separate executable, the BlackBox functionality is very good and the tunneling works closely that way.

After no success and a lot of struggling, I downloaded and tested with another SSH Package from here http://www.devart.com/sbridge/ They also have pure VCL SSH Components.
With those Devart SSH components I was able to drop them on the Form, and then the MicroOlap MySQL components. I was easily able to open the SSH Tunnel and DB connection within the same Form and application.

So it looks like their SSH tunneling components has diffrent approach than BlackBox SSH. At least my testing with their demo version proves that the VCL SSH tunnel can be threaded or piped or something, inside the same application.

I wonder if it would be possible for Black Box SSH component to have that same capability in the near future?
Also if that is not possible for BlackBox components, lets say within two months or so, then also that would be important information for our own near time plans.

Thanks for all the comments and information this far.
-Sanna
#11653
Posted: 11/12/2009 00:37:16
by Ken Ivanov (EldoS Corp.)

Quote
With my standard Indy-10 components I could not get that compiled. I saw there is some BlackBox README for Indy, but I did not take time to evaluate it.

I assume that the demo is based on Indy 9 socket components.

Actually, the only parts of the demo that worth looking at are the TQueryThread class (QueryThread.pas unit) and handling of its creation and termination (btnExecuteClick, QueryThreadTerminate methods of the main form). Those parts are Indy-independent and can be re-used in any other environment. Most of the messy stuff you have encountered is related to dealing with low-level forwarding components and is not needed if simple forwarding components are used.

Quote
I still kind of think that it is the SSH Tunneling Component or functionality that should run within its own thread, and not to push that burden to Database components.

It does run in its own thread. The problem is caused by a blocking client socket who hangs when trying to connect to the listening socket within the same process.

Quote
So it looks like their SSH tunneling components has diffrent approach than BlackBox SSH. At least my testing with their demo version proves that the VCL SSH tunnel can be threaded or piped or something, inside the same application.

Thank you for letting us know, will take a look. I hope we will be able to give you an answer whether we can do something in this regard in few days.
#11782
Posted: 11/23/2009 06:08:41
by Ken Ivanov (EldoS Corp.)

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.

However, as simple forwarding components use synchronization when firing the events, special care should be taken to prevent deadlocks, as blocking SQL request does not allow those events to get to the main thread until the request is completed. There are two ways you can go to exclude the deadlock:
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),
b) just do not handle events of TElSSHLocalPortForwarding component.
Also by EldoS: Callback File System
Create virtual file systems and disks, expose and manage remote data as if they were files on the local disk.

Reply

Statistics

Topic viewed 7426 times

Number of guests: 2, 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!