Correct Connection String VFP to MariaDB

[dylim]

Hi Guys,

I am trying to use MariaDB as an alternative to MySQL. So, I got MariaDB Server 11.4 installed on a test PC, also MariaDB ODBC 3.2.5.

I simply cannot seem to make it work!

This is the string I used:

lnHandle=SQLSTRINGCONNECT("Driver={MariaDB ODBC 3.2 Driver};Server=localhost;Port=3306;Database=MyDB;Uid=root;Pwd=12345;")

I also tried the following variations:

a) Driver={MariaDB ODBC 3.2 Driver}
b) Driver=MariaDB ODBC 3.2 Driver
c) Driver={MariaDB ODBC 3.0 Driver}
d) Driver=MariaDB ODBC 3.0 Driver
e) Driver={MariaDB ODBC 3.2.5 Driver}
f) Driver=MariaDB ODBC 3.2.5 Driver
g) Driver={MariaDB ODBC Driver}
h) Driver=MariaDB ODBC Driver

What is crazy is that, I used MySQL ODBC 8 (driver=MySQL ODBC 8.0 ANSI Driver), and it works like a charm!

Should I just use MySQL ODBC instead of MariaDB ODBC? (this seems very counter-intuitive though)

Thanks in advance!

 

[Chriss Miller]

why does the MySQL ODBC function work, while the MariaDB ODBC does not?

for you. Maybe it's Win11.

 

[Chriss Miller]

One more thing to consider: MariaDB Server runs the MariaDB service that is mysqld.exe under the user MariaDB - more precisely NT SERVICE\MariaDB.

The data folder in the installation has MariaDB with full contol in Security. If your data folder is elsewhere or even on yet another networked computer, that would be something to look into.

You posted a change of your ini in that respect:

datadir=D:/MariaDBData

Since it works with the MySQL driver it's still not likely what differs, as that would affect any driver used.

To me it all sounds like you're using something completely different when using the MySQL Driver in the aspects of all other connection values or like another MySQL Server that also still runs on the same port. You can also step on your own feet doing something like that. Just remember the eror message of your connection speaks of an access violation.

 

[dylim]

One more thing to consider: MariaDB Server runs the MariaDB service that is mysqld.exe under the user MariaDB - more precisely NT SERVICE\MariaDB.

The data folder in the installation has MariaDB with full contol in Security. If your data folder is elsewhere or even on yet another networked computer, that would be something to look into.

You posted a change of your ini in that respect:

datadir=D:/MariaDBData

Since it works with the MySQL driver it's still not likely what differs, as that would affect any driver used.

To me it all sounds like you're using something completely different when using the MySQL Driver in the aspects of all other connection values or like another MySQL Server that also still runs on the same port. You can also step on your own feet doing something like that. Just remember the eror message of your connection speaks of an access violation.

Chriss,

Did you like tweak anything in MariaDB? More specifically, the file my.ini? Are you running on Windows 10 or 11?

Thanks.

 

[Chriss Miller]

No tweak, I'm running Win10. Also, I showed you screenshots of my setup and talked about it, didn't I? Can you please just read what I wrote?

And before you go Win10. I wonder why your error indirectly states the connection is already done. Try this and see whether it works when you don't try to connect to a database, only to the server:

lnHandle=SQLSTRINGCONNECT("Driver={MariaDB ODBC 3.2 Driver};Server=127.0.0.1;Port=...whatever port you configured in setup...;Uid=root;Pwd=...whatever password you configured at setup...;")

You start with an empty server, you can't a) connect to a non existing database like "MyDB" before creating it and b) you can't just point my.ini to a directory of database files and neither set the permissions of the folder nor have this database in the system meta data tables of the server.

 

[dylim]

No tweak, I'm running Win10. Also, I showed you screenshots of my setup and talked about it, didn't I? Can you please just read what I wrote?

And before you go Win10. I wonder why your error indirectly states the connection is already done. Try this and see whether it works when you don't try to connect to a database, only to the server:

lnHandle=SQLSTRINGCONNECT("Driver={MariaDB ODBC 3.2 Driver};Server=127.0.0.1;Port=...whatever port you configured in setup...;Uid=root;Pwd=...whatever password you configured at setup...;")

You start with an empty server, you can't a) connect to a non existing database like "MyDB" before creating it and b) you can't just point my.ini to a directory of database files and neither set the permissions of the folder nor have this database in the system meta data tables of the server.

Hi Chriss,

I tried connecting using an omitted server. Still no luck.

But, when I change {MariaDB ODBC 3.2 Driver} to {MySQL ODBC 8.0 Unicode Driver}, it connects.

I reinstalled the test PC with Windows 10 LTSC Evaluation, thinking it may be because of Win 11. Same undesired behaviour.

You think it is "safe" to use MySQL OBDC 8.0 Unicode Driver in production?

Also, when I remotely access the test PC, the Driver string MySQL OBDC 8.0 Unicode Driver works with or without curly braces.

If I access locally (right on the test PC itself), curly braces do not work. You have to remove them.

 

[Chriss Miller]

I tried connecting using an omitted server. Still no luck.

What do you mean with "omitted server". You can omit the database in the connection string, but clearly not the server. What are you talking about?

 

[Chriss Miller]

I reinstalled the test PC with Windows 10 LTSC Evaluation, thinking it may be because of Win 11. Same undesired behaviour.

Could you install with defaults and just set a root password on that Win10 machine, install the 11.4.5 Server, the 3.2.5 driver and keep the ini at defaults.

Then run this:

h=Sqlstringconnect("DRIVER={MariaDB ODBC 3.2 Driver};SERVER=127.0.0.1;PORT=3306;OPTION=67108992;UID=root;PWD=yourpassword")

Text To lcSQL NoShow
   Create Or Replace Database MyDB;
   Use MyDB;
 
   Create Or Replace Table Animals (
     Id MEDIUMINT NOT NULL AUTO_INCREMENT,
     Name Char(30) NOT NULL,
     Primary Key (Id)
   );

   Insert Into Animals (Name) VALUES
      ('dog'),('cat'),('penguin'),
      ('fox'),('whale'),('ostrich');
EndText


SQLExec(h,lcSQL)
SQLExec(h,"Select * From Animals","crsAnimals")
Browse Nowait
SQLTables(h,"TABLES","crsTables")
Browse Nowait

To state the obvious: Run the VFP code from a VFP on the same computer as the server and ODBC driver, adapt the password. Alternatively I think you can skip setting the password in the setup and just run without the PWD=... part of the connection string.

 

[dylim]

What do you mean with "omitted server". You can omit the database in the connection string, but clearly not the server. What are you talking about?

Am so sorry Chriss. What I meant was that -- I omitted the database. My brain cells must be fried.

And... GUESS WHAT?!

I added "skip-ssl=1" to the connection string like so:

lnConn = SQLSTRINGCONNECT( "DRIVER={MariaDB ODBC 3.2 Driver};SERVER=192.168.0.25;PORT=1696;UID=appuser;PWD=p@55w0rd;skip-ssl=1" )

It WORKED!!!

 

[Chriss Miller]

Wow, that took some time. Now see what databases the server knows about:

h=Sqlstringconnect(...)
SQLExec(h,"Show Databases;","crsDBs")
Browse Nowait

 

[dylim]

Wow, that took some time. Now see what databases the server knows about:

h=Sqlstringconnect(...)
SQLExec(h,"Show Databases;","crsDBs")
Browse Nowait

How come you didn't need skip-ssl setting?


The screenshot has same contents are the results in the remote laptop. I am now in the test computer running MariaDB.

 

[Chriss Miller]

How come you didn't need skip-ssl setting?

I don't know. And I think you actually don't need this, either. Because - weill, intuitively - skip-ssl means to not use a secure SSL connection, doesn't it? And, well, there's nothing to do to not use SSL. Contrary, if you want to use a secured connection, you'd need to configure a few things and establish a certificate, I think. Nothing of that exists straight out of the box, as it will be server specific, so there's not even a chance Oracle or the MariaDB Foundation could package that in an installer.

Edit: By the way: Googling MariaDB skip-ssl the first sentence in Google's result is "The MariaDB Connector/ODBC does not have a direct "skip-ssl" option". And secondary: It means what I assume - to turn off SSL, which is something that isn't on by default. Which means, actually you would in turn need to explain to me why an option the driver doesn't even support and that turns off something that isn't on makes a difference, now. I remember you already mentioned skip-ssl earlier and there it didn't help.

I don't really need to know, I can now think in several directions, but I spare myself the effort. To me it's very simple: I don't think you need anything else than what I suggested. The best thing would be to get a few of this forum to check out what works for them to find a pattern of what works, what doesn't work and what are the differencess in the systems. I just don't think at this point there's anyone but me and you reading the posts in this thread.

 

[Chriss Miller]

Not to forget. You don't have a richline database after just installing MariaDB, also not just after adding a directory to the my.ini, so you did something more to add that database, which you never talked about. Sorry, I still can't read minds. There are all kinds of exlpanations why things work or don'T work for you that are impossible to know without knowing what you do. Anyway, good luck with your future use of MariaDB.

For comparison, after running my script I get this list of databases:

mysql, information_schema, performance_schema, and sys are MariaDB system databases. From the top of my head the list of databases is in information_schema, could also be in mysql or sys, or on two or all of them, or different aspects of data about the databases. Anyway, the system data does not change by adding a directory in an ini file.