MariaDB server v10.3.12 - 32 bits for Windows

Installed on : Windows Server 2016 64 bit, Intel Xeon CPU e5-2620

I have done more testing:

The problem is present on 32 bit / 64 bit MariaDB. I have also tried with MariaDB v 10.3.6 win64, which have the connect engine version 1.06.007. Same problem.

The problem is each request I send eat up between 600k and 800k of RAM (monitoring with Windows Task Manager) and that RAM is not released. I can send 10 times the same SELECT request, each time it will consume a little bit of RAM. I have waited over 12 hours without anymore requests sent to the server, the memory usage did not went down a single byte.

I was suspecting the ODBC driver, so I have tried to create a CONNECT table of type ODBC using the PostgreSQL driver. If I send 10 times the same SELECT request, memory usage climbs up a little bit, but stops after the second or third time that they are sent. Then, after a while, the memory usage goes down slowly.

I have 2 different 32bit ODBC drivers, from different vendors, that makes the memory usage goes up without being released and the PostgreSQL driver seems to react more normally. Is the problem inside the ODBC drivers or in the way the CONNECT engine use / interact with the ODBC driver ?!?

CONNECT does not allocate any storage in RAM. It has its own memory handling, which is to allocate a big storage area when used for the first time and only do sub-allocations in it during queries and clearing it between queries.

There are a few exceptions but not concerning the ODBC table type.

Thank you for your answer Olivier. Ok, let say that my old ODBC drivers that I am using are the one to blame... I was able to reproduce the same situation with a MySQL setup. I know that the FEDERATE engine would have been more appropriate, but using the CONNECT engine enable you to see the problem without having access to the same old ODBC drivers that I am using.

I am joining an image of my current setup (everything is under Windows). Just send 10 - 20 times the same SELECT request from MySQL Workbench and watch through Windows Task Explorer the RAM usage climb up and never go back to it's original usage

I am using the Bitnami WAMP stack as the MySQL server.

Thanks !

Olivier, did you had time to try and see the problem using the latest infos I have put here ?

The only solution I have right now, is a daily schedule restart of the Windows service... The ODBC part of the Connect engine is dangerous to the stability of the server in his actual state.

Thanks !

As I have previously said, this seems to be an ODBC driver problem, not a CONNECT one.

So, what is left to use the Connect ODBC functionnality with if all ODBC drivers are faulty except the PostgreSQL ?!? It does not seems normal to me that 3 differents ODBC drivers (different vendors / developpers) are all consuming RAM without releasing it when used within the Connect engines but do release RAM when being used outside of Connect.

Olivier, did you test the latest MySQL faulty setup that I have put here ?!?

I don't want to be rude, but... As a programmer, I never assume that the fault is elsewhere than in my own code until I rule it out by doing some tests.

Thank you.

If you want me to do some tests, give me the exact scenario causing this error:

What ODBC driver you are using, preferably a known one: did you say the mysql driver could be used
What external source: Oracle, Mysql, Postgresql etc...
The external table you are accessing
The CREATE TABLE
the SQL statements you are executing

Thanks

For instance I did the following:

create or replace table dorx (

command varchar(128) not null,

number int(5) not null flag=1,

message varchar(255) flag=2)

engine=connect table_type=odbc connection='DSN=ConnectEngineOracle;UID=SYSTEM;PWD=manager'

option_list='Execsrc=1';

select * from dorx where command='drop table memo';

select * from dorx

where command='create table memo (nofourn int, nom varchar(25), ville varchar(25))';

select * from dorx where command="INSERT INTO memo VALUES (1,'Martin','Paris')";

select * from dorx where command="INSERT INTO memo VALUES (1,'Dupond','Marseille')";

select * from dorx where command="INSERT INTO memo VALUES (1,'Dupont','Bordeaux')";

drop table xmemo;

create table xmemo engine=connect table_type=ODBC tabname='MEMO'

CONNECTION='DSN=ConnectEngineOracle;UID=system;PWD=manager';

select * from xmemo;

I could execute all this and repeat the last select a hundred time with no memory problem.

Hello Olivier, thanks for checking it out. All the details of software / setup / versions are in the "setup for testing.jpg" image that I have upload in this thread.

Anything that you do from there on the Connect table will consume and not release RAM.

The detail of the remote MySQL table is in the "tblfourn on MySQL server.jpg" image.

For the memory usage going up, I use the Windows Task Manager (Gestionnaire des tâches Windows).

I don't know if I did it all right, but here is a log / dump of the faulty MySQL setup consuming RAM. Call 2 - MySQL.txt

What is the bitnami WAMP stack? And you are connected to what remote server? Another MariaDB server or SQL Server or what?

I was using WTM indeed to check the memory.

Call 2 - MySQL.txt: Shows something that could explain the memory loss. Did you use ODBC to access via ODBC another CONNECT table?

Olivier, I sent you an email yesterday with a little bit more details in French. Again, the software / ODBC configuration is in the attach image and the table definition in MySQL is in

"MySQL Workbench" === talks to ===> "MariaDB server" === CONNECT engine talks to ===> "MySQL ODBC driver" === talks to ===> "MySQL server (part of Bitnami WAMP stack)"

I did this setup to see if the RAM problem was there so that you can test it without having access to my old ODBC drivers that are facing the same problem.

When I try to access the CONNECT table in MariaDB for regular business usage, the setup is:

"Windows .NET program" === talks to ===> "MariaDB or MySQL ODBC driver" === talks to ===> "MariaDB server" === CONNECT engine talks to ===> "Wathever 3rd party ODBC driver" === talks to ===> "Wathever DB server"

Currently I can't download the MySQL or MariaDB ODBC connectors. When clicking on DOWNLOAD both fail with a 404 error code.

I have put in place this temporary OneDrive folder with (almost) everything you need to test... the only thing missing is the MariaDB server, but you are supposed to already have it.

Thanks. First test I did is with the MySQL ODBC Connector 64 bits.

/* This is the remote server 3306 Debug */

create or replace table fourn (

nofourn int(10) not null,

nom varchar(25) not null,

ville varchar(25) not null)

engine=CONNECT table_type=CSV file_name='C:/Data/FMT/fourn.csv' header=1;

INSERT INTO fourn VALUES (1,'Martin','Paris');

INSERT INTO fourn VALUES (2,'Dupond','Marseille');

INSERT INTO fourn VALUES (3,'Dupont','Bordeaux');

select * from fourn;

/* This is the local server 3308 bin distribution */

create table xfourn engine=connect table_type=ODBC tabname='fourn'

CONNECTION='DSN=MariaDB';

select * from xfourn;

insert into xfourn values(4,'Guignol','Lyon');

update xfourn set nom = 'Bonaparte' where nofourn = 1;

From one MariaDB server, I access via ODBC a CONNECT table fourn that was created on another MariaDB server.
Doing some commands and executing the select many times raised no memory problems.

I don't know whether using a 32 bits driver is possible from and to 64 bits servers. There maybe also problems coming from the "Binami WAMP Stack" (Don't know what this is and how to install and use it)

But you could also try the simple test I did. Just having two MariaDB servers on different ports and a Data Source connecting one server to the other using the MySQL Connector as driver.

Seems like my drawing and my explanations are not clear enough... I am switching to French, seems like you are from France.

Le but est de tester un serveur MariaDB dans lequel une table de l'engin CONNECT a été créée... une table de type ODBC. Dans mon entreprise, ce lien ODBC est le logiciel/base de données FileMaker et le logiciel comptable/base de données Acomba. Étant donné que ces 2 logiciels sont sous licence et pas très répandu, pour fin de tests, j'ai remplacé la base de données "terminales" par MySQL. En aucuns cas, j'essai de brancher réellement un serveur MariaDB et un serveur MySQL.... je voulais simplement fournir un cas de test pour trouver la cause de la RAM qui n'est pas relâchée.

Les requêtes SQL doivent ensuite êtres effectuées de l'externe à MariaDB sur la table CONNECT de type ODBC. C'est à ce moment que l'utilisation de la RAM monte en flèche.

Merci !

I agree about not clear enough whatever language is used. French is Ok for mail but please stick to English on JIRA. Here is what I understand from one of your last comments:

"Windows .NET program" === "MariaDB or MySQL ODBC driver" ===> "MariaDB server CONNECT engine" === ODBC ===> "Wathever DB server"

So there are not one but two use of ODBC but only one called by CONNECT. As I previously said, CONNECT uses suballocation in a memory pool but this pool is allocated using malloc or virtualAlloc on Windows. If a memory leak was done by CONNECT, it is that memory pool not being freed after having been allocated. By default, the size of this memory pool is 64M, which seems bigger that the 600K to 800K you previuosly mentioned. But even 800K is pretty small and would not exhaust the memory in only a tenth of executions.

So, let's assume CONNECT is the culprit. An easy way to check this is to change the size of this memory pool. This can be done by setting, even after MariaDB is started, the connect_work_size session variable. Making it bigger should cause the system crash faster and the opposite to crash only after more tries.

Note however that during a statement execution MariaDB is also doing memory usage for parsing the query and different other tasks. Tests can be done using the Visual Link Detector (I think valgrind works only on Linux) but for this, I need to be able to reproduce the "bug"; that I havn't been able to do so far.

I have tried to change the size of connect_work_size session variable... it does not change nor solve the memory consumption. The size of "memory loss" per each request is about the same.

But you said in a previous message that you saw something in the log file Call 2 - MySQL.txt that was showing something that may explain the memory loss. Doesn't that help you to investigate in a certain parts of the CONNECT code ?!?

I have tried on Windows server 2016, Windows 7, Windows 10.... I have tried 32 bit software under 64 bit Windows, I have tried 64 bit software under 64 bit Windows... problem is always there.

I have tried to query the CONNECT table through MySQL Workbench, through another ODBC, through MySQL client command line that comes with MariaDB... problem is always there.

I have tried many versions of MariaDB 10.3.xx that are using different version of CONNECT... problem is always there. I just tried latest MariaDB version 10.4.3... same problem.

If the problem is not in CONNECT, than it is somewhere else in MariaDB.

I am joining every details to make sure that we have the same setup.

create local CONNECT table MariaDB.txt create remote InnoDB table MySQL.txt local MariaDB my.ini remote MySQL my.ini

But you said in a previous message that you saw something in the log file Call 2 - MySQL.txt that was showing something that may explain the memory loss. Doesn't that help you to investigate in a certain parts of the CONNECT code ?!
No, it was the idea that it could have been the storage pool not freed, but this seems discarded.

Just another clue, the remote table is a innodb one. Did you try another engine?

Nope, I did not try another engine for the table in the remote MySQL... I had the same problem when the remote server was not MySQL, so I don't think that the problem is from there. But I will try it just to make sure.

I forgot to mention that I have also tried both MySQL ODBC drivers... the Unicode and the ANSI.

I am about to play with differents characters set to see if it comes from there.

I did test with a remote INNODB table but no memory problem whatsoever.

Ok, I did many tests.... It does not seems to be the remote database in cause... I have tried MySQL and MariaDB as remote database, both having an InnoDB table. The problem occurs only when I put the MySQL ODBC driver between the local MariaDB server and the remote MariaDB or MySQL server. When I use the MariaDB ODBC driver, there is no problem.

So, you are right that the probable cause seems to be the ODBC driver being used, but it's the "mysqld.exe" program from MariaDB that is consuming the memory and not releasing it.

You can use the MySQL ODBC driver that I have put there... I have used the Unicode version to setup my System DSN.

The problem occurs only when I put the MySQL ODBC driver between the local MariaDB server and the remote MariaDB or MySQL server. When I use the MariaDB ODBC driver, there is no problem.
What is strange is that doing the same thing with the MySQL ODBC driver I have no memory problem on my machine (Windoxs 10 64 bits)

Anyway, I always said that using ODBC to access a MariaDB or MySQL server is not a good idea. The normal thing to do is to use the FEDERATED engine or CONNECT with a MYSQL table type.

I also personnaly think that JDBC is better than ODBC.

Again Olivier, the point is not to really connect MariaDB and MySQL through ODBC... but to try to find why some ODBC drivers are consuming RAM. My database that I am trying to use only have an old ODBC 32 bit driver and no JDBC.

On my 2 differents machines, I have 3 different ODBC drivers causing RAM problems and 2 that don't.

Is it possible for someone else in the development team to do the test too ?!? Anyone that can test under different Windows version ?

Therefore, this discusion should be now transfered to the concerned drivers and I'll close this case. You can still contact me directly for informations.