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!

 

[dylim]

I have a class which I call ConnectionManager. It has a property called ConnDataFileType (1- text file, 2 - dbf) and ConnDataFileName.

ConnDataFileName defaults to "sql.ini". The file extension is immaterial; I just rename it say "sql.txt" or "sqlconndata.txt". As long as it is a simple text file that contains lines in the following format:

keyword=value

Like so:
driver={MariaDB ODBC 3.2 Driver}
server=localhost
port=1696
option=67108992
uid=root
pwd=12345
database=richline

So, the method GetConnString() basically opens the specified file "sql.ini" or whatever is the value of ConnDataFileName, or some DBF file if ConnDataFileType is 2 (dbf)). And goes thru each line to create the connection string and separate each one with a semicolon.

So, something like:

lcString = oConnectMgr.GetConnString()
gnConnection = oConnectionMgr.SQLSTRINGCONNECT( lcString )

You get the drift. c",)

By the way, curly braces DO NOT WORK WITH MYSQL ODBC 8.0 for me. They work in the old versions 3.5.

Thanks!

 

[Chriss Miller]

By the way, curly braces DO NOT WORK WITH MYSQL ODBC 8.0 for me.

The work for me, though. Double checked and confiremed. The driver name has to just be exactly as stated in the Administrator. Notice wheil ANSI is in all caps, Unicode is not, to point out a difference. And without the exact driver name I get the error, as I already said. Look, if things work as you describe in your case, we have a fundamental difference in our computers, Windows versions, VFP versions, Windows configurations, whatever. But I actually doubt that.

Edit: One possibility, why your INI doesn't work when it sets the driver into curly brackets is, the oConnectMgr put them in brackets again Or you had a space after the = or whatever. Inspect the final result.

lcString = oConnectMgr.GetConnString()
_cliptext = lcString

How about you run that and then post the computed connection string here by pasting the clipboard?
Even set a breakpoint there. You wouldn't be the first here to claim something always worked some way and we found out it was actually not used, instead a workaround somewhere else in code.

 

[Chriss Miller]

The least thing you could then do is

lcString = oConnectMgr.GetConnString()
gnConnection = oConnectionMgr.SQLSTRINGCONNECT( lcString )
IF gnConnection<1
   AERROR(laError)
   SUSPEND
ENDIF

And if the code suspends look into the debugger watch window into lcString and into the laError array.

 

[dylim]

Hi Chriss,

Here is the connection string as pasted:

DRIVER=MariaDB ODBC 3.2 Driver;PORT=1696;SERVER=192.168.0.25;DATABASE=richline;UID=appuser;PWD=p@55w0rd

I also tried with curly braces like so:

DRIVER={MariaDB ODBC 3.2 Driver};PORT=1696;SERVER=192.168.0.25;DATABASE=richline;UID=appuser;PWD=p@55w0rd

Attached pic is the result of the AERROR().

If I replace it with MySQL ODBC 8.0 Unicode Driver, it works perfectly.

Regards

 

[Chriss Miller]

access violation would mean you've got the password wrong, or the user name. Has nothing to do with the driver not working. A Syntax error would not apply to a connection string, a syntax problem in the connection string would lead to aynthing from driver not found to server not found and connection not made. This seems to come from after the connection has been made.

Notice that any query error also is reported with the error message starting "Connectivity error". When, for example, I do

h=SQLSTRINGCONNECT("DRIVER={MariaDB ODBC 3.2 Driver};SERVER=127.0.0.1;PORT=3307;OPTION=67108992;UID=root;PWD=...redacted...;database=test")
SQLEXEC(h,"Select * FROM dfjkshdfkjk","result")

With a non existing table dfjkshdfkjk I get an error array containing:

Every error is an ODBC error, so within ODBC connectivit, it's not a connection error , though, it's simply a query error and in this case about the non existing table I intentionally gave to showcase this. So you might be chasing the wrong problem all along.

Sorry, that was a screenshot from the previous connection I made with MySQL ODBC, here's the one with MariaDB 3,2:

So, again, this is not a connection problem, this is simly a query error.

In comparison, here's what an actual connection error looks like. When I simply chnage 3.2 to 3.1, though I don't have a 3.1 driver I get this error:

Notice the origin of the error is [Microsoft] ODBC Driver Manager. Not from MariaDB. Because MariaDB isn't even yet connected.

In your screenshot the origin of the error is [ma-3.2.5][11.4.5-MariaDB], that is from MariaDB, you already have the connection and the problem is with some command following that. Get your debugging going to see where that actually comes from.

 

[Chriss Miller]

If I look back and interpolate all you said previously, I guess you will be able to connect and get correct results with the original MySQL Server you had and the 8.0.0.35 driver, IIRC.

Anyway, you wasted your time in investigating the connection string problem, you have actual query errors, as your message origin is [ma-3.2.5][11.4.5-MariaDB].

If you get everything going with MySQL ODBC 8.0 Unicode Driver your using something that only works with a MySQL driver. CCMD itself I thinki is using the MySQL command-line client. Not sure how you do this, but this is not using the server directly, this is using this tool via ODBC and that might be a specialty you only have with a MySQL driver. Your problem is not about the MariaDB 3.2 ODBC driver not connecting though, your error message is proving that.

 

[Chriss Miller]

One more idea:

SERVER=192.168.0.25

Since you're not using localhost and you're not using standard port 3306, a firewall could be your problem.

 

[dylim]

Chriss,

I have two PCs. The one with IP 192.168.0.25 is the one running MariaDB server. The one with IP 192.168.0.10 is where I am connecting from. And yes, I have already checked my firewall settings. It's all good. It's kinda automatic for both MySQL and MariaDB I believe; they both opened it during installation.

I am doing all this from just the Command window.

**********************************

So, here in PC with IP 192.168.0.10:

If I try to do SQLSTRINGCONNECT() using DRIVER={MariaDB ODBC 3.2 Driver};PORT=1696;SERVER=192.168.0.25;DATABASE=richline;UID=appuser;PWD=p@55w0rd, it returns -1.

If I try to do SQLSTRINGCONNECT() using DRIVER={MySQL ODBC 8.0 ANSI Driver};PORT=1696;SERVER=192.168.0.25;DATABASE=richline;UID=appuser;PWD=p@55w0rd, it returns 1. (by the way, I ran this with curly braces and without them, and both of them worked!)

I did not run or execute any other line except SQLSTRINGCONNECT() sir.

***********************************

I also tried to connect right there at the MariaDB server (192.168.0.25).

If I try to do SQLSTRINGCONNECT() using DRIVER={MariaDB ODBC 3.2 Driver};PORT=1696;SERVER=localhost;DATABASE=richline;UID=appuser;PWD=p@55w0rd, it returns -1.

If I try to do SQLSTRINGCONNECT() using DRIVER={MySQL ODBC 8.0 ANSI Driver};PORT=1696;SERVER=localhost;DATABASE=richline;UID=appuser;PWD=p@55w0rd, it returns 1.

I did not run or execute any other line except SQLSTRINGCONNECT() as well sir.

The question is, why is it successful using MySQL ODBC 8.0 ANSI Driver and not on MariaDB ODBC 3.2 Driver?

I have to say though, when I was able to connect via MySQL ODBC, it seems to be way snappier!

Thanks

 

[Chriss Miller]

I have to say though, when I was able to connect via MySQL ODBC, it seems to be way snappier!

A successful connection is made fast, a connection fails only after a number of tries and at a timeout, that always takes longer.

I don't know what happens, your AERROR tells the error origin is from within MariaDB, so some part of the connection has to work, otherwise the origin wouldn't be MariaDB 11.4.5.

Anyway, the major point now could be within the database server not allowing all connections. By default localhost connections are the only allowed, what speaks against that and the 1696 port firewall problem is that you can successfully establish a connection with the MySQL driver. Is the server a new installation for all these tests? Did you configure it in any way?

During MariaDB setup, did you configure it for latin1?

 

[dylim]

A successful connection is made fast, a connection fails only after a number of tries and at a timeout, that always takes longer.

I don't know what happens, your AERROR tells the error origin is from within MariaDB, so some part of the connection has to work, otherwise the origin wouldn't be MariaDB 11.4.5.

Anyway, the major point now could be within the database server not allowing all connections. By default localhost connections are the only allowed, what speaks against that and the 1696 port firewall problem is that you can successfully establish a connection with the MySQL driver. Is the server a new installation for all these tests? Did you configure it in any way?

During MariaDB setup, did you configure it for latin1?

Here are the contents of my.ini (MariaDB):

[mysqld]
datadir=D:/MariaDBData
port=1696
skip-ssl=1
innodb_buffer_pool_size=1010M
[client]
port=1696
plugin-dir=C:\Program Files\MariaDB 11.4/lib/plugin

These are the modifications I made:

1) change port to 1696
2) added skip-ssl=1
3) changed datadir to D:/MariaDBData

Thanks

 

[Chriss Miller]

I just deinstalled MariaDB Server to go through the setup once more.

At this point there's an option to allow root user to access the db from remote machine, which is the case in your setup. You establish an appuser, though, so might not affect you. You have to do some configuration work in MariaDB to have that user and password and be able to connect, you must have gone through it, otherwise the MySQL driver also doesn't work.

Since you want to make a principle test, I'd first go for local installation and root and then go forward from there, though. Your testing multiple things at the same time and have no good separation of problems you face, if you want to go fully in one go.

Next step I have this:

Your case will have been 1696, of course. That's fine.

And that's all to specify in the server setup.

 

[dylim]

I did exactly as you have shown in the pictures. Only thing different is I change port 3306 to 1696. Of course, the password you supplied is different from what I put in.

I used both root and appuser in my SQLSTRINGCONNECT() right in the local machine (192.168.0.25) where MariaDB is running. Still no luck.

 

[Chriss Miller]

Here are the contents of my.ini (MariaDB):

If you changed the port in the INI after setup, I doubt this will establish any rules in the Windows Firewall. Did you use the MySQL driver before, with a MySQL Server backend and for got you configured exceptions for it in the firewall? I think it's a problem in that direction and may be solved using the setup once more with the options highlighted.

 

[Chriss Miller]

I used both root and appuser in my SQLSTRINGCONNECT() right in the local machine (192.168.0.25) where MariaDB is running. Still no luck.

Did you change server to 127.0.0.1 when trying that from there?

Even if, you're still using a network connection through a port and firewall rule are applied. So try changing the port back to 3306 in the INI and restart MariaDB and try with a connection string using server=127.0.0.1;port=3306;UID=root plus obviously the driver, password, etc.

And if that does not work, what's the MSI file you installed?
I used mariadb-11.4.5-winx64.msi for the MariaDB server and - as already said - mariadb-connector-odbc-3.2.5-win32.msi for the driver.

my.ini as default:
[mysqld]
datadir=C:/Program Files/MariaDB 11.4/data
port=3306
innodb_buffer_pool_size=502M
[client]
port=3306
plugin-dir=C:\Program Files\MariaDB 11.4/lib/plugin

In my case, as I specified 3307 in setup that's the only lines changed. Go for that and then, when you get this going, change one thing after another and see where it breaks. Otherwise you can't analyze what's the problem, could even be the skip-ssl.

 

[dylim]

If you changed the port in the INI after setup, I doubt this will establish any rules in the Windows Firewall. Did you use the MySQL driver before, with a MySQL Server backend and for got you configured exceptions for it in the firewall? I think it's a problem in that direction and may be solved using the setup once more with the options highlighted.

When I installed MySQL server, I already changed the port from 3306 to 1686 during the installation process. Never had any issues with Firewall. I did the same with MariaDB Server installation process.

 

[dylim]

Did you change server to 127.0.0.1 when trying that from there?

Even if, you're still using a network connection through a port and firewall rule are applied. So try changing the port back to 3306 in the INI and restart MariaDB and try with a connection string using server=127.0.0.1;port=3306;UID=root plus obviously the driver, password, etc.

driver={MariaDB ODBC 3.2 Driver}
server=127.0.0.1
port=1696
uid=root
pwd=p@55w0rd
database=richline

Same unwanted result! Drats!

 

[Chriss Miller]

I already changed the port from 3306 to 1686 during the installation process. Never had any issues with Firewall.

Well, when you do that via the installation, it's totally plausible that does something. But just chaing a text file, doesn't make Windows change any firewall filters. MS knows the standard ports for MSSQL and MySQL and some more and has them open for local access, but also not necessarily for networked access. If you have done something in the past, that's what you're living from and that must be sepcific to the MySQL driver. I remember that you can get dialogs popping up in Windows whether you want to allow certain applications to go through a firewall, including things like Apache Web Sever or PHP. But that's then done specific for one application or driver only.

For now I will stop, you have enough advice to go through, I don't think I can add to that, except telling doubts you don't want to hear, like: If you now switched to testing on the computer you have installed MariaDB, it seems quite spontaneous to me, though I welcome the effort to make your and my situation comparable and find clues. Have you installed the ODBC Driver(s) on the server? Do you get the exact same errors? As already said, I recommend to start with the defaults. Or - as that also worked fro me - specify another port in the setup, which I assume allows the setup which runs elevated to not only make the automatic changes to the ini but also the firewall itself. Noteice no application opens "its" ports, ports are maintained by the Windows firewall and not by any ini or other file, that just configures which ports a server listenes to, but not what comes through to that port. So if the setup doesn't know by using the option Windows doesn't know and MariaDB listenning on a port working with one driver but not the other points out ports have been opend previously for one but not the other driver, as far as I can tell.

 

[dylim]

Well, when you do that via the installation, it's totally plausible that does something. But just chaing a text file, doesn't make Windows change any firewall filters. MS knows the standard ports for MSSQL and MySQL and some more and has them open for local access, but also not necessarily for networked access. If you have done something in the past, that's what you're living from and that must be sepcific to the MySQL driver. I remember that you can get dialogs popping up in Windows whether you want to allow certain applications to go through a firewall, including things like Apache Web Sever or PHP. But that's then done specific for one application or driver only.

And to think that the PC (with 192.168.0.25), I just did a clean install of Windows 11 LTSC Evaluation. Then install MariaDB Server 11.4.5. Then the ODBC.

It is so frustrating!

 

[Chriss Miller]

One more simple thing I tried for your information: I can change port in the ini and it works - of course after restarting the MariaDB Service. I just remember FTP uses random ports in active or passive mode for the data transfer after establishing the connection on some standard port and you don't have to sepcifically open all these random ports, i.e. there's a range of ports usable for anything, but it was depending on incoming vs outgoing traffic. So the firewall probelm is less likely. Still could be anything and making one change from a working default towards the goal is the only thing I'm sure of will reveal what step is causing the problem.

I understand with restarting even with a fresh Windows installation you're on the way to get to the default working state. Good luck with that, because if that fails it will be impossible to get anywhere. I bet there's a router or switch between your two PCs. Won't play a role with the localhost test, but you might swap that out, too, obvioulsy.

 

[dylim]

One more simple thing I tried for your information: I can change port in the ini and it works - of course after restarting the MariaDB Service. I just remember FTP uses random ports in active or passive mode for the data transfer after establishing the connection on some standard port and you don't have to sepcifically open all these random ports, i.e. there's a range of ports usable for anything, but it was depending on incoming vs outgoing traffic. So the firewall probelm is less likely. Still could be anything and making one change from a working default towards the goal is the only thing I'm sure of will reveal what step is causing the problem.

I understand with restarting even with a fresh Windows installation you're on the way to get to the default working state. Good luck with that, because if that fails it will be impossible to get anywhere. I bet there's a router or switch between your two PCs. Won't play a role with the localhost test, but you might swap that out, too, obvioulsy.

For now, the very obvious thing is, all thing being equal... same server, same settings.. why does the MySQL ODBC function work, while the MariaDB ODBC does not?