Zeos/MariaDB/Lazarus: Instead of Embedded libmysqld.dll, Using mysqld.exe via named pipe

[PaulNormanNZ]

Hi,

(Cross Post advisory :-)
I originally posted over on Lazarus Forum but the feeling there is that I need to post here.
https://forum.lazarus.freepascal.org/in ... #msg396683

I am using:
ZeosLib 7.2.8.0 from the Lazarus "Online Package Manager"
Lazarus 2.0.8 r62944 FPC 3.0.4 x86_64-win64-win32/win64
Windows 8.1 Prof

I want to use Mariadb (64bit) as effectly embedded, with Zeos components,
and have been trialing with the supplied

Code: Select all

zeosdbo\examples\embedded\Embedded.lpr

converted from Delphi to Lazarus .lpi
and a 28 ‎January ‎2018 version 10.2.12.0, copy of libmysqld.dll from a Mariadb support forum enetereed as TZConnection.LibraryLocation, but can neither get it recognised under TZConnection.Protocol as any available Mariadb or MySql setting at all.

(Mariadb have not been supplying binary releases of libmysqld.dll since early version 10 for a while now saying that they haven't the Windows based resources to do it, and no one else seems to have filled the gap.)

So I looked into building my own libmysqld.dll - but that is pretty problamatic it seems on Windows, the current MariaDB build advice page does not even seem (as far as I can tell) to list instructions for (libmysqld) libmysqld.dll (unless it is renamed now?) as a target at all any longer.
https://mariadb.com/kb/en/Building_MariaDB_on_Windows/

The general kind of advice used to be for example ...

Code: Select all

\mariadb-10.5.9>cmake . -DWITH_EMBEDDED_SERVER=1 && cmake --build . --config rel
withdebinfo --target libmysqld

But that is just not going to happen. Too many compile problems even with their reccomended Visual Studio 2019 (Community Edition) from the comand line.

Then I read a reference (for C languages):

https://jira.mariadb.org/plugins/servlet/mobile#issue/MDEV-15100/comment/106350

--- for avoiding the embeded version all together and to now instead use arguments on starting MariaDB:--

Code: Select all

Embedded provides some convenience, I agree, but this convenience is minimal. 

You can achieve the same effect as embedded rather trivially , by starting:

mysqld.exe --skip-networking --enable-named-pipe --named-pipe=<some-unique-name>

 instead of mysql_library_init()

and connecting via normal C API, libmariadb.dll, via this named pipe <some-unique-name>. 

This does not need mysqld.exe to be:

- run as service, 
- doesnot use TCP port, 
- does not need any privileges.

Sounds absolutely ideal for our purposes. And keeps away from the (ongoing) .dll situation.

Does any one please have any advice on how that could be done under Lazarus wth Zeos(?) components?

Here is some info from the MariaDb api reference:

Code: Select all

https://mariadb.com/kb/en/configuring-mariadb-connectorc-with-option-files/#pipe

MYSQL_OPT_NAMED_PIPE
MYSQL_OPT_PROTOCOL=3


https://mariadb.com/kb/en/connecting-to-mariadb/

pipe

--pipe
-W

On Windows systems that have been started with the --enable-named-pipe option, use this option to connect to the server using a named pipe.


protocol

--protocol=name

Specifies the protocol to be used for the connection for the connection. It can be one of TCP, SOCKET, PIPE or MEMORY (case-insensitive). Usually you would not want to change this from the default. For example on Unix, a Unix socket file (SOCKET) is the default protocol, and usually results in the quickest connection.

    TCP: A TCP/IP connection to a server (either local or remote). Available on all operating systems.
    SOCKET: A Unix socket file connection, available to the local server on Unix systems only.
    PIPE. A named-pipe connection (either local or remote). Available on Windows only.
    MEMORY. Shared-memory connection to the local server on Windows systems only.

Any advice or pointers appreciated please - even a good repository for precompiled Mariadb libmysqld.dll and share folder etc.
Kind regards,

Paul

ZeosLib 7.2.8.0 from the Lazarus "Online Package Manager"
Lazarus 2.0.8 r62944 FPC 3.0.4 x86_64-win64-win32/win64
Windows 8.1 Prof

[marsupilami]

Hello Paul,

I seem to remember that MySQL has discontinued their embedded server and that MariaDB never supported it officially. If you start something new, the embedded Firebird server might be something for you.

Continuing with MariaDB, this is what you could try:

• specify MYSQL_OPT_NAMED_PIPE and MYSQL_OPT_PROTOCOL=3 in the Properties of ZConnection.
• go with the default name for the named pipe

But I assume this might give you trouble when there already ia a MariaDB or MySQL server running on the same machine.

Unfortunately Zeos currently doesn't allow to specify the name of the named pipe. According to Oracles Documantation on mysql_real_connect the name of a named pipe has to be specified as a separate parameter. This parameter is currently set to nil by Zeos at all times.

Another option might be to try to use a shared memory connection. You probably have to use MYSQL_SHARED_MEMORY_BASE_NAME and MYSQL_OPT_PROTOCOL in that case. Please read up on these parameters in the MySQL / MariaDB documentation.

Please keep in mind: I just browsed the documentation of MySQL. I didn't test any of this.

Best regards,

Jan

[PaulNormanNZ]

Thanks Jan,

I've been over on https://jira.mariadb.org/browse/MDEV-25082

One of their ace fellows there, Vladislav Vaintroub, has come forward with some suggestions, so I'll simply reporduce what he had to say there.

... We did not build it on Windows for 5 years, or something close to that.
When I try to build 10.5 now on Windows, it fails, because apparently,
something has changed in the way we make static libs out of static libs,
and there are other errors.

I doubt think there will be a revival of Windows embedded soon.

Instead I'd suggest to talk to whoever builds that framework that you are using, to support named pipes.

If this Zeos, whatever it is, uses C client library underneath,
you can pass string "." as host, and named pipe name as unix socket.

Or, you can use TCP as well, just find the port that is not used, and change the server's bind parameter to only accept local connections.

I would like to avoid TCP for this side of things if I can.

So, any one have any further thoughts please on pipes and socket name under Zeos as Vladislav Vaintroub suggests?

One of the advantages of using Embeded, or this new pipe/socket approach, is that the data created is initially effortlessly network blind, and transfer/share-able, but nonetheless immediately deployable also as a reguly TCP scenario straight out of the starting blocks whenever required.

And effectively MariaDB is acting as if SQLite3 on mega mega-steriods!

[marsupilami]

Hello Paul,

If this Zeos, whatever it is, uses C client library underneath,

Yes - we do.

you can pass string "." as host, and named pipe name as unix socket.

This is the problem. Right now we don't support passing the name of a named pipe. It will take some days to add support for that kind of connection to Zeos. There is a tickt for this in our feature requests tracker.

If you need this to work sooner, you might want to look into the shared memory connection.

Best regards,

Jan

[PaulNormanNZ]

Thanks Jan,

Vladislav Vaintroub closed the ticket on the MariaDB forum as he said that the answer was here ten years ago (he supplied the link below), but when I looked at that I was not sure it was still valid, how does that look now please? (Just in case it is relevent as well to the Feature request you referenced ... "#30 MySQL / Mariadb + Named Pipes " ...

viewtopic.php?t=3364

If you need this to work sooner, you might want to look into the shared memory connection.

Does this still look accurate please ( 9 years, 5 months ago) as it is the only thing I can easily find ...
(And the poster added: "Hope this will help you somehow. I haven't tested it because I don't have the proper environment.")

https://stackoverflow.com/questions/770 ... y-protocol

And again,

viewtopic.php?t=3318

Kind regards,
Paul