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.

 

[Chriss Miller]

To be very clear, and I already said it within this conversation, the original question was about how connecting and what driver works. I understeand you finally want to use your database, but testing server, ODBC driver and connection you start with the empty server. What your databsae requires as specialties adds to the problem. Notice the connection to richline failed because of what the error message told: Access violation. If you just configure another directory that's not prepared by MariaDB with the service user of the database, so it has no access. Also, whatever you configured for the database at its origin server doesn't just come over by pointing the new server to the directory. Data migration is a task performed from the old to the new server and while some databses allow just attaching some file or directory, as VFP, others already require at least some detach and attach code like MSSQL with sp_detach_db and sp_attach_db stored procs and that also has some prerequisites, like the same (not just similar, exactly the same) security principals. And other dbswork by actually exporting and importing data. Your impatience about this was standing in your way, nothing else. I can't even pull you back and tell you to take it slower and from the start, if you don't even listen. And if you just confront me with things you don't mention at the start, I'm stumped. I think you think all you did additional doesn't matter and can remain unmentioend, but ask me whether I did something I didn't mention. Are you getting it? Are you getting anything? Are even aware about the unbalance here? I can't work with people like you.

When you used a certificate on your earlier server, that doesn't just become valid for MariaDB, you're starting from scratch and you should respect that, i.e. you first create new databases and migrate data into them, then apply things like secure connections and encryption, perhaps. But first , to see whether VFP works with MariaDB server and ODBC drivers, use them on the bare metal, first.

And I know, I know, you mentioned richline, you mentioned the configuration works just by switching to the Oracly MySQL driver, it's still not getting you a millimenter forward to find out whether MariaDB works by starting in the end scenario, you start with initial scenario, and you didn't but also didn't inform about this, not straight away. You're wasting both your and my time. And that's a bummer to me, still.

 

[dylim]

To be very clear, and I already said it within this conversation, the original question was about how connecting and what driver works. I understeand you finally want to use your database, but testing server, ODBC driver and connection you start with the empty server. What your databsae requires as specialties adds to the problem. Notice the connection to richline failed because of what the error message told: Access violation. If you just configure another directory that's not prepared by MariaDB with the service user of the database, so it has no access. Also, whatever you configured for the database at its origin server doesn't just come over by pointing the new server to the directory. Data migration is a task performed from the old to the new server and while some databses allow just attaching some file or directory, as VFP, others already require at least some detach and attach code like MSSQL with sp_detach_db and sp_attach_db stored procs and that also has some prerequisites, like the same (not just similar, exactly the same) security principals. And other dbswork by actually exporting and importing data. Your impatience about this was standing in your way, nothing else. I can't even pull you back and tell you to take it slower and from the start, if you don't even listen. And if you just confront me with things you don't mention at the start, I'm stumped. I think you think all you did additional doesn't matter and can remain unmentioend, but ask me whether I did something I didn't mention. Are you getting it? Are you getting anything? Are even aware about the unbalance here? I can't work with people like you.

When you used a certificate on your earlier server, that doesn't just become valid for MariaDB, you're starting from scratch and you should respect that, i.e. you first create new databases and migrate data into them, then apply things like secure connections and encryption, perhaps. But first , to see whether VFP works with MariaDB server and ODBC drivers, use them on the bare metal, first.

And I know, I know, you mentioned richline, you mentioned the configuration works just by switching to the Oracly MySQL driver, it's still not getting you a millimenter forward to find out whether MariaDB works by starting in the end scenario, you start with initial scenario, and you didn't but also didn't inform about this, not straight away. You're wasting both your and my time. And that's a bummer to me, still.

Hi Chriss,

I found the darn culprit! The real reason of not being able to connect was because of my Connection Manager class!

In my Connected() method, which returns .T. or .F., the code is as follows:

RETURN ( This.DataSource > 0 ) AND ( SQLEXEC( This.DataSource, "DO 1" ) > 0 )

It appears that MariaDB returns an error for a "DO 1" statement. Issuing DO 1 at the Workbench works though (weird).

I changed "DO 1" to "select 1", and now it is okay.

RETURN ( This.DataSource > 0 ) AND ( SQLEXEC( This.DataSource, "select 1" ) > 0 )

I don't understand why "DO 1" does not work with MariaDB.

Thanks

 

[Chriss Miller]

Okay, that explains and also confirms what I already told you: That the connection is made and the error "access violation" originates from the MariaDB Server after the connection. Not what I expected, but there you are.

It does not match what you told: That you tested in a separate PRG making the connection only. So you didn't. You should be clear and precise about what you communicate with a forum you want to get help from.

General advice on testing something completely new: Create a new project for that. And in the sense of tesating make component tests first, then integration tests. Otherwise you very easily fall into the pit you've been fallen into: You assume anything you already use for years works, and can't be the culprit of what you now add.

There is still another reason: Adding the database into the connection. Again, this is your final goal, but you're actually skipping component testing and go right away into integration. That's not a way to test and conquer anything new.

Besides all that, DO is documented to do the same in MariaDB as it does in MySQL. It's truie, nevertheless, that SQLEXEC(handle,"Do 1") results in -1 and AERROR shows the error message "access violation or syntax error". So MariaDB's DO statement must have some difference to MySQL's DO. If you do this to test the connection, I'd recommend doing something else, anyway. In MSSQL a statement I also do in a long interval timer to keep a connection ali
ve is querying the server time. Something that actually has a result would show problems like you also faced in the early testing with several MySQL ODBC drivers.

Anyway, it's done. Do it better, next time, with what you learned from this. It's not hard.

 

[dylim]

Okay, that explains and also confirms what I already told you: That the connection is made and the error "access violation" originates from the MariaDB Server after the connection. Not what I expected, but there you are.

It does not match what you told: That you tested in a separate PRG making the connection only. So you didn't. You should be clear and precise about what you communicate with a forum you want to get help from.

General advice on testing something completely new: Create a new project for that. And in the sense of tesating make component tests first, then integration tests. Otherwise you very easily fall into the pit you've been fallen into: You assume anything you already use for years works, and can't be the culprit of what you now add.

There is still another reason: Adding the database into the connection. Again, this is your final goal, but you're actually skipping component testing and go right away into integration. That's not a way to test and conquer anything new.

Besides all that, DO is documented to do the same in MariaDB as it does in MySQL. It's truie, nevertheless, that SQLEXEC(handle,"Do 1") results in -1 and AERROR shows the error message "access violation or syntax error". So MariaDB's DO statement must have some difference to MySQL's DO. If you do this to test the connection, I'd recommend doing something else, anyway. In MSSQL a statement I also do in a long interval timer to keep a connection ali
ve is querying the server time. Something that actually has a result would show problems like you also faced in the early testing with several MySQL ODBC drivers.

Anyway, it's done. Do it better, next time, with what you learned from this. It's not hard.

Chriss,

That is a bad habit of mine. Coz it has been working for the longest time, that's why I immediately "rule them out" since they never were a problem maker for me.

Anyways, thanks be to our Lord, and thank you Chriss.

Cheers!

 

[dylim]

Hi Guys,

Am so glad that after installation of the latest stable release of MariaDB server (11.4.5) and ODBC (3.2.5), albeit with some booboos along the way, it has so far been a generally smooth transition. My apps have run so much snappier. Am keeping my fingers (and toes as well) crossed, that the worse is behind me.

This is in stark contrast to when I went up to MySQL Server 8.x from 5.7.x. It has caused me so many sleepless nights and still unresolved issues and/or behaviors, so much so that I decided to go back to 5.7.x. Even its ODBC 8.x, I had to stick to 8.0.31; any version higher than this one has caused so many issues.

Thank you to all who participated and helped share their wisdom in this thread, most especially to Chriss, who didn't let up on his advises, observations and admonitions.