A Mormot, ZEOS, MySQL and a Windows service ...

wai-kit · 2013-11-28 14:51:03

Hi,

I have a mormot server with a connection to a MySQL database. The connection looks like this

TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dMySQL, 'libmysql.dll'),
                                                DatabaseProperties.DatabaseName,
                                                DatabaseProperties.Username,
                                                DatabaseProperties.Password ).

When I run this server, a windows executable, my corresponding windows client can connect to it. Everything
works accordingly, I can fetch data from the server. The client is implemented with TSQLRestClientURI.
I can connect to the server and fetch data from it.
However, when I implemented the server as a Windows Service, I got an EZSQLException with message 'Requested database driver
was not found' when fetching data from it. Connecting seems no problem.

The code for the windows service looks like:

procedure TServiceSiM.ServiceExecute(Sender: TService);
var
  aSimServer:TSiMServer;
begin
  // create the server and initialize
  aSimServer := TSiMServer.Create('888', false);
  try
    aSiMServer.SetDatabase('localhost', aDatabasename, aUsername, aPasswd); // sets database properties for TSQLDBConnectionProperties

    // start the server
    if aSimServer.StartServer then begin
    // create a hold up while service is running
      while not Terminated do begin
        Sleep(1000);
        ServiceThread.ProcessRequests(false);
      end;
    end;
  finally
    FreeAndNil(aSimServer); // stop server by destroying the instance
  end;
end;

TSimServer=class(TMormotServer)
...
end;

TMormotServer is a class in which a TSQLRest, a TSQLModel and a TSQLHttpServer is encapsulated. The procedure SetDatabase sets the database properties to be used
for the TSQLDBConnectionProperties.

Anybody has an idea?

Thanks,
Wai

ab · 2013-11-28 18:12:33

Check the path of the user runni g the service, or specify the client dll place.

wai-kit · 2013-12-02 14:10:48

I change : fDatabase := TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dMySQL, 'libmysql.dll'),
fDatabaseProperties.DatabaseName,
fDatabaseProperties.Username,
fDatabaseProperties.Password );

to

fDatabase := TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dMySQL, 'C:\libmysql.dll'),
fDatabaseProperties.DatabaseName,
fDatabaseProperties.Username,
fDatabaseProperties.Password );

and making sure libmysql.dll is in 'C:\' but I still get the Exception.

ab · 2013-12-02 14:50:06

Windows expects ALL .dll used by the client library to be in your path, or the current directory.

Perhaps the MariaDB client, as supplied with Zeos, is a better fit: it is a single small dll.
See https://mariadb.com/kb/en/mariadb-client-library-for-c/

wai-kit · 2013-12-03 09:47:26

the dll is in the same directory as the Windows service executable.
I also tried with the dll in the system path. To no avail. Also tried changing the user of the service.

It looks to me that the problem is not so much the dll, but that the service cannot find the dll.

ab · 2013-12-03 10:53:26

Did you try to change the user running the service from SYSTEM to your current (administrator) user?

wai-kit · 2013-12-03 11:07:13

Yes, I did.

ab · 2013-12-03 13:02:42

I do not find any reason, then....

Perhaps you may ask for support in the ZEOS forum.
They are very reactive and helpfull...
You may have to write a "pure ZDBC" version first, to ensure that the problem is not in our wrapper.
Also ensure you got the latest ZEOS version (7.1.2 AFAIR).

Please post your solution here when you found it!

EgonHugeist · 2013-12-03 14:48:39

Just an idea before asking me in Zeos-Forum:

Compile-Target is equal with the dll?
32Bit-App only works with 32Bit library.
64Bit-App only works witha a 64Bit lib.

Keep track you use the right one!

ab · 2013-12-03 16:12:17

wai-kit · 2013-12-03 18:09:43

, I might as well ask it here. Maybe it will be of use for my fellow Mormots .
I have 2 versions, a windows service and a 'normal' executable. The normal executable can find
the libmysql.dll, but the windows service can't.

What I can remember if when the dlls are incompatible I get a different exception.

EgonHugeist · 2013-12-03 18:25:20

Well Zeos !actually! rasies always the same exception if the lib couldn't be loaded. It might be an idea to check
if LibExists then
raise Exception.Create('Incompatible library found! Check compile-target.. blabla")
else
raise KnownException.

Or something like this. 99% of the users have the same problem.

To your isse: Compile-Target of Service and App are equal? Dll is located in a path which is included in you environment-variables or in same directore of you executables?
You could also connect having both version located on your computer.

Just move the !32Bit! libmysql to !\Windows\SYSWOW64!
and the !64Bit! libmysql to !\Windows\System32!.
I know this is a bit confusing -> Microsoft.
Than keep track there are NO other libs available and -> have fun.

wai-kit · 2013-12-17 11:18:08

Okay.
I made sure all libmysql.dll are the same. Compile target and dll is the same.
I also made a console version of the Mormot server and I also get the same EZSQLException. I traced it into the following code:

constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
inherited Create(aProperties);
fDatabase := DriverManager.GetConnectionWithParams(
(fProperties as TSQLDBZEOSConnectionProperties).fURL.URL,nil);
fDatabase.SetAutoCommit(true);
fDatabase.SetTransactionIsolation(tiNone);
end;

And the exception occurs at fDatabase := DriverManager.GetConnectionWithParams( ...) . Setting up a watch at fProperties (I assume fProperties is assigned the value of parameter aProperties)revealed that
LibLocation is assigned the right location of the libmysql.dll.

Last edited by wai-kit (2013-12-17 12:32:22)

EgonHugeist · 2013-12-18 11:30:13

Well, first of all my TService apps are running fine with Zeos. So we've to find out whats wrong.

After reading your first post again.. the dll doesn't seem to be your issue. There is something weired with the dbc sources on your side.

Some steps to find your issue:
1. which protocol-name are you using for? "mysql"? (adapts always to the latest protocol plaindriver)
2. are you sure ZDbcMySQL is included?
3. Set a break point to:

.......
initialization
  MySQLDriver := TZMySQLDriver.Create;
  DriverManager.RegisterDriver(MySQLDriver);

Or add a MessageDlg there (The TService is minior debugable)
This registers the TZDriver where the IZDriverManager(ZDbcIntfs.pas) gets the connection from. IF this doesn't happen than track why it happens. Is this file missing? Something wrong in you path-settings?
Or did you disable {ENABLE_MYSQL} define in Zeos.inc.

4. I'll wait for reply

Last edited by EgonHugeist (2013-12-18 11:47:54)

wai-kit · 2013-12-22 14:21:51

Hi EgonHugeist,

some answers to the questions:
1. using TSQLDBZEOSConnectionProperties from the Mormot framework I have not set the protocol name anywhere.

Then I made a new project using TZConnection component. The following properties of the component were set:
Hostname, database, librarylocation, user, password. I then tried to set connected to true and I got the error: Requested database driver was not found. Then the property
Protocol was set to Mysqld-5 and the connection succeeded.

So, now I need to find out why property protocol is not set or has a wrong value when using TSQLDBZEOSConnectionProperties to connect to the database.

ab · 2013-12-22 16:22:48

AFAIR you used TSQLDBZEOSConnectionProperties.URI(dMySQL) which will set the protocol name.

BTW could you try to set ZEOS_PROVIDER line 517 from 'mysql' to 'mysqld-5' ?

wai-kit · 2013-12-23 10:34:02

changed mysql to mysqld-5. No succes.

Traced the execution into ZDbIntfs.
The callstack looks something like this :
...
SynDB.TSQLDBConnectionProperties.ExecuteInlined
PrepareInlined
...
eventually into

function TZDriverManager.GetConnectionWithParams(const Url: string; Info: TStrings):
IZConnection;
var
Driver: IZDriver;
begin
Driver := GetDriver(Url);
if Driver = nil then
raise EZSQLException.Create(SDriverWasNotFound);
Result := Driver.Connect(Url, Info);
end;

the exception is raised because Driver = nil

I set a breakpoint in the initialization part of ZdbMYSQL.pas, but this breakpoint never gets referenced.
Also this unit is not in the uses clause of SynDBZeos.

Last edited by wai-kit (2013-12-23 10:34:57)

wai-kit · 2013-12-23 11:54:51

Update.

The initialization code in ZdbcMysql was not executed, so I added this unit in the uses clause of SyndbZEOS. Now the code is executed and causing
Getdriver(Url) to return a driver and the exception not being raised.

Question now is, in case of a Mormot using ZEOS to access a MySQL database, does it make sense to create the driver in TSQLDBZEOSConnectionProperties.URI(dMySQL, 'libmysql.dll')?

Last edited by wai-kit (2013-12-23 12:02:15)

EgonHugeist · 2013-12-23 12:37:18

You are right. I don't know if the units are included somewhere else..

propose to add

{$IFNDEF UNIX}
{$IFDEF ENABLE_ADO}
  ZDbcAdo,
{$ENDIF}
{$ENDIF}
{$IFDEF ENABLE_DBLIB}
  ZDbcDbLib,
{$ENDIF}
{$IFDEF ENABLE_MYSQL}
  ZDbcMySql,
{$ENDIF}
{$IFDEF ENABLE_POSTGRESQL}
  ZDbcPostgreSql,
{$ENDIF}
{$IFDEF ENABLE_INTERBASE}
  ZDbcInterbase6,
{$ENDIF}
{$IFDEF ENABLE_SQLITE}
  ZDbcSqLite,
{$ENDIF}
{$IFDEF ENABLE_ORACLE}
  ZDbcOracle,
{$ENDIF}
{$IFDEF ENABLE_ASA}
  ZDbcASA,
{$ENDIF}
{$IFDEF ENABLE_POOLED}
  ZDbcPooled,
{$ENDIF}

to interface uses clause of SynDBZEOS.pas

Btw. you can patch your local Zeos.inc and comment these defines to exclude plains you don't need.

wai-kit wrote:

Question now is, in case of a Mormot using ZEOS to access a MySQL database, does it make sense to create the driver in TSQLDBZEOSConnectionProperties.URI(dMySQL, 'libmysql.dll')?

Of course! you can also spezify another location to load your lib's with ZDbc

ab · 2013-12-29 14:32:45

Good idea.
SynDBZeos unit will now add all available physical ZDBC providers by default.
See http://synopse.info/fossil/info/488a6a5f4e

It will be less confusing.

mORMot Open Source

#1 2013-11-28 14:51:03

A Mormot, ZEOS, MySQL and a Windows service ...

#2 2013-11-28 18:12:33

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#3 2013-12-02 14:10:48

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#4 2013-12-02 14:50:06

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#5 2013-12-03 09:47:26

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#6 2013-12-03 10:53:26

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#7 2013-12-03 11:07:13

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#8 2013-12-03 13:02:42

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#9 2013-12-03 14:48:39

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#10 2013-12-03 16:12:17

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#11 2013-12-03 18:09:43

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#12 2013-12-03 18:25:20

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#13 2013-12-17 11:18:08

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#14 2013-12-18 11:30:13

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#15 2013-12-22 14:21:51

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#16 2013-12-22 16:22:48

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#17 2013-12-23 10:34:02

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#18 2013-12-23 11:54:51

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#19 2013-12-23 12:37:18

Re: A Mormot, ZEOS, MySQL and a Windows service ...

#20 2013-12-29 14:32:45

Re: A Mormot, ZEOS, MySQL and a Windows service ...

Board footer