User avatar
DougieLawson
Posts: 38854
Joined: Sun Jun 16, 2013 11:19 pm
Location: A small cave in deepest darkest Basingstoke, UK
Contact: Website Twitter

Re: REAL MySQL instead of MariaDB

Thu Jul 05, 2018 6:29 am

Have you changed the bind-address to 0.0.0.0 in your my.cnf file. The default is bind-address 127.0.0.1 which prevents any remote connections from being made.
Note: Any requirement to use a crystal ball or mind reading will result in me ignoring your question.

Criticising any questions is banned on this forum.

Any DMs sent on Twitter will be answered next month.
All non-medical doctors are on my foes list.

pcmm
Posts: 24
Joined: Tue Feb 06, 2018 3:18 pm

Re: REAL MySQL instead of MariaDB

Wed Jul 11, 2018 2:27 pm

DougieLawson wrote:
Thu Jul 05, 2018 6:29 am
Have you changed the bind-address to 0.0.0.0 in your my.cnf file. The default is bind-address 127.0.0.1 which prevents any remote connections from being made.
I'm attempting to use a GUI client locally on the PI, So far I've tried
MySQL workbench - can't connect
phpmyadmin - can't connect.
dbforge - can't connect

so far I can only connect to mariadb with the builtin client. my php webpages connect using dbo and cannot connect to my db's when run on Mariadb.

Do I still need to change the bind address if I'm connecting locally from my webserver? Why does the cli client work and no others?

User avatar
DougieLawson
Posts: 38854
Joined: Sun Jun 16, 2013 11:19 pm
Location: A small cave in deepest darkest Basingstoke, UK
Contact: Website Twitter

Re: REAL MySQL instead of MariaDB

Wed Jul 11, 2018 4:02 pm

php7.0 only supports MySQLi (the old connector was dropped).

sudo apt install php-mysql php7.0-mysql

There's a good example of a PHP mysqli program at: https://github.com/RaspberryPiFoundatio ... tation-www
Note: Any requirement to use a crystal ball or mind reading will result in me ignoring your question.

Criticising any questions is banned on this forum.

Any DMs sent on Twitter will be answered next month.
All non-medical doctors are on my foes list.

User avatar
rpdom
Posts: 17011
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: REAL MySQL instead of MariaDB

Wed Jul 11, 2018 6:44 pm

DougieLawson wrote:
Wed Jul 11, 2018 4:02 pm
php7.0 only supports MySQLi (the old connector was dropped).
Or the PDO extension.

User avatar
rpdom
Posts: 17011
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: REAL MySQL instead of MariaDB

Wed Jul 11, 2018 6:45 pm

phpmyadmin should still work, pretty much out of the box with a standard installation. I've got one running.

fbe
Posts: 639
Joined: Thu Aug 17, 2017 9:08 pm

Re: REAL MySQL instead of MariaDB

Wed Jul 11, 2018 9:11 pm

pcmm wrote:
Wed Jul 11, 2018 2:27 pm
so far I can only connect to mariadb with the builtin client. my php webpages connect using dbo and cannot connect to my db's when run on Mariadb.

Do I still need to change the bind address if I'm connecting locally from my webserver? Why does the cli client work and no others?
Are you trying to connect as root database user from your webserver? This won't work out of the box.

pcmm
Posts: 24
Joined: Tue Feb 06, 2018 3:18 pm

Re: REAL MySQL instead of MariaDB

Tue Jul 17, 2018 12:17 pm

DougieLawson wrote:
Wed Jul 11, 2018 4:02 pm
php7.0 only supports MySQLi (the old connector was dropped).

sudo apt install php-mysql php7.0-mysql

There's a good example of a PHP mysqli program at: https://github.com/RaspberryPiFoundatio ... tation-www
Well that sucks! That makes no sense! Stretch installs php7 but php7 only support mysql, which you can't install on stretch!


I tried installing php5 on stretch and it won't I always end up with php7 so I guess all of this won't work until someone (def smarter than me) works out the bugs! grrr 1 step forward and 3 steps back.

DirkS
Posts: 10346
Joined: Tue Jun 19, 2012 9:46 pm
Location: Essex, UK

Re: REAL MySQL instead of MariaDB

Tue Jul 17, 2018 12:50 pm

pcmm wrote:
Tue Jul 17, 2018 12:17 pm
DougieLawson wrote:
Wed Jul 11, 2018 4:02 pm
php7.0 only supports MySQLi (the old connector was dropped).

sudo apt install php-mysql php7.0-mysql

There's a good example of a PHP mysqli program at: https://github.com/RaspberryPiFoundatio ... tation-www
Well that sucks! That makes no sense! Stretch installs php7 but php7 only support mysql, which you can't install on stretch!
Please put on your reading glasses and look again.
It's about MySQLi (with an 'i') at the end: http://php.net/manual/en/book.mysqli.php
https://en.wikipedia.org/wiki/MySQLi

Francois117
Posts: 11
Joined: Thu Apr 07, 2016 11:26 pm

Re: REAL MySQL instead of MariaDB

Wed Oct 10, 2018 4:21 pm

We use the Mysql command "CHECKSUM TABLE ZZZ EXTENDED;" to compare tables on our back end server database with raspberries in the field as part of a differential table sync. Our table currently has about 100k rows and this is quite efficient. We update about 1 - 10 rows per minute and run the checksum afterwards to verify that we are in sync.

Our back end runs on AWS RDS, mysql 5.6.39.
All our devices in the field run Raspbian Jesse, on mysql 5.5.x and all their checksums match the checksums from the server when the tables are in sync.

HOWEVER

Rasperry 3B+ forced us to upgrade to Raspbian strech (9.4), and this runs MariaDB 10.1.23. All other mysql command seems to work fine, except the "CHECKSUM TABLE ZZZ EXTENDED;" commands. The checksums do not match the checksums from the server.

I have built a completely different way of doing the checksums and it works on the server and the stretch MariaDB, but this process is about 16X slower than the Checksum Table Extended (305ms):
Read the full table in a loop, 100 rows at a time; convert each block of rows to json, and MD5 each json string, add this md5 to an array of MD5's and when done, do an MD5 on the array of MD5's (5 seconds).

According to https://mariadb.com/kb/en/library/maria ... atibility/ mariadb should calculate the same checksums as any mysql older than 5.1. Not true. Drop-in replacement - NOT - read the doc and you will be shocked.

So I really need a way to install Mysql 5.5 or 5.6 on stretch!

lbdroidman
Posts: 13
Joined: Wed Oct 17, 2018 7:46 pm

Re: REAL MySQL instead of MariaDB

Thu Oct 18, 2018 4:09 pm

@Francois117: MySQL 5.6 is ancient. Goes all the way back to 2013. That's not in and of itself a bad thing, but keep in mind that things change over time. Fact is that MyOracle 8.0 is *also* not a drop in replacement for MySQL 5.6, and you would end up running into similar or worse problems going forward in that route.

Now the reality is that you are eventually going to have to move on from the old software and bring your whole system up to date, so my recommendation would be that you get that step over with now, rather than ultimately wasting time by trying to hack the old software into running for you.

Note that AWS *does* support MariaDB: https://aws.amazon.com/rds/mariadb/ -- so this update really should be quite painless for you.

Also note that your slowdown issue on your custom checksum implementation is very likely a result of your using MD5SUM. MD5SUM is a cryptographic hash, and has very high CPU overhead compared to a simple checksum. Try using CRC32 instead (which is the same calculation used by CHECKSUM TABLE), you will find that it runs a heck of a lot faster.

ppowers
Posts: 4
Joined: Tue Jan 15, 2019 7:28 pm

Re: REAL MySQL instead of MariaDB

Tue Jan 15, 2019 7:40 pm

Absolutely not a replacement. I don't want to trash Maria but none of my code works with maria. I had to install an older version of Raspbian to get things to work. It would be nice to have a choice. A frustrating factor is if I request apt-get install MySQL-server, I should get MySQL and not maria.

User avatar
rpdom
Posts: 17011
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: REAL MySQL instead of MariaDB

Tue Jan 15, 2019 8:52 pm

ppowers wrote:
Tue Jan 15, 2019 7:40 pm
Absolutely not a replacement. I don't want to trash Maria but none of my code works with maria. I had to install an older version of Raspbian to get things to work. It would be nice to have a choice. A frustrating factor is if I request apt-get install MySQL-server, I should get MySQL and not maria.
Funny. Most of my code worked without any changes. The only difference was the switch to PHP7, which uses a different library. That applies to MySQL access as much as it does Mariadb.

pfletch101
Posts: 623
Joined: Sat Feb 24, 2018 4:09 am
Location: Buffalo, NY, USA

Re: REAL MySQL instead of MariaDB

Tue Jan 15, 2019 10:23 pm

ppowers wrote:
Tue Jan 15, 2019 7:40 pm
Absolutely not a replacement. I don't want to trash Maria but none of my code works with maria. I had to install an older version of Raspbian to get things to work. It would be nice to have a choice. A frustrating factor is if I request apt-get install MySQL-server, I should get MySQL and not maria.
My experience is the same as rpdom's. Can you give a couple of examples of code that works for you with MySQL but not with MariaDB?

ppowers
Posts: 4
Joined: Tue Jan 15, 2019 7:28 pm

Re: REAL MySQL instead of MariaDB

Wed Jan 16, 2019 1:48 pm

pfletch101 wrote:
Tue Jan 15, 2019 10:23 pm
ppowers wrote:
Tue Jan 15, 2019 7:40 pm
Absolutely not a replacement. I don't want to trash Maria but none of my code works with maria. I had to install an older version of Raspbian to get things to work. It would be nice to have a choice. A frustrating factor is if I request apt-get install MySQL-server, I should get MySQL and not maria.
My experience is the same as rpdom's. Can you give a couple of examples of code that works for you with MySQL but not with MariaDB?
What I learned yesterday after reading through a lot of comments is that in my old code I was connecting as root but Maria wont allow that from my php server code, so I created a new user and changed my code to connect with the new user and everything worked again. What is really frustrating is everyone uses the term "drop in replacement" but in my opinion if you need to change anything, its not drop in, it may be comparable but not drop in. The requirement to make a new user was not obvious nor stated clearly anywhere. Here is a sample of my code that now works with the new user instead of root.

function get_value($table,$get_key,$where_key,$is,$server,$db){

$msql = mysqli_connect("localhost", "powers", "mypassword", $db);

$q="Select " . $get_key . " from " . $table . " where " . $where_key . " = " . "BINARY '$is'";

$result=$msql->query($q);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$answer=$row[$get_key];
}
}
return $answer;
}

pfletch101
Posts: 623
Joined: Sat Feb 24, 2018 4:09 am
Location: Buffalo, NY, USA

Re: REAL MySQL instead of MariaDB

Wed Jan 16, 2019 2:06 pm

ppowers wrote:
Wed Jan 16, 2019 1:48 pm
pfletch101 wrote:
Tue Jan 15, 2019 10:23 pm
ppowers wrote:
Tue Jan 15, 2019 7:40 pm
Absolutely not a replacement. I don't want to trash Maria but none of my code works with maria. I had to install an older version of Raspbian to get things to work. It would be nice to have a choice. A frustrating factor is if I request apt-get install MySQL-server, I should get MySQL and not maria.
My experience is the same as rpdom's. Can you give a couple of examples of code that works for you with MySQL but not with MariaDB?
What I learned yesterday after reading through a lot of comments is that in my old code I was connecting as root but Maria wont allow that from my php server code, so I created a new user and changed my code to connect with the new user and everything worked again. What is really frustrating is everyone uses the term "drop in replacement" but in my opinion if you need to change anything, its not drop in, it may be comparable but not drop in. The requirement to make a new user was not obvious nor stated clearly anywhere. Here is a sample of my code that now works with the new user instead of root.

function get_value($table,$get_key,$where_key,$is,$server,$db){

$msql = mysqli_connect("localhost", "powers", "mypassword", $db);

$q="Select " . $get_key . " from " . $table . " where " . $where_key . " = " . "BINARY '$is'";

$result=$msql->query($q);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$answer=$row[$get_key];
}
}
return $answer;
}
I'm glad you got things working! It's been a while since I installed Maria, but my recollection is that the instructions I followed went to some lengths to emphasize the need to create a new user with full privileges and not to try to use the root user.

mfa298
Posts: 1387
Joined: Tue Apr 22, 2014 11:18 am

Re: REAL MySQL instead of MariaDB

Wed Jan 16, 2019 2:22 pm

ppowers wrote:
Wed Jan 16, 2019 1:48 pm
What I learned yesterday after reading through a lot of comments is that in my old code I was connecting as root but Maria wont allow that from my php server code, so I created a new user and changed my code to connect with the new user and everything worked again.
Realistically your webapp shouldn't have been connecting as root to the DB before so the fault might lie with whatever tutorial suggested doing that.

The standard approach for security is to give the fewest permissions needed (so create a DB user per app and only give them permissions to their own DB). If you give root privileges to your web app then that has full control over the DB server (and potentially the underlying OS) which can be a security risk. If this is a private web app that's never available over the internet then the risk might be acceptable, but it's usually advisable to work to best practices regardless of risk (you never know when you might decide to open it up - at which point you'll forget about the risks that it poses).

User avatar
rpdom
Posts: 17011
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: I want MySQL instead of MariaDB

Wed Jan 16, 2019 2:35 pm

I suspect the removal of root access to the database was a security fix by the Debian maintainer, not specifically a difference between MySQL and Mariadb. I have used (non-debian) Mariadb systems where almost all database access was performed by the root user (much to my horror!). On my old personal MySQL systems I disabled root access apart from directly from the Linux root user and created users for each database with appropriate access levels.

ppowers
Posts: 4
Joined: Tue Jan 15, 2019 7:28 pm

Re: REAL MySQL instead of MariaDB

Wed Jan 16, 2019 8:04 pm

mfa298 wrote:
Wed Jan 16, 2019 2:22 pm
ppowers wrote:
Wed Jan 16, 2019 1:48 pm
What I learned yesterday after reading through a lot of comments is that in my old code I was connecting as root but Maria wont allow that from my php server code, so I created a new user and changed my code to connect with the new user and everything worked again.
Realistically your webapp shouldn't have been connecting as root to the DB before so the fault might lie with whatever tutorial suggested doing that.

The standard approach for security is to give the fewest permissions needed (so create a DB user per app and only give them permissions to their own DB). If you give root privileges to your web app then that has full control over the DB server (and potentially the underlying OS) which can be a security risk. If this is a private web app that's never available over the internet then the risk might be acceptable, but it's usually advisable to work to best practices regardless of risk (you never know when you might decide to open it up - at which point you'll forget about the risks that it poses).
My point is its not drop in if there are differences. I would agree there could be a security issue using root to login although I've never encountered a problem. Using root or another user is another discussion. This is the difference I found that makes MariaDB not a drop in for MySQL. There may be others. There are good reasons to stick with what is working and MariaDB may even be the better database but if it doesn't work with someone's code then it doesn't matter unless it can be fixed. Often recourses needed to fix are far more extensive than reverting back to a known working solution. Im not a fan either of getting a package I didn't ask for. Its like going into a place asking for a Coke and getting Pepsi. I don't mind being asked if Pepsi is ok instead but I should have the choice to refuse it and find a way to get my Coke if its that important to me.

mfa298
Posts: 1387
Joined: Tue Apr 22, 2014 11:18 am

Re: REAL MySQL instead of MariaDB

Wed Jan 16, 2019 8:35 pm

ppowers wrote:
Wed Jan 16, 2019 8:04 pm
My point is its not drop in if there are differences. I would agree there could be a security issue using root to login although I've never encountered a problem. Using root or another user is another discussion. This is the difference I found that makes MariaDB not a drop in for MySQL.
You may well find the newer versions of MySQL have changed how they do authentication for the root user as well so what you're really comparing is an old version of MySQL against newer versions of MySQL/MariaDB/Percona.

I've certainly seen the same change in newer versions of Percona's version of MySQL (which seems to track Oracle MySQL fairly closely).

ppowers
Posts: 4
Joined: Tue Jan 15, 2019 7:28 pm

Re: REAL MySQL instead of MariaDB

Wed Jan 16, 2019 9:01 pm

mfa298 wrote:
Wed Jan 16, 2019 8:35 pm
ppowers wrote:
Wed Jan 16, 2019 8:04 pm
My point is its not drop in if there are differences. I would agree there could be a security issue using root to login although I've never encountered a problem. Using root or another user is another discussion. This is the difference I found that makes MariaDB not a drop in for MySQL.
You may well find the newer versions of MySQL have changed how they do authentication for the root user as well so what you're really comparing is an old version of MySQL against newer versions of MySQL/MariaDB/Percona.

I've certainly seen the same change in newer versions of Percona's version of MySQL (which seems to track Oracle MySQL fairly closely).
I think you are missing the point. I have never had an issue with MySQL with root access even as soon as last week, The point is, the question was how to get MySQL instead of MariaDB. Why does everyone insist on knowing why, or that they are the same when they arnt and just give the answer to the actual question.

fruitoftheloom
Posts: 23106
Joined: Tue Mar 25, 2014 12:40 pm
Location: Delightful Dorset

Re: REAL MySQL instead of MariaDB

Wed Jan 16, 2019 9:31 pm

ppowers wrote:
Wed Jan 16, 2019 9:01 pm
mfa298 wrote:
Wed Jan 16, 2019 8:35 pm
ppowers wrote:
Wed Jan 16, 2019 8:04 pm
My point is its not drop in if there are differences. I would agree there could be a security issue using root to login although I've never encountered a problem. Using root or another user is another discussion. This is the difference I found that makes MariaDB not a drop in for MySQL.
You may well find the newer versions of MySQL have changed how they do authentication for the root user as well so what you're really comparing is an old version of MySQL against newer versions of MySQL/MariaDB/Percona.

I've certainly seen the same change in newer versions of Percona's version of MySQL (which seems to track Oracle MySQL fairly closely).
I think you are missing the point. I have never had an issue with MySQL with root access even as soon as last week, The point is, the question was how to get MySQL instead of MariaDB. Why does everyone insist on knowing why, or that they are the same when they arnt and just give the answer to the actual question.

YMMV

https://dev.mysql.com/downloads/repo/apt/
Rather than negativity think outside the box !
RPi 4B 4GB (SSD Boot)..
Asus ChromeBox 3 Celeron is my other computer...

mfa298
Posts: 1387
Joined: Tue Apr 22, 2014 11:18 am

Re: REAL MySQL instead of MariaDB

Wed Jan 16, 2019 9:51 pm

ppowers wrote:
Wed Jan 16, 2019 9:01 pm
I think you are missing the point. I have never had an issue with MySQL with root access even as soon as last week,
You're also missing the last point I made.

Yes an old version of MySQL on an older OS will still ask for a root password, that doesn't mean newer versions will still do the same thing.

Trying the latest version of Oracle MySQL (8.0.13) on an Ubuntu (x86) system (I'd just found the same stuff for installing it as FoTL - I don't think they support ARM though)

Code: Select all

ubuntu@ubuntu:~$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
ubuntu@ubuntu:~$ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.13 MySQL Community Server - GPL
...
mysql> 
That looks very much like the sort of authentication you were complaining about in MariaDB. I'm sure you could configure the latest MySQL to use a root password (as old versions have done), you could also do the same in MariaDB.

To be fair, MySQL 8 is pretty new so might not be in many distributions yet, I don't know what 5.7 does and different distributions may have different defaults. However the issue you're complaining about in MariaDB could well occur for you at some point with MySQL as well.

abkrim
Posts: 12
Joined: Fri Apr 03, 2015 7:15 pm

Re: REAL MySQL instead of MariaDB

Mon Feb 04, 2019 3:04 pm

kamilosxd678 wrote:
Mon Dec 11, 2017 7:07 pm
I have a lot of issues with MariaDB, I don't like it and I want to install MySQL. My question was - Does anyone successfully installed it on RPi? That was all, I don't want to be converted - I want a simple answer - I need a QEMU expert or cross-compilation expert - not MariaDB evangelists
What a great reason. One asks about something, and there are evangelists who respond to things that have nothing to do with the question.

MariaDB has her loves, and as MySQL has many others.

The question was and is, Mysql instead of MariaDB in raspbian

User avatar
scruss
Posts: 3153
Joined: Sat Jun 09, 2012 12:25 pm
Location: Toronto, ON
Contact: Website

Re: REAL MySQL instead of MariaDB

Mon Feb 04, 2019 6:45 pm

abkrim wrote:
Mon Feb 04, 2019 3:04 pm
The question was and is, Mysql instead of MariaDB in raspbian
This is likely the wrong forum to ask that question. Because MySQL can no longer be included in Raspbian, it's not a topic that attracts useful discussion here.
‘Remember the Golden Rule of Selling: “Do not resort to violence.”’ — McGlashan.
Pronouns: he/him

Andyroo

Re: REAL MySQL instead of MariaDB

Mon Feb 04, 2019 7:05 pm

scruss wrote:
Mon Feb 04, 2019 6:45 pm
abkrim wrote:
Mon Feb 04, 2019 3:04 pm
The question was and is, Mysql instead of MariaDB in raspbian
This is likely the wrong forum to ask that question. Because MySQL can no longer be included in Raspbian, it's not a topic that attracts useful discussion here.
Interesting point but we help others to load programs that are not included in Raspbian. :D

As for the original question - I’ve not tried this on a Pi but the following may work if you have plenty of time for the compile

https://dev.mysql.com/doc/refman/8.0/en ... ution.html or for the dev versions
https://dev.mysql.com/doc/refman/8.0/en ... -tree.html

Return to “Advanced users”