Connecting (or not) to MariaDB

[mccormw]

I have had a TS412 for nearly 8 years now and some of he applications I run on it use MySQL- things like my tax returns, investment portfolio, investment research I have written and they run well on the MySQL platform.

I have recently purchased a TS-253Be with 2 10TB Ironwolf Pro HDs and it is awesome apart from one thing it doesn't have MySQL pre-installed. So that left me with two options:
(i) Get my apps to work with MariaDB. Now this approach seemed to go really well. I used phpMyAdmin (on my 412) to export portfolio database and I then tried to import it into MariaDB and this went fairly smoothly apart from the fact it didn't like the User Defined Functions (UDFs) I had written into the portfolio DB - but I was happy with that, these could be easily rewritten - RESULT.
So now I downloaded and installed the Windows ODBC64 driver for Mariadb and run up the ODBC admin program. However, when I input the server details, Name (or IP), Port #, user & password and hit the 'test DSN' button I get the following message: [ma-3.0.8]Host '192.168.1.101' is not allowed to connect to this MariaDB server. ps 192.168.1.101 is the ip of my windows PC.
Now that was confusing but even more confusing was the fact that if I now entered the server name as TS-412, which as I said is running MySQL but NOT MariaDB, the testDSN button gives a positive result and in the dropdown below I can see the databases on this server.

So I have a MariaDB ODBC driver that will connect to MySQL but not MySQL.

(ii) the second option would be to install MySQL on my 253 but the fact that the MariaDB driver connects to MySQL makes me think that the drivers for MariaDB and MySQL are basically the same. So the problem I see with MariaDB may also occur with MySQL.

I have done a Google search but virtually all the hits I get are for this problem on MySQL even though I put MariaDB in the search.

ps Perhaps I should have mentioned, although it should be obvious from the above, is that I can connect to MariaDB with phpMyAdmin!

Any ideas?

[iam@nas]

For me MySQL and MariaDB are the same: https://mariadb.com/kb/en/library/confi ... nt-access/

You could try to re-initialize the database and then stop and start it as this may fix things.

[mccormw]

Already done that. I have also picked up on the fact that in my.cnf it has the line: skip-networking and I am going to comment this out because it stops the system using TCP/IP to connect - I think. Will report back.

[mccormw]

No that didn'twork.

[iam@nas]

Did you execute SQL statement "GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;" already?

[mccormw]

No. The fact that I could import database into MariaDB on my new server seems to suggest that privileges have been granted.

Do you mean: GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION

[mccormw]

I ran the following SQL statement in phpMyAdmin:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'admin' WITH GRANT OPTION
btw admin is still the password for the root user - so is my statement correct?

btw it does not make any difference.

[mccormw]

I am going to give up for tonight - it is my 70th birthday today and me and the mem sahib are going out for a Chinese meal.

Talk to you guys later.

[iam@nas]

'show grants;' should show the new entry ...
Interesting that it still does not work.


phpMyAdmin should be a good as a a local socket connection:

Code: Select all

/usr/local/mariadb/bin/mysql --protocol socket --user root --password
admin
SHOW DATABASES;
SELECT User, Host, Password FROM mysql.user;
SHOW GRANTS;

[mccormw]

Thanks iam@nas

I had trouble running the linux commands from putty, it would not let the local user connect to mariadb - ie not allowed as I was running putty on my PC.
So I went into phpMyAdmin and just run the SQL code that you suggested:

SHOW DATABASES
Database
information_schema
mysql
performance_schema
test


SELECT User, Host, Password FROM mysql.user

But I couldn't cut & paste anything sensible - gave lots of stuff - so I did a screen snip and I have attached it to this message (hopefully).

[iam@nas]

Code: Select all

I had trouble running the linux commands from putty, it would not let the local user connect to mariadb - ie not allowed as I was running putty on my PC.

"/usr/local/mariadb/bin/mysql --protocol socket --user root --password" uses the local socket. If you can't connect this way then something is wrong. That's the only connection option if the TCP port is disabled.

The connect works if you get the 'Host X is not allowed to connect.' message from MariaDB. As the "GRANT ..." looks fine I wonder what is wrong. Other user?

[mccormw]

I have turned on IP connection.

[mysqld]
#skip-networking=0
skip-bind-address

But I don't think that this would make a difference.

I have looked around on this forum and there lots of posts about not being able to connect to phpMyAdmin and yet I can do that no problem, as well as being able to connect to MySQL on myTS412. I have to say I am foxed, I think I will compare the two config files (MySQL/TS-412 with MariaDB/TS-253Be).

Thanks for you efforts Iam it is appreciated.

[mccormw]

I saved the my.cnf files from my 412 & 253 servers and compared them side-by-side and although there were some differences there was nothing that looked like a game changer.

One thing that did confuse me was the section where you were asked to uncomment if you were using innodb, which I do and certainly MySQL anyway is not really a relational database using any other file type - at least when I last looked art the available types. But this section was still commented and I am happily using Innodb, which when I built this database was the only file type that gave you things like: prime key and foreign key support. Most odd, but I guess I must of overridden this behaviour and enabled innodb as a property entry when building the database.

[mccormw]

It suddenly started working and I think I know why.

I had entered the following lines at the end of the my.cnf file:
[mysqld]
#skip-networking=0
skip-bind-address

But I think I must have forgotten to restart the database server and when I did it all works. I then took them out, restarted and it stopped working, put them back in, restarted and it's working again.

That is good because the 253 is so much quicker than the 412 and will have a greater snap, not that the apps I run are slow on the 412 server. The only time I have to sit there waiting for a time is when I upload stock prices but to be honest even that is not that bad on the 412. As they say if it aint broken then fix it.

Thanks again iam.

[mccormw]

OK so I have got it up and running and I imported my portfolio database into the MariaDB on my new server - all ticketyboo apart from user defined functions (UDFs), but this was not a worry to me.

So I run the program up and virtually nothing worked and it wasn't the undefined UDFs, that I was prepared for. It initially seemed to be as if something was missing in the connection object provided for me in Powerbuilder, which considering this was an ODBC connection was a worry. After a bit of messing around I found out that the problems were centred around dynamic cursors or I should say appeared to be! The connection object seemed to be 'fouling' when I was trying to access its inbuilt properties, it wasn't even throwing a catchable exception. But then I noticed a few lines of code down a dynamic cursor that was not causing problems - was I going mad! No, not yet anyway, the first dynamic cursor statement had in its FROM clause a VIEW and the dynamic cursor statement that was fine had tables only NO VIEWs. So a bit more information showed that the export from MySQL (via phpMyAdmin) was creating a very odd CREATE VIEW statement which MariaDB could make no sense of. The situation was made worse by the fact that the export statement not only CREATEd actual the VIEW statement with its SQL SELECT etc it earlier in the script issued a series of 'DEFINERS' for the views. This is basically a virtual view without any guts (ie NO SELECT), this meant that the views showed up in Powerbuilder but were invalid because only the column types had been defined and this for a little while just sent me down the wrong track.
So anyone else having troubles going from MySQL to MariaDB then have a look at your VIEWs, ie do they actually work.