MariaDB is driving me mad. Can't access as root to create users

Help and Support Raspberry Pi
1

Hello to all. I just recreated my two PIs sd cards to start from scratch with a clean install. I previously configured everything to use Mysql server on one of the PIs as the database.

Now I found that upon installation as per the wiki, the database is no longer mysql but rather mariaDB, which already drove me mad on the Synology nas as it refused to start (that’s where I wanted to save the database before deciding for one of the PIs as a “patch” solution because of the impossibility of starting up Maria DB on my old DS110j).
In fact, it seems me and MariaDB won’t be friends, ever…

The wiki gives instruction to access as root, it appears it should have a blank password but giving a blank password doesn’t allow to enter. I tried “root” as a password as well, but to no avail. tried countless methods to access as root in maintenance / safe mode and reset the password but I’m always getting a syntax error, no matter what the syntax I use is (SET PASSWORD, ALTER USER etc.)

I’m losing my hope of getting it to work. Does MariaDB have a specific password b y default? Does it create a random one on startup like I read somewhere? In the latter case, how do I read the random password created by MariaDB?

Thanks to everybody who will be willing to help.

2

Did you ever try sudo mysql?

1 Like
3 
sudo mysql -u root -p
11

Sorry but this led to no result. Couldn’t access. I managed to access using dillthedog’s suggestion.

12

Thanks a lot, dillthedog. This allowed me to access and set up the database. Don’t know why trying the usual way didn’t work.

13

It seems to be a Debian innovation, for want of a better term.

There’s a reference to it in the file /usr/share/doc/mariadb-server-10.0/README.Debian.gz under the ROOT USER AUTHENTICATION heading.

14

It’s the way Debian has MariaDB configured by default. I’ve also seen this and never really worried about it as I can just either use sudo or create a user with full access.

@dillthedog mentioned this in private:

I haven’t read through it, but I suspect that you can change the root access there.

15

OP, you mention Synology, MariaDB 10 running fine here, configured with phpMyadmin, everything worked first time and have a mix of clients. But my main unit is a 416play, though MariaDB 10 also runs on my 214+.

16

Unfortunately, the 110j is quite outdated. MariaDB fails to startup due to a “failed to start mariadb with customized my.cnf, restart with default configuration” error and no matter what I tried to solve the problem, I coulnd’t overcome it.

17

The Syno 110j has only 128MB of RAM.

18

In which way could it have something to do with the error above? Generally speaking, unfortunately I know that it is quite an outdated device and its becoming sluggish a bit more very day, but at the moment I can’t afford buying a newer model :confused:

19

Did you try as I suggested, use sudo to get in as root and then create a new user will full access rights to use in the future. Or just use sudo when you need to get in as root.

20

Yes, yes, using sudo mysql brilliantly solved the issue. :slightly_smiling_face: we switched over to the Synology MariaDB “subtopic” , that’s what I was I was referring to, when I mentioned “the error above” in post #18.
I initially tried to have the Syno as a mysql server but gave up with that a long ago due to the failing startup. Now I successfully have one RPi (the most used one) acting also as a server for the second (the least used and less powerful).

Thanks a lot to everybody